Last-Minute Preparation Tips for a Database Class Exam

Getting to learn SQL is fun. You don’t really need to spend much time to understand some basic SQL concepts and use them fluently. If you’re a student facing your first database class exam soon, we’ve got you covered. In this article, we explain what you should focus on while learning SQL and studying for an entry-level database exam. Check your knowledge with our 10 sample questions and discover where you can find more SQL practice.

Relational databases are used all around the world. They are a very popular way of storing massive amounts of data that can then be easily retrieved with the SQL language. It’s no wonder that many universities offer basic classes in databases to a wide range of students.

These database classes may be taught in a variety of ways depending on the type of degree curriculum. You may expect a slightly different approach when databases are just an addition to your degree curriculum in economics, compared to when you’re studying computer science. In each case, however, you will most probably have to learn SQL.

In this article, we’ll try to get you prepared for your first database exam. Inevitably, we’ll have to make some assumptions about the curriculum. We recommend that you compare the materials contained in this article with the materials taught in your database class to make sure you learn the right things.

This article assumes that you’ve heard about (relational) databases in general and that you know what a table in a database looks like. If that’s not the case, take a look at our article titled Essential SQL Terms to Know for Beginners and Pros. You can also watch my YouTube tutorial—it is one of the episodes of the We Learn SQL series.

Entry-level database classes typically focus on retrieving data with SELECT statements written in the SQL language. In these classes, your instructor assumes that there is a database with a predefined set of tables already available, and that all you have to do is to get the right information out of those tables. This is also the most frequent use case in your daily work—unless you’re a database administrator, you’ll typically retrieve data from existing databases rather than create them from scratch. Consequently, this article focuses on learning to use SELECT statements.

Chances are, however, that your database class exam will also cover other SQL instructions that aim at creating, modifying, and deleting tables and/or data inside tables. That’s why we’ve also included two sample questions of this kind with references to additional study materials. However, if your curriculum only contains SELECT statements, which is highly probable in an entry-level class, you may skip the last two questions altogether.

Below, we present ten sample questions about SQL that you may come across in a database exam. Each time, you’ll find a database problem (possible exam question) with an answer (SQL instruction) and an explanation. We will also tell you where you can find more information about a given topic if you want to deepen your knowledge. The questions are presented from the easiest to the most difficult. At the end, we also explain where you can practice SQL queries once you know the theory.

Remember to take breaks while you study! You can also read the tips we present in an article titled How to Stay Healthy When Learning SQL.

As we mentioned earlier, most of the questions are on SELECT statements, which means that you will be writing queries for an already defined table or set of tables. Your database instructor may present the structure of those tables in a variety of ways. We’ll show them to you as entity relationship diagrams (ERDs) and as tables with sample rows. For your exam, however, you may only see one or the other, so find out which database presentation technique your instructors use.

So, without further ado, let’s get started!

Question 1: Simple SELECT Statement

Task: You are given the following table named student. Write a query which will SELECT the first and last name of each student.

Answer:

SELECT first_name, last_name
FROM student;

Explanation: This is one of the simplest SQL statements we can write. It consists of two clauses: SELECT and FROM. SELECT lists all the columns we want to show while FROM specifies the table from which we want to query.

Additional materials: Take a look at “Part 2: Selecting from one table” in our SQL Basics course.

Question 2: Simple SELECT With WHERE

Task: You are given the following table named class. Show the name and the rating of every class with the rating above 4.0.

Answer:

SELECT name, rating
FROM class
WHERE rating > 4.0;

Explanation: In this case, we need to add a WHERE clause which is used to filter rows. Since we need courses with a rating above 4.0, we use the following condition: rating > 4.0. By the same token, you can use other operators such as < (less than) or != (not equal to).

Additional materials: Take a look at “Part 2: Selecting from one table” in our SQL Basics course.

Question 3: SELECT With a Complex WHERE Clause

Task: You are given the following table named lecturer. Show the first name, last name, and the rating of each lecturer from departments with Department ID of 1 or 2, whose rating is 4.50 or higher.

Answer:

SELECT first_name, last_name, rating
FROM lecturer
WHERE rating > 4.50 AND (department_id = 1 OR department_id = 2);

Explanation: In this question, we introduce logical operators (AND and OR) in the WHERE clause. They are used to specify more than one condition. In this case, we need lecturers with a rating greater than 4.50 and with a specific department_id. We may also introduce brackets to specify the order of operators. Note: if you know the IN operator, you can simply reduce the last clause to: WHERE rating > 4.50 AND department_id IN (1, 2);

Additional materials: Take a look at “Part 2: Selecting from one table” in our SQL Basics course.

Question 4: SELECT With Row Sorting

Task: You are given the following table named lecturer. Show all the information for each lecturer. Sort the rows in the ascending order by department_id, and then in the descending order by the rating.

Answer:

SELECT *
FROM lecturer
ORDER BY department_id ASC, rating DESC;

Explanation: This time, we have to select all the columns. Rather than write all the column names separated by commas, we use the asterisk (*) which means “all columns” in SQL. Then, we introduce row sorting with the ORDER BY clause. ASC is used to get the ascending order while DESC is used for the descending order.

