# PostgreSQL database hands-on session 2

originally by Yuxing Chen

## Initialize dvdrental demo database

1. See previous demo in [Session 1](https://hackmd.io/CHBzTAb1QxaYrg2WctNasA?view).
2. Main tutorial page for [PostgreSQL](https://www.postgresqltutorial.com/postgresql-getting-started/).
3. Management tool [pgAdmin](https://www.pgadmin.org/) for PostgreSQL.
### Exercises

1. **Grouping Data** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-group-by/)
a. Find the customers’ IDs who spend a total amount of money greater than 190.00. (Hint: "SUM", "GROUP BY", and "HAVING")
b. Find customers’ full names who spend an average amount of money less than 3.00. (Hint: "AVG", "GROUP BY", "HAVING", and "JOIN")
2. **Set Operations** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-union/)
a. Find customers’ IDs whose total amount (i.e. SUM(amount) for each customer_id in payment table) is more than 200.00 or less than 40.00. (Hint: "UNION")
b. Find customers’ IDs who have spent more than 90.00 but have less than 20 payment records (i.e. COUNT(amount) for each customer_id in payment table). (Hint: "INTERSECT")
3. **Grouping sets, Cube, and Rollup** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-grouping-sets/)
a. Find customers’ last names who have more than 20 payment records. (Hint: use subquery and “IN”)
b. Find the top/first 10 customers’ IDs whose total payment amounts are more than each customer’s average total amount. (Hint: use subquery and “AVG”)
4. **Subquery** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-subquery/)
a. For each cateogry of films, there is one film which has the maximum length. The following SQL query finds the films whose lengths are greater than or equal to the maximum length of one of the categories:

    SELECT title, length 
    FROM film WHERE length >= ANY(
        SELECT MAX(length) FROM film
        INNER JOIN film_category USING(film_id)
        GROUP BY category_id ) 
    LIMIT 10;

Rewrite the above query without “ANY” keyword to implement the exact same function. (Hint: use “MIN” instead of “MAX”.)

5. **Common Table Expressions** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-cte/)
a. Define a CTE that returns customers’ IDs and amounts ranking by the amount of each payment, then find the customers’ IDs and amounts whose ranking is the first. (HINT: "WITH", "RANK() OVER")