Structured Query Language: (T-SQL) Level 2
Create queries using transact structured query language (T-SQL) commands.
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
Main Campus — Evenings
Structured Query Language: (T-SQL) Level 2
Total classroom hours
18 hours
Schedule
Main Campus
Evenings, 6:00 PM - 9:00 PM, 6 T Th - 5/16/2023 - 6/1/2023
Class Location:
3000 Landerholm Cir. SE Bellevue, WA 98007
Building A, Room 134
Campus Map
Quarter: Spring
Total cost
$629
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.
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