Additional materials: Take a look at “Part 4: Aggregation and grouping” in our SQL Basics course.

Question 5: SELECT With a JOIN

Task: You are given the following two tables: lecturer and department. Write a query that will show the first and last names of each lecturer alongside the name of his/her department. The name of the department should be shown in a column named department_name.

Answer:

SELECT first_name, last_name, name AS department_name
FROM lecturer JOIN department ON lecturer.department_id = department.id;

Explanation: In this exercise, we have to join two tables together. That’s why there is a JOIN clause following the FROM clause. The ON clause is used to specify which two columns from both tables should match. Note that we also use AS in the SELECT clause to specify an alias for the column name from the department table.

Additional materials: Take a look at “Part 3: Querying more than one table” in our SQL Basics course. You can also watch our video about JOIN basics on YouTube.

Question 6: Simple Aggregate Functions

Task: You are given the following table named class. Write a query that will show the average class rating in a column named avg_rating.

Answer:

SELECT AVG(rating) AS avg_rating
FROM class;

Explanation: Even though the question may seem difficult at first, it’s actually pretty simple to show the average value from a column in SQL. All we have to do is use the AVG(column_name) aggregate function in the SELECT clause.

Additional materials: Take a look at “Part 4: Aggregation and grouping” in our SQL Basics course. We also have a video on YouTube devoted to basic aggregate functions.

Question 7: Aggregate Functions With GROUP BY

Task: You are given the following two tables: lecturer and department. Write a query that will show the ID and the name of each department alongside the average rating over all lecturers from that department. Sort the rows in the descending order by the average rating.

Answer:

SELECT department.id, name, AVG(rating)
FROM lecturer JOIN department ON lecturer.department_id = department.id
GROUP BY department.id, name
ORDER BY AVG(rating) DESC;

Explanation: This is a slightly more complex query to write. We need to join two tables like we have done in Question 5. Then, we have to group all rows by their department ID and name with a GROUP BY clause. When we have that in place, we can write our SELECT clause, where we include the two columns from the GROUP BY clause, and we also throw in the AVG(column_name) function.

Additional materials: Take a look at “Part 4: Aggregation and grouping” in our SQL Basics course. You can also watch our video on YouTube about the SQL GROUP BY clause.

Question 8: Simple Subqueries

Task: You are given the following table named lecturer. Write a query that will show all the information for every lecturer whose rating is greater than or equal to the rating of the lecturer with ID 4.

Answer:

SELECT *
FROM lecturer
WHERE rating >= (SELECT rating FROM lecturer WHERE id = 4);

Explanation: Subqueries are “queries within queries” and are introduced within parentheses. They may appear in various places of an SQL instruction. The example you see here is a simple single-value subquery in a WHERE clause.

Additional materials: Take a look at “Part 6: Subqueries” in our SQL Basics course. You can also watch our video about simple subqueries available on YouTube.

Question 9: Creating Tables

Task: Write a CREATE instruction in SQL that will create a table named degree_course with the following fields:

• id: an integer field; cannot be null
• max_students: an integer field; cannot be null
• name: a text field of up to 64 characters; cannot be null
• is_paid: a yes/no field; may be null

Answer:

CREATE TABLE degree_course (
   id int  NOT NULL,
   max_students int  NOT NULL,
   name varchar(128)  NOT NULL,
   is_paid bool
);

Explanation: Here, we no longer write a SELECT statement that retrieves data from predefined tables. Instead, we provide the definition of a new table. Creating and modifying tables is a slightly more advanced topic which requires the knowledge of data types. Chances are that your database exam will not contain this type of question.

Additional materials: Take a look at our Creating Tables in SQL course.

Question 10: Inserting and Modifying Data in Tables

Task: You are given the following table named degree_course.

Write a query which will insert a new row into the table with the following information:

• Course ID: 5
• Maximum number of students: 50
• Name: Object Oriented Programming
• Is it paid: No

Answer:

INSERT INTO public.degree_course(
	id, max_students, name, is_paid)
	VALUES (5, 50, 'Object Oriented Programming', false);

Explanation: The topic of inserting, updating, and deleting data in SQL is another that may not be covered in your entry-level database exam.

Additional materials: Check out our How to INSERT, UPDATE, and DELETE Data in SQL course on LearnSQL.com.

More Practice

Even if you could answer the questions above, you may still want to get some additional SQL practice to make sure you pass your exam with flying colors. If that’s the case, we highly recommend that you take a look at our SQL Practice Track which consists of two courses: a general SQL Practice Set course which covers most of the concepts mentioned in this article, and a detailed SQL Joins course which focuses on joining two or more tables. We know JOINs can be quite tricky, so be sure to check out these courses! This is really the best place to practice SQL queries.

Get Ready for Your Exam!

Learning SQL is fun! If you were able to correctly answer the ten questions above, you’re likely well prepared for your basic SQL exam. However, don’t worry if you’ve struggled—there are many online resources to improve your skills. If you don’t feel like setting up your own database or if you simply don’t have the time, try our LearnSQL.com platform. All you need to learn is a web browser. Everything from the database, the tables, to the data, is predefined for you to speed up the learning process. See you there!