Create queries using a standard structured query language (SQL) that is applicable to almost any relational database. Gain hands-on practice creating queries to retrieve data in various forms, including filtered, calculated, grouped and aggregated, sorted and consolidated from multiple data sources. You’ll also practice writing queries to manipulate data in a table and create, alter and drop tables and views. 

• Basic Relational Database Essentials or familiarity with relational database products 

Objective 1: Use the WHERE clause in a SELECT statement to retrieve filtered data from a relational database. 

• Describe the role and classifications of Structured Query Language (SQL) commands 
• Describe how to set up and use the Query Editor window in SQL Server Management Studio (SSMS) 
• Use the SELECT and FROM clauses in a SELECT statement, without the WHERE clause to retrieve data 
• Use a WHERE clause that applies relational operators and other operators, such as IN, BETWEEN/AND, and LIKE, to retrieve filtered data 
• Use a WHERE clause in conjunction with the IS NULL operator to retrieve data with NULL values in a specific field 
• Use a WHERE clause that applies compounded conditions connected with the AND, OR and NOT logical operators to retrieve filtered data 

Objective 1 Assignments 

• Read: 
• Chapter 1: pages 1–3 (Theoretical background, SQL) 
• Chapter 2: pages 27–32 (SELECT statement, FROM and WHERE clauses), 36–43 (SELECT and ORDER BY clauses), 49–52 (Predicates and operators), 71–73 (LIKE predicate) 
• Complete labs in class. 
• Complete Skill Check A (Required) 

Objective 2: Apply aggregate, date and string functions to data retrieved from a relational database 

• Use the DATEPART, DATEADD and DATEDIFF date functions to retrieve calculated and filtered data 
• Use the SUM, MAX, MIN, COUNT and AVG aggregate functions to retrieve aggregated data 
• Manipulate string data types using string functions 
• Format dates using the CAST and CONVERT functions 

Objective 2 Assignments 

• Read chapter 2: pages 80–83 (Date and time functions, CAST and CONVERT functions), 85–87 (DATEADD, DATEDIFF and DATEPART functions), 54–58 (NULLs), 32–35 (GROUP BY clause), 63–69 (String concatenation, SUBSTRING, LEFT, RIGHT, LEN, REPLACE, UPPER, LOWER, RTIM and LTRIM functions) 
• Complete labs in class 
• Complete skill check B (Required) 

Objective 3: Use the ORDER BY, GROUP BY and HAVING clauses to retrieve data from a relational database 

• Use the ORDER BY clause to sort data 
• Use the GROUP BY clause to retrieve aggregated and non-aggregated data 
• Use the GROUP BY clause in conjunction with the ROLLUP and CUBE options to retrieve aggregated and non-aggregated data 
• Use the HAVING clause in conjunction with aggregate functions to filter data 

Objective 3 Assignments 

• Read chapter 2: pages 32–36 (GROUP BY and HAVING clauses), 41–43 (ORDER BY clause) 
• Chapter 7: pages 234–237 (Grouping sets, CUBE and ROLLUP subclauses) 
• Complete labs in class. 
• Complete Skill Check C (Required) 

Objective 4: Use a self-join operation to retrieve data from a single data source or a variety of operators, or subqueries to retrieve data from multiple data sources 

• Use the JOIN (INNER, OUTER, CROSS, FULL) operators to retrieve data from two different tables 
• Use a self-join operation to retrieve data from a single table related to itself 
• Use multiple joins to retrieve data from tables not directly related to each other 
• Use the UNION and UNION ALL operators to combine data from multiple tables 
• Use a subquery in the SELECT, FROM and WHERE clauses of a SELECT statement to retrieve data 
• Use a subquery in conjunction with the ANY and ALL comparison operators in a WHERE clause to retrieve filtered data 
• Use a correlated subquery and the EXISTS function in a WHERE clause to retrieve filtered data 

Objective 4 Assignments 

• Read chapters 3, 4 
• Chapter 6: pages 193–196 (Set operators, UNION operator) 
• Complete labs in class 
• Complete Skill Check D (Required) 

Objective 5: Use the INSERT, UPDATE, DELETE and TRUNCATE commands to manipulate data in a table 

• Use the INSERT command to insert new rows into an existing table 
• Use the UPDATE command to update data in existing rows 
• Use the INSERT command to insert new rows into an existing table 
• Use the UPDATE command to update data in existing rows 

Objective 5 Assignments 

• Read chapter 8: pages 249–251 (INSERT VALUES and INSERT SELECT statements), 262–265 (Deleting data, DELETE and TRUNCATE statements), 266–268 (Updating data, UPDATE statement) 
• Complete labs in class 
• Complete Skill Check E (Required) 

Objective 6: Revise a relational database by creating, altering and dropping tables and views 

• Use the SELECT INTO command to create a new table from an existing table. 
• Use the CREATE TABLE command in conjunction with field and table level constraints to create a table with enhanced data integrity 
• Use the ALTER TABLE command in conjunction with the ADD command to add a column to an existing table 
• Use the ALTER TABLE command in conjunction with the REFERENCES key word to set a relationship between two tables and apply the referential integrity constraint 
• Use the DROP TABLE command to drop a table 
• Create a view using the CREATE VIEW command in conjunction with the ENCRYPTION, SCHEMABINDING and TOP key words 
• Alter a view using the ALTER VIEW command 
• Drop a view using the DROP VIEW command 

Objective 6 Assignments 

• Read chapter 8: pages 252–253 (SELECT INTO statement) 
• Chapter 1: pages 20–25 
• Chapter 5: pages 171–177 (read up to CHECK OPTION) 
• Complete labs in class 
• Complete Skill Check F 
• Complete class evaluations in class