Structured Query Language: (T-SQL) Level 2

Create queries using transact structured query language (T-SQL) commands.

course image

Course details

ceus icon CEUs 1.8

schedule icon Schedule Evenings

location icon Location Online

cost icon Cost

Registration details

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

expand drawer icon collaspe drawer icon

In this intermediate SQL course, youll 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

expand drawer icon collaspe drawer icon
  • Required: Structured Query Language: (SQL) Level 1 or equivalent knowledge  
  • Recommended: Beginning SQL Server 

Syllabus

expand drawer icon collaspe drawer icon

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 271275 (Merging data), pages 280287 (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 5254 (CASE expressions)  
  • Read chapter 7, pages 216219 (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 180183 (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 167171 (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 

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 

Next available start dates

Online — Evenings

Registration open expand drawer icon collaspe drawer icon
Structured Query Language: (T-SQL) Level 2
hours icon

Total classroom hours

18 hours

schedule icon

Schedule

Online

Evenings, 6:00 PM - 9:00 PM, 6 T Th - 11/30/2021 - 12/16/2021

Quarter: Fall

cost icon

Total cost

$629

instructor icon

Instructor: Victor Leal

Data Engineering Manager at Amazon Web Services (AWS). An exceptional record of success managing and performing database design, development, enhancement, and deployment. Instructing at Tombolo since 2017.

notes icon

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