Zenler Player
Your course is loading. Hang tight.
Writing T-SQL Queries for SQL Server
Back to curriculum
0% Complete
0% Complete
Who is this course for?
Who is Greg?
What will I learn in this course?
Overview of lab setup
What is SQL Server?
What is SQL?
What is T-SQL?
Types of T-SQL statements
What are Server Instances?
Connecting to SQL Server Logins
What are databases?
Connecting to SQL Server users
Using Client Tools to Connect to the Database
Finding help and examples for T-SQL syntax
Lab 1: Introduction to querying SQL Server
Quiz 1: Introduction to querying SQL Server
What are tables?
Finding out what's in a table
What are schemas?
Introduction to PopkornKraze
SELECT statements
Using statement terminators
Quoting (delimiting) names
Using 2 part names
Using aliases for columns and tables
ORDER BY clause
Restricting output with OFFSET and FETCH
Removing duplicates with DISTINCT
Querying literals, expressions and functions
Lab 2: Querying a single table
Lab 2: Answers
Quiz 2: Querying a single table
Filtering output with the WHERE clause
Finding patterns with the LIKE operator
Logical operations with AND, OR, and NOT
Ranges of values with the BETWEEN operator
Checking lists of values by using the IN operator
Limiting output rows by using the TOP operator
Using TOP WITH TIES
Working with NULL values (the lack of data)
What are user-defined functions?
Lab 3: Filtering the rows to return
Lab 3: Answers
Quiz 3: Filtering the rows to return
Changing databases with the USE statement
Understanding batches, scripts, and GO
Repeating batches with GO n
Adding comments to your scripts
Formatting your scripts for readability
Quiz 4: Working with SQL Server queries
What are data types?
Exact whole numbers
Exact decimal numbers
Approximate numbers
Numeric operators
Lab 5: Querying numbers
Lab 5: Answers
Quiz 5: Querying numbers
String data types
Literal character values
Comparing strings with collations
Character operators
Trimming strings with LTRIM, RTRIM, and TRIM
Changing case with UPPER, LOWER
Extracting parts of strings with LEFT, RIGHT, SUBSTRING
Determining length with LEN, DATALENGTH
Replace substrings using REPLACE and STUFF
Duplicating strings with REPLICATE
Finding substrings with CHARINDEX and PATINDEX
Splitting strings with STRING_SPLIT
Replacing characters with TRANSLATE
Lab 6: Querying strings
Lab 6: Answers
Quiz 6: Querying strings
Date and time data types
Literal date and time values
Current date and time with SYSDATETIME, GETDATE, SYSUTCDATETIME
Extracting date components with YEAR, MONTH, DAY
Adding and subtracting periods using DATEADD
Subtracting dates using DATEDIFF
Extracting other date components with DATEPART
Get string names of date parts with DATENAME
Calculating end of month with EOMONTH
Constructing dates from components with DATEFROMPARTS
Constructing datetimeoffset values with TODATETIMEOFFSET
Changing datetimeoffset values to different offsets with SWITCHOFFSET
Custom formats of dates and times with FORMAT
Lab 7: Querying dates and times
Lab 7: Answers
Quiz 7: Querying dates and times
Replacing NULL values with ISNULL, COALESCE
Converting data types with CAST
Converting data types with CONVERT
Converting data types with PARSE
Checking if a string is a number or date with ISNUMERIC, ISDATE
Testing conversions with TRY_CAST, TRY_CONVERT, TRY_PARSE
Other data types
Lab 8: Converting between data types
Lab 8: Answers
Quiz 8: Converting between data types
Counting rows and columns with COUNT
Summarizing data with SUM, AVG, MIN, MAX
Summarizing data in sections with GROUP BY
Filtering returned groups with HAVING
Understanding logical query execution order
Numbering rows with ROW_NUMBER, RANK, DENSE_RANK, NTILE, and OVER
Partitioning ranking and numbering with PARTITION BY
Lab 9: Aggregating data
Lab 9: Answers
Quiz 9: Aggregating data
Outputting messages with PRINT
Using conditional expressions with CASE, IIF
Defining variables and assigning values to them using DECLARE, SET
Querying system variables and functions
Adding conditional logic with IF, ELSE
Creating composite statements with BEGIN, END
Creating loops with WHILE
Lab 10: Implementing logic in scripts
Lab 10: Answers
Quiz 10: Implementing logic in scripts
Using CROSS JOIN
What is a primary key?
What is a foreign key?
Using INNER JOIN
Using modern join syntax
Using LEFT OUTER JOIN
Other outer joins with RIGHT OUTER JOIN, FULL OUTER JOIN
Joining more than two tables
Joining a table to itself (self joins)
Joins without equality (non-equi joins)
Lab 11: Querying multiple tables
Lab 11: Answers
Quiz 11: Querying multiple tables
Using UNION and UNION ALL
Excluding data with EXCEPT
Finding common data with INTERSECT
Selecting from table-valued functions
Repetitively selecting with CROSS APPLY, OUTER APPLY
Lab 12: Applying set operations to tables
Lab 12: Answers
Quiz 12: Applying set operations to tables
Using subqueries
Creating scalar subqueries
Creating lists from subqueries
Checking for existence with EXISTS subqueries
Creating table expressions from subqueries
Using VALUES row constructors
Simplifying queries with common table expressions (CTEs)
Using multiple CTEs in a single query
Lab 13: Using subqueries
Lab 13: Answers
Quiz 13: Using subqueries
What is a stored procedure?
Executing stored procedures
Executing dynamic SQL statements
Querying the system catalog
Lab 14: Working with stored procedures and catalog views
Lab 14: Answers
Quiz 14: Working with stored procedures and catalog views
Inserting data into a table
Inserting multiple rows at once with VALUES
What are default constraints?
What are identity columns?
Identifying the last value inserted
What is a check constraint?
What is a unique constraint?
What is a foreign key constraint?
Deleting rows from a table
Inserting rows with data from another table
Inserting rows returned by a stored procedure
Selecting rows into a new table
Truncating a table vs deleting all rows
Updating data in a table
Merging data into a table
Lab 15: Modifying data
Lab 15: Answers
Quiz 15: Modifying data
Summary and further steps
Module 0: Getting started
Who is this course for?
Preview
Who is Greg?
Preview
What will I learn in this course?
Preview
Overview of lab setup
Module 1: Introduction to querying SQL Server
What is SQL Server?
What is SQL?
What is T-SQL?
Types of T-SQL statements
What are Server Instances?
Connecting to SQL Server Logins
What are databases?
Connecting to SQL Server users
Using Client Tools to Connect to the Database
Finding help and examples for T-SQL syntax
Lab 1: Introduction to querying SQL Server
Quiz 1: Introduction to querying SQL Server
Module 2: Querying a single table
What are tables?
Finding out what's in a table
What are schemas?
Introduction to PopkornKraze
SELECT statements
Using statement terminators
Quoting (delimiting) names
Using 2 part names
Using aliases for columns and tables
ORDER BY clause
Restricting output with OFFSET and FETCH
Removing duplicates with DISTINCT
Querying literals, expressions and functions
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
Finding patterns with the LIKE operator
Logical operations with AND, OR, and NOT
Ranges of values with the BETWEEN operator
Checking lists of values by using the IN operator
Limiting output rows by using the TOP operator
Using TOP WITH TIES
Working with NULL values (the lack of data)
What are user-defined functions?
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
Understanding batches, scripts, and GO
Repeating batches with GO n
Adding comments to your scripts
Formatting your scripts for readability
Quiz 4: Working with SQL Server queries
Module 5: Querying numbers
What are data types?
Exact whole numbers
Exact decimal numbers
Approximate numbers
Numeric operators
Lab 5: Querying numbers
Lab 5: Answers
Quiz 5: Querying numbers
Module 6: Querying strings
String data types
Literal character values
Comparing strings with collations
Character operators
Trimming strings with LTRIM, RTRIM, and TRIM
Changing case with UPPER, LOWER
Extracting parts of strings with LEFT, RIGHT, SUBSTRING
Determining length with LEN, DATALENGTH
Replace substrings using REPLACE and STUFF
Duplicating strings with REPLICATE
Finding substrings with CHARINDEX and PATINDEX
Splitting strings with STRING_SPLIT
Replacing characters with TRANSLATE
Lab 6: Querying strings
Lab 6: Answers
Quiz 6: Querying strings
Module 7: Querying dates and times
Date and time data types
Literal date and time values
Current date and time with SYSDATETIME, GETDATE, SYSUTCDATETIME
Extracting date components with YEAR, MONTH, DAY
Adding and subtracting periods using DATEADD
Subtracting dates using DATEDIFF
Extracting other date components with DATEPART
Get string names of date parts with DATENAME
Calculating end of month with EOMONTH
Constructing dates from components with DATEFROMPARTS
Constructing datetimeoffset values with TODATETIMEOFFSET
Changing datetimeoffset values to different offsets with SWITCHOFFSET
Custom formats of dates and times with FORMAT
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
Converting data types with CAST
Converting data types with CONVERT
Converting data types with PARSE
Checking if a string is a number or date with ISNUMERIC, ISDATE
Testing conversions with TRY_CAST, TRY_CONVERT, TRY_PARSE
Other data types
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
Summarizing data with SUM, AVG, MIN, MAX
Summarizing data in sections with GROUP BY
Filtering returned groups with HAVING
Understanding logical query execution order
Numbering rows with ROW_NUMBER, RANK, DENSE_RANK, NTILE, and OVER
Partitioning ranking and numbering with PARTITION BY
Lab 9: Aggregating data
Lab 9: Answers
Quiz 9: Aggregating data
Module 10: Implementing logic in scripts
Outputting messages with PRINT
Using conditional expressions with CASE, IIF
Defining variables and assigning values to them using DECLARE, SET
Querying system variables and functions
Adding conditional logic with IF, ELSE
Creating composite statements with BEGIN, END
Creating loops with WHILE
Lab 10: Implementing logic in scripts
Lab 10: Answers
Quiz 10: Implementing logic in scripts
Module 11: Querying multiple tables
Using CROSS JOIN
What is a primary key?
What is a foreign key?
Using INNER JOIN
Using modern join syntax
Using LEFT OUTER JOIN
Other outer joins with RIGHT OUTER JOIN, FULL OUTER JOIN
Joining more than two tables
Joining a table to itself (self joins)
Joins without equality (non-equi joins)
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
Excluding data with EXCEPT
Finding common data with INTERSECT
Selecting from table-valued functions
Repetitively selecting with CROSS APPLY, OUTER APPLY
Lab 12: Applying set operations to tables
Lab 12: Answers
Quiz 12: Applying set operations to tables
Module 13: Using subqueries
Using subqueries
Creating scalar subqueries
Creating lists from subqueries
Checking for existence with EXISTS subqueries
Creating table expressions from subqueries
Using VALUES row constructors
Simplifying queries with common table expressions (CTEs)
Using multiple CTEs in a single query
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?
Executing stored procedures
Executing dynamic SQL statements
Preview
Querying the system catalog
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
Inserting multiple rows at once with VALUES
What are default constraints?
What are identity columns?
Identifying the last value inserted
What is a check constraint?
What is a unique constraint?
What is a foreign key constraint?
Deleting rows from a table
Inserting rows with data from another table
Inserting rows returned by a stored procedure
Selecting rows into a new table
Truncating a table vs deleting all rows
Updating data in a table
Merging data into a table
Lab 15: Modifying data
Lab 15: Answers
Quiz 15: Modifying data
Module 16: Next steps
Summary and further steps
×
This is an unpublished lesson. This lesson will not be shown for students unless you set it as Public.
Back to Dashboard
No contents are available in this lesson!
No lessons available !
Back to Dashboard
Lesson contents locked
Enroll to unlock this lesson.
Enroll to unlock
Next Lesson