Your cart is empty.
Keep shopping
Teach on Udemy
Turn what you know into an opportunity and reach millions around the world.
Learn MoreLearn SQL Queries
Rating: 4.4 out of 54.4(980 ratings)
7,211 students
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
Danish [Auto],English [Auto],
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
Explore related topics
Course content
7 sections • 67 lectures • 4h 24m total length
- Course Description2:26
- * * SECTION DESCRIPTION * *2:03
- SELECT *3:29
Learn how to select from a table using select star to retrieve all fields in schema order, or name and reorder fields, and pick only the ones you need.
- 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
Learn how to use the max aggregate function to find the highest salary in an employee table, and explore field aliasing and optional square bracket syntax in SQL queries.
- SELECT MIN()1:13
- SELECT MAX() and MIN() Example1:44
- SELECT AVG()1:37
Explore the average function in sequel server by selecting salaries from the employee table, computing the average as an aggregate function, and naming the result with square brackets.
- 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
Explore the from clause and how to pull records from one or more tables, then learn how inner and left joins connect related data efficiently.
- 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
We compare left join and right join, showing how they produce the same results when switching table order, and how inner joins differ by returning only matching records.
- FROM - INNER JOIN with Temp Table4:04
Perform an inner join using a temp table with employees and departments, selecting first name, last name, and the full department name.
- FROM - INNER JOIN with Table Variable4:33
- UNION QUERY - TSQL Command Line8:09
Learn how to build union queries in T-SQL to combine employee data from different databases, align fields, optionally concatenate names, and display salaries from highest to lowest.
- 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
Explore the difference between using the in clause and a subquery to filter employees by department, highlighting dynamic lookups versus hard-coded values for robust sql querying.
- WHERE - AND Operator5:48
Explore using the where and operator with the datepart function to filter employees by hire year, and combine department and year conditions for readable sql queries.
- 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
Learn how to use the descending keyword to sort results, see examples sorting by state, and apply order by to view newest records or per-department salary rankings.
- ORDER BY CASE - Part 10:52
Learn how to order results with an order by case expression to prioritize New York records in a single query, contrasting simple two-query methods with a more efficient approach.
- 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
Learn how the group by clause uses aggregate functions like min, max, and count to summarize customers by last name and their largest order total.
- 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 populate dropdowns and keep sql logic centralized, avoiding code changes. Filter visible = 1 to hide inactive customers, updating the dropdown without recompiling client applications.
- 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
Discover user defined functions in SQL, name and call them with parameters, and see text manipulation examples like concatenation and leading zero padding used for sample IDs.
- 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.