Webshop SQL Exercises

First exercise is to get the necessary data set up.

If you don't have the webshop schema with data, you first need to download the files, create the schema and tables, then import the data.

Download first the dll file here:

webshop dll

You must import this by running the ddl code, like this:

Right click on your schema folder, and select the "Run SQL Script...".

This should import a new schema, the webshop.

Then, download the data from here:

webshop data

Import the data the same way.

Verify you now have a webshop schema, with tables, and data in the tables.

A Global Relations diagram for the schema is shown below:

You may want to right-click and open in a new tab, to increase the size, or download.

Over the following slides, you will be asked questions, which must be answered by querying the dvdrental database. All questions can be answered with a single sql statement (consider this a challange), but sometimes it is easier to do multiple statements.

For most questions, you can also find the an/swer as a hint. Do give it a serious try first, before you look at the answer. Some of these questions are tough, and you need to google for extra SQL "tools".

Exercise 2

Find all customers whose first name starts with 'Jo'. Create a list of id and names.

SELECT customer_id, first_name, middle_name, last_name
FROM customer
WHERE first_name LIKE 'Jo%';

Exercise 3

Find all customers whose first name contains 'ella' and who does not have a middle name.

Create a list of id and names.

SELECT customer_id, first_name, middle_name, last_name
FROM customer
WHERE first_name LIKE '%ella%'
    AND middle_name ISNULL;

Exercise 4

Find customers who have made more than 4 orders.

Show first name, last name, and the number of ordered items.

Sort by highest count, then first name.

SELECT first_name, last_name, COUNT(*) AS no_orders
FROM customer c,
     "order" o
WHERE c.customer_id = o.customer_id
GROUP BY first_name, last_name
HAVING COUNT(*) > 4
ORDER BY no_orders DESC, first_name;

Exercise 5

Find the customer id of Neysa Aldins (first and last name).

SELECT customer_id
FROM customer
WHERE first_name = 'Neysa'
    AND last_name = 'Aldins';

Exercise 6

What is the price of the most expensive product?

SELECT MAX(product_price)
FROM product;

Exercise 7

Find the most expensive product.

Display the product_id, product_name, and the price.

You can use your previous answer as a sub-query

SELECT product_id, product_name, product_price
FROM product
WHERE product_price = (
    SELECT MAX(product_price)
    FROM product
);

Exercise 8

Find the sum of the quantity ordered of the most expensive product, i.e. how many of the most expensive product have been ordered. Again, you may find it useful to use (a slightly modified version) of the previous query as a subquery.

SELECT SUM(quantity)
FROM orderedproduct
WHERE product_id = (
    SELECT product_id
    FROM product
    WHERE product_price = (
        SELECT MAX(product_price)
        FROM product
        )
    );

Exercise 9

Who have ordered the most expensive product? Return their full names as a string, e.g. "Neysa Aldins".

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM    customer c,
        "order" om
        orderedproduct op
WHERE c.customer_id = o.customer_id
AND o.order_id = op.order_id
AND op.product_id = (
    SELECT product_id
    FROM product
    WHERE product_price = (
        SELECT MAX(product_price)
        FROM product
    )
);

Exercise 10

Return the first name of all female customers whose last name's second letter is 'o', and who were born in the eighties.

SELECT first_name
FROM customer
WHERE gender = 'F'
    AND last_name LIKE '_o%'
    AND birthdate BETWEEN '1980-01-01' AND '1989-12-31';

Exercise 11

How many customers have not placed any orders?

SELECT COUNT(customer_id) AS no_order_customers
FROM customer
WHERE customer_id NOT IN (SELECT customer_id FROM "order");

Exercise 12

Which customers have birthday today? Return names as a string and their age. Note: answer will vary, the query is the same.

SELECT  CONCAT_WS(' ', first_name, middle_name, last_name) AS fulle_name,
        DATE_PART('year', AGE(birthdate)) AS age
FROM customer
WHERE DATE_PART('day', birthdate) = DATE_PART('day', CURRENT_TIMESTAMP)
    AND DATE_PART('month', birthdate) = DATE_PART('month', CURRENT_TIMESTAMP);

Exercise 13

Find the first name and age of all customers from Denmark.

SELECT first_name, date_part('year', age(birthdate)) as age
FROM    customer c,
        address a,
        zipcode z,
        country co
WHERE c.customer_id = a.customer_id
AND a.zip_code = z.zip_code
AND z.country_code = co.country_code
AND co.country_name = 'Denmark';

Exercise 14

Find the name of the products, sorted alphabetically, who have reviews that contain any of the keywords: 'Great', 'Super', 'Excellent', 'Good' but with no case-sensitivity.

SELECT DISTINCT product_name
FROM    review r,
        product p 
WHERE r.product_id = p.product_id
AND review_text ILIKE ANY (ARRAY ['%Great%', '%Super%', ''%Excellent%', '%Good%'])
ORDER BY product_name;

Exercise 15

Find the name of the products sorted alphabetically who have reviews that contain ALL of the keywords: 'Great', 'Super', 'Excellent', 'Good' but with no case-sensitivity.

SELECT DISTINCT product_name
FROM    review r,
        product p 
WHERE r.product_id = p.product_id
AND review_text ILIKE ALL (ARRAY ['%Great%', '%Super%', ''%Excellent%', '%Good%'])
ORDER BY product_name;