1. Schauspieler und Filmgenres
Welche Schauspieler haben nicht in Action-Filmen mitgewirkt?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT first_name , last_name FROM actor EXCEPT SELECT first_name , last_name FROM film fi JOIN film_category fc ON fi.film_id = fc.film_id JOIN category ca ON ca.category_id = fc.category_id JOIN film_actor fa ON fi.film_id = fa.film_id JOIN actor ac ON fa.actor_id = ac.actor_id WHERE name = 'Action' ORDER BY last_name , first_name; |
+----------+-----------+ |first_name|last_name | +----------+-----------+ |Debbie |Akroyd | |Cuba |Allen | |Harrison |Bale | |Scarlett |Bening | |Liza |Bergman | |Goldie |Brody | |Emily |Dee | |Lucille |Dee | |Gregory |Gooding | |Penelope |Guiness | |Tim |Hackman | |Meg |Hawke | |Kenneth |Hoffman | |Whoopi |Hurt | |Jane |Jackman | |Albert |Johansson | |Ray |Johansson | |Milla |Keitel | |Fay |Kilmer | |Cary |Mcconaughey| |Gene |Mckellen | |Tom |Miranda | |Christian |Neeson | |Warren |Nolte | |Rita |Reynolds | |John |Suvari | |Burt |Temple | |Russell |Temple | |Thora |Temple | |Kenneth |Torn | |Groucho |Williams | |Ben |Willis | |Fay |Wood | |Cameron |Wray | +----------+-----------+
2. Kundenbedarfsprüfung
- Welche Filme haben wir aus den populärsten Filmen im Bestand?
- Falls unser Bestand die Liste der populärsten Filme nicht abdeckt, welche sollten wir beschaffen?
Zu 1.
|
1 2 3 4 5 6 7 8 9 |
SELECT title FROM most_popular_film JOIN film f ON most_popular_film.film_id = f.film_id INTERSECT SELECT title FROM inventory JOIN film f ON f.film_id = inventory.film_id; |
+-------------------+ |title | +-------------------+ |Pure Runner | |Chainsaw Uptown | |Champion Flatliners| |Spirit Flintstones | |Beach Heartbreakers| |Sugar Wonka | |Tarzan Videotape | |Blade Polish | |Storm Happiness | +-------------------+
Zu 2.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT title FROM most_popular_film JOIN film f ON most_popular_film.film_id = f.film_id EXCEPT SELECT title FROM most_popular_film JOIN film f ON most_popular_film.film_id = f.film_id INTERSECT -- INTERSECT bindet stärker als EXCEPT SELECT title FROM inventory JOIN film f ON f.film_id = inventory.film_id; |
+-------------+ |title | +-------------+ |Psycho Shrunk| +-------------+