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
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 - 5/4/2021 - 5/20/2021
Quarter: Spring
Total cost
$629
Instructor: Victor Leal
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