SQL Practice for Students: 11 Exercises with Solutions

Calling all students! Are you SQL-curious? Want to learn or improve your SQL skills? This article has 11 SQL practice exercises with detailed solutions.

Have you ever heard that “Practice makes perfect”? You probably have – that’s why you’re looking for SQL practice resources. If you want to develop strong skills or become an expert in a certain domain, you have to practice.

As Rebecca McKeown wrote in her outstanding article Learning SQL? 12 Ways to Practice SQL Online: “All theory and no practice will make you a frustrated SQL user!”. And she’s right! Every time you learn a new concept, you need to apply it right away by practicing. That’s the point behind the exercises presented in this article.

Most of the following exercises are taken from our interactive course SQL Practice: University. This interactive online course is designed for people who know SQL and are looking for practice exercises that cover single table queries, joins, sorting, grouping, aggregation, and more. It’s part of our SQL Practice track, which contains more SQL practice courses for beginners.

Overview of an interactive exercise on LearnSQL.com.

But maybe you don’t want to commit to a full course just yet. That’s okay! These 11 exercises will help you practice and improve your SQL skills – no cost and no commitment! Fasten your seatbelt, and let’s start!

Improve Your SQL Practice

Our SQL practice exercises are a perfect match for students: I will be using the university model, something you should be familiar with! Here are the tables we will be using during the exercises:

• student contains basic data related to university students: first name, last name, email, birthdate, and start date.
• lecturer contains basic data related to lecturers: first name, last name, degree, and email.
• academic_semester contains basic information related to the academic semesters: calendar year, term, start date, and end date.
• course_edition is a table used to join the three other tables: course (which we won’t use in this article), academic_semester, and lecturer.

If you want to practice using the full university database, go to the Basic SQL Practice: University course. (We have a special offer and special prices for students and teachers! If you are interested, check our SQL courses for Students deal.)

11 Basic SQL Practice Exercises

In this section, I will provide 11 SQL practice exercises with solutions. The difficulty will increase gradually: I will start with very easy and generic exercises and introduce new SQL concepts little by little.

Are you ready to improve your SQL practice?

Exercise 1: List All Students

Exercise

Select all data for the students in the database.

Solution

SELECT * 
FROM student;

Explanation

We want to retrieve student information from the database. Therefore, we have to use the SELECT clause and the FROM clause on the student table. The asterisk symbol (*) is used here as shorthand to represent the names of all the columns in the table students. Note that all SQL statements end with the semicolon character (;).

Exercise 2: List All Student Names

Exercise

Select the first name and the last name of all students.

Solution

SELECT first_name, last_name
FROM student;

Explanation

This time, we want to retrieve partial information from the database. We can easily do it by indicating the name of the columns we want to retrieve – in this case, first_name and last_name. The names of the columns are separated with a comma and followed by the FROM statement with the table name (student).

Exercise 3: Select a Specific Lecturer by ID

Exercise

Select the email for the lecturer with the ID of 5 from the database.

Solution

SELECT email
FROM lecturer
WHERE id = 5;

Explanation

This time, we want to retrieve lecturer information from the database. Therefore, we have to use the SELECT clause and the FROM clause on the lecturer table.

In a database, each table usually has a primary key column (generally called ID) that represents a unique identifier for each entry. In this exercise, we retrieve only one lecturer (i.e. one row) from the lecturer table. We do this with the WHERE clause with the condition id=5.

In this case, we’re using the ‘equal’ comparison operator (=). We could also write a condition with other comparison operators, such as >, <, >=, <=, or <> (not equal to).

Exercise 4: Select Students by Last Name

Exercise

Select all data for any student whose last name is Durrand.

Solution

SELECT *
FROM student
WHERE last_name = 'Durrand';

Explanation

Back to the student table! In this exercise, we want to retrieve all students whose last name is Durrand. The column last_name allows duplicate values; therefore, when we use the WHERE clause, multiple rows can be returned.

