Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Writing Queries for SQL Server
Module 0. Getting started
Who is this course for? (0:57)
Who is Greg? (1:07)
What will I learn in this course? (2:08)
Configuring your lab environment (12:16)
Introduction to querying SQL Server
What is SQL Server? (1:07)
What is SQL? (1:58)
What is T-SQL? (1:28)
Types of T-SQL statements (1:12)
What are Server Instances? (1:54)
Connecting to SQL Server Logins (1:35)
What are databases? (2:14)
Connecting to SQL Server users (2:54)
Using SQL Server Management Studio (SSMS) (3:12)
Lab 1: Introduction to querying SQL Server
Quiz 1: Introduction to querying SQL Server
Finding help and examples for T-SQL syntax (3:27)
Module 2: Querying a single table
What are tables? (2:07)
Finding out what's in a table (0:44)
What are schemas? (2:25)
Introduction to PopkornKraze (3:34)
SELECT statements (1:57)
Using statement terminators (1:04)
Quoting (delimiting) names (2:31)
Using 2 part names (2:04)
Using aliases for columns and tables (2:46)
ORDER BY clause (1:24)
Restricting output with OFFSET and FETCH (1:04)
Removing duplicates with DISTINCT (1:03)
Querying literals, expressions and functions (1:15)
Lab 2: Querying a single table
Lab 2 Answers
Quiz 2: Querying a single table
Module 3: Filtering the rows to return
Filtering output with the WHERE clause (1:38)
Finding patterns with the LIKE operator (1:56)
Logical operations with AND, OR, and NOT (1:44)
Ranges of values with the BETWEEN operator (1:57)
Checking lists of values by using the IN operator (0:47)
Limiting output rows by using the TOP operator (2:04)
Using TOP WITH TIES (1:28)
Working with NULL values (the lack of data) (1:36)
What are user-defined functions? (1:47)
Lab 3: Filtering the rows to return
Lab 3 Answers
Quiz 3: Filtering the rows to return
Module 4: Working with SQL Server queries
Changing databases with the USE statement (2:26)
Understanding batches, scripts, and GO (1:09)
Repeating batches with GO n (2:47)
Adding comments to your scripts (1:37)
Formatting your scripts for readability (2:21)
Quiz 4: Working with SQL Server queries
Module 5: Querying numbers
What are data types? (1:49)
Exact whole numbers (1:33)
Exact decimal numbers (1:07)
Approximate numbers (4:13)
Numeric operators (2:45)
Lab 5: Querying numbers
Lab 5 Answers
Quiz 5: Querying numbers
Module 6: Querying strings
String data types (1:52)
Literal character values (3:45)
Comparing strings with collations (2:20)
Character operators (1:30)
Trimming strings with LTRIM, RTRIM, and TRIM (1:19)
Changing case with UPPER, LOWER (0:56)
Extracting parts of strings with LEFT, RIGHT, SUBSTRING (0:56)
Determining length with LEN, DATALENGTH (1:03)
Replace substrings using REPLACE and STUFF (1:38)
Duplicating strings with REPLICATE (0:39)
Finding substrings with CHARINDEX and PATINDEX (2:00)
Splitting strings with STRING_SPLIT (1:43)
Replacing characters with TRANSLATE (1:21)
Lab 6: Querying strings
Lab 6 Answers
Quiz 6: Querying strings
Module 7: Querying dates and times
Date and time data types (2:34)
Literal date and time values (1:58)
Current date and time with SYSDATETIME, GETDATE, SYSUTCDATETIME (2:22)
Extracting date components with YEAR, MONTH, DAY (0:29)
Adding and subtracting periods using DATEADD (1:37)
Subtracting dates using DATEDIFF (1:04)
Extracting other date components with DATEPART (1:04)
Get string names of date parts with DATENAME (0:53)
Calculating end of month with EOMONTH (1:12)
Constructing dates from components with DATEFROMPARTS (1:10)
Constructing datetimeoffset values with TODATETIMEOFFSET (0:44)
Changing datetimeoffset values to different offsets with SWITCHOFFSET (1:23)
Custom formats of dates and times with FORMAT (2:43)
Lab 7: Querying dates and times
Lab 7 Answers
Quiz 7: Querying dates and times
Module 8: Converting between data types
Replacing NULL values with ISNULL, COALESCE (1:30)
Converting data types with CAST (1:52)
Converting data types with CONVERT (1:10)
Converting data types with PARSE (1:32)
Checking if a string is a number or date with ISNUMERIC, ISDATE (1:04)
Testing conversions with TRY_CAST, TRY_CONVERT, TRY_PARSE (1:14)
Other data types (3:44)
Lab 8: Converting between data types
Lab 8 Answers
Quiz 8: Converting between data types
Module 9: Aggregating data
Counting rows and columns with COUNT (1:22)
Summarizing data with SUM, AVG, MIN, MAX (1:19)
Summarizing data in sections with GROUP BY (2:28)
Filtering returned groups with HAVING (0:52)
Understanding logical query execution order
Numbering rows with ROW_NUMBER, RANK, DENSE_RANK, NTILE, and OVER (3:16)
Partitioning ranking and numbering with PARTITION BY (1:02)
Lab 9: Aggregating data
Lab 9 Answers
Quiz 9: Aggregating data
Module 10: Implementing logic in scripts
Outputting messages with PRINT (0:49)
Using conditional expressions with CASE, IIF (1:58)
Defining variables and assigning values to them using DECLARE, SET (2:27)
Querying system variables and functions (0:59)
Adding conditional logic with IF, ELSE (1:13)
Creating composite statements with BEGIN, END (0:44)
Creating loops with WHILE (0:57)
Lab 10: Implementing logic in scripts
Lab 10 Answers
Quiz 10: Implementing logic in scripts
Module 11: Querying multiple tables
Using CROSS JOIN (1:50)
What is a primary key? (0:53)
What is a foreign key? (0:51)
Using INNER JOIN (1:31)
Using modern join syntax (1:30)
Using LEFT OUTER JOIN (1:25)
Other outer joins with RIGHT OUTER JOIN, FULL OUTER JOIN (1:49)
Joining more than two tables (1:12)
Joining a table to itself (self joins) (2:17)
Joins without equality (non-equi joins) (1:21)
Lab 11: Querying multiple tables
Lab 11 Answers
Quiz 11: Querying multiple tables
Module 12: Applying set operations to tables
Using UNION and UNION ALL (1:31)
Excluding data with EXCEPT
Finding common data with INTERSECT (1:10)
Selecting from table-valued functions (1:01)
Repetitively selecting with CROSS APPLY, OUTER APPLY (1:17)
Lab 12: Applying set operations to tables
Lab 12 Answers
Quiz 12: Applying set operations to tables
Module 13: Using subqueries
Using subqueries (1:07)
Creating scalar subqueries (1:52)
Creating lists from subqueries (1:18)
Checking for existence with EXISTS subqueries (2:34)
Creating table expressions from subqueries (1:28)
Using VALUES row constructors (1:30)
Simplifying queries with common table expressions (CTEs) (1:49)
Using multiple CTEs in a single query (1:28)
Lab 13: Using subqueries
Lab 13 Answers
Quiz 13: Using subqueries
Module 14: Working with stored procedures and catalog views
What is a stored procedure? (0:57)
Executing stored procedures (1:04)
Executing dynamic SQL statements (2:38)
Querying the system catalog (0:58)
Lab 14: Working with stored procedures and catalog views
Lab 14 Answers
Quiz 14: Working with stored procedures and catalog views
Module 15: Modifying data
Inserting data into a table (1:29)
Inserting multiple rows at once with VALUES (1:07)
What are default constraints? (1:09)
What are identity columns? (0:53)
Identifying the last value inserted (2:02)
What is a check constraint? (1:05)
What is a unique constraint? (1:17)
What is a foreign key constraint? (1:06)
Deleting rows from a table (0:58)
Inserting rows with data from another table (1:12)
Inserting rows returned by a stored procedure (1:21)
Selecting rows into a new table (0:47)
Truncating a table vs deleting all rows (1:06)
Updating data in a table (0:54)
Merging data into a table (1:35)
Lab 15: Modifying data
Lab 15 Answers
Quiz 15: Modifying data
Module 16: Next steps
Summary and further steps (1:09)
Outputting messages with PRINT
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock