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:

• download the DB DVD Rental: http://www.postgresqltutorial.com/postgresql-sample-database/.
• connect to it using psql, which is the PostgreSQL command line interface.
• try the queries on it. It will be simpler to answer the questions.

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:

• PostgreSQL Sample Database
• SQL Tutorial - W3School