17 questions to test your SQL knowledge

These are 17 questions that will give you an idea about how good you are with SQL.

Why did I write 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 thought that a test with many questions would be more useful for you.

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?

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

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

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

    • MariaDB
    • MongoDB
    • SQLite

SQL Queries

Given the following tables:

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

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

  • 5) Retrieve all the customers having gmail as domain of the email

  • 6) Retrieve all the customers that has been created in 2006

  • 7) Retrieve the list of full names in a single column ordered by last_name

  • 8) Retrieve all the customers that do not have an email

  • 9) Retrieve, 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.

  • 10a) Retrieve the list of first_name, last_name with the relevant address (stored in the address table). Retrieve only the customers that have the address specified.

  • 10b) Retrieve the list of first_name, last_name with the relevant address (stored in the address table). This time retrieve all the customers, even if they don't have a specified address.

  • 10c) Rewrite the question (10b) using the ALIAS for the tables

  • 11) Retrieve the total number of customers

  • 12) Retrieve the total number of active and non active customers

  • 13) Using SQL insert a new customer having the following info: firstname: Paolo, lastname: Rossi, email: paolo@rossi.it, storeid: 1, addressid: 10

  • 13a) What is a view?

  • 13b) Create a view of the question (11c) and name it customer_address_view

You did it! Now check your answers.

ANSWERS

  • 1) Structured Query Language

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

  • 3) Oracle and Microsoft SQL Server

  • 4) MongoDB: it does not support SQL and it is a document- oriented database engine

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

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

    All the customers were created at '2006-02-14' so you'll get the the whole list of customer customers (599).

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

  • 8) SELECT * FROM customer WHERE email IS NULL;

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

  • 9) Use UNION

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

    Note: ALL allows duplicate values

  • 10a) Use an INNER JOIN to combine rows from the tables customer and address

    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.

  • 10b) Use a LEFT JOIN SELECT first_name, last_name, address FROM customer LEFT JOIN address ON customer.address_id = address.address_id;

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

    or you could use the keyword AS to specify the alias. 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;

  • 11) select count(*) from customer;

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

    You should get this result:

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

    584 active customers and 15 non active.

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

  • 14a) 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.

  • 14b)

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


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:

Alessandro DS

Hi, I am Sw Developer working in London, passionate about start ups and working on agile environment. I am also (happy) daddy of a little boy and girl.

London