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!
What does SQL mean?
Structured Query Language
What are the 2 most popular open source SQL DB engines?
MySQL and PostgreSQL (https://db-engines.com/en/ranking)
What are the 2 most popular commercial SQL DB engines?
Oracle and Microsoft SQL Server
Which of these DB engines is non relational and does not support SQL?
MongoDB: it is a document-oriented DB engine
Given the tables:
customer_id | first_name | last_name | email | create_date | ...
address_id | address | address2 | district | city_id | postal_code | phone | last_update
Fetch all the customers having gmail in the email domain
SELECT * FROM customer WHERE email LIKE '%gmail.com%';
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.
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;
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.
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.
SELECT first_name, last_name FROM actor UNION ALL SELECT first_name, last_name from customer;
Note: ALL allows duplicate values
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.
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;
Retrieve the total number of customers
select count(*) from customer;
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.
Insert a new customer having the following info:
- first_name: Paolo
- last_name: Rossi
- email: email@example.com
- store_id: 1
- address_id: 10
INSERT INTO customer (first_name, last_name, email, store_id, address_id) VALUES ('Paolo', 'Rossi', 'firstname.lastname@example.org', 1, 10);
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.
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.
How many did you get right?
Whatever the result is, today you've practiced your SQL skills, well done!