Musterlösungen zu „Unterabfragen“

1. Film-Ranking

Erstellen Sie eine Liste der 3 am häufigsten ausgeliehenen Filmtitel. Beachten Sie eventuelle Mehrfachnennungen der Ausleihzahlen.

SELECT DISTINCT
f.title,
count(i.film_id) AS count
FROM rental    r
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY
f.title
HAVING count(i.film_id) >= (
SELECT DISTINCT -- Ausleihzahl  für TOP-3 ermitteln
count(i.film_id) AS count
FROM rental    r
JOIN inventory i ON i.inventory_id = r.inventory_id
GROUP BY
i.film_id
ORDER BY
count DESC
LIMIT 1 OFFSET 2
)
ORDER BY count DESC, title;

SELECT DISTINCT

f.title,

count(i.film_id) AS count

FROM rental r

JOIN inventory i ON i.inventory_id = r.inventory_id

JOIN film f ON i.film_id = f.film_id

GROUP BY

f.title

HAVING count(i.film_id) >= (

SELECT DISTINCT -- Ausleihzahl für TOP-3 ermitteln

count(i.film_id) AS count

FROM rental r

JOIN inventory i ON i.inventory_id = r.inventory_id

GROUP BY

i.film_id

ORDER BY

count DESC

LIMIT 1 OFFSET 2

)

ORDER BY count DESC, title;

+-------------------+-----+
|title              |count|
+-------------------+-----+
|Bucket Brotherhood |34   |
|Rocketeer Mother   |33   |
|Forward Temple     |32   |
|Grit Clockwork     |32   |
|Juggler Hardly     |32   |
|Ridgemont Submarine|32   |
|Scalawag Duck      |32   |
+-------------------+-----+

2. Kunden-Umsatzanalyse

Wieviel Umsatz wird von Kunden generiert, die mehrmals 30 Ausleihungen haben? Gruppieren Sie nach der Anzahl der Verleihvorgänge und zeigen Sie die Anzahl der sich dahinter verbergenden Kunden,
den Gesamtumsatz der Gruppe sowie Durchschnittsumsatz pro Kunde.

Das erwartete Ergebnis sieht wie folgt aus:

+-------+---------+-------+--------------------+
|rentals|customers|amount |avg                 |
+-------+---------+-------+--------------------+
|46     |1        |216.54 |216.54              |
|45     |1        |221.55 |221.55              |
|42     |2        |371.16 |185.58              |
|41     |1        |155.59 |155.59              |
|40     |2        |332.2  |166.1               |
|39     |3        |564.83 |188.2766666666666667|
|38     |4        |661.48 |165.37              |
|37     |4        |619.52 |154.88              |
|36     |6        |864.84 |144.14              |
|35     |13       |1943.45|149.4961538461538462|
|34     |21       |2936.86|139.8504761904761905|
|33     |19       |2670.73|140.5647368421052632|
|32     |35       |4687.79|133.9368571428571429|
|31     |22       |2852.18|129.6445454545454545|
+-------+---------+-------+--------------------+

Augenscheinlich gibt es ein paar sehr filmbegeisterte Kunden. Die Zahlen sind nicht ganz realistisch – was soll’s.

SELECT
r.rentals
, count(DISTINCT r.customer_id)                 AS customers
, sum(p.amount)                                 AS amount
, sum(p.amount) / count(DISTINCT r.customer_id) AS avg
FROM (
SELECT
customer_id
, count(rental_id) AS rentals
FROM rental
GROUP BY
customer_id
HAVING count(rental_id) > 30
)       r
JOIN payment p ON p.customer_id = r.customer_id
GROUP BY
r.rentals
ORDER BY
r.rentals DESC;

SELECT

r.rentals

, count(DISTINCT r.customer_id) AS customers

, sum(p.amount) AS amount

, sum(p.amount) / count(DISTINCT r.customer_id) AS avg

FROM (

SELECT

customer_id

, count(rental_id) AS rentals

FROM rental

GROUP BY

customer_id

HAVING count(rental_id) > 30

) r

JOIN payment p ON p.customer_id = r.customer_id

GROUP BY

r.rentals

ORDER BY

r.rentals DESC;

3. Top-3-Filme

Erstellen Sie eine Top-3-Liste der am häufigsten verliehenen Filme inkl. deren Schauspielern. Beachten Sie, dass sich mehrere Filme einen Rang teilen können.

Das erwartete Resultat sieht wie folgt aus:

+-------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------+
|rentals|title              |actors                                                                                                                                |
+-------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------+
|34     |Bucket Brotherhood |Kirsten Akroyd, Rip Crawford, Charlize Dench, Tim Hackman, Gary Phoenix, Burt Temple                                                  |
|33     |Rocketeer Mother   |Meryl Allen, Chris Bridges, Judy Dean, Warren Jackman, Tom Miranda, Jayne Silverstone, Renee Tracy                                    |
|32     |Forward Temple     |Greg Chaplin, Sean Williams, Rip Winslet                                                                                              |
|32     |Grit Clockwork     |Kevin Garland, Geoffrey Heston, Cameron Zellweger                                                                                     |
|32     |Juggler Hardly     |Meryl Gibson, Ewan Gooding, Ed Guiness, Penelope Monroe, Salma Nolte, Milla Peck, Penelope Pinkett, Morgan Williams, Cameron Zellweger|
|32     |Ridgemont Submarine|Michael Bolger, Julianne Dench, Julia Fawcett, Whoopi Hurt, Johnny Lollobrigida                                                       |
|32     |Scalawag Duck      |Jude Cruise, Chris Depp, Reese Kilmer, Christian Neeson, Groucho Sinatra                                                              |
+-------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------+

