SQL взаимоотношения между таблицами

entity-relationship diagram 

PK - Primary key
FK - Foreign key

Переименование полей и таблиц. Псевдонимы

SELECT EXTRACT(YEAR FROM CAST(i.invoice_date AS DATE)) AS year_of_purchase,
       MIN(i.total) AS min_cost,
         MAX(i.total) AS max_cost,
         SUM(i.total) AS total_revenue,
         COUNT(i.total) AS total_purchases,
         ROUND(SUM(i.total)/COUNT(DISTINCT(i.customer_id))) AS average_receipt
FROM invoice AS i
WHERE billing_country IN ('USA', 'United Kingdom', 'Germany')
GROUP BY year_of_purchase
ORDER BY year_of_purchase DESC;

К псевдонимам нельзя обратиться в WHERE или HAVING, потому что в SQL операторы выполняются не в том порядке, в котором их указывают в запросе. Сначала данные отбираются по условию, а только потом полям назначаются псевдонимы. Во многих СУБД нельзя обращаться к псевдонимам и в GROUP BY, но в PostgreSQL можно.

SELECT billing_country,
       count(invoice_id) AS total_purchases,
       SUM(total) AS total_revenue,
       ROUND(AVG(total), 2) AS average_revenue
FROM invoice
GROUP BY billing_country
ORDER BY average_revenue DESC
LIMIT 10;

SELECT rating AS rating_of_epic,
       release_year AS year_of_epic,
       AVG(rental_duration) AS average_rental
FROM movie
WHERE description LIKE '%Epic%'
GROUP BY rating_of_epic, release_year;

SELECT CASE
           WHEN movie.rating = 'G' THEN 'без ограничений'
           ELSE 'с ограничениями'
       END AS new_rating,
       SUM(rental_rate)
FROM movie
GROUP BY new_rating;

Операторы JOIN

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

SELECT t.name,
	   SUM(i.quantity)
FROM track AS t
INNER JOIN invoice_line AS i ON i.track_id=t.track_id
GROUP BY t.name
LIMIT 20;

SELECT t.name,
       SUM(i.quantity),
       p.playlist_id
FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id=i.track_id
INNER JOIN playlist_track AS p ON t.track_id=p.track_id
GROUP BY t.name, p.playlist_id
LIMIT 20;

SELECT t.name,
       SUM(i.quantity),
       pt.playlist_id,
       pl.name
FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id=i.track_id
INNER JOIN playlist_track AS pt ON t.track_id=pt.track_id
INNER JOIN playlist AS pl ON pt.playlist_id=pl.playlist_id
GROUP BY t.name, pt.playlist_id, pl.name
LIMIT 20;

SELECT p.name AS playlist_name,
       SUM(i.unit_price) AS total_revenue
FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id=i.track_id
INNER JOIN playlist_track AS pl ON t.track_id=pl.track_id
INNER JOIN playlist AS p ON pl.playlist_id = p.playlist_id
GROUP BY playlist_name
ORDER BY total_revenue DESC;

SELECT genre.name,
       SUM(il.quantity) AS orders
FROM genre
INNER JOIN track ON genre.genre_id=track.genre_id
INNER JOIN invoice_line AS il ON track.track_id = il.track_id
--INNER JOIN invoice AS i ON il.invoice_id=i.invoice_id
GROUP BY genre.name
ORDER BY orders DESC;

SELECT DISTINCT category.name
FROM film_category
INNER JOIN film_actor ON film_category.film_id=film_actor.film_id
INNER JOIN actor ON actor.actor_id=film_actor.actor_id
INNER JOIN category ON film_category.category_id=category.category_id
WHERE actor.first_name = 'Uma' AND actor.last_name = 'Wood';

SELECT art.name,
       alb.title
FROM artist AS art
LEFT OUTER JOIN album AS alb ON art.artist_id = alb.artist_id
LIMIT 10;

Таблицы artist и album объединены по ключу artist_id, и благодаря оператору LEFT OUTER JOIN все значения из поля name таблицы artist попадут в итоговую таблицу. Если для какого-то исполнителя не найдётся альбома, на этом месте в поле title появится значение NULL.

SELECT art.name,
       alb.title
FROM album AS alb
RIGHT OUTER JOIN artist AS art ON art.artist_id = alb.artist_id
LIMIT 10;

SELECT a.actor_id,
       a.first_name,
       a.last_name,
       c.first_name,
       c.last_name
FROM actor AS a
FULL OUTER JOIN client AS c ON a.last_name = c.last_name
LIMIT 10;

Крупные таблицы при использовании FULL OUTER JOIN могут упереться в оперативную память.