SQL and Databases

What a database is, how tables, rows and columns work, and how to set up a database to practise on.

• Why applications use a database
• Tables, rows and columns
• What SQL is and its main parts: DDL, DML, DQL
• Relational database management systems
• Installing MySQL and a client tool
• Running your first statements
• Creating a database and a user
• Loading practice data

• Why applications use a database
• Tables, rows and columns
• Install MySQL Server and MySQL Workbench
• Run your first SQL in the mysql client

Defining the structure of your data with CREATE TABLE and choosing correct column types.

• CREATE TABLE and DROP TABLE
• Integer, DECIMAL and floating-point types
• VARCHAR, CHAR, TEXT and ENUM
• Dates, times and booleans
• Auto-generated ids
• ALTER TABLE: add, change and drop columns
• Naming conventions
• Storage engines and character sets

• CREATE TABLE and DROP TABLE
• Number types and DECIMAL for money
• Dates, times and booleans
• Change a table with ALTER TABLE

Keeping data correct by letting the database enforce the rules.

• Primary keys and surrogate keys
• NOT NULL and DEFAULT
• UNIQUE constraints
• CHECK constraints
• Foreign keys and referential integrity
• ON DELETE and ON UPDATE actions
• Composite keys

• Primary keys
• UNIQUE and CHECK constraints
• Foreign keys: linking two tables
• ON DELETE: CASCADE, RESTRICT and SET NULL

Adding, editing and removing rows safely.

• INSERT one row and many rows
• UPDATE with WHERE
• DELETE and TRUNCATE
• Generated ids and affected row counts
• Upserts
• Why a missing WHERE is dangerous
• Loading data from files

• INSERT one row and many rows
• UPDATE with a WHERE clause
• DELETE and TRUNCATE
• Upserts with ON DUPLICATE KEY UPDATE

Reading exactly the rows and columns you need, in the order you want.

• SELECT columns and aliases
• WHERE with comparison and logical operators
• IN, BETWEEN, LIKE and pattern matching
• NULL and IS NULL
• ORDER BY and LIMIT
• DISTINCT
• CASE expressions
• String functions
• Number and date functions

• SELECT columns and aliases
• Filter rows with WHERE
• NULL values and IS NULL
• ORDER BY and LIMIT
• DISTINCT and CASE expressions

Turning many rows into summaries and reports.

• COUNT, SUM, AVG, MIN and MAX
• COUNT(*) versus COUNT(column)
• GROUP BY one and several columns
• HAVING versus WHERE
• The logical order a SELECT runs in
• Monthly and per-customer report queries

• COUNT, SUM, AVG, MIN and MAX
• GROUP BY one and several columns
• HAVING versus WHERE
• The order a SELECT runs in

Combining data that is split across several tables.

• Why data is split across tables
• INNER JOIN
• LEFT and RIGHT JOIN
• FULL OUTER JOIN and CROSS JOIN
• Self joins
• Joining three or more tables
• Finding missing data with LEFT JOIN and IS NULL
• Join conditions and common mistakes

• Why data is split across tables, and joins
• INNER JOIN step by step
• LEFT and RIGHT JOIN
• Self join: employees and managers
• Finding missing data with LEFT JOIN ... IS NULL

Writing advanced queries that answer real business questions.

• Subqueries in WHERE, FROM and SELECT
• EXISTS and NOT EXISTS
• Common table expressions with WITH
• Views
• ROW_NUMBER, RANK and DENSE_RANK
• PARTITION BY and ORDER BY in windows
• Running totals, LAG and LEAD
• Classic interview questions: second highest, duplicates

• Subqueries in WHERE
• Common table expressions with WITH
• Views: a saved query as a virtual table
• Ranking with ROW_NUMBER, RANK and DENSE_RANK
• Running totals, LAG and LEAD

Planning tables so that data is stored once and stays consistent.

• Entities, attributes and ER diagrams
• One-to-one and one-to-many relationships
• Many-to-many with join tables
• Update, insert and delete anomalies
• First, second and third normal form
• When denormalising is reasonable

• Entities, attributes and ER diagrams
• Many-to-many with join tables
• Anomalies and why we normalise
• Second and third normal form

Why some queries are slow and how indexes and query plans help.

• Full table scan versus index lookup
• How a B-tree index works
• Creating single and composite indexes
• Reading EXPLAIN and EXPLAIN ANALYZE
• When an index is not used
• The cost of indexes on writes

• Full table scan versus index lookup
• How a B-tree index works
• Reading EXPLAIN
• When an index is not used

Keeping data correct when many users and failures are involved.

• START TRANSACTION, COMMIT and ROLLBACK
• ACID properties
• Dirty, non-repeatable and phantom reads
• Isolation levels
• Row locks with SELECT ... FOR UPDATE
• Deadlocks and how to avoid them

• START TRANSACTION, COMMIT and ROLLBACK
• ACID with a bank transfer
• Isolation levels in MySQL
• Deadlocks: what they are and how to avoid them

Design, build, query and tune a complete database, then meet PostgreSQL.

• Gathering requirements and designing the schema
• Writing the CREATE script and seed data
• Business report queries
• Backup and restore
• A SQL interview problem set
• PostgreSQL for MySQL users: what changes

• ShopLite project 1: requirements and schema design
• ShopLite project 2: CREATE script and seed data
• ShopLite project 3: business report queries
• SQL interview problem set
• PostgreSQL for MySQL users: what changes