Learn SQL Queries

Learn about SQL queries quickly. Get up to speed with SELECT, FROM, WHERE, ORDER BY. BONUS: Stored Procedures!

Created byDave Merton

Last updated 12/2017

English

What you'll learn

Learn how to INSERT, DELETE, UPDATE and QUERY from tables
Learn about many of the useful built in T-SQL functions (SUM, CNT, AVG...)
Learn about temp tables and table variables
Learn about the power of STORED PROCEDURES

Course content

7 sections • 67 lectures • 4h 24m total length

Course Description2:26
* * SECTION DESCRIPTION * *2:03
SELECT *3:29
SELECT COUNT(*)3:05
Count rows with select count(*) from a table and compare total rows to filtered results with a where clause, naming the count column with an alias.
SELECT DISTINCT3:53
SELECT ISNULL()4:04
SELECT SUM()8:02
Discover how to use the sum function to total salaries, group results by department, and join employee and department data for meaningful payroll insights.
SELECT MAX()2:23
SELECT MIN()1:13
SELECT MAX() and MIN() Example1:44
SELECT AVG()1:37
SELECT MAX(), MIN() and AVG() Example2:39
Concatenation Example2:58
Learn how to concatenate first and last names in SQL, using plus signs and spaces, alias the result as name, and cast numbers to text when combining fields.
SELECT CASE Example3:48
Learn how to use the case statement in SQL to label six-figure salaries, select first name, last name, and salary, and populate a notes field with conditional notes.
SELECT CASE and Concatenation Example3:25
SECTION 1 - REVIEW QUIZ

* * SECTION DESCRIPTION * *2:32
FROM - NO JOIN13:52
FROM - INNER JOIN6:44
FROM - LEFT JOIN5:40
Learn how a left join combines all employees with their departments, returning matching department data where available and including all employee records.
FROM - LEFT vs INNER7:50
Explore the difference between left join and inner join in SQL by linking the employees and department tables with primary and foreign keys, showing matches and unmatched left rows.
FROM - LEFT vs RIGHT8:20
FROM - INNER JOIN with Temp Table4:04
FROM - INNER JOIN with Table Variable4:33
UNION QUERY - TSQL Command Line8:09
SECTION 2 - REVIEW QUIZ

* * SECTION DESCRIPTION * *2:11
WHERE X = Y5:31
WHERE X > Y6:37
WHERE X IN (a,b,c,d)5:59
Learn how to filter records using the IN operator in SQL, selecting departments 3 or 4 from the employee table and handling text with single quotes.
WHERE X NOT IN (a,b,c,d)2:54
WHERE X BETWEEN ...7:06
Learn to use the between keyword to define a range, such as salary between 98 and 110, for clearer and more concise SQL queries.
WHERE X IN (SELECT ...)4:01
WHERE X NOT IN (SELECT ...)8:09
WHERE - IN Clause vs Sub Query4:41
WHERE - AND Operator5:48
WHERE - OR Operator8:35
Examine how the or operator differs from and in SQL Server, using datepart to extract years, filtering employees by department or hire year, and understanding when or expands results.
WHERE - AND Operator, OR Operator and Parentheses11:28
SECTION 3 - REVIEW QUIZ

* * SECTION DESCRIPTION * *1:18
ORDER BY 1, 24:04
Sort customer data with order by using ordinals or field names to group by state and city, then order by name, and learn ordinals, default ascending, and common pitfalls.
ORDER BY 1, 2 (Possible Issues)3:24
ORDER BY <column name>, <column name>4:01
ORDER BY - ASCII VALUES1:47
This video will discuss the importance of being familiar with ASCII values and how htis ties in with the ORDER BY Clause.

You really need to watch this video along with the next video, about COLATION to get the entire message.
ORDER BY - COLATION3:51
This video will discuss the importance of being familiar with "COLATION" and how this ties in with the ORDER BY Clause.

You really need to watch this video along with the previous video, about ASCII values to get the entire message.
ORDER BY - ASC1:47
ORDER BY - DESC2:27
ORDER BY CASE - Part 10:52
ORDER BY CASE - Part 22:26
In this video we continue on with a strange ordering request: "Put NY first, then all other states in order." This video shows a specific approach that works but is more complicated than what is necessary.
ORDER BY CASE - Part 33:17
ORDER BY (SELECT ...)3:46
SECTION 4 - REVIEW QUIZ

