4.47 out of 5
4.47
7285 reviews on Udemy

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

From Tables and SELECT queries to advanced SQL. SQL Server 2012, 2014, 2016, 2017, 2019, exams 70-461 and 70-761
Instructor:
Phillip Burton
37,427 students enrolled
English More
create tables in a database and ALTER columns in the table.
Know what data type to use in various situations, and use functions to manipulate date, number and string data values.
retrieve data using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
JOIN two or more tables together, finding missing data.
INSERT new data, UPDATE and DELETE existing data, and export data INTO a new table.
Create constraints, views and triggers
Use UNION, CASE, MERGE, procedures and error checking
Apply ranking and analytic functions, grouping, geography and geometry database
Create subqueries and CTEs, PIVOTs, UDFs, APPLYs, synonyms.
Manipulate XMLs and JSONs.
Learn about transactions, optimise queries and row-based v set-based operations

Previously available as seven separate courses, now presented in one big course.

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 itHayford 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” and 70-761 “Querying Data with Transact-SQL”.

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. If you want to take the 70-461 exam, 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.

We’ll have a look at XML. Finally, for SQL Server 2016 and later (exam 70-761), we’ll examine JSON and Temporal Tables.

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.

Session 1 - Let's begin, and let's download SQL Server

1
Introduction

Hello! In this video, I'll talk in general terms who this course is for, and what you will be learning today.

2
Welcome to Udemy
3
The Udemy Interface
4
Exam update
5
Curriculum

What more specifically we will cover in this course, and how this fits into the 70-461 exam.

6
Do you have Windows 7, 8 or Windows Vista?
7
Downloading SQL Server Developer 2019

Here we will find the relevant SQL Server program, and what to download.

You will need download it to create your own statements and solidify your knowledge of T-SQL.

8
Installing SQL Server Developer 2019

Step by step how to install the back engine of SQL Server on your computer.

9
Installing SQL Server Management Studio (SSMS)

Step by step how to install SQL Server Management Studio (SSMS) onto your computer.

Session 1 - Starting SQL Server

1
Opening SQL Server
2
Looking at SQL Server Management Studio

Here we will see the main components of Management Studio, which is where we will do most of our work.

3
Create a database

We will create a database, which will be used to hold all of the components of our work.

4
Creating our first queries

Our first queries, using mathematical calculations - SELECT and GO.

5
Practice Activity Number 1 - Writing mathematical queries

Now it's your turn. 

This exercise is to practice writing a query, and creating simple mathematical expressions.

6
Coding Exercises
7
Writing mathematical queries
8
Databases and Queries

This quiz is a brief test on what you have just learned.

Session 1 - Creating tables - First pass

1
Creating a table - first pass using GUI

Here we will create a table using the graphical user interface

2
Creating a table - first pass using T-SQL

Here we will create a table using T-SQL using CREATE TABLE, GO

3
New Tables

Two quick questions

4
Entering data using the GUI

We will use the Graphical User Interface to add data.

5
Entering data using T-SQL

We will enter data in a table using T-SQL. INSERT

6
Entering data using T-SQL

Let's test what you've learned.

7
Retrieving data

Using the SELECT statement, and whether to use the semicolon.

8
Retrieving data

Let's test what you have learned.

9
The FROM clause - connecting to one table
10
Deleting the data, then the table

We will delete all the data using TRUNCATE, and then the table using DROP TABLE.

11
Deleting data using T-SQL

Let's test what you have learned.

12
Practice Activity Number 2

Session 1 - Number types and functions

1
Creating an Employee table

We will recreate a table using T-SQL with multiple fields (EmployeeNumber and EmployeeName), and find why we need to expand on the information we learnt in the last section before we can proceed much further.

2
Session 1 Resources
3
Creating temporary variables

Using the DECLARE function in an SQL batch to create a temporary variable, setting it to a value, and then querying it.

4
Integer numbers

We will look at the four major integer types: tinyint, smallint, int and bigint [8a and 8b]. We'll also look at the +, -, *, / and ^ signs, and the danger of integer division (e.g. 3/2).

5
Integer numbers

Can you remember which is which? I will give you a few values, and I want you to give me the smallest integer type which will hold ALL these values.

Yes, bigint will hold all of these integers, but I want the SMALLEST type.

6
Practice Activity Number 3
7
Practice Activity Number 3 - Solution
8
Non-integer numbers

Now it's turn for non-integer numbers: float and real, money and smallmoney, decimal and numeric.

9
Non-integer numbers

Let's test what you have just learned.

10
Mathematical functions

SQUARE, POWER and SQRT, FLOOR, CEILING and ROUND, PI and EXP, ABS and SIGN, RAND and trigonometric functions.

11
Mathematical functions

Quick questions.

12
Converting between number types

How to convert between integers and non-integer numbers - CAST and CONVERT.

13
Converting between number types

Quick question:

14
Practice Activity Number 4
15
Practice Activity Number 4 - Solution

Session 1 - String data types and functions

1
Strings

We will look at the four major string types: char, varchar, nchar and nvarchar.

2
Strings

A few questions about what you have learned.

3
String Functions - extraction

Looking at how to extract strings - LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, REPLACE, UPPER and LOWER.

4
TRIM
5
String Functions - extraction

A few questions about what you have learned.

6
NULL - an introduction

What is NULL? Is it good, bad, or just plain necessary? Also, we'll look at TRY_CAST and TRY_CONVERT.

7
Joining two strings together

Why a + sign doesn't always work, and how CONCAT is better. Also, converting from number data to strings.

8
NULL

Some questions about NULL.

9
Joining a string to a number

We've seen how to join two strings together. Now, we need to convert a number to a string to join them together.

10
Joining numbers to strings

Let's test your knowledge. You may have to think more carefully about the answers to the questions.

11
Practice Activity Number 5
12
Practice Activity Number 5 - The Solution

Session 1 - Date data types and functions

1
Date data types

The various non-offset date data types: datetime, datetime2, smalldatetime, time and date.

2
Using a computer language other than English?
3
Setting dates and Date extraction

Setting dates using quotation marks and DATEFROMPARTS, DATETIMEFROMPARTS and TIMEFROMPARTS, and YEAR, MONTH, DAY

4
Date data types

Let's test your knowledge about date data types.

5
Today's date, and more date functions

CURRENT_TIMESTAMP, GETDATE and SYSDATETIME, Plus DATEADD, DATENAME, DATEPART and DATEDIFF.

6
Today's date and more date functions

Let's see what you can remember.

7
Date offset

International dates: datetimeoffset, SYSDATETIMEOFFSET, SYSUTCDATETIME, TODATETIMEOFFSET, DATETIMEOFFSETFROMPARTS, SWITCHOFFSET.

8
Converting from date to strings

A quick question to end this first session...

9
Converting from date to strings

A quick question to end this first session...

Session 1 - Conclusion

1
BONUS - Review what you have learnt (PDF)
2
Well done!

Welcome to Session 2

1
Introduction

I'll talk about what we learnt in Session 1, and what we will learn in this Session 2.

2
Spreadsheet Data
3
Session 2 Resources

Session 2 - Creating and querying part of a table

1
Creation of tblEmployee table

We will recreate the Employee table we tried to create earlier, and will consider what the appropriate data types should be used.

2
Adding additional columns

We will create a new column in existing tables , and then alter it.

3
SELECTing only part of a table - strings

We will use the WHERE clause to retrieve only part of a table, and the LIKE clause to use pattern matching.

4
SELECTing only part of a table - strings

A few questions, if I may.

5
SELECTing only part of a table - numbers

Still using the WHERE clause, we will use =, <, >, <=, >=, !, NOT, BETWEEN and IN.

6
SELECTing only part of a table - numbers

Let's see what you remember about using the WHERE with numbers

Session 2 - Summarising and ordering data

1
Summarising and ordering data

We investigate the WHERE clause for dates, and use a date criteria to summarise data, using the GROUP BY clause, and then order it using the ORDER BY clause.

2
Summarising and ordering data

That's a lot to remember. Let's test yourself, and maybe learn a bit more!

3
Criteria on summarised data

The GROUP BY reduced the number of rows to look at. What if we want to run criteria on this summary? We look at the HAVING clause.

4
Criteria on summarised data

It's important to get the clauses in the right order - let's see if you can remember them.

5
Changing blank strings to NULLs
6
Exercise

We do an exercise, based on what we have learnt so far.W

Session 2 - Adding a second table

1
Adding a second table

In this lecture we will start designing a Transaction table. We'll consider what we need, and what data types should be used.

2
Designing a connection

Having designed part of tblTransaction, how can we in computing terms link it back to tblEmployee? We'll look at the possibilities.

3
New spreadsheet data
4
Importing data and showing tables graphically

We'll import random data that I've generated, using the MAX function to determine the limit of that data, and then graphically show these two tables together.

5
Writing a JOIN query

In this lecture we'll write a query which JOINs both of these tables together. We'll look at aliasing the tables as part of the JOIN, and referring to the tables in the SELECT clause.

6
Writing a JOIN query

Two questions about what you've just learned.

7
The FROM clause - connecting to two tables
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.5
4.5 out of 5
7285 Ratings

Detailed Rating

Stars 5
3905
Stars 4
2582
Stars 3
610
Stars 2
101
Stars 1
75
30-Day Money-Back Guarantee

Includes

30 hours on-demand video
29 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion
70-461, 761: Querying Microsoft SQL Server with Transact-SQL
Price:
$218.98 $169

Community

For Professionals

For Businesses

We support Sales, Marketing, Account Management and CX professionals. Learn new skills. Share your expertise. Connect with experts. Get inspired.

Community

Partnership Opportunities

Layer 1
samcx.com
Logo
Register New Account
Compare items
  • Total (0)
Compare
0