Lab Exercises, Experiments and Notes on SQL
Completed for Microsoft-Issued SQL Certification
Covers:
1.Introduction to Transact-SQL
- Select Statement
- Working with Data Types
- Working with Nulls
2.Querying Tables
- Removing Duplicates
- Sorting Results
- Filtering and using predicates
3.Querying multiple tables with Joins
- Inner Joins
- Outer Joins
- Cross Joins
- Self Joins
4.Using Set Operators
- Union Queries
- Intersect
- Except
5.Using Functions and Aggregating Data
- Scalar Functions
- Logical Functions
- Window Functions
- Aggregate Functions
- Grouping Aggregated Data
- Filtering Groups
6.Using Subqueries and APPLY
- Self-contained Subqueries
- Correlated Subqueries
- Using the APPLY Operator
7.Using Table Expressions
- Querying Views
- Using Temporary Tables and Table Variables
- Querying Table-valued Functions
- Using Derived Tables
- Using Common Table Expressions
8.Grouping Sets and Pivoting Data
- Grouping Sets
- Roll Up and Cube
- Identifying Groupings in Results
- Pivoting Data
- Using PIVOT and UNPIVOT
9.Modifying Data
- Inserting Data into tables
- Generating Identifiers
- Updating Data in Tables
- Deleting Data in Tables
10.Programming with Transact-SQL Fundamentals
- Batches
- Comments
- Variables
- Conditional Branching
- Loops
- Stored Procedures
11.Error Handling and Transactions
- Errors and Error messages
- Raising Errors
- Catching and Handling Errors
- Introduction to Transactions
- Implementing explicit transactions