SELECT -- Top-3 Filme mit Schauspielern
top3_films.rentals
, title
, string_agg(first_name || ' ' || last_name, ', ' ORDER BY last_name, first_name) AS actors
FROM film       f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor      a ON fa.actor_id = a.actor_id
JOIN (
-- Anzahl der Ausleihungen pro Film in Top-3
SELECT
count(rental_id) AS rentals
, f.film_id
FROM rental    r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film      f ON f.film_id = i.film_id
GROUP BY
f.film_id
, title
HAVING count(rental_id) >= (
-- Ausleihhäufigkeiten für Top-3
SELECT DISTINCT 
count(r.rental_id) AS rentals
FROM rental    r
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film      f ON i.film_id = f.film_id
GROUP BY
f.film_id
ORDER BY
rentals DESC
LIMIT 1 OFFSET 2
)
)          top3_films ON f.film_id = top3_films.film_id
GROUP BY
top3_films.rentals
, title
ORDER BY
top3_films.rentals DESC;

SELECT -- Top-3 Filme mit Schauspielern

top3_films.rentals

, title

, string_agg(first_name || ' ' || last_name, ', ' ORDER BY last_name, first_name) AS actors

FROM film f

JOIN film_actor fa ON f.film_id = fa.film_id

JOIN actor a ON fa.actor_id = a.actor_id

JOIN (

-- Anzahl der Ausleihungen pro Film in Top-3

SELECT

count(rental_id) AS rentals

, f.film_id

FROM rental r

JOIN inventory i ON r.inventory_id = i.inventory_id

JOIN film f ON f.film_id = i.film_id

GROUP BY

f.film_id

, title

HAVING count(rental_id) >= (

-- Ausleihhäufigkeiten für Top-3

SELECT DISTINCT

count(r.rental_id) AS rentals

FROM rental r

JOIN inventory i ON i.inventory_id = r.inventory_id

JOIN film f ON i.film_id = f.film_id

GROUP BY

f.film_id

ORDER BY

rentals DESC

LIMIT 1 OFFSET 2

)

) top3_films ON f.film_id = top3_films.film_id

GROUP BY

top3_films.rentals

, title

ORDER BY

top3_films.rentals DESC;

4. Schauspieler in Erfolgsfilmen

Welche Schauspieler, ohne Mehrfachnennungen, haben in den Top-Filmen aus Aufgabe 3 mitgewirkt?

SELECT DISTINCT
first_name
, last_name
FROM film       f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor      a ON fa.actor_id = a.actor_id
JOIN (
-- Anzahl der Ausleihungen pro Film in Top-3
SELECT
count(rental_id) AS rentals
, f.film_id
FROM rental    r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film      f ON f.film_id = i.film_id
GROUP BY
f.film_id
, title
HAVING count(rental_id) >= (
-- Ausleihhäufigkeiten für Top-3
SELECT DISTINCT
count(r.rental_id) AS rentals
FROM rental    r
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film      f ON i.film_id = f.film_id
GROUP BY
f.film_id
ORDER BY
rentals DESC
LIMIT 1 OFFSET 2
)
)          top3_films ON f.film_id = top3_films.film_id
ORDER BY
last_name
, first_name;

SELECT DISTINCT

first_name

, last_name

FROM film f

JOIN film_actor fa ON f.film_id = fa.film_id

JOIN actor a ON fa.actor_id = a.actor_id

JOIN (

-- Anzahl der Ausleihungen pro Film in Top-3

SELECT

count(rental_id) AS rentals

, f.film_id

FROM rental r

JOIN inventory i ON r.inventory_id = i.inventory_id

JOIN film f ON f.film_id = i.film_id

GROUP BY

f.film_id

, title

HAVING count(rental_id) >= (

-- Ausleihhäufigkeiten für Top-3

SELECT DISTINCT

count(r.rental_id) AS rentals

FROM rental r

JOIN inventory i ON i.inventory_id = r.inventory_id

JOIN film f ON i.film_id = f.film_id

GROUP BY

f.film_id

ORDER BY

rentals DESC

LIMIT 1 OFFSET 2

)

) top3_films ON f.film_id = top3_films.film_id

ORDER BY

last_name

, first_name;

+----------+------------+
|first_name|last_name   |
+----------+------------+
|Kirsten   |Akroyd      |
|Meryl     |Allen       |
|Michael   |Bolger      |
|Chris     |Bridges     |
|Greg      |Chaplin     |
|Rip       |Crawford    |
|Jude      |Cruise      |
|Judy      |Dean        |
|Charlize  |Dench       |
|Julianne  |Dench       |
|Chris     |Depp        |
|Julia     |Fawcett     |
|Kevin     |Garland     |
|Meryl     |Gibson      |
|Ewan      |Gooding     |
|Ed        |Guiness     |
|Tim       |Hackman     |
|Geoffrey  |Heston      |
|Whoopi    |Hurt        |
|Warren    |Jackman     |
|Reese     |Kilmer      |
|Johnny    |Lollobrigida|
|Tom       |Miranda     |
|Penelope  |Monroe      |
|Christian |Neeson      |
|Salma     |Nolte       |
|Milla     |Peck        |
|Gary      |Phoenix     |
|Penelope  |Pinkett     |
|Jayne     |Silverstone |
|Groucho   |Sinatra     |
|Burt      |Temple      |
|Renee     |Tracy       |
|Morgan    |Williams    |
|Sean      |Williams    |
|Rip       |Winslet     |
|Cameron   |Zellweger   |
+----------+------------+

1. Film-Ranking

2. Kunden-Umsatzanalyse

3. Top-3-Filme

4. Schauspieler in Erfolgsfilmen

Das könnte dir auch gefallen

Musterlösungen zu „Einfache SQL-Abfragen“

Musterlösungen zu „Mengen-Operationen“

Musterlösungen zu „Daten filtern“