Note that single quotes (') are used in SQL to enclose text values (‘Durrand’).

Exercise 5: Select Students Whose Last Name Starts with D

Exercise

Select the first and last names of students whose last name starts with the letter D.

Solution

SELECT first_name, last_name
FROM student
WHERE last_name LIKE 'D%';

Explanation

In this exercise, we are not looking for students with an exact last name but for students whose last name starts with a specific letter (D). The comparison operator equal (=) cannot be used in this exercise because we are not looking for an exact match. Fortunately, SQL provides the LIKE operator that allows us to look for a specified pattern.

Using the WHERE clause in combination with the LIKE operator on the column last_name does the trick! The percent character (%) is used to represent zero, one, or multiple characters. Therefore, ‘D%’ here means “A name that starts with D followed by zero, one, or many more characters”.

Do you like the exercises in this article? You can find more in the excellent article 10 Beginner SQL Practice Exercises With Solutions by my friend Tihomir Babic.

Exercise 6: Use Multiple Conditions to Select an Academic Semester

Exercise

Select all data for academic semesters where these two conditions are met: the year is 2020 and the term is spring.

Solution

SELECT *
FROM academic_semester
WHERE calendar_year = 2020
  AND term = 'spring';

Explanation

This time, we are asked to retrieve information for academic semesters. We are going to perform a SELECT clause on the academic_semester table to achieve it. In comparison with the previous exercises, something is new: we have 2 conditions!

You may wonder if multiple conditions are allowed in SQL; yes, they are! The only thing you have to do is use a logical operator in combination with the WHERE clause. SQL provides three logical operators: AND, OR, and NOT. In this exercise, we want the 2 conditions to be met, so we use the logical operator AND.

Note that the calendar_year column is an integer type, while the term is a string. That’s why we don’t use any quotes when searching for 2020 in calendar_year and but we use single quotes searching term for ‘Spring’.

Exercise 7: Sort Students by Last Name

Exercise

Select the last name and the birthdate for students born in or after 2003 and sort them by last name in descending (Z to A) order.

Solution

SELECT last_name, birth_date
FROM student
WHERE birth_date >= '2003-01-01'
ORDER BY last_name DESC;

Explanation

In this exercise, we retrieve all students born on or after January 1st, 2003. Therefore, our filter will apply to the column birth_date in the student table.

If you look again at the database diagram I provided at the beginning of this article, you will figure out that the column birth_date has a date data type.

In SQL, dates are stored in the following order: year, month, and day. We call it the yyyy-mm-dd format. For example, January 10, 2023, would be stored as 2023-01-10 in a database.

SQL also uses single quotes to enclose dates (January 10th, 2023 is written as ‘2023-01-10’) and comparisons are allowed on dates. Therefore, to build the query we need, we use the WHERE clause with the comparison symbol greater than or equal to (>=) on the birthdate.

We next order the result by last name thanks to the ORDER BY keyword. The DESC keyword indicates here that the result will be sorted in descending order (from Z to A). If you want to sort by ascending order, use the ASC keyword.

Exercise 8: Students Born Between 2003 and 2004

Exercise

Select the first name, last name, and birthdate for students born between 2003 and 2004 from the database.

Solution

SELECT first_name, last_name, birth_date
FROM student
WHERE birth_date >= '2003-01-01'
AND birth_date <='2004-12-31';

Explanation

In this exercise, we retrieve records for all students born on or after January 1st, 2003, and before or on December 31st, 2003. We use the SELECT clause.

We have seen in the previous exercise that comparisons are allowed on dates, so our condition here is  birth_date >= '2003-01-01' AND birth_date <='2004-12-31'

Exercise 9: Count Students for Each Start Date

Exercise

Select the start date for all students and show how many students have the same start date.

Solution

SELECT start_date, COUNT(*)
FROM student
GROUP BY start_date;

Explanation

In this exercise, we want to retrieve the start date for all students and show how many students have the same start date. So, basically, we need one column that shows the start dates without duplicate values and another column that shows the number of students with identical start dates.

The GROUP BY statement allows grouping the rows with the same values (here, the start date). Additionally, we use the aggregate function COUNT() to show the number of rows that match the start date value.

Exercise 10: Select, Group, and Filter the Results

Exercise

Find start dates on which there were more than 15 students. Display the start date and the number of students that started on this date.

Solution

SELECT start_date, COUNT(*)
FROM student
GROUP BY start_date
HAVING COUNT(*) > 15;

Explanation

We want to perform the same query as in the previous exercise, but with a slight difference: we want to filter the results to show only those that match a certain amount.

In the same way, we perform a SELECT on the student table. We group the result by start date and use the COUNT() aggregate function. Finally, we use the HAVING clause at the end of the query to remove start dates that have less than 15 students from the result set.

Exercise 11: How Many Courses Does Each Lecturer Teach?

Exercise

For every lecturer, find out how many courses they teach in each academic semester. Display the first and last name of the lecturer, the calendar year, the term, and the count of courses taught by the lecturer in this semester.

Solution

SELECT
   lec.first_name,
   lec.last_name,
   acs.calendar_year,
   acs.term,
   COUNT(course_id)
FROM lecturer AS lec
JOIN course_edition AS ce 
  ON ce.lecturer_id = lec.id
JOIN academic_semester AS acs 
  ON ce.academic_semester_id = acs.id
GROUP BY lec.id, acs.id;

Explanation

This exercise is a bit more difficult. We have to join three tables: lecturer, course_edition, and academic_semester. We do this by usingthe JOIN operator. Then we use the GROUP BY clause to group course editions taught by the same lecturer in the same academic semester together. Finally, we use the COUNT() function to count how many courses a lecturer has in this semester.

The article SQL Joins: 12 Practice Questions with Detailed Answers by Tihomir Babic has more SQL join practice if you’re interested.

Itching for More SQL Practice?

I really hope this article made you practice your SQL skills and inspired you to start a SQL learning path!

If you like the exercises in this article, remember that most of them are taken from the course SQL Practice: University on LearnSQL.com. It’s part of our SQL Practice track, which contains 8 more courses and nearly 1,000 exercises; that’s a lot of hands-on experience!

If you want to dive deep into SQL, I recommend our All Forever SQL Package. This offer contains all current and future SQL courses in four different SQL dialects. We also have a special deal for students.

Thanks for reading this article; see you in the next one!