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:
- 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!
- What does SQL mean?
- What are the 2 most popular open source SQL DB engines?
- What are the 2 most popular commercial SQL DB engines?
- Which of these DB engines is non relational and does not support SQL?
Given the following tables:
customer_id | first_name | last_name | email | create_date | ...
address_id | address | address2 | district | city_id | postal_code | phone | last_update
Retrieve all the customers having gmail as domain of the email
Retrieve all the customers that has been created in 2006
Retrieve the list of full names in a single column ordered by last_name
Retrieve all the customers that do not have an email
- 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
- Retrieve the total number of customers
- Retrieve the total number of active and non active customers
- Using SQL insert a new customer having the following info:
firstname: Paolo, lastname: Rossi, email: email@example.com, 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.
- Structured Query Language
- MySQL and PostgreSQL (https://db-engines.com/en/ranking)
- Oracle and Microsoft SQL Server
- MongoDB: it does not support SQL and it is a document-
oriented database engine
SELECT * FROM customer WHERE email LIKE '%gmail.com%';
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).
SELECT first_name || ' ' || last_name AS "Full name" FROM customer ORDER BY last_name;
SELECT * FROM customer WHERE email IS NULL;
All the customers have an email, so the list will be empty.
- 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;
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;
select count(*) from customer;
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.
INSERT INTO customer (first_name, last_name, email, store_id, address_id) VALUES ('Paolo', 'Rossi', 'firstname.lastname@example.org', 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.
CREATE VIEW customer_address_view AS SELECT first_name, last_name, address FROM customer LEFT JOIN address ON customer.address_id = address.address_id;
How many did you get right?
Whatever the result is, today you've practiced your SQL skills, well done!