SQL Querying - Fundamentals

In this 2 day 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.

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