Structured Query Language: (T-SQL) Level 2
Create queries using transact structured query language (T-SQL) commands.

Course details
CEUs 1.8
Schedule Evenings
Location Main Campus, Online
Cost
$689
Structured Query Language: (T-SQL) Level 2
Create queries using transact structured query language (T-SQL) commands.
What you’ll be able to do — competencies
- Create queries using transact structured query language (T-SQL) commands that are used with SQL Server databases
- Understand how to create queries in order to merge data between tables, track data changes on tables, rank data and compare tables
- Join data from subqueries and table-valued functions, perform recursive queries with hierarchical data, use common table expressions in place of complex joins, query Excel data and transfer XML data between a SQL Server table and an outside file
Course description
In this intermediate SQL course, you’ll create queries using transact structured query language (T-SQL) commands that are used with SQL Server databases. Gain hands-on practice creating queries to merge data between tables, track data changes on tables, rank data and compare tables. Students will also join data from subqueries and table-valued functions, perform recursive queries with hierarchical data, use common table expressions in place of complex joins, query Excel data, and transfer XML data between a SQL Server table and an outside file.
Prerequisites
- Required: Structured Query Language: (SQL) Level 1 or equivalent knowledge
- Recommended: Beginning SQL Server
Syllabus
Objective 1
Use the MERGE statement in conjunction with the INSERT, UPDATE and DELETE statements to manipulate data between two tables, and in conjunction with the OUTPUT clause to track changes
- Use the OUTPUT clause to display data changes made during the execution of the INSERT, UPDATE and DELETE statements
- Use the OUTPUT clause to store data changes made during the execution of INSERT, UPDATE and DELETE statements into an audit table
- Use the MERGE statement to merge data in one table with data in another table
- Use the OUTPUT clause to display changes made during the execution of the MERGE statement
- Use the OUTPUT clause to store changes made during the execution of the MERGE statement into an audit table
Objective 1 Assignments
- Read chapter 8, pages 271–275 (Merging data), pages 280–287 (The OUTPUT clause)
- Complete labs in class
- Complete skill check 1 (Required)
Objective 2
Use ranking functions and the CASE expression in the SELECT statement to set values in columns of the result set where they are applied
- Use the CASE expression to determine the resulting values in a column of the result set based on specified criteria in the expression
- Use the ROW_NUMBER function in conjunction with the PARTITION BY clause to assign a row number to each row within the partition of a result set
- Use the RANK function in conjunction with the PARTITION BY clause to return the rank of each row, within the partition of a result set that may include gaps in the ranking
- Use the DENSE_RANK function in conjunction with the PARTITION BY clause to return the rank of each row, within the partition of a result set without any gaps in the ranking
Objective 2 Assignments
- Read chapter 2, pages 52–54 (CASE expressions)
- Read chapter 7, pages 216–219 (Ranking window functions)
- Complete labs in class
- Complete skill check 2 (Required)
Objective 3
Use the INTERSECT, EXCEPT, CROSS APPLY and OUTER APPLY operators to retrieve data from a variety of data sources
- Use the INTERSECT operator to retrieve duplicate data existing in two data sources
- Use the EXCEPT operator to retrieve data from one source that does not exist in another source
- Use the CROSS APPLY and OUTER APPLY operators to retrieve data jointly from a subquery and a table
- Use the CROSS APPLY and OUTER APPLY operators to retrieve data jointly from a table-valued function and a table
Objective 3 Assignments
- Read chapter 5, pages 180–183 (The APPLY operator); Chapter 6
- Complete labs in class
- Complete skill check 3 (Required)
Objective 4
Use Common Table Expressions (CTEs) to query multiple data sources
- Use a simple CTE to retrieve data
- Use multiple CTEs to retrieve data
- Use a CTE to retrieve summarized, aggregated data
- Use a CTE to minimize use of subqueries to retrieve data
- Use a recursive query built into a CTE to retrieve hierarchical data that includes level values
Objective 4 Assignments
- Read chapter 5, pages 167–171 (Common table expressions)
- Complete labs in class
- Complete skill check 4 (Required)
Objective 5
Apply data manipulation language (DML) statements to Excel worksheets and named ranges to retrieve and update data
- Export data from a SQL Server table to an XML file using a SELECT statement that contains the FOR XML clause with only the AUTO and ELEMENTS arguments
- Import data from an XML file into a SQL Server table using an INSERT INTO SELECT statement that contains the OPENXML function in the FROM clause
Objective 5 Assignments
- http://msdn.microsoft.com/en-us/library/bb545450.aspx
- Complete labs in class
- Complete skill check 5
Objective 6
Transfer XML data between a SQL Server table and an XML file
- Export data from a SQL Server table to an XML file using a SELECT statement that contains the FOR XML clause with only the AUTO and ELEMENTS arguments
- Import data from an XML file into a SQL Server table using an INSERT INTO SELECT statement that contains the OPENXML function in the FROM clause
Objective 6 Assignments
- http://msdn.microsoft.com/en-us/library/bb545450.aspx
- Complete labs in class
- Complete skill check 6
- Complete class evaluation
Next available start dates
Online — Evenings
Structured Query Language: (T-SQL) Level 2
Total classroom hours
18 hours
Schedule
Online
Evenings, 6:00 PM - 9:00 PM, 6 T Th - 11/7/2023 - 11/28/2023, No class 11/23
Quarter: Fall
Total cost
$689
Instructor: Victor Leal
Victor Leal is a Data Engineering manager at Amazon Web Services (AWS). He has an exceptional record of success in developing, enhancing, deploying and managing database designs.
Notes
Online courses require an internet connection and the ability to interact both through audio and video using either a web cam/microphone, speakers/headset using a computer or via a mobile phone connection. Any costs associated with connectivity or equipment are the responsibility of the student.
Interested in custom learning solutions for your team?
Tombolo Institute Corporate Solutions offers tailor-made professional development programs to meet your organization’s goals.
Get started