/ SQL

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?
    1. What are the 2 most popular open source SQL DB engines?
    1. What are the 2 most popular commercial SQL DB engines?
    1. 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
    1. Retrieve all the customers that has been created in 2006
    1. Retrieve the list of full names in a single column ordered by last_name
    1. Retrieve all the customers that do not have an email
    1. 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

    1. Retrieve the total number of customers
    1. Retrieve the total number of active and non active customers
    1. Using SQL insert a new customer having the following info:
      first_name: Paolo, last_name: Rossi, email: paolo@rossi.it, store_id: 1, address_id: 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
    1. MySQL and PostgreSQL (https://db-engines.com/en/ranking)
    1. Oracle and Microsoft SQL Server
    1. MongoDB: it does not support SQL and it is a document-
      oriented database engine
    1. SELECT * FROM customer WHERE email LIKE '%gmail.com%';
    1. 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).

    1. SELECT first_name || ' ' || last_name AS "Full name" FROM customer ORDER BY last_name;
    1. SELECT * FROM customer WHERE email IS NULL;

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

    1. 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;

    1. select count(*) from customer;
    1. 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.

    1. 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.

Read More
17 questions to test your SQL knowledge
Share this