This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012".
In Sessions 1 and 2, we learned all about dates, strings and number data types and DML statements. In sessions 3 and 4, we created views, procedures, triggers, constraints and combining datasets, and in session 5 we learned about ranking and aggregate functions.
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.
No prior knowledge other than what we covered in Sessions 1 to 5 is required. This course builds on the knowledge previously gained in those previous sessions.
There are regular quizzes to help you remember the information.
Once finished, you will know what how to write subqueries, functions, sequences and more, and we'll have expanded on our current knowledge of T-SQL.
A reminder of the tables that we created all the way back in Session 2.
We'll have a look at using subqueries in the WHERE clause.
We'll have a look at using subqueries in the WHERE clause, and how much care you have to use with using the word NOT.
We'll expand our use of the WHERE clause by looking at ANY, SOME and ALL.
We'll look at using subqueries in the SELECT clause, and see that we have to use correlated subqueries in the SELECT clause.
We'll revisit the WHERE clause, and use correlated subqueries in the WHERE clause.
We'll look at objective 6a, and use a subquery to obtain the top 5 from various categories.
We'll introduce the WITH statement, and see how it can clarify our code.
We'll find out what Employee Numbers have not been used, and find out why we have to generate a list of numbers using WITH and ROW_NUMBER to do so.
We'll summarise the above table by grouping together consecutive numbers.
We'll look at creating a PivotTable in Excel, and doing the same thing in SQL Server.
Unfortunately, the PIVOT function creates a lot of NULLs. We'll see how to change them to zero,
We'll do the reverse of PIVOT, and unspread columns back into rows.
We'll have a look at objective 14, and see what we already know (and briefly revisit it).
We'll create our first, basic, function.
We'll write a second function, a bit more complicated.
We'll write a function which uses one SELECT statement to return a table.
We'll write a more complex function, creating a table and populating it with the results.
We'll "join" the results of a function with a table.
We'll revisit the IDENTITY default, and see what the disadvantages are.
We'll look at the uniqueidentifiers NEWID and NEWSEQUENTIALID, and how to use them.
We'll create a sequence, and look at all the options.
Having created a SEQUENCE, how can we get the next value?
We'll review what we have learned in Session 6, and look forward to what we will do in Session 7.
Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.
He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.
He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.
He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.
His interests are working with data, including Microsoft Excel, Access and SQL Server.