Data and AI Training

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

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

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.

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.