SyntaxPilot
Courses/SQL and Databases

Beginner · Free course

SQL and Databases

Real SQL on a real MySQL server: queries, joins, design and performance.

90 lessons1 videos so far11 min of videoNotes for every lesson

What you’ll learn

  • Tables and SELECT
  • Joins and aggregation
  • Schema design and normalisation
  • Indexes and transactions
0 of 90 complete

Curriculum

  1. 1What a database is: tables, rows, columns and SQLsoon
  2. 2Running SQL: sqlite3 from Python and the SQLite shellsoon
  3. 3Your first SELECT: columns, expressions and aliases11 min
  4. 4Filtering rows with WHERE and comparison operatorssoon
  5. 5Combining conditions: AND, OR, NOT, IN and BETWEENsoon
  6. 6Pattern matching with LIKEsoon
  7. 7NULL and three-valued logicsoon
  8. 8Sorting and limiting: ORDER BY, LIMIT, OFFSET, DISTINCTsoon
  9. 9Data types across SQLite, MySQL and PostgreSQLsoon
  10. 10Creating and changing tables: CREATE, ALTER, DROPsoon
  11. 11Primary keys: natural, surrogate and compositesoon
  12. 12Adding data with INSERTsoon
  13. 13Changing data safely: UPDATE and DELETEsoon
  14. 14Upserts and conflict handlingsoon
  15. 15Numeric expressions, CAST and type conversionsoon
  16. 16String functions and collationsoon
  17. 17Dates and times 1: SQLite date functionssoon
  18. 18Dates and times 2: time zones, MySQL and PostgreSQLsoon
  19. 19Conditional logic with CASEsoon
  20. 20Aggregate functions: COUNT, SUM, AVG, MIN, MAXsoon
  21. 21Grouping rows with GROUP BYsoon
  22. 22HAVING and the logical order of a querysoon
  23. 23Relationships and foreign keyssoon
  24. 24Constraints and referential actionssoon
  25. 25Joins 1: INNER JOINsoon
  26. 26Joins 2: LEFT, RIGHT and FULL OUTER joinssoon
  27. 27Joins 3: CROSS, self, non-equi and multi-table joinssoon
  28. 28Subqueries 1: scalar, IN and derived tablessoon
  29. 29Subqueries 2: correlated, EXISTS, ANY, ALL and LATERALsoon
  30. 30Set operations: UNION, INTERSECT, EXCEPTsoon
  31. 31Common table expressions and temporary tablessoon
  32. 32Views and materialized viewssoon
  33. 33Normalization 1: anomalies and first normal formsoon
  34. 34Normalization 2: 2NF, 3NF, BCNF and denormalizationsoon
  35. 35Entity-relationship modellingsoon
  36. 36Designing a schema: an online storesoon
  37. 37Window functions 1: OVER, PARTITION BY and rankingsoon
  38. 38Window functions 2: running totals and share of totalsoon
  39. 39Window functions 3: frames with ROWS, RANGE and GROUPSsoon
  40. 40Window functions 4: LAG, LEAD and value functionssoon
  41. 41Window functions 5: NTILE, percentiles and QUALIFYsoon
  42. 42Recursive CTEs 1: generating series and date spinessoon
  43. 43Recursive CTEs 2: trees, hierarchies and graphssoon
  44. 44Pivoting and unpivotingsoon
  45. 45Subtotals with GROUPING SETS, ROLLUP and CUBEsoon
  46. 46JSON in SQL 1: storing and extracting JSONsoon
  47. 47JSON in SQL 2: arrays, unnesting and JSON indexessoon
  48. 48Full-text searchsoon
  49. 49Advanced DDL: generated columns, sequences and schemassoon
  50. 50Triggerssoon
  51. 51Stored procedures and user-defined functionssoon
  52. 52SQL from application code: parameters, injection and ORMssoon
  53. 53Transactions and ACIDsoon
  54. 54Concurrency anomalies: dirty reads to write skewsoon
  55. 55Isolation levels in practicesoon
  56. 56MVCC: how databases keep many versions of a rowsoon
  57. 57Locking 1: shared, exclusive and row lockssoon
  58. 58Locking 2: deadlocks, optimistic and pessimistic lockingsoon
  59. 59How databases store data: pages, WAL and the buffer poolsoon
  60. 60Indexes 1: what an index is and when it helpssoon
  61. 61Indexes 2: B-tree and B+tree internalssoon
  62. 62Indexes 3: clustered, secondary and special index typessoon
  63. 63Indexes 4: composite, covering, partial and expression indexessoon
  64. 64The query planner: reading EXPLAINsoon
  65. 65Join algorithms and physical operatorssoon
  66. 66Query optimisation 1: rewriting slow queriessoon
  67. 67Query optimisation 2: measuring, hints and bulk loadingsoon
  68. 68Partitioning large tablessoon
  69. 69Replication basicssoon
  70. 70Scaling out: sharding and distributed SQLsoon
  71. 71Database security: users, roles and privilegessoon
  72. 72Operations: backups, recovery and schema migrationssoon
  73. 73OLTP vs OLAP and data warehousessoon
  74. 74Dimensional modelling: star and snowflake schemassoon
  75. 75Fact table types and slowly changing dimensionssoon
  76. 76Analytics SQL patterns: sessions, gaps and islands, cohortssoon
  77. 77SQL vs NoSQL and choosing a databasesoon
  78. 78SQL interview 1: rapid-fire concept questionssoon
  79. 79SQL interview 2: Nth highest salary and ranking puzzlessoon
  80. 80SQL interview 3: finding and deleting duplicatessoon
  81. 81SQL interview 4: top-N per group and latest recordsoon
  82. 82SQL interview 5: consecutive days, streaks and gapssoon
  83. 83SQL interview 6: running totals, moving averages and growthsoon
  84. 84SQL interview 7: anti-joins, pivots and reporting puzzlessoon
  85. 85SQL interview 8: product metrics questionssoon
  86. 86SQL interview 9: hierarchy, graph and self-join puzzlessoon
  87. 87SQL interview 10: design, performance and transaction scenariossoon
  88. 88Project 1: analytics for a food-delivery appsoon
  89. 89Project 2: a concurrent ticket-booking systemsoon
  90. 90Capstone: build, tune and model a complete e-commerce databasesoon