Structured Query Language (SQL)

Overview

Structured Query Language (SQL) has been the de facto way to query a relational database since becoming an ANSI standard in 1986, and subsequently an ISO standard in 1987.

SQL is a broad language, and practical experience varies even among those with a Computer Science background. Despite this, it’s required on nearly every IT project in some shape or form. Even newer NoSQL Data Stores are better thought of as “Not Only SQL”.

The course below is expansive but can be easily tailored to suit a different audience, move at a different pace, and or shortened to fewer days.

What you’ll need

• Laptop and headphones and microphone
• Microsoft Office (Word, PowerPoint, Excel)
• Google Chrome or Microsoft Edge
• Visual Studio Code with the MSSQL extension (and the ability to add extensions)
• SQL Server Management Studio

Learning outcomes

Attendees will be able to:

• Understand strategies for normalising data (how to represent a flat file of data in terms of multiple related tables).
• CREATE and DROP Database objects.
• INSERT, UPDATE and DELETE rows of data.
• Interrogate large datasets using a wide range of SELECT statements
• Create Views and Stored Procedures

In addition, they’ll learn good coding standards for the SQL language, tips for making queries that are fast to run, and some useful tips regarding ensuring that the query you’ve written is correct.

Course topics

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 Visual Studio Code and the MSSQL extension or 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 8601 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 Visual Studio Code and the MSSQL extension 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