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!
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?
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
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: firstname.lastname@example.org, 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.
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
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.
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;
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', 'email@example.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.
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!