I wrote these 17 questions to give you an idea about how good you are with SQL.

Why I wrote this post

Recently I had to brush up my SQL skills and I wanted to write a post about it.

Online there are already good SQL tutorials, so I decided to write a test with questions to help you to practice SQL.

Before you start:

  • The DB engine used for this test is PostgreSQL, although other ones like MySQL should be good. You'll not find complex questions that need to leverage PostgreSQL capabilities.
  • This test uses a sample DB called DVD Rental, which is commonly used for learning purposes.

I suggest you to:

I hope you'll enjoy it. Let's start!

General Knowledge

  1. What does SQL mean?

    Structured Query Language

  2. What are the 2 most popular open source SQL DB engines?

    MySQL and PostgreSQL (https://db-engines.com/en/ranking)

  3. What are the 2 most popular commercial SQL DB engines?

    Oracle and Microsoft SQL Server

  4. Which of these DB engines is non relational and does not support SQL?

    • MariaDB
    • MongoDB
    • SQLite

    MongoDB: it is a document-oriented DB engine

SQL Queries

Given the tables:

customer: customer_id | first_name | last_name | email | create_date | ...

and

address: address_id | address | address2 | district | city_id | postal_code | phone | last_update

  1. Fetch all the customers having gmail in the email domain

    SELECT * FROM customer WHERE email LIKE '%gmail.com%';

  2. Fetch all the customers that has been created in 2006

    SELECT * from customer WHERE (EXTRACT(YEAR FROM create_date))=2006;

    Note: all the customers were created on '2006-02-14' so the query will collect the whole list of 599 customers.

  3. Fetch the list of full names in a single column ordered by last_name

    SELECT first_name || ' ' || last_name AS "Full name" FROM customer ORDER BY last_name;

  4. Fetch all the customers that do not have an email

    SELECT * FROM customer WHERE email IS NULL;

    All the customers have an email, so the list will be empty.

  5. Fetch, in a single query, the list of first_name and last_name of the actors table plus the list of first_name and last_name of the of the customers table.

    Use UNION:

    SELECT first_name, last_name FROM actor UNION ALL SELECT first_name, last_name from customer;

    Note: ALL allows duplicate values

  6. Fetch the list of first_name, last_name and the relevant address stored in the address table.

    Use INNER JOIN: SELECT first_name, last_name, address FROM customer INNER JOIN address ON customer.address_id = address.address_id;

    Note that you could omit the keyword INNER. The JOIN is an inner join as default.

  7. Re-write the previous query including the first_name, last_name that do not have an address linked.

    SELECT first_name, last_name, address FROM customer c LEFT JOIN address a ON c.address_id = a.address_id;

    Note: you could use the aliases for the tables (keyword AS). The result is the same, it's just matter of style:

    SELECT first_name, last_name, address FROM customer AS c LEFT JOIN address AS a ON c.address_id = a.address_id;

  8. Retrieve the total number of customers

    select count(*) from customer;

  9. Retrieve the total number of active and non active customers

    select active, count(*) from customer group by active;

    You should get this result:

     active | count
 -------+-------
      0 |    15
      1 |   584

    584 active and 15 non active customers.

  10. Insert a new customer having the following info:

    • first_name: Paolo
    • last_name: Rossi
    • email: paolo.rossi@example.com
    • store_id: 1
    • address_id: 10

    INSERT INTO customer (first_name, last_name, email, store_id, address_id) VALUES ('Paolo', 'Rossi', 'paolo.rossi@example.com', 1, 10);

  11. What is a view?

    A view is a named SQL query, it's sometimes called virtual table, because it can be used as a table, even if it does not store any data.

  12. Create a view of the question (11c) and name it customer_address_view

    CREATE VIEW customer_address_view AS SELECT first_name, last_name, address FROM customer LEFT JOIN address ON customer.address_id = address.address_id;

You did it! Now check your answers.

Result

How many did you get right?

1..5: Newbie

6..10: Beginner

11..15: Skillful

15..17: Expert

Whatever the result is, today you've practiced your SQL skills, well done!

Useful links: