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:

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

  1. Retrieve all the customers having gmail as domain of the email

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

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

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

Retrieve only the customers that have the address specified.

This time retrieve all the customers, even if they don't have a specified address.

firstname: Paolo, lastname: Rossi, email: paolo@rossi.it, storeid: 1, addressid: 10

You did it! Now check your answers.

ANSWERS

oriented database engine

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

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

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

Note: ALL allows duplicate values

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.

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;

You should get this result:

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

584 active customers and 15 non active.

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: