Introduction to SQL Training Course

This course is ideal for individuals who:

• Want to learn the fundamentals of SQL and relational databases

• Need to work with databases in their current or future roles

• Are looking to gain practical skills in data retrieval and manipulation

To gain most benefit from this course, you should be comfortable working with computers and have a basic understanding of data concepts (e.g. files, spreadsheets).

We can customise the training to match your team's experience and needs - for instance with more time and coverage of fundamentals for budding new data professionals. Get in touch to find out how.

This SQL course is available for private / custom delivery for your team - as an in-house face-to-face workshop at your location of choice, or as online instructor-led training via MS Teams (or your own preferred platform).

Get in touch to find out how we can deliver tailored training which focuses on your project requirements and learning goals.

Introduction to Databases and SQL

• Welcome and Course Outline

• What is a Database?

• Why Use a Database? (Emphasize benefits for different roles)

• Relational Databases:

  • Core Concepts (Tables, Rows, Columns, Keys)

  • Examples of Popular Relational Database Management Systems (RDBMS)

• SQL and RDBMS: The language of relational databases

• Introduction to NoSQL Databases:

  • Key characteristics and types (Key-Value, Document, Column-Family, Graph)

  • Examples of Popular NoSQL Databases

• Relational vs. NoSQL: Key Differences and Use Cases

Querying Data with the SELECT Statement

• Structured Query Language (SQL): Overview and Importance

• Basic SELECT statement: Selecting all columns (*)

• Selecting specific attributes (columns)

• Eliminating duplicate rows with DISTINCT

• Filtering Data with the WHERE Clause:

  • Comparison operators (=, >, <, >=, <=, !=)

  • Logical operators (AND, OR, NOT)

• Sorting Data with the ORDER BY Clause:

  • Ascending (ASC) and descending (DESC) order

  • Ordering by multiple columns

  • Combining ORDER BY and WHERE

• Limiting results with LIMIT (or TOP in some systems)

• SQL Comments (--, /* ... */)

Advanced SELECT Techniques

• Filtering with Range and Membership: IN, NOT IN, BETWEEN, NOT BETWEEN

• Aggregate Functions: MIN(), MAX(), COUNT(), AVG(), SUM()

• Date and Time Functions: Introduction to common date/time functions (system-specific examples could be useful)

• Pattern Matching: LIKE operator with wildcards (%, _)

• Renaming Columns and Tables: AS alias

• Grouping Data: GROUP BY clause

• Filtering Groups: HAVING clause

• Handling Missing Values: NULL values, IS NULL, IS NOT NULL

• Conditional Logic: CASE statement (simple and searched CASE)

Data Definition Language (DDL) - Table Management

• SQL DDL: Overview of commands for defining database schema

• CREATE DATABASE (and schema concepts)

• CREATE TABLE: Defining table structure, column names, and data types

• Common Data Types (with examples across different RDBMS if relevant)

• ALTER TABLE: Modifying table structure (adding, modifying, dropping columns, constraints)

• DROP TABLE: Removing tables

• Viewing Table Metadata (system-specific commands or tools)

• GENERATED ALWAYS AS (if applicable to common RDBMS)

Database Design Fundamentals

• Introduction to Database Design Principles

• Database Normalization: Goals and benefits

• First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)

• Brief mention of higher normal forms (optional)

Data Manipulation Language (DML) - Data Maintenance

• SQL DML: Overview of commands for manipulating data

• INSERT INTO: Adding new rows

• Inserting data from another table (INSERT INTO ... SELECT)

• UPDATE: Modifying existing data

• DELETE FROM: Removing rows

• Data Constraints:

  • NOT NULL

  • UNIQUE

  • PRIMARY KEY

  • FOREIGN KEY (and referential integrity)

  • CHECK constraints

  • Defining constraints at the table and column level

Querying Multiple Tables

• The Need for Joining Tables

• UNION, UNION ALL Operators (combining result sets)

• Table Joins:

  • INNER JOIN (and the ON clause)

  • LEFT JOIN (or LEFT OUTER JOIN)

  • RIGHT JOIN (or RIGHT OUTER JOIN)

  • FULL OUTER JOIN (availability across RDBMS)

  • Cross Joins (Cartesian Products)

  • Self-Joins

• Subqueries (Nested Queries):

  • Subqueries in the WHERE clause

  • Subqueries in the SELECT clause (correlated and non-correlated)

  • IN, NOT IN with subqueries

  • ANY, SOME, ALL with subqueries

  • EXISTS, NOT EXISTS operators

Views, Indexes, and Introduction to Stored Procedures

• SQL Views:

  • Introduction to views (virtual tables)

  • CREATE VIEW

  • ALTER VIEW (if supported)

  • DROP VIEW

  • Uses and benefits of views

• Temporary Tables: CREATE TEMPORARY TABLE (local and global temporary tables)

• SQL Indexes:

  • Introduction to indexes (improving query performance)

  • CREATE INDEX (single and composite indexes)

  • DROP INDEX

  • Considerations for index creation

• Introduction to Stored Procedures:

  • What are stored procedures?

  • Benefits of using stored procedures

  • CREATE PROCEDURE (basic syntax)

  • EXECUTE (or CALL) stored procedures

SQL Data Integrity and Transactions

• Data Quality: Importance and challenges

• Ensuring Data Quality through Database Rules (Constraints)

• Database Triggers:

  • Introduction to triggers (event-driven actions)

  • CREATE TRIGGER (basic syntax: BEFORE, AFTER, INSERT, UPDATE, DELETE)

  • DROP TRIGGER

  • Considerations for using triggers

• Transactions:

  • Introduction to transactions (grouping operations)

  • ACID Properties (Atomicity, Consistency, Isolation, Durability)

  • BEGIN TRANSACTION (or START TRANSACTION)

  • COMMIT

  • ROLLBACK

Course Wrap-up and Next Steps

• Review of key concepts

• Further learning resources

• Q&A

• DB Browser for SQLite: https://sqlitebrowser.org/ - A free, open-source tool for creating, designing, and editing SQLite databases.

• MySQL Documentation: https://dev.mysql.com/doc/ - The official documentation for MySQL, a popular open-source RDBMS.

• PostgreSQL Documentation: https://www.postgresql.org/docs/ - The official documentation for PostgreSQL, a powerful open-source RDBMS.

• Microsoft SQL Server Documentation: https://learn.microsoft.com/en-us/sql/sql-server/ - The official documentation for Microsoft SQL Server.

• Oracle SQL Documentation: https://docs.oracle.com/en/database/ - The official documentation for Oracle Database.