Data Training from Zomalex

Home | Power BI | Excel | Python | SQL | Visualising Data | Generative AI

SQL Course Notes

This contains notes to accompany the SQL courses.

Getting Started with SQL and the course

Why bother with SQL at all?

Power BI, for example, connects to databases and does it all for you

Why you will like SQL

• English-like
• Elegant and powerful, declarative
• Created in the 1980’s – and will still be around in 40 years
• ANSI and ISO standard – not proprietary
• Commercially important

A table by any other name would smell as sweet

• dataset, table, query
• column, field, variable, attribute
• row, observation
• quantity, measures – numeric columns, usually aggregated

The SQL Fab Four

• SELECT
• INSERT
• DELETE
• UPDATE

We need only 5 verbs with “tidy” data

• Project (choose the columns)
• Filter (choose the rows)
• Mutate (create a new column with calculated values)
• Sort (put the rows in a certain order)
• Group by / aggregate (summarise)

Project (SQL) by listing the columns

SELECT ReceivedDate,
       Region,
       Category,
       Movement
FROM dbo.MessageView;

Filter (SQL) with the WHERE clause

SELECT MessageId,
       ReceivedDate,
FROM dbo.MessageView
WHERE Movement >= 5; 
-- only return large movements

WHERE is flexible; in a list, between, not exists, >and AND and OR clauses

Mutate (SQL) – add calculation to column list

SELECT MessageId,
       Movement * 2 AS DoubleMovement
FROM dbo.MessageView;

Sort: use the ORDER BY clause

SELECT MessageId,
       ReceivedDate,
       Region,
       Category,
       Movement
FROM dbo.MessageView
ORDER BY Movement;

Group By / Aggregate

SELECT ReceivedDate,
       SUM(Movement) AS TotalMovement
FROM dbo.MessageView
GROUP BY ReceivedDate,

JOIN

SELECT 
m.MessageId,
c.[Date],
c.DayOfWeek
FROM dbo.MessageView m 
INNER JOIN dbo.Calendar c 
ON m.ReceivedDate = c.[Date]

+

joins together to form

Why bother with SQL at all? (again)

• useful and powerful
• easy to learn the basics
• unlocks much of of the world’s important data

We need a SQL editor

We need a SQL editor to write and run SQL. Examples are

• Azure Data Studio, lightweight, modern, all Microsoft Azure databases
• SQL Server management Studio (SSMS), shown below: traditional, heavy, popular, only Microsoft SQL Server
• DBeaver, has community edition, helpful, most popular databases

Example tables used in the course

PatientStay is a small table that we use in many of the exercises.

The Land Registry Tables sho sales of properties in London SW12.

The Chinook database represents an online music store.

The basic structure of a SQL SELECT statement

Example

SELECT 
         PatientID
       , Hospital
       , Ward
       , AdmittedDate
FROM PatientStay
WHERE Hospital IN (‘Kingston’, ‘PRUH’)
ORDER BY AdmittedDate DESC;

Syntax

SELECT
    <column>
       , <column>
       , <column
…
FROM <table>
WHERE <where-clause>
ORDER BY <column>;

A SQL SELECT statement with a GROUP BY

Example

SELECT 
       Hospital
       , Ward
       , COUNT(*) AS NumberOfPatients
       , SUM(Tariff) as TotalTariff
FROM PatientStay
WHERE AdmittedDate > ‘2024-03-01’
GROUP BY Hospital, Ward
ORDER BY Hospital, Ward;

Syntax

SELECT <column1>
       , <column2>
       , AGG(<column>)
…
FROM <table>
WHERE <where-clause>
GROUP BY <column1>, <column2>
ORDER BY <column>, <column2>;

Write a SQL statement in baby steps!

Start with SELECT ps.* FROM PatientStay ps;

Use the autocomplete. It is very helpful.

Take tiny baby steps Start from something that works, not something that doesn’t.

Read the error messages – they are sometimes useful.

Look at where the red squiggerly line starts.

Top tips to run SQL statements in DBeaver

Always put a blank line between statements.

Never have a blank line within a statement.

Click <Ctrl-Return> anywhere in statement to run

DBeaver is a great SQL editor

• Has a free edition
• Will connect to any database.
• Has database diagram feature.
• Lots of helpful features for editing , formatting and running SQL
• Has local Chinook database (SQLite)

Use AI to solve SQL challenges

AI is a useful, practical, everyday tool, that we can use now to help us write SQL and learn SQL.

Generative AI large language models (LLMs) such as ChatGPT, Copilot, Claude, Gemini have free editions and are available from a browser.

Pair programmers help us write better SQL from inside the SQL editor e.g. GitHub Copilot inside VSCode editor

The VSCode editor with GitHub Copilot

• VSCode is popular, free editor.
• GitHub Copilot is an AI pair programmer
• Describe what you want and GitHub Copilot will write the SQL

Can always revert back to DBeaver

SQL Window Functions

SQL Window functions are extremely useful, for example:

• Cumulative Totals
• Moving Averages
• Ranking
• Top N per Category

Example: Cumulative Totals

SELECT
	ReceivedMonth
	, ReceivedDate
	, Movement
	, ROW_NUMBER() OVER (PARTITION BY 	ReceivedMonth  ORDER BY ReceivedDate) AS RowIndex
	, SUM(Movement) OVER (PARTITION BY ReceivedMonth ORDER BY ReceivedDate) AS RunningRollingMovement
FROM
	Message;

Special functions used in a Window function

• ROW_NUMBER() – creates a sequence
• LEAD() – get the next value
• LAG() – gets the previous value These all need an ORDER BY clause.

Advanced Syntax

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
and
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
further limit the rows to be aggregated.