SQL & Database Design

What you will be able to do

• Write joins, aggregates and subqueries
• Design normalised tables
• Use indexes and transactions

What you will learn

1. 8.1Databases & MySQL Setup8 lessons

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

   1. Why applications use a database25 min
   2. Tables, rows and columns30 min
   3. SQL, and why we start with MySQL30 min
   4. Install MySQL Server and MySQL Workbench35 min
   5. Run your first SQL in the mysql client35 min
   6. Create a database and a user35 min
   7. Load the practice databases35 min
   8. Practice: databases and setup45 min
2. 8.2Creating Tables & Data Types9 lessons

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

   1. CREATE TABLE and DROP TABLE35 min
   2. Number types and DECIMAL for money40 min
   3. Text types: VARCHAR, TEXT, CHAR and ENUM35 min
   4. Dates, times and booleans35 min
   5. Auto-generated ids with AUTO_INCREMENT35 min
   6. Change a table with ALTER TABLE40 min
   7. Naming, storage engines and character sets35 min
   8. Practice: tables and data types45 min
   9. Mini project: college library tables50 min
3. 8.3Keys & Constraints7 lessons

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

   1. Primary keys40 min
   2. NOT NULL and DEFAULT40 min
   3. UNIQUE and CHECK constraints45 min
   4. Foreign keys: linking two tables45 min
   5. ON DELETE: CASCADE, RESTRICT and SET NULL45 min
   6. Practice: keys and constraints50 min
   7. Mini project: hostel room allocation60 min
4. 8.4Inserting, Updating & Deleting Data7 lessons

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

   1. INSERT one row and many rows35 min
   2. UPDATE with a WHERE clause35 min
   3. DELETE and TRUNCATE35 min
   4. Generated ids and affected rows35 min
   5. Upserts with ON DUPLICATE KEY UPDATE40 min
   6. Practice: changing data45 min
   7. Mini project: student marks register50 min
5. 8.5Querying Data10 lessons

   Read exactly the rows and columns you need with SELECT.

   1. SELECT columns and aliases35 min
   2. Filter rows with WHERE40 min
   3. IN, BETWEEN, LIKE and REGEXP40 min
   4. NULL values and IS NULL40 min
   5. ORDER BY and LIMIT40 min
   6. DISTINCT and CASE expressions40 min
   7. String functions40 min
   8. Number and date functions45 min
   9. Practice: single-table queries50 min
   10. Mini project: IPL player statistics50 min
6. 8.6Aggregates & Grouping7 lessons

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

   1. COUNT, SUM, AVG, MIN and MAX40 min
   2. GROUP BY one and several columns45 min
   3. HAVING versus WHERE45 min
   4. The order a SELECT runs in40 min
   5. Report queries: sales per month and top customers50 min
   6. Practice: aggregates and grouping50 min
   7. Mini project: ShopLite monthly sales report60 min
7. 8.7Joins9 lessons

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

   1. Why data is split across tables, and joins30 min
   2. INNER JOIN step by step35 min
   3. LEFT and RIGHT JOIN40 min
   4. FULL OUTER JOIN and CROSS JOIN40 min
   5. Self join: employees and managers35 min
   6. Joining three or more tables40 min
   7. Finding missing data with LEFT JOIN ... IS NULL35 min
   8. Practice: joins50 min
   9. Mini project: railway reservation queries60 min
8. 8.8Subqueries, Views & Window Functions10 lessons

   Answer harder questions with queries inside queries, CTEs and ranking functions.

   1. Subqueries in WHERE35 min
   2. Subqueries in FROM and SELECT40 min
   3. EXISTS and NOT EXISTS40 min
   4. Common table expressions with WITH45 min
   5. Views: a saved query as a virtual table40 min
   6. Ranking with ROW_NUMBER, RANK and DENSE_RANK45 min
   7. Running totals, LAG and LEAD45 min
   8. Classic SQL interview questions50 min
   9. Practice: subqueries, views and window functions50 min
   10. Mini project: coding contest leaderboard60 min
9. 8.9Database Design & Normalisation9 lessons

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

   1. Entities, attributes and ER diagrams40 min
   2. One-to-one and one-to-many relationships40 min
   3. Many-to-many with join tables40 min
   4. Anomalies and why we normalise40 min
   5. First normal form (1NF)40 min
   6. Second and third normal form45 min
   7. When denormalising is reasonable45 min
   8. Practice: database design50 min
   9. Mini project: courier delivery database60 min
10. 8.10Indexes & Query Performance9 lessons

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

    1. Full table scan versus index lookup40 min
    2. How a B-tree index works40 min
    3. Creating indexes45 min
    4. Reading EXPLAIN45 min
    5. EXPLAIN ANALYZE: real timings45 min
    6. When an index is not used50 min
    7. The cost of indexes on writes40 min
    8. Practice: indexes and query plans50 min
    9. Mini project: speed up ShopLite queries60 min
11. 8.11Transactions & ACID8 lessons

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

    1. START TRANSACTION, COMMIT and ROLLBACK40 min
    2. ACID with a bank transfer40 min
    3. Dirty, non-repeatable and phantom reads45 min
    4. Isolation levels in MySQL45 min
    5. Row locks with SELECT ... FOR UPDATE50 min
    6. Deadlocks: what they are and how to avoid them45 min
    7. Practice: transactions and concurrency55 min
    8. Mini project: cinema seat booking60 min
12. 8.12SQL Practice, ShopLite & PostgreSQL7 lessons

    Design and query the ShopLite store database that the next sections build on, practise interview SQL, and port the database to PostgreSQL.

    1. ShopLite project 1: requirements and schema design60 min
    2. ShopLite project 2: CREATE script and seed data60 min
    3. ShopLite project 3: business report queries60 min
    4. Backups with mysqldump and restoring40 min
    5. SQL interview problem set50 min
    6. PostgreSQL for MySQL users: what changes45 min
    7. ShopLite project 4: ShopLite on PostgreSQL60 min