SQL notes

helpers.sql

# Skeleton
SELECT
FROM
WHERE
GROUP BY
ORDER BY
;


# use of SELECT - all values
SELECT
    *
FROM
    actor; 


# Select first 10
SELECT
    *
FROM
    actor
limit
    10;


# use of WHERE to set a condition
# count() function, (tip: count ids)
SELECT
    count(film_id)
FROM
    film
WHERE
    rental_rate = .99;


# use of GROUP BY to group columns
# e.g. 'rental_rate' or 2 (cause second value in SELECT part)
SELECT
    count(title), rental_rate
FROM
    film
GROUP BY
    2; 


# use of WHERE to connect two tables
# give the tables nicknames
SELECT
    f.title,
    c.name,
    l.name
FROM
    film f,
    language l,
    film_category fc,
    category c
WHERE
    f.film_id = fc.film_id
    and c.category_id = fc.category_id
    and f.language_id = l.language_id;


# use of ORDER BY to order columns
# Rename table output names
# by default ascending order
SELECT
    f.title as "Film Title",
    count(r.rental_id) as "Number of Rentals",
    f.rental_rate as "Rental Price",
    count(r.rental_id) * f.rental_rate as Revenue
FROM
    film f,
    rental r,
    inventory i
WHERE
    f.film_id = i.film_id
    and i.inventory_id = r.inventory_id
GROUP BY
    1
ORDER BY
    4 desc;


# sum() function
SELECT
    i.store_id as "Store ID",
    sum(p.amount) as Revenue
FROM
    inventory  i,
    payment p,
    rental r
WHERE
    p.rental_id = r.rental_id
    and r.inventory_id = i.inventory_id
GROUP BY
    1 
ORDER BY
    2 desc ;


# left() function to modify strings
# left(2005-05-14 15:16:03, 7), 7 because the first 7 characters are 2005-05
SELECT
    left(r.rental_date, 7),
    count(r.rental_id)
FROM
    rental r 
GROUP BY
    1
ORDER BY
    2 desc;


# min() max() and function
# GROUP BY a coloum that is not in the SELECT part
SELECT
    f.title as "Film Title",
    max(r.rental_date) as "Last Rental Date",
    min(r.rental_date) as "Fist Rental Date"
FROM
    rental r,
    inventory i,
    film f
WHERE
    r.inventory_id = i.inventory_id
	and i.film_id = f.film_id
GROUP BY f.film_id;


# concat() function
SELECT
    concat(c.first_name, " ", c.last_name) as Name,
    c.email, max(r.rental_date) as "Last rental Date"
FROM
    customer c,
    rental r
WHERE
    r.customer_id = c.customer_id
GROUP BY
    c.customer_id;


# use of distinct
# distinct = recognizably different in nature from something else of a similar type.
SELECT
    left(r.rental_date, 7) as Month,
    count(r.rental_id) as "Total rentals", 
    count(distinct r.customer_id) as "Unique renters",
    count(r.rental_id)/count(distinct r.customer_id) as "Average num rentals per renter"
FROM
    rental r
GROUP BY
    1;


# in() function, filter for specific data
# the parameters have to be written exactly like in the data base
SELECT
    c.name as category,
    count(r.rental_id) as num_rentals
FROM
    rental r,
    inventory i,
    film f,
    film_category fc,
    category c
WHERE
    r.inventory_id = i.inventory_id
    and i.film_id = f.film_id
    and f.film_id = fc.film_id
    and fc.category_id = c.category_id
    and c.name in ("Comedy", "Sports", "Family")
GROUP BY
    1;


# comparison operator (<, >, =, <=, >=, !=)
# use of HAVING - similar to WHERE but allows functions like count()
SELECT
    r.customer_id as customer,
    count(r.rental_id) as num_rentals
FROM
    rental r
GROUP BY
    1
HAVING
    count(r.rental_id) >= 3;


# nested queries
SELECT
    rentals_per_customer.num_rentals,
    count(distinct rentals_per_customer.customer_id) as num_customer, 
    sum(p.amount) as  total_revenue
FROM (
	SELECT
        r.customer_id,
        count(distinct r.rental_id) as num_rentals
	FROM
        rental r
	GROUP BY
        1
    ) as rentals_per_customer,
    payment p
WHERE
    rentals_per_customer.customer_id = p.customer_id
    and rentals_per_customer.num_rentals > 20
GROUP BY
    1;


# tempoary table - alternative to nested queries
create tempoary table rentals_per_customer as
SELECT
    r.customer_id,
    count(distinct r.rental_id) as num_rentals
FROM
    rental r
GROUP BY
    1;

SELECT
    sum (p.amount) as  total_revenue
FROM
    rentals_per_customer, payment p
WHERE
    rentals_per_customer.customer_id = p.customer_id
    and rentals_per_customer > 20;


# use of JOIN
SELECT
    ac.customer_id,
    ac.fav_color,
    rc.num_purchases
FROM
    active_customer ac
JOIN
    reward_customer rc
ON
    ac.customer_id = rc.customer_id;

# LEFT JOIN
# result the operlapping of these two tables, and the whole part of the first (left) table
SELECT
    ac.customer_id, ac.fav_color,
    rc.num_purchases
FROM
    active_customer ac
LEFT JOIN
    reward_customer rc
ON
    ac.customer_id = rc.customer_id;


# c.* - return alls colums in customer table
create tempoary table activeUsers
SELECT
    c.*,
    a.phone
FROM
    customer c
JOIN
    address a
ON
    a.address_id = a.address_id
WHERE
    c.active = 1
GROUP BY
    1;