* * SECTION DESCRIPTION * *1:23
GROUP BY - MIN() & MAX() - Part 11:56
GROUP BY - MIN() & MAX() - Part 21:24
GROUP BY - MIN() & MAX() - Part 32:48
GROUP BY - COUNT(*)0:48
GROUP BY - SUM()0:57
Apply the sum function to a query, add a sum field, show total dollar amount and record counts, and order customer names by the sum field in descending order.
GROUP BY - AVG()2:24
GROUP BY - HAVING CLAUSE - Part 10:52
Explore how to use group by with the having clause to filter aggregated results, such as showing customers who spent over $2000 or exceeded sales counts.
GROUP BY - HAVING CLAUSE - Part 22:08
GROUP BY - HAVING CLAUSE - Part 2
GROUP BY - HAVING CLAUSE - Part 32:26
Master group by and having to sum the total field and filter customers with total greater than or equal to 2000, and compare having with group by and order by.
SECTION 5 - REVIEW QUIZ

* * SECTION DESCRIPTION * *2:18
STORED PROCEDURES - EXAMPLE 1 - Part 12:57
xxx
STORED PROCEDURES - EXAMPLE 1 - Part 23:16
STORED PROCEDURES - EXAMPLE 1 - Part 33:16
STORED PROCEDURE - EXAMPLE 2 - How to Write to a Text File4:39
VIDEO - The video will show you how to write to a text file from SQL Server.

The following notes are references to the additional support files that are connected with this section. These files are downloadable so that you can copy and paste the code right into SSMS / T-SQL.

01 - CREATE PROCEDURE Write To File - This is the TSQL code to create your own [WriteToFile] STORED PROCEDURE.*

02 - Write To Text File - Command Line SQL - This is an example of how to EXECUTE the [WriteToFile] STORED PROCEDURE.*

03 - Write To Text File Command Line SQL - With Parameters - This is an example of how to EXECUTE the [WriteToFile] STORED PROCEDURE - with Parameters/Variables/Arguments.*

04 - Write To Text File Command Line SQL - With Parameters And Date In Filename - This is an example of how to execute the [WriteToFile] STORED PROCEDURE - with Parameters/Variables/Arguments and INSERTING a FORMATTED DATE-TIME STAMP into the file name.*

Ole Automation SQL - This is the code that you will need to run to initially turn on the OLE AUTOMATION Permissions in SQL SERVER.*

* This T-SQL code can be copied into the SSMS command line environment.

** SECTION DESCRIPTION **1:45
USER DEFINED FUNCTIONS - EXAMPLE 1 - FormatCustName()4:02
USER DEFINED FUNCTIONS - EXAMPLE 2 - Part 1 - PrePad() Function2:12
VIDEO- The video will show you how to make use of a User Defined Function. In this particular example the function will return a value that has been formatted with n instances of a specific text character.

The following notes are references to the additional support files that are connected with this section. These files are downloadable so that you can copy and paste the code right into SSMS / T-SQL.

01 - CREATE FUNCTION – dbo.PrePad() Example- This is the TSQL code to create your own dbo.[PrePad]USER DEFINED FUNCTION.*

02 – USER DEFINED FUNCTION – dbo.PrePad() Example- This is an example of how toCALL / USE the dbo.[PrePad]USER DEFINED FUNCTION.*

* This T-SQL code can be copied into the SSMS command line environment.
USER DEFINED FUNCTIONS - EXAMPLE 2 - Part 2 - PrePad() Function8:03
Learn to use a user defined function in a real-world sql example, including scalar valued functions, owner qualification, and a prepared function that pads order numbers to six digits.

Requirements

The student should have a basic understanding of databases.

Description

This is an in depth course about using and programming with SQL Server. It assumes that the student has at least a rudimentary understanding of database concepts and architecture and gets right into the meat of the subject.

This course will get you up to speed on executing queries. The final part of the course briefly touches on stored procedures and user defined functions.

Who this course is for:

Any technical person who wants to have an increased understanding of the real world uses of production database development.

Learn SQL Queries

What you'll learn

Explore related topics

Course content

Queries - Part 1 - SELECT Records15 lectures • 47min

Queries - Part 2 - FROM Clause9 lectures • 1hr 2min

Queries - Part 3 - WHERE Clause12 lectures • 1hr 13min

Queries - Part 4 - ORDER BY Clause12 lectures • 33min

Queries - Part 5- GROUP BY Clause10 lectures • 17min

A Quick Look at the value of STORED PROCEDURES5 lectures • 16min

A Quick Look at the value of USER DEFINED FUNCTIONS4 lectures • 16min

Requirements

Description

Who this course is for: