Enroll in both Levels of our instructor-led SQL Querying classes and save $135. This course package includes:
In this course you will compose basic SQL queries to retrieve desired information from a database. You will explore database concepts, write simple SELECT queries, use built-in functions and expressions, join tables, build subqueries and unions, and finally perform some advanced where clauses.
SQL Querying - Basic (Days 1 & 2)
Exploring Database Concepts
What Is SQL Server?
Databases in the Cloud
Service vs. Application
Database as Container
Data Is Stored in Tables
Data Types
Normalization
Understanding Indexes
Database Schemas
Writing Simple SELECT Queries
Using the SELECT Statement
Selecting a Literal Value
Retrieving from a Table
Generating a Select-List
Mixing Literals and Column Names
Filtering Data
Adding a WHERE Clause
Using WHERE Clauses with Alternate Operators
Using BETWEEN
Using BETWEEN with NOT
Filtering on Date and Time
Using WHERE Clauses with Two Predicates
Using the IN Operator
Working with NULL
Sorting Data
Thinking About Performance
Taking Advantage of Indexes
Viewing Execution Plans
Using Built-In Functions and Expressions
Expressions Using Operators
Concatenating Strings
Concatenating Strings and NULL
CONCAT
ISNULL and COALESCE
Concatenating Other Data Types to Strings
Using Mathematical Operators
Using String Functions
RTRIM and LTRIM
LEFT and RIGHT
LEN and DATALENGTH
CHARINDEX
SUBSTRING
CHOOSE
REVERSE
UPPER and LOWER
REPLACE
Nesting Functions
Using Date and Time Functions
GETDATE and SYSDATETIME
DATEADD
DATEDIFF
DATENAME and DATEPART
DAY, MONTH, and YEAR
CONVERT
FORMAT
DATEFROMPARTS
EOMONTH
Using Mathematical Functions
ABS
POWER
SQUARE and SQRT
ROUND
RAND
Logical Functions and Expressions
The CASE Expression
IIF
COALESCE
Administrative Functions
Using Functions in the WHERE and ORDER BY Clauses
The TOP Keyword
Thinking About Performance
Joining Tables
Using INNER JOIN
Joining Two Tables
Avoiding an Incorrect Join Condition
Joining on a Different Column Name
Joining on More Than One Column
Joining Three or More Tables
Using OUTER JOIN
Using LEFT OUTER JOIN
Using RIGHT OUTER JOIN
Using OUTER JOIN to Find Rows with No Match
Adding a Table to the Right Side of a LEFT JOIN
Adding a Table to the Main Table of a LEFT JOIN
FULL OUTER JOIN
CROSS JOIN
Self-Joins
Thinking About Performance
Grouping and Summarizing Data
Aggregate Functions
The GROUP BY Clause
Grouping on Columns
Grouping on Expressions
The ORDER BY Clause
The WHERE Clause
The HAVING Clause
DISTINCT Keyword
Using DISTINCT vs. GROUP BY
DISTINCT Within an Aggregate Expression
Aggregate Queries with More Than One Table
Aggregate Functions and NULL
Thinking About Performance
Building on Subqueries and Unions
Writing Subqueries
Using a Subquery in an IN List
Using a Subquery and NOT IN
Using a Subquery Containing NULL with NOT IN
Using EXISTS
Using CROSS APPLY and OUTER APPLY
Writing UNION Queries
Using EXCEPT and INTERSECT
Advanced WHERE Clauses
Using CONTAINS
Using Multiple Terms with CONTAINS
Searching Multiple Columns
Using FREETEXT
Thinking About Performance
SQL Querying - Advanced (Day 3)
Building on Subqueries, Common Table Expressions, and Unions
Using Derived Tables and Common Table Expressions
Using Derived Tables
Using Common Table Expressions
Using a Common Table Expression to Solve a Complicated Join Problem
Thinking About Performance
Advanced Joining Tables
Merge Join
Nested Loop
Hash Match
Advanced WHERE Clauses
Pattern Matching
Using LIKE
Restricting the Characters in Pattern Matches
Searching for Wildcards
Combining Wildcards
Using PATINDEX
Using WHERE Clauses with Three or More Predicates
Using NOT with Parentheses
Performing a Full-Text Search
Writing Advanced Queries
Advanced CTE Queries
Alternate CTE Syntax
Using Multiple CTEs
Referencing a CTE Multiple Times
Joining a CTE to Another CTE
Writing a Recursive Query
Data Manipulation with CTEs
Isolating Aggregate Query Logic
Correlated Subqueries in the SELECT list
Using Derived Tables
Common Table Expressions
Using CROSS APPLY and OUTER APPLY
The OUTPUT Clause
Using OUTPUT to View Data
Saving OUTPUT Data to a Table
The MERGE Statement
GROUPING SETS
CUBE and ROLLUP
Pivoted Queries
Pivoting Data with CASE
Using the PIVOT Function
Using the UNPIVOT Function
Paging