Structured Query Language (SQL)

Getting started with SQL

• An Introduction to Relational Databases

• What is SQL and why is it useful? (Crash Course in SQL)

• Types of data stores, when is a Relational Database a good choice? 

• Connecting to a database with Azure Data Studio and SQL Server Management Studio

• Understanding the advantages of fully managed databases such as Azure SQL vs self-hosted databases. 

Building simple queries

• Query the Northwind Database

• Adopting SQL Coding Standards

• Be able to build queries that use the following keywords

  • WHERE, AND, OR, BETWEEN, IN, LIKE, TOP, ORDER BY, WITH TIES, OFFSET FETCH

• Dealing with NULLs (3 Value Logic)

Creating calculated columns

• Data Types

• CAST(), CONVERT()

• Calculated Columns

• Column Aliases

• Dates and DateTime Calculations

• Creating variables

• Character Functions

  • LEN()

  • UPPER()

  • LOWER()

  • LEFT()

  • RIGHT()

  • CONCAT()

  • COALESCE()

  • REPLICATE()

  • FORMAT()

• Date and Time

  • Understanding the different Date types available in SQL Server.

  • The ISO 8604 date standard

  • Formatting dates with CONVERT() and FORMAT()

  • Getting the current date and time using GETDATE(), CURRENT_TIMESTAMP and variations

  • Best practices for searching a range of dates

  • Declaring date variables 

  • DateFromParts()

  • ISDATE()

  • YEAR(), MONTH(), DAY()

  • DATEDIFF()

  • DATEADD()

  • EOMONTH()

  • Accurately calculating someone's age from their date of birth

Joining Tables and Unions

• INNER

• RIGHT OUTER, LEFT OUTER

• FULL OUTER

• Self Joins

• UNION and UNION ALL

DDL - Modifying databases

• CREATE TABLE

• ALTER TABLE

• DROP TABLE

• Using Azure Data Studio for creating tables and joins

Modifying Data

• INSERT

• UPDATE 

• DELETE

• SELECT INTO

• INSERT INTO SELECT

Basic Statistics with SQL

• AVG()

• COUNT(*), COUNT(column), COUNT DISTINCT()

• SUM()

• MIN()

• MAX()

• GROUPINGS

• GROUP BY

• GROUP BY - HAVING

• GROUP BY - HAVING - WHERE

• The Logical Order of Processing

Advanced

• Executing multiple steps as a TRANSACTION, and rolling back if unsuccessful.

• Creating and Executing STORED PROCEDURES and VIEWS

• Table Indexes

• Nested SELECTs