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. 

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

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