SQL – Foundation

Who is this course for? Much of the world’s valuable data is locked away in databases and SQL is the key to unlocking it. This course is for people who need to extract this data into their analyses and projects.

Learning Objectives: Attendees will be able to write basic SQL statements to extract data from databases and be able to specify exactly what data is required and transform it into the shape needed for their business purposes.

Pre-requisites: None. This is an introduction course.

Course Content:
Introduction to SQL and Databases. Why is SQL important? SQL’s declarative nature. Whetting your appetite – a quick demo of what SQL can do and what the course will cover.

Get started with the tools to connect to and query databases such as Azure Data Studio, DBeaver, SSMS. Use these to connect to a database and see its contents (tables, views, stored procedures).

Understand the structure of databases: tables (columns, data types, nullability, cardinality, primary keys) and relationships between tables (common columns, foreign keys, referential integrity).

SQL SELECT Basics. The structure of a simple SELECT statement: the column list, WHERE, ORDER BY and GROUP BY clauses.

Filter data with the WHERE clause. Use the WHERE clause to get exactly the rows that you require from an (often very large) table.

GPOUP BY and aggregations. How to summarise your data (and why this is often useful). Common aggregation functions: e.g. COUNT, SUM, AVG. How to filter data once it is grouped with the HAVING clause.

Some common, useful SQL functions and operations: e.g. ROUND, TRIM. Useful date-related functions (DATEADD, DATEPART, DATEDIFF) and date arithmetic.

Create calculated columns in your SQL statement.

Below are some images from the lab exercises on the course.

SQL query
This SQL statement retrieves the latest 25 property sales of in a particular street.
SQL SELECT statement
This SQL statement retrieves football matches in which the home team scored 3 or more goals.
This SQL statement links two tables with a LEFT join.
This SQL statement links two tables with a LEFT join to ensure all rows from the Geography table are returned.