SQL & Database Design

What you will be able to do

• Install PostgreSQL and run SQL from psql and a GUI tool
• Design normalised tables with keys and constraints from a written requirement
• Write queries with filters, sorting, joins, grouping and subqueries
• Explain how an index speeds up a query and read a simple query plan
• Use transactions and explain ACID with a money-transfer example
• Build the database for the ShopLite store that later sections turn into an application

What it will cover

1. 12.1Databases & PostgreSQL Setup

   Why applications store data in a database and how to get PostgreSQL running on your computer.

   Show 6 topics
   • Files vs databases: why we need a DBMS
   • Relational databases: tables, rows and columns
   • Why this course uses PostgreSQL
   • Install PostgreSQL and pgAdmin
   • Run your first SQL in psql
   • Create a database and a user

2. 12.2Creating Tables & Data Types

   Create tables with the right column types so the database stores data correctly.

   Show 5 topics
   • CREATE TABLE and DROP TABLE
   • Numbers, text, dates, booleans and NUMERIC for money
   • Auto-generated ids: identity columns
   • ALTER TABLE: add, rename and remove columns
   • Schemas and naming conventions

3. 12.3Keys & Constraints

   Let the database reject bad data with primary keys, foreign keys and other constraints.

   Show 5 topics
   • Primary keys and natural vs surrogate keys
   • NOT NULL, UNIQUE, CHECK and DEFAULT
   • Foreign keys: linking two tables
   • ON DELETE: CASCADE, RESTRICT and SET NULL
   • Common constraint errors and how to read them

4. 12.4Inserting, Updating & Deleting Data

   Change the data in your tables safely and see what changed.

   Show 5 topics
   • INSERT one row and many rows
   • UPDATE with a WHERE clause (and what happens without one)
   • DELETE and TRUNCATE
   • RETURNING: get generated ids back
   • Upserts with ON CONFLICT

5. 12.5Querying Data

   Read exactly the rows and columns you need with SELECT.

   Show 6 topics
   • SELECT columns and aliases
   • WHERE with AND, OR, IN, BETWEEN and LIKE
   • NULL and IS NULL
   • ORDER BY, LIMIT and OFFSET
   • DISTINCT and CASE expressions
   • String, number and date functions

6. 12.6Aggregates & Grouping

   Turn many rows into totals, counts and averages for reports.

   Show 5 topics
   • COUNT, SUM, AVG, MIN and MAX
   • GROUP BY one and several columns
   • HAVING vs WHERE
   • Order of execution of a SELECT
   • Report queries: sales per month, top customers

7. 12.7Joins

   Combine data from several tables, the most asked SQL skill in interviews.

   Show 6 topics
   • INNER JOIN step by step
   • LEFT and RIGHT JOIN: keeping unmatched rows
   • FULL OUTER JOIN and CROSS JOIN
   • Self join: employees and managers
   • Joining three or more tables
   • Finding missing data with LEFT JOIN ... IS NULL

8. 12.8Subqueries, Views & Window Functions

   Answer harder questions with queries inside queries and ranking functions.

   Show 6 topics
   • Subqueries in WHERE, FROM and SELECT
   • EXISTS and NOT EXISTS
   • Common table expressions with WITH
   • Views: saving a query as a virtual table
   • ROW_NUMBER, RANK and DENSE_RANK
   • Classic questions: second highest salary, top N per group

9. 12.9Database Design & Normalisation

   Turn a requirement into a clean set of tables without duplicate or inconsistent data.

   Show 6 topics
   • Entities, attributes and ER diagrams
   • One-to-one, one-to-many and many-to-many
   • Join tables for many-to-many
   • Anomalies and why we normalise
   • 1NF, 2NF, 3NF with worked examples
   • When denormalising is reasonable

10. 12.10Indexes & Query Performance

    Understand why some queries are slow and how indexes make them fast.

    Show 6 topics
    • Full table scan vs index lookup
    • How a B-tree index works (the idea)
    • Creating single-column, composite and unique indexes
    • Reading EXPLAIN and EXPLAIN ANALYZE
    • When an index is not used
    • The cost of indexes on writes

11. 12.11Transactions & ACID

    Group changes so they all succeed or all fail, even when many users work at once.

    Show 6 topics
    • BEGIN, COMMIT and ROLLBACK
    • ACID with a bank transfer
    • Dirty, non-repeatable and phantom reads
    • Isolation levels in PostgreSQL
    • Row locks with SELECT ... FOR UPDATE
    • Deadlocks: what they are and how to avoid them

12. 12.12SQL Practice & ShopLite Database Project

    Design and query the ShopLite store database that the next sections build on, plus interview-style SQL practice.

    Show 6 topics
    • Requirements: customers, products, categories, orders and order items
    • Design the schema and draw the ER diagram
    • Write the CREATE script and seed data
    • Business report queries for the store
    • Interview SQL problem set
    • Backups with pg_dump and restoring a database