A 60-day study plan for MySQL and SQL
AdvertisementSQL is one of the most useful skills in technology. Analysts, developers, testers and managers all need to ask questions of data, and MySQL is among the most widely used databases. The free MySQL course has 50 lessons in nine sections. At roughly one lesson a day with weekly reviews and a revision week, you can finish in about 60 days. This plan shows how to schedule it, what to practise each week and how to know you are ready for the exam.
How the plan is built
- Lessons are short (about ten minutes), but SQL needs practice. Plan 30 to 60 minutes a day: one lesson, then queries of your own.
- Weekly reviews. Every seventh day is a review and a mini project, not new material.
- One database. The course uses a single sample shop database throughout. Install it on day 4 and keep using it. Practising on the same data means you start to remember what is in each table.
- Revision week. The last week before the exam is for revising, not for learning new things.
Weeks 1 and 2: getting started and reading data (days 1 to 14)
| Day | Lessons |
|---|---|
| 1 | What MySQL is and how it works |
| 2 | Install MySQL |
| 3 | Connect and run your first commands |
| 4 | The sample shop database |
| 5 | SELECT: reading rows and columns |
| 6 | WHERE: filtering rows |
| 7 | Review: write ten queries on the customers table |
| 8 | AND, OR, NOT, IN and BETWEEN |
| 9 | ORDER BY and LIMIT |
| 10 | LIKE, wildcards and REGEXP |
| 11 | DISTINCT and aliases |
| 12 | NULL values |
| 13 | INSERT: adding rows |
| 14 | Review: answer ten questions about the shop using only SELECT |
Checkpoint: given a question in English ("which products cost more than 500 and are in stock?"), you can write the query without help.
Practice idea: invent 20 questions about the shop and answer them, then check your results. Write the question first, as you would hear it from a manager.
Weeks 3 and 4: changing data, functions and summaries (days 15 to 28)
| Day | Lessons |
|---|---|
| 15 | UPDATE: changing rows |
| 16 | DELETE and TRUNCATE |
| 17 | Upserts: ON DUPLICATE KEY, IGNORE and REPLACE |
| 18 | String functions |
| 19 | Numeric functions |
| 20 | Date and time functions |
| 21 | Review: clean up and change data on a copy of a table |
| 22 | CASE, IF, IFNULL and COALESCE |
| 23 | COUNT, SUM, AVG, MIN and MAX |
| 24 | GROUP BY |
| 25 | HAVING and WITH ROLLUP |
| 26 | Practice day: a sales report by month |
| 27 | Practice day: the top five customers |
| 28 | Review: redo the quizzes from days 15 to 25 |
Checkpoint: you can summarise data (totals per customer, orders per month) and you know why a condition on an aggregate goes in HAVING.
Important habit: always test UPDATE and DELETE with a SELECT using the same WHERE first. Work on a copy of the table while learning.
Weeks 5 and 6: combining tables (days 29 to 42)
| Day | Lessons |
|---|---|
| 29 | INNER JOIN |
| 30 | LEFT JOIN and RIGHT JOIN |
| 31 | Self joins and cross joins |
| 32 | UNION and UNION ALL |
| 33 | Subqueries |
| 34 | EXISTS, ANY and ALL |
| 35 | Review: draw the tables and write three join queries |
| 36 | CREATE DATABASE and CREATE TABLE |
| 37 | Data types |
| 38 | Primary keys and AUTO_INCREMENT |
| 39 | NOT NULL, UNIQUE, DEFAULT and CHECK |
| 40 | Foreign keys |
| 41 | ALTER TABLE and DROP TABLE |
| 42 | Table design and normalisation, then review |
Checkpoint: you can explain the difference between join types using a drawing, and you can design three related tables for a small system, such as a library with books, members and loans.
Practice idea: create a database of your own, perhaps for a hobby, with at least three tables linked by foreign keys, add ten rows to each and write five join queries.
Weeks 7 and 8: faster, safer and advanced (days 43 to 56)
| Day | Lessons |
|---|---|
| 43 | Indexes |
| 44 | EXPLAIN and query optimisation |
| 45 | Views |
| 46 | Transactions |
| 47 | Storage engines and character sets |
| 48 | Window functions |
| 49 | Review: run EXPLAIN before and after adding an index |
| 50 | Common table expressions (WITH) |
| 51 | Working with JSON |
| 52 | Stored procedures and functions |
| 53 | Triggers |
| 54 | Variables and prepared statements |
| 55 | Users and privileges; Backup and restore |
| 56 | Using MySQL from code and avoiding SQL injection |
The last section of the course also contains the final project, reports for the shop. Do it at the end of this stretch, or move it into the revision week.
Checkpoint: you can explain what an index is for, what a transaction guarantees, and how a prepared statement protects you.
Week 9: revision and exam (days 57 to 60)
| Day | Task |
|---|---|
| 57 | Redo the project from a blank file |
| 58 | Read the summary line at the top of each lesson; list what you cannot explain |
| 59 | Go back to your list, rerun the examples, redo the quizzes |
| 60 | Take the final exam |
The exam is 15 questions in 30 minutes, with a 70% pass mark (11 correct) and three attempts per 24 hours. Read MySQL certificate: how the final exam works for the topics that catch people out, such as NULL behaviour, join types and WHERE versus HAVING.
A daily routine
- Read the lesson and run its examples exactly as written.
- Change them. Alter a condition, add a column, swap the join, and predict the result before you run it.
- Write two queries of your own that use the lesson's idea.
- Do the quiz and the exercise.
- Keep a file of your best queries with a comment above each one. It becomes your reference.
Tips for learning SQL
- Read results critically. When a query returns a number, ask whether it is plausible.
- Learn to read error messages, which usually tell you the exact place of the problem.
- Write queries in a consistent layout, one clause per line, so you can see the structure.
- Never practise destructive commands on data you care about.
- Say the query in English. "Select the name and total from orders joined to customers, where the total is above 1000, sorted by total, highest first." If you can say it, you can write it.
- Use the references. The MySQL cheat sheet and the SQL joins cheat sheet are useful, as are the MySQL interview questions once you have finished.
If you only have half the time
Do the course in 100 days at a lesson every other day, or compress to 30 days with two lessons daily. Whichever pace you choose, keep the order and keep practising. If you know HTML and CSS already, a good previous step is the 30-day HTML and CSS plan; if you plan to use MySQL from a web page, the PHP course comes next.
Quick answers
Can a beginner finish this in 60 days? Yes, at about 30 to 60 minutes a day.
Do I need programming experience? No.
Which version of MySQL should I install? A current supported release. The install lesson explains how.
Do I need to take the exam? No, but the certificate is proof of completion.
What should I do after? Build a small web app with PHP and MySQL, and practise on real data sets.