70-461 Session 6: Querying Microsoft SQL Server (SQL code)

What you'll learn:

• Learn all about subqueries, using them in the SELECT, FROM and WHERE clauses.
• Learn about correlated subqueries, where the results of the subquery depends on the main query.
• Learn about Common Table Expressions (CTE), using the WITH statement.
• Use ROW_NUMBER, LAG and CTEs to group consecutive numbers.
• Learn about PIVOT and UNPIVOT to spread data across columns.
• Find out all about functions, including UDFs (user defined functions): scalar, inline table and multi-statement table functions
• "Join" the functions into tables using the CROSS APPLY and OUTER APPLY.
• Learn about synonyms, dynamic SQL and SQL injection.
• Find out about uniqueidentifiers (NEWID and NEWSEQUENTIALID functions), and create and use Sequences.

Requirements:

Description:

If you are in UfB, welcome.

If you are not, don't buy this course. Instead, please look at my 70-461 Session 1-7: Querying Microsoft SQL Server (SQL code) course instead, which includes this course and 6 other courses.

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.

Who this course is for:

• This course is for you if want a refresher course in SQL, or are learning SQL for the first time.
• This course is for you if you are working towards the requirements for exam 70-461, or if you just want to expand your knowledge of T-SQL.
• If you are starting from the beginning, then you should look at my sessions 1 to 5 courses before taking this session 6.
• If you want more advanced topics, then please look at my other sessions.
• Machine Learning A-Z™: Hands-On Python & R In Data Science
• React - The Complete Guide (incl Hooks, React Router, Redux)
• Microsoft Excel - Excel from Beginner to Advanced
• Microsoft Excel - Advanced Excel Formulas & Functions

Course Details:

• 5.5 hours on-demand video
• 2 articles
• 3 downloadable resources
• Full lifetime access
• Access on mobile and TV
• Certificate of completion

70-461 Session 6: Querying Microsoft SQL Server (SQL code) udemy courses free download

Using SQL, correlated subqueries, CTEs, Pivots, UDFs and Sequences. SQL Server 2012 to 2017 (inc. 70-761).