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:30 PM - 9:30 PM, 6 T Th - 10/15/2020 - 11/3/2020

cost icon

Total cost

$629

instructor icon

Instructor: Victor Leal

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