SQL Server 101
Welcome to the SQL Server 101 podcast. This podcast is an audio version of the video courses that I created for Microsoft SQL Server. They aim to get you from beginner level to that needed for the 70-461 certification, and beyond. Learn the skills that could you need to get your next dream job.
The 70-461 certification, called “Querying Microsoft SQL Server”, is intended for SQL Server database administrators, system engineers and developers with two or more years of experience, who are seeking to validate their skills and knowledge in writing queries.
In my courses, I teach the content which is examined in this certification, and in other aspects of SQL Server. In other words, I show you what Microsoft wants you to learn. I should say, however, that my courses and this audio podcast is completely unofficial, and has no connection to the Microsoft corporation, or any of its subsidiaries.
Video courses
My video courses contain several hours of training each. The cost is only US$10 if you use the coupon code LOVESQL, and there is a 30-day money-back guarantee.
Buy now from Udemy for only $10
Reviews
“The instructor explain the things in great details. Very easy to follow.” – Linda Shen
“Excellent course, valuable lessons, very well taught at a great pace.” – Shane Tanberg
“Must get tutorial. Love it“ – Hayford I Osumanu
“Perfect step by step guide to learning. Best I’ve seen.” – Charles Schweiger
“This course is very well thought out. Its one of the better 70-461 courses on Udemy.” – Isrrael M
————————-
This course is the foundation for the Microsoft Certificate 70-461: “Querying Microsoft SQL Server 2012”.
Session 1
The basics presented are: how to install SQL Server, and how to create and drop tables.
We then try to create a more advanced table, but find that we need to know more about data types – so we go into some detail about data types and data functions, the foundation of T-SQL.
Session 2
We’ll create tables which use these, and then INSERT some data into them. Then we’ll write queries which will retrieve and summary this data, using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
We’ll then JOIN these tables together to find where we are missing data and where we have inconsistent data. We’ll then UPDATE and DELETE data from the tables. This will allow up to fully complete objective number 1 from the 70-461 exam.
Session 3
We’ll now use that data to create views, which enable us to store these SELECT queries for future use, and triggers, which allow for code to be automatically run when INSERTing, DELETEing or UPDATEing data.
We’ll look at the database that we developed in session 2, and see what is wrong with it. We’ll add some constraints, such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints, to stop erroneous data from being added some data. By doing this, we will complete objectives 2, 3, 4 and 5 from the 70-461 exam
Session 4
We will further encapsulate our routines by creating procedures, allowing us to EXECUTE parameterised commands with just one statement, and we’ll add some error handling with TRY, CATCH and THROW.
We’ll also combine datasets together, by looking at UNION and UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL and Coalesce, and the mighty MERGE statement. By doing this, we will complete objectives 11, 12, 13 and parts of 6 and 18 from the 70-461 exam.
Session 5
We’ll will now be creating aggregate queries, working through objective 9 of the exam 70-461. We’ll be reviewing the ranking functions ROW_NUMBER, RANK, DENSE_RANK and NTILE. We’ll look at the 8 analytic functions news to SQL Server 2012, such as LAG, LEAD, FIRST_VALUE and LAST_VALUE.
We’ll look at alternative ways of grouping and adding totals, using ROLLUP, CUBE, GROUPING SETS and GROUPING_ID. We’ll also look at the geometry and geography data types, plotting locations on a grid, together with functions and aggregates.
Session 6
We’ll will now be creating sub-queries, working through objectives 7b-e of the exam 70-461. We’ll be created correlated subqueries, where the results of the subquery depend on the main query. We’ll be looking at Common Table Expressions using the WITH statement, and we’ll be using what we have learned to solve a common business problem.
We’ll be looking at functions (objective 14), including the three different types of User Defined Functions (UDF): scalar functions, inline table functions, and multi-statement table functions. We’ll then complete objective 6 by looking at synonyms and dynamic SQL, and objective 8 by looking at the use of GUIDs. We’ll also look at sequences.
Finally, we’ll have a look at XML.
Session 7
In this session we’ll be looking at transactions, seeing how to explicitly start and end them, and finding out how they can block other users in the database. Then we’ll see about how to indexes and their role in optimising queries.
We’ll also see how we can use Dynamic Management Views to see how we can improve our use of indexes. We’ll then look at how to write a cursor, and when to use this row-based operation, and the impact of using scalar UDFs.
No prior knowledge is required – I’ll even show you how to install SQL Server on your computer for free!
There are regular quizzes to help you remember the information.
Once finished, you will know what how to manipulate numbers, strings and dates, and create database and tables, create tables, insert data and create analyses, and have an appreciation of how they can all be used in T-SQL.
Buy now from Udemy for only $10
Reviews:
“Brilliant! This tutorial will definitely save you hours of figuring out things yourself.” — Koos ten Bras
“Excellent course, well presented and clear examples.” – Peter Cawthorne
This course is all about creating reports in SQL Server Reporting Services (SSRS). No prior knowledge is needed, but some knowledge of T-SQL would be useful.
We will download a version of SQL Server which includes SSRS – for free. We’ll also download a database called AdventureWorks, which we will use in our reports.
We’ll create various reports, developing our skills bit by bit. As part of designing a report, we will:
- Select report components (matrix, table, chart and other data visualisation component),
- Identify the data source and parameters,
- Design a grouping structure,
- Create drilldown reports and drillthrough reports.
As part of implementing a report layout, we will:
- Find out how to format, including formatting colours, dates and numbers;
- Configure the page;
- Implement headers and footers;
- Implement matrices, tables, chart, images, indicators, maps and groupings in reports;
- Create a range of reports using different data regions;
- Define custom fields;
- Implement global collections;
- Define expressions; and
- Implement data visualisation components.
As part of implementing interactivity in a report, we will:
- Create drilldown reports;
- Create drillthrough reports;
- Add interactive sorting;
- Investigate parameters, including multi-value parameters;
- Create dynamic reports in SSRS using parameters;
- Implement show/hide property;
- Add actions (jump to report and URL);
- Add Fixed headers and a document map.
By the end of this course, you should be confident in creating your own reports in SSRS.
It will assist with the “Build a report with SQL Server Reporting Services (SSRS)” section of Microsoft exam 70-766 “Implementing Data Models and Reports with Microsoft SQL Server”.
- This course is for you if you want to know how to create reports in SSRS.
- No prior experience with T-SQL is required, although the more knowledge you have it, the better.
- Having SharePoint on your computer is not necessary; we will use the stand-alone version of SQL Server. However, I will show the differences and similarities between Report Builder (SharePoint) and SSDT (the stand-alone version).
- In this course we will cover several aspects which are tested in Microsoft’s 70-466 exam “Implementing Data Models and Reports with Microsoft SQL Server”.
- This course is not for you if you only want to know about managing your existing SSRS reports in SharePoint.
Buy now from Udemy for only $10
Reviews:
“Instructor is easy to follow. He gives very clear examples. Course moves quickly enough to be engaging without skipping over material.” — Sarah Hale
“This course works for me because it progresses with a level of detail that will be useful as I think through each step necessary to execute my own integration packages. There are several courses to choose from and I’m glad I chose this one.” — David McNickle
“Very good introductory course. Didn’t really know what SSIS was until recently. Working through the course opened my eyes to past projects that would have benefited from an SSIS ETL package or two.” — Stephen Benton
Welcome to this course about SQL Server Integration Services.
In this course you’ll be downloading and installing the latest 2016 SQL Server Developer – a full version for free, so that you can use it on your machine. And previous versions of SQL Server Developer have previously retailed for around $100, and now it’s free.
So what is SSIS? It’s a way to automate in the importing and exporting and data – or ETL: extract, transform and load. It’s a very visual part of SSIS, using tasks, components and flowcharts, so it’s easy to learn the basics of it.
We’ll start by extracting some data, and then loading it into another table. I’ll take nice as slow as we start to introduce transformations, such as aggregation, conditional split, merging and auditing. There’ll be plenty of practice activities too, so you can get to practice your new-found skills. And if you are doing Microsoft certificates, it can help towards your 70-463 certification as well.
By the end of this course, you’ll be confident in using the main Data Flow components and Control Flow tasks, and would be able to use it in your own work.
- You should take this course if you are not experienced in SSIS, or if you want to know what it is for.
- SSIS is good for automating certain processes, and is very visible.
- It is great for beginners, and good if you have had a little prior experience.
- As it is going through the basics, it is not useful if you commonly use SSIS.
- It can help towards your 70-463 certification.
Reviews:
“Good Stuff Overall!!! In my opinion, the instructor did great with the “How-tos” which helped for sure in grasping the whole concept of how to create cubes, set up data source and source views, dimensions, add attributes etc.” — Lakeside David-Debo
“A fantastic course which gets you rolling very quickly and comfortably, thanks for the short and condensed knowledge delivery. Thanks, Phillip you made SSAS very simple for me.” – Anup Kale
“This is really the perfect course for beginners! Easy to learn and very inspirational for further investigations in SSAS. Thank you very much, Phillip!” – Marina Barinova
Welcome to this course on SQL Server SSAS and MDX Cubes – an Introduction.
You may have become experienced with creating SQL statements in SQL Server Management Studio. Building databases is ideal when you want to quickly add data – that’s why they are called OLTP – Online Transaction Processing – they are designed for speed for adding transactions.
But what if you want to get to get information about? OLTP databases are not based designed for this. What you need instead is a process whereby data is pre-aggregated – in other words, a lot of the calculations you may write have been calculated before you ask for them. It saves a lot of time. It would also be useful if the end user didn’t have to bother with SQL queries, and could use something a bit more hands-on, although retaining something more advanced for advanced users. That’s where cubes come in, full of pre-aggregated data, and SQL Server Analytical Services– or SSAS – (Online Analytical Processing) allows you to make these cubes.
This course is designed for the complete beginner in Multidimensional cubes, or someone who wants to refresh their memory. We’ll create a cube to start with from an ordinary database, and then I’ll ask you to create one from a special database known as a Data Warehouse. We’ll export our cube in SQL Server Management Studio, and into SSRS – and we’ll even have a bit of a look at the more advanced way of querying that is MDX.
It will assist with the “Design a multidimensional business intelligence (BI) semantic model” section of Microsoft exam 70-768 “Developing SQL Data Models”.
- This is for you if you want to learn about SQL Server Analysis Services.
- No previous experience using SSAS is necessary.
- It would be useful if you have previously used SQL Server (T-SQL), but far from essential.
Reviews:
“In just the first few lessons, I have already been able to apply what I have learned at my job. Thank you for this course!” — Cynthia Smith
“The Instructor is easy to understand and he gives exercises to test your understanding which is what I prefer rather than just listening to lectures. I will definitely take more of Mr Burton’s classes as I like his teaching style.” — Tamuka Mazana
——————
This course covers one of the most useful add-ins in Microsoft Excel, PowerPivot, and the Tabular model in SQL Server Analytical Services.
We’ll activate PowerPivot if you have the right version of Excel, and if not, then we’ll install SQL Server on your computer, which can do the same job. We’ll install the Developer edition, which used to cost $100, for free.
After PowerPivot is activated, or SQL Server is installed, we’ll create our first analysis in Excel in just a matter of minutes.
After only these first few minutes, you will be streets ahead of anyone who doesn’t know anything about Power Pivot.
After this introduction, we’ll go into some detail into PowerPivot or SSAS – the initial data, and the various options that are available to you. We will go into advanced options that most people don’t even know about, but which are very useful.
Then we’ll develop DAX formulas, to further extend the model. A lot of the formulas are based on Excel formulas, but there are several additional things too.
By the end, you will be an Expert user of both PowerPivot and SSAS, able to create reliable analyses which are able to be drilled-down quickly, and you’ll be able to help others with their data analysis.
It will assist with the Model Data section of Microsoft exam 70-779 “Analysing and Visualizing Data with Microsoft Excel”.
It will also assist with the “Design a tabular BI semantic model” and “Develop queries using Data Analysis Expressions (DAX)” section of Microsoft exam 70-768 “Developing SQL Data Models”.
- This course is for people who want to create analyses.
- You either want to do this in Excel, or you want to learn about SQL Server Analytical Services (Tabular edition).
- This course isn’t for you if you want to learn SSAS (MDX version) – there are other courses for this.
Do you want to learn how to write more advanced queries in Access. And do you want your queries to be portable into SQL Server and Access?
In this course learn how to:
- Write SQL code, using the six SELECT clauses.
- Use functions and formulas in your SELECT statements.
- Create joins between tables, including more complex joins which cannot be evaluated using the graphical interface.
- Write action queries and cross-queries.
- This course is for you if you want to develop your Microsoft Access skills by learning how to code SQL queries.
- This course is for you if you want to create more advanced queries in Access.
- This course will use the latest version of Access, but is ideal for you if you use any modern version – Access 2007, 2010, 2013, 2016 or 365.
- This course is also for you if you use an older version of Microsoft Access, but want to learn the newer versions.
