100 SQL Exercises

Written by: Joseph Anthony Debono.
Email: Joseph Debono.

Licence: MIT

These are a set of exercises to introduce and drill students in the fundamentals of SQL. Each exercise poses a challenge and provides relevant notes and hints to the solution. A solution has also been included for every exercise.

Learning Outcomes

1. Basic SQL Queries: Students will learn to write simple queries to retrieve and manipulate data from databases. This includes using SELECT statements, WHERE clauses, and basic filtering and sorting operations.

2. Advanced SQL Techniques: The exercises cover more complex SQL concepts such as joins (INNER, OUTER, and CROSS), subqueries, and set operations. This helps in understanding how to combine data from multiple tables and perform more sophisticated data analysis.

3. Data Manipulation: Students will practice data insertion, updates, and deletions using INSERT, UPDATE, and DELETE commands. These skills are crucial for maintaining and managing database records.

4. Database Schema Understanding: Through exercises that involve working with the HR schema, students will gain insights into relational database design, including primary and foreign keys, and how tables relate to each other within a schema.

Recommended approach

These exercises are structured in ascending order of complexity, and each exercise is built upon the previous one. Interspersed among the exercises are refresher ones. The idea is that each subsequent exercises builds on previous ones, not only dripping in new knowledge sequentially but ensuring that new exercises build, and exercise, skills and knowledge acquired from previous ones.

The recommended approach is to work your way through every exercise, drilling each one repetitively. Before you move on to the next exercise, make sure that you can work out the previous exercise fluently and easily, preferably using multiple approaches to solve the exercise. Every so many exercises, return to previous ones and make sure you can still solve them with ease. This drilling approach will ensure that you layer your knowledge on solid foundations, and assimilate concepts, syntax and techniques such that they become second nature.

Links

Oracle SQL and MySQL implement the SQL standard a little differently. For this reason, these exercises have been adapted to both implementations.

For exercises that have been adapted to Oracle SQL,
click here: Oracle SQL Exercises.
For exercises that have been adapted to MySQL,
click here: MySQL exercises.

Sources

The material, schemas, and data have been adapted from the following sources:

1. Beighley, Lynn, Headfirst SQL;
2. Beaulieu, Alan, Learning SQL 3rd Edition;
3. DeBarros, Anthony, Practical SQL 2nd edition;
4. Forta, Ben, Sams Teach Yourself SQL in 10 Minutes 5th Edition;
5. Silva, Rick, MySQL Crash Course;

---