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

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

4.5
( 13 Reviews )

Created by: Phillip Burton

Produced in 2018

icon
What you will 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.

icon
Quality Score

Content Quality
/
Video Quality
/
Qualified Instructor
/
Course Pace
/
Course Depth & Coverage
/

Overall Score : 90 / 100

icon
Course 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.
Read More product length iconTime: 5.5h

icon
Instructor Details

placeholder

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 him to maintain up to date and pro-active systems to help control and monitor day-to-day activities. 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.

Read More

icon
Students also recommend

SQL in Motion

$59.99

Introduction to SQL

Free

Intro to SQL: Querying and managing data

Free

More Courses

icon
More courses by Phillip Burton

70-461, 761: Querying Microsoft SQL Server with Transact-SQL

$11.99

SQL Server Integration Services (SSIS) - An Introduction

$11.99

70-462: SQL Server Database Administration (DBA)

$11.99

Microsoft SQL Server Reporting Services (SSRS)

$11.99

70-778, DA-100: Analyzing and Visualizing Data with Power BI

$11.99

SQL Server SSAS (Multidimensional MDX) - an Introduction

$11.99

All courses by Phillip Burton

icon
Reviews

4.5

13 total reviews

5 star 4 star 3 star 2 star 1 star
% Complete
% Complete
% Complete
% Complete
% Complete
Reset Filter
Write a Review arrow

By Jeremy Wdowicki

Thorough info

By Hermes Crespo

Excellent session

By Francois Klemensberger

The explanations that Phillip gives are quite easy to understand and process.

By Chantal Marshall

Great examples, clear instruction.

By Mark Smith

easy to follow along and apply what was learned from the 70-461 Training Kit.

By Charles Schweiger

Best of its' kind. Please keep in mind, your audience may not be as experienced and this is their first exposure to many of these concepts. There have been times where the examples, while great for an advanced course, are overly complicated and very hard to follow.

By Mary Holcombe

Pivot and Unpivot were great new additions to my SQL learning Still wish we had the quizzes to answer questions, but the information provided is greatly demonstrated.

By Mohammed Salih

I felt empowered with bite size learning, but missed the exercises as there were none to do or questions to answer

By Karishma Agarwal

The last chapter seems that they have been rushed.

By Reza K Ghazi

Session 6 has been covered lots of contents in T-SQL that are really needed.
There are some points, that may be useful for making it better.
1) It should be more concentrated on different Functions with more examples.
2) Dynamic SQL's and how we can create them in stored procedures had not discussed.
3) Synonyms are not well defined and introduced. For example, what are their abilities for security, making generic stored procedures and so on, has never discussed.
4) Ther were no Quizzes and assignments.
‌But, None of these points has not diminished the importance and beauty of this session.
I am very glad that I've got this series of SQL courses, and I would recommend to anybody who wants to really learn T-SQL from scratch, to take this series and enjoy the learning.
I thank ful for the wise and knowledgeable instructor, Philip, who taught lots to me.
And the last thing is I am looking for session 7.
Best regards,

By Andre Stone

Very detailed series of courses. Overall the best Sql course that I have seen on the web.

By Mervyn Sheppard

The course progressively builds up and revises knowledge clearly explaining the principals behind and uses of the code.

Load More

70-461 Session 6: Querying Microsoft SQL Server (SQL code)
Visit Course Write a Review
  • Video Duration: icon 5.5h
  • Price: 11.99 Track Price 11.99 Track Price
  • Provider: icon Udemy
  • Year: icon 2018
  • Language: icon English, English [Auto-generated]
  • Certificate: icon Yes
30-Day Money-Back Guarantee

Udemy Coupon Price Tracker

For any issues or feature requests please email CourseDuck's Co-Founder at michaelk@courseduck.com