Übungsaufgaben
Übungen mit der Sakila Datenbank. Das Schema finden Sie hier:
Einfache Abfragen
-
[00] - Zeigen Sie alle Spalten und alle Zeilen aus der Tabelle
customer.+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ | customer_id | store_id | first_name | last_name | email | address_id | active | create_date | last_update | +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ | 1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | | 2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | | ... | ... | ... | ... | ... | ... | ... | ... | ... | +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ 599 rows in set (0.001 sec)
-
[01] - Zeigen Sie nur die Spalten
first_name,last_nameundemailaus der Tabellecustomer.+------------+-----------+-------------------------------------+ | first_name | last_name | email | +------------+-----------+-------------------------------------+ | MARY | SMITH | MARY.SMITH@sakilacustomer.org | | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | | ... | ... | ... | +------------+-----------+-------------------------------------+ 599 rows in set (0.001 sec)
-
[02] - Gleiche Abfrage wie zuvor, aber die Spalten
first_nameundlast_namesollen inVornameundNachnameumbenannt werden.ALIAS verwenden.
+----------+----------+-------------------------------------+ | Vorname | Nachname | email | +----------+----------+-------------------------------------+ | MARY | SMITH | MARY.SMITH@sakilacustomer.org | | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | | ... | ... | ... | +----------+----------+-------------------------------------+ 599 rows in set (0.001 sec)
-
[03] - Gleiche Abfrage wie zuvor, aber es sollen nur die ersten 5 Kunden angezeigt werden.
Verwenden Sie LIMIT
+-----------+----------+-------------------------------------+ | Vorname | Nachname | email | +-----------+----------+-------------------------------------+ | MARY | SMITH | MARY.SMITH@sakilacustomer.org | | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | +-----------+----------+-------------------------------------+ 5 rows in set (0.000 sec)
-
[04] - Geben Sie die Vornamen (actor.first_name) aller Schauspieler aus. Sortieren Sie nach den Vornamen.
Verwenden Sie ORDER BY
ASC: Aufsteigend (Ascending)
DESC: Absteigend (Descending)+-------------+ | first_name | +-------------+ | ADAM | | ADAM | | AL | | ALAN | | ALBERT | | ALBERT | | ALEC | | ANGELA | | ANGELA | | ... | | WOODY | | WOODY | | ZERO | +-------------+ 200 rows in set (0.001 sec)
-
[05] - Gleiche Abfrage wie zuvor, aber eliminieren Sie alle Duplikate.
Verwenden Sie SELECT DISTINCT
+-------------+ | first_name | +-------------+ | ADAM | | AL | | ALAN | | ALBERT | | ALEC | | ANGELA | | ANGELINA | | ANNE | | AUDREY | | BELA | | BEN | | BETTE | | BOB | | ... | | WOODY | | ZERO | +-------------+ 128 rows in set (0.001 sec)
Abfragen mit Filter
Mit der WHERE-Klausel filtern
-
[06] - Finden Sie alle Filme (Tabelle
film) mit Länge (length) > 150 Minuten. Zeigen Sie nur die Spaltentitleundlength.+-------------------------+--------+ | Titel | Länge | +-------------------------+--------+ | AGENT TRUMAN | 169 | | ALLEY EVOLUTION | 180 | | ANALYZE HOOSIERS | 181 | | ANONYMOUS HUMAN | 179 | | ANTITRUST TOMATOES | 168 | | APOLLO TEEN | 153 | | ARTIST COLDBLOODED | 170 | | ATLANTIS CAUSE | 170 | | BABY HALL | 153 | | BADMAN DAWN | 162 | | BAKED CLEOPATRA | 182 | | BALLROOM MOCKINGBIRD | 173 | | BEAR GRACELAND | 160 | | BEAUTY GREASE | 175 | | BEETHOVEN EXORCIST | 151 | | BIRCH ANTITRUST | 162 | | BIRD INDEPENDENCE | 163 | | ... | ... | | YOUNG LANGUAGE | 183 | | YOUTH KICK | 179 | +-------------------------+--------+ 242 rows in set (0.000 sec)
-
[07] - Finden Sie alle Filme, deren Mietdauer (rental_duration) genau 5 Tage beträgt. Zeigen Sie nur die Spalten
titleundrental_duration.+-------------------------+-----------+ | Filmtitel | Mietdauer | +-------------------------+-----------+ | AFFAIR PREJUDICE | 5 | | ALIEN CENTER | 5 | | ANTHEM LUKE | 5 | | ANTITRUST TOMATOES | 5 | | APACHE DIVINE | 5 | | APOLLO TEEN | 5 | | ARMAGEDDON LOST | 5 | | ARTIST COLDBLOODED | 5 | | ATTACKS HATE | 5 | | ATTRACTION NEWTON | 5 | | BABY HALL | 5 | | ... | ... | | YENTL IDAHO | 5 | | ZOOLANDER FICTION | 5 | +-------------------------+-----------+ 191 rows in set (0.001 sec)
-
[08] - Finden Sie alle Filme mit Mietpreis (rental_rate) <= 2.99 und Mietdauer von genau 6 Tagen. Zeigen Sie nur die Spalten
title,rental_rateundrental_duration.+-------------------------+-----------+-----------+ | Filmtitel | Mietpreis | Mietdauer | +-------------------------+-----------+-----------+ | ACADEMY DINOSAUR | 0.99 | 6 | | AFRICAN EGG | 2.99 | 6 | | ALAMO VIDEOTAPE | 0.99 | 6 | | ALASKA PHANTOM | 0.99 | 6 | | ALICE FANTASIA | 0.99 | 6 | | ALLEY EVOLUTION | 2.99 | 6 | | ALTER VICTORY | 0.99 | 6 | | AMADEUS HOLY | 0.99 | 6 | | AMISTAD MIDSUMMER | 2.99 | 6 | | ANALYZE HOOSIERS | 2.99 | 6 | | ARABIA DOGMA | 0.99 | 6 | | ARK RIDGEMONT | 0.99 | 6 | | ATLANTIS CAUSE | 2.99 | 6 | | BADMAN DAWN | 2.99 | 6 | | ... | ... | ... | | YOUNG LANGUAGE | 0.99 | 6 | | ZHIVAGO CORE | 0.99 | 6 | +-------------------------+-----------+-----------+ 146 rows in set (0.001 sec)
Um nach Mustern in Spalten zu suchen, verwenden Sie den LIKE Operator
-
[09] - Welche Schauspieler haben den Vornamen 'Tom'?
+----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 38 | TOM | MCKELLEN | | 42 | TOM | MIRANDA | +----------+------------+-----------+ 2 rows in set (0.000 sec)
-
[10] - Welche Schauspieler haben den Nachnamen 'Johansson'?
+----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 8 | MATTHEW | JOHANSSON | | 64 | RAY | JOHANSSON | | 146 | ALBERT | JOHANSSON | +----------+------------+-----------+ 3 rows in set (0.000 sec)
-
[11] - Welche Schauspieler haben einen Nachnamen, der an der 2. Stelle ein 'i' hat?
+----------+------------+-------------+ | actor_id | first_name | last_name | +----------+------------+-------------+ | 6 | BETTE | NICHOLSON | | 23 | SANDRA | KILMER | | 42 | TOM | MIRANDA | | 45 | REESE | KILMER | | 54 | PENELOPE | PINKETT | | 55 | FAY | KILMER | | 68 | RIP | WINSLET | | 72 | SEAN | WILLIAMS | | 78 | GROUCHO | SINATRA | | 83 | BEN | WILLIS | | 84 | JAMES | PITT | | 96 | GENE | WILLIS | | 137 | MORGAN | WILLIAMS | | 144 | ANGELA | WITHERSPOON | | 147 | FAY | WINSLET | | 153 | MINNIE | KILMER | | 154 | MERYL | GIBSON | | 162 | OPRAH | KILMER | | 164 | HUMPHREY | WILLIS | | 168 | WILL | WILSON | | 172 | GROUCHO | WILLIAMS | | 180 | JEFF | SILVERSTONE | | 189 | CUBA | BIRCH | | 195 | JAYNE | SILVERSTONE | +----------+------------+-------------+ 24 rows in set (0.001 sec)
-
[12] - Welche Schauspieler haben einen Vornamen, der mit einem 'A' anfängt und mit einem 'a' aufhört?
Bei der Suche nach Mustern in Strings ist Groß- und Kleinschreibung egal.
+----------+------------+-------------+ | actor_id | first_name | last_name | +----------+------------+-------------+ | 65 | ANGELA | HUDSON | | 76 | ANGELINA | ASTAIRE | | 144 | ANGELA | WITHERSPOON | +----------+------------+-------------+ 3 rows in set (0.000 sec)
-
[13] - Welche Schauspieler haben einen Nachnamen, der genau 5 Zeichen lang ist?
+----------+-------------+-----------+ | actor_id | first_name | last_name | +----------+-------------+-----------+ | 3 | ED | CHASE | | 4 | JENNIFER | DAVIS | | 9 | JOE | SWANK | | 10 | CHRISTIAN | GABLE | | 12 | KARL | BERRY | | 20 | LUCILLE | TRACY | | 25 | KEVIN | BLOOM | | 29 | ALEC | WAYNE | | 39 | GOLDIE | BRODY | | 60 | HENRY | BERRY | | 66 | MARY | TANDY | | 71 | ADAM | GRANT | | 75 | BURT | POSEY | | 81 | SCARLETT | DAMON | | 82 | WOODY | JOLIE | | 88 | KENNETH | PESCI | | 89 | CHARLIZE | DENCH | | 91 | CHRISTOPHER | BERRY | | 97 | MEG | HAWKE | | 101 | SUSAN | DAVIS | | 103 | MATTHEW | LEIGH | | 105 | SIDNEY | CROWE | | 106 | GROUCHO | DUNST | | 108 | WARREN | NOLTE | | 110 | SUSAN | DAVIS | | 117 | RENEE | TRACY | | 118 | CUBA | ALLEN | | 122 | SALMA | NOLTE | | 123 | JULIANNE | DENCH | | 125 | ALBERT | NOLTE | | 126 | FRANCES | TOMEI | | 142 | JADA | RYDER | | 145 | KIM | ALLEN | | 150 | JAYNE | NOLTE | | 155 | IAN | TANDY | | 159 | LAURA | BRODY | | 176 | JON | CHASE | | 183 | RUSSELL | CLOSE | | 189 | CUBA | BIRCH | | 194 | MERYL | ALLEN | +----------+-------------+-----------+ 40 rows in set (0.000 sec)
-
[14] - Finden Sie alle Schauspieler, deren Vorname genauso lang ist wie ihr Nachname.
Verwenden Sie die eingebaute Funktion LENGTH
+----------+------------+-----------+--------+ | actor_id | first_name | last_name | Länge | +----------+------------+-----------+--------+ | 11 | ZERO | CAGE | 4 | | 14 | VIVIEN | BERGEN | 6 | | 21 | KIRSTEN | PALTROW | 7 | | 23 | SANDRA | KILMER | 6 | | 25 | KEVIN | BLOOM | 5 | | 35 | JUDY | DEAN | 4 | | 50 | NATALIE | HOPKINS | 7 | | 59 | DUSTIN | TAUTOU | 6 | | 60 | HENRY | BERRY | 5 | | 65 | ANGELA | HUDSON | 6 | | 69 | KENNETH | PALTROW | 7 | | 73 | GARY | PENN | 4 | | 78 | GROUCHO | SINATRA | 7 | | 82 | WOODY | JOLIE | 5 | | 101 | SUSAN | DAVIS | 5 | | 110 | SUSAN | DAVIS | 5 | | 117 | RENEE | TRACY | 5 | | 122 | SALMA | NOLTE | 5 | | 150 | JAYNE | NOLTE | 5 | | 153 | MINNIE | KILMER | 6 | | 159 | LAURA | BRODY | 5 | | 169 | KENNETH | HOFFMAN | 7 | | 175 | WILLIAM | HACKMAN | 7 | | 182 | DEBBIE | AKROYD | 6 | | 190 | AUDREY | BAILEY | 6 | | 191 | GREGORY | GOODING | 7 | | 194 | MERYL | ALLEN | 5 | +----------+------------+-----------+--------+ 27 rows in set (0.000 sec)
-
[15] - Finden Sie alle Filme, deren Beschreibung (film.description) den Teilstring "database" enthält.
+---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------+ | film_id | title | description | +---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------+ | 2 | ACE GOLDFINGER | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | | 9 | ALABAMA DEVIL | A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat | | 14 | ALICE FANTASIA | A Emotional Drama of a A Shark And a Database Administrator who must Vanquish a Pioneer in Soviet Georgia | | 21 | AMERICAN CIRCUS | A Insightful Drama of a Girl And a Astronaut who must Face a Database Administrator in A Shark Tank | | 27 | ANONYMOUS HUMAN | A Amazing Reflection of a Database Administrator And a Astronaut who must Outrace a Database Administrator in A Shark Tank | | 29 | ANTITRUST TOMATOES | A Fateful Yarn of a Womanizer And a Feminist who must Succumb a Database Administrator in Ancient India | | 40 | ARMY FLINTSTONES | A Boring Saga of a Database Administrator And a Womanizer who must Battle a Waitress in Nigeria | | 91 | BOUND CHEAPER | A Thrilling Panorama of a Database Administrator And a Astronaut who must Challenge a Lumberjack in A Baloon | | 108 | BUTCH PANTHER | A Lacklusture Yarn of a Feminist And a Database Administrator who must Face a Hunter in New Orleans | | 113 | CALIFORNIA BIRDS | A Thrilling Yarn of a Database Administrator And a Robot who must Battle a Database Administrator in Ancient India | | ... | ... | ... | | 969 | WEST LION | A Intrepid Drama of a Butler And a Lumberjack who must Challenge a Database Administrator in A Manhattan Penthouse | | 971 | WHALE BIKINI | A Intrepid Story of a Pastry Chef And a Database Administrator who must Kill a Feminist in A MySQL Convention | | 996 | YOUNG LANGUAGE | A Unbelieveable Yarn of a Boat And a Database Administrator who must Meet a Boy in The First Manned Space Station | +---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------+ 76 rows in set (0.001 sec)
Abfragen mit Aggregationen
-
[16] - Wie viele Filme sind in der Datenbank gespeichert?
+-----------+ | num_films | +-----------+ | 1000 | +-----------+ 1 row in set (0.000 sec)
-
[17] - Wie viele Schauspieler gibt es insgesamt?
+------------+ | num_actors | +------------+ | 200 | +------------+ 1 row in set (0.000 sec)
-
[18] - Wie viele unterschiedliche Nachnamen (actor.last_name) gibt es in der actor Tabelle?
DISTINCT (Auf der Seite runterscrollen)
COUNT DISTINCT (stackoverflow)+----------------------+ | distinct_actor_names | +----------------------+ | 121 | +----------------------+ 1 row in set (0.000 sec)
-
[19] - Wie viele verschiedene Filmbewertungen (film.rating) gibt es?
+------------------------+ | disctinct_film_ratings | +------------------------+ | 5 | +------------------------+ 1 row in set (0.000 sec)
-
[20] - Wie ist die durchschnittliche Laufzeit (film.length) aller Filme?
+-----------------+ | avg_film_length | +-----------------+ | 115.272 | +-----------------+ 1 row in set (0.000 sec)
-
[21] - Finden Sie den kleinsten Mietpreis (film.rental_rate) aller Filme.
+-----------------+ | min_rental_rate | +-----------------+ | 0.99 | +-----------------+ 1 row in set (0.000 sec)
-
[22] - Finden Sie die maximale Mietdauer (film.rental_duration) aller Filme.
+-----------------+ | max_rental_days | +-----------------+ | 7 | +-----------------+ 1 row in set (0.001 sec)
-
[23] - Finden Sie heraus, wieviele Zahlungen bisher gespeichert wurden.
Die Einträge zu Zahlungen sind in der Tabelle
paymentgespeichert.+--------------+ | num_payments | +--------------+ | 16044 | +--------------+ 1 row in set (0.003 sec)
-
[24] - Summieren Sie die Beträge (payment.amount) aller Zahlungen.
+--------------+ | sum_payments | +--------------+ | 67406.56 | +--------------+ 1 row in set (0.004 sec)
-
[25] - Finden Sie den kleinsten, größten und den durchschnittlichen Betrag (payment.amount) aller Zahlungen.
+------+-------+----------+ | min | max | avg | +------+-------+----------+ | 0.00 | 11.99 | 4.201356 | +------+-------+----------+ 1 row in set (0.004 sec)
Abfragen mit Aggregationen und Gruppierung
-
[26] - Wie viele Filme gibt es pro Altersfreigabe (film.rating)?
+--------+------------+ | rating | film_count | +--------+------------+ | G | 178 | | PG | 194 | | PG-13 | 223 | | R | 195 | | NC-17 | 210 | +--------+------------+ 5 rows in set (0.001 sec)
-
[27] - Wie hoch ist die durchschnittliche Mietdauer (film.rental_duration) pro Altersfreigabe (film.rating)?
+--------+---------------------+ | rating | avg_rental_duration | +--------+---------------------+ | G | 4.8371 | | PG | 5.0825 | | PG-13 | 5.0538 | | R | 4.7744 | | NC-17 | 5.1429 | +--------+---------------------+ 5 rows in set (0.001 sec)
-
[28] - Wie ist die durchschnittliche Laufzeit (film.length) der Filme, gruppiert nach Altersfreigabe (film.rating)?
+--------+------------+ | rating | avg_length | +--------+------------+ | G | 111.0506 | | PG | 112.0052 | | NC-17 | 113.2286 | | R | 118.6615 | | PG-13 | 120.4439 | +--------+------------+ 5 rows in set (0.001 sec)
-
[29] - Finden Sie den kleinsten, größten und den durchschnittlichen Betrag (payment.amount) aller Zahlungen pro Kunde (payment.customer_id).
+-------------+------+-------+----------+ | customer_id | min | max | avg | +-------------+------+-------+----------+ | 1 | 0.99 | 9.99 | 3.708750 | | 2 | 0.99 | 10.99 | 4.767778 | | 3 | 0.99 | 10.99 | 5.220769 | | 4 | 0.99 | 8.99 | 3.717273 | | 5 | 0.99 | 9.99 | 3.805789 | | 6 | 0.99 | 7.99 | 3.347143 | | 7 | 0.99 | 8.99 | 4.596061 | | 8 | 0.99 | 9.99 | 3.865000 | | 9 | 0.99 | 7.99 | 3.903043 | | 10 | 0.99 | 8.99 | 3.990000 | | 11 | 0.99 | 9.99 | 4.448333 | | 12 | 0.99 | 10.99 | 3.704286 | | ... | ... | ... | ... | | 597 | 0.99 | 8.99 | 3.990000 | | 598 | 0.99 | 7.99 | 3.808182 | | 599 | 0.99 | 9.99 | 4.411053 | +-------------+------+-------+----------+ 599 rows in set (0.017 sec)
-
[30] - Welche Altersfreigabe (film.rating) hat die meisten Filme?
Den 1. Teil dieser Aufgabe haben Sie schon in Aufgabe [26] erledigt.
Sortieren Sie nach der aggregierten Spalte und limitieren Sie das Ergebnis auf eine Zeile.+--------+------------+ | rating | film_count | +--------+------------+ | PG-13 | 223 | +--------+------------+ 1 row in set (0.001 sec)
Abfragen mit Joins
-
[31] - Geben Sie für jeden Kunden (customer) seine vollständigen Adressinformationen aus.
Tabellen: customer, address, city, country
Anzuzeigende Spalten: customer_id, first_name, last_name, address, district, postal_code, city, country+-------------+-------------+--------------+-------------------------------------+----------------------+-------------+-----------------+-----------------+ | customer_id | first_name | last_name | address | district | postal_code | city | country | +-------------+-------------+--------------+-------------------------------------+----------------------+-------------+-----------------+-----------------+ | 1 | MARY | SMITH | 1913 Hanoi Way | Nagasaki | 35200 | Sasebo | Japan | | 2 | PATRICIA | JOHNSON | 1121 Loja Avenue | California | 17886 | San Bernardino | United States | | 3 | LINDA | WILLIAMS | 692 Joliet Street | Attika | 83579 | Athenai | Greece | | 4 | BARBARA | JONES | 1566 Inegöl Manor | Mandalay | 53561 | Myingyan | Myanmar | | 5 | ELIZABETH | BROWN | 53 Idfu Parkway | Nantou | 42399 | Nantou | Taiwan | | 6 | JENNIFER | DAVIS | 1795 Santiago de Compostela Way | Texas | 18743 | Laredo | United States | | 7 | MARIA | MILLER | 900 Santiago de Compostela Parkway | Central Serbia | 93896 | Kragujevac | Yugoslavia | | 8 | SUSAN | WILSON | 478 Joliet Way | Hamilton | 77948 | Hamilton | New Zealand | | 9 | MARGARET | MOORE | 613 Korolev Drive | Masqat | 45844 | Masqat | Oman | | 10 | DOROTHY | TAYLOR | 1531 Salé Drive | Esfahan | 53628 | Esfahan | Iran | | 11 | LISA | ANDERSON | 1542 Tarlac Parkway | Kanagawa | 1027 | Sagamihara | Japan | | 12 | NANCY | THOMAS | 808 Bhopal Manor | Haryana | 10672 | Yamuna Nagar | India | | 13 | KAREN | JACKSON | 270 Amroha Parkway | Osmaniye | 29610 | Osmaniye | Turkey | | ... | ... | ... | ... | ... | ... | ... | ... | | 593 | RENE | MCALISTER | 1895 Zhezqazghan Drive | California | 36693 | Garden Grove | United States | | 594 | EDUARDO | HIATT | 1837 Kaduna Parkway | Inner Mongolia | 82580 | Jining | China | | 595 | TERRENCE | GUNDERSON | 844 Bucuresti Place | Liaoning | 36603 | Jinzhou | China | | 596 | ENRIQUE | FORSYTHE | 1101 Bucuresti Boulevard | West Greece | 97661 | Patras | Greece | | 597 | FREDDIE | DUGGAN | 1103 Quilmes Boulevard | Piura | 52137 | Sullana | Peru | | 598 | WADE | DELVALLE | 1331 Usak Boulevard | Vaud | 61960 | Lausanne | Switzerland | | 599 | AUSTIN | CINTRON | 1325 Fukuyama Street | Heilongjiang | 27107 | Tieli | China | +-------------+-------------+--------------+-------------------------------------+----------------------+-------------+-----------------+-----------------+ 599 rows in set (0.005 sec)
-
[32] - Finden Sie alle deutschen Kunden.
Tabellen: customer, address, city, country
+-------------+------------+------------+---------+ | customer_id | first_name | last_name | country | +-------------+------------+------------+---------+ | 114 | GRACE | ELLIS | Germany | | 195 | VANESSA | SIMS | Germany | | 196 | ALMA | AUSTIN | Germany | | 201 | VICKI | FIELDS | Germany | | 227 | COLLEEN | BURTON | Germany | | 251 | VICKIE | BREWER | Germany | | 448 | MIGUEL | BETANCOURT | Germany | +-------------+------------+------------+---------+ 7 rows in set (0.001 sec)
-
[33] - Welche Schauspieler spielen in dem Film „ACADEMY DINOSAUR“ mit?
Tabellen: film, actor, film_actor
+----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 1 | PENELOPE | GUINESS | | 10 | CHRISTIAN | GABLE | | 20 | LUCILLE | TRACY | | 30 | SANDRA | PECK | | 40 | JOHNNY | CAGE | | 53 | MENA | TEMPLE | | 108 | WARREN | NOLTE | | 162 | OPRAH | KILMER | | 188 | ROCK | DUKAKIS | | 198 | MARY | KEITEL | +----------+------------+-----------+ 10 rows in set (0.001 sec)
-
[34] - Welche Kunden haben den Film „ACADEMY DINOSAUR“ bereits ausgeliehen?
Tabellen: film, inventory, rental, customer
+-------------+------------+-------------+ | customer_id | first_name | last_name | +-------------+------------+-------------+ | 8 | SUSAN | WILSON | | 34 | REBECCA | SCOTT | | 39 | DEBRA | NELSON | | 44 | MARIE | TURNER | | 92 | TINA | SIMMONS | | 161 | GERALDINE | PERKINS | | 170 | BEATRICE | ARNOLD | | 252 | MATTIE | HOFFMAN | | 279 | DIANNE | SHELTON | | 301 | ROBERT | BAUGHMAN | | 344 | HENRY | BILLINGSLEY | | 345 | CARL | ARTIS | | 359 | WILLIE | MARKHAM | | 406 | NATHAN | RUNYON | | 411 | NORMAN | CURRIER | | 431 | JOEL | FRANCISCO | | 487 | HECTOR | POINDEXTER | | 518 | GABRIEL | HARDER | | 541 | DARREN | WINDHAM | | 554 | DWAYNE | OLVERA | | 581 | VIRGIL | WOFFORD | | 587 | SERGIO | STANFIELD | | 597 | FREDDIE | DUGGAN | +-------------+------------+-------------+ 23 rows in set (0.001 sec)
-
[35] - In der Datenbank werden auch Städte und Länder verwaltet. Finden Sie heraus, wie viele Städte pro Land gespeichert sind.
Tabellen: city, country
+------------+---------------------------------------+------------+ | country_id | country | num_cities | +------------+---------------------------------------+------------+ | 1 | Afghanistan | 1 | | 2 | Algeria | 3 | | 3 | American Samoa | 1 | | 4 | Angola | 2 | | 5 | Anguilla | 1 | | 6 | Argentina | 13 | | 7 | Armenia | 1 | | 8 | Australia | 1 | | 9 | Austria | 3 | | 10 | Azerbaijan | 2 | | 11 | Bahrain | 1 | | 12 | Bangladesh | 3 | | 13 | Belarus | 2 | | 14 | Bolivia | 2 | | 15 | Brazil | 28 | | 16 | Brunei | 1 | | 17 | Bulgaria | 2 | | 18 | Cambodia | 2 | | 19 | Cameroon | 2 | | 20 | Canada | 7 | | 21 | Chad | 1 | | 22 | Chile | 3 | | 23 | China | 53 | | 24 | Colombia | 6 | | 25 | Congo, The Democratic Republic of the | 2 | | 26 | Czech Republic | 1 | | 27 | Dominican Republic | 3 | | 28 | Ecuador | 3 | | 29 | Egypt | 6 | | 30 | Estonia | 1 | | 31 | Ethiopia | 1 | | 32 | Faroe Islands | 1 | | 33 | Finland | 1 | | 34 | France | 4 | | 35 | French Guiana | 1 | | 36 | French Polynesia | 2 | | 37 | Gambia | 1 | | 38 | Germany | 7 | | 39 | Greece | 2 | | 40 | Greenland | 1 | | 41 | Holy See (Vatican City State) | 1 | | 42 | Hong Kong | 1 | | 43 | Hungary | 1 | | 44 | India | 60 | | 45 | Indonesia | 14 | | 46 | Iran | 8 | | 47 | Iraq | 1 | | 48 | Israel | 4 | | 49 | Italy | 7 | | 50 | Japan | 31 | | 51 | Kazakstan | 2 | | 52 | Kenya | 2 | | 53 | Kuwait | 1 | | 54 | Latvia | 2 | | 55 | Liechtenstein | 1 | | 56 | Lithuania | 1 | | 57 | Madagascar | 1 | | 58 | Malawi | 1 | | 59 | Malaysia | 3 | | 60 | Mexico | 30 | | 61 | Moldova | 1 | | 62 | Morocco | 3 | | 63 | Mozambique | 3 | | 64 | Myanmar | 2 | | 65 | Nauru | 1 | | 66 | Nepal | 1 | | 67 | Netherlands | 5 | | 68 | New Zealand | 1 | | 69 | Nigeria | 13 | | 70 | North Korea | 1 | | 71 | Oman | 2 | | 72 | Pakistan | 5 | | 73 | Paraguay | 3 | | 74 | Peru | 4 | | 75 | Philippines | 20 | | 76 | Poland | 8 | | 77 | Puerto Rico | 2 | | 79 | Réunion | 1 | | 78 | Romania | 2 | | 80 | Russian Federation | 28 | | 81 | Saint Vincent and the Grenadines | 1 | | 82 | Saudi Arabia | 5 | | 83 | Senegal | 1 | | 84 | Slovakia | 1 | | 85 | South Africa | 11 | | 86 | South Korea | 5 | | 87 | Spain | 5 | | 88 | Sri Lanka | 1 | | 89 | Sudan | 2 | | 90 | Sweden | 1 | | 91 | Switzerland | 3 | | 92 | Taiwan | 10 | | 93 | Tanzania | 3 | | 94 | Thailand | 3 | | 95 | Tonga | 1 | | 96 | Tunisia | 1 | | 97 | Turkey | 15 | | 98 | Turkmenistan | 1 | | 99 | Tuvalu | 1 | | 100 | Ukraine | 6 | | 101 | United Arab Emirates | 3 | | 102 | United Kingdom | 8 | | 103 | United States | 35 | | 104 | Venezuela | 7 | | 105 | Vietnam | 6 | | 106 | Virgin Islands, U.S. | 1 | | 107 | Yemen | 4 | | 108 | Yugoslavia | 2 | | 109 | Zambia | 1 | +------------+---------------------------------------+------------+ 109 rows in set (0.001 sec)
-
[36] - In wie vielen Filmen hat jeder Schauspieler mitgespielt?
Tabellen: actor, film_actor
Gemeinsame Spalten: actor_id+----------+-------------+--------------+------------+ | actor_id | first_name | last_name | film_count | +----------+-------------+--------------+------------+ | 1 | PENELOPE | GUINESS | 19 | | 2 | NICK | WAHLBERG | 25 | | 3 | ED | CHASE | 22 | | 4 | JENNIFER | DAVIS | 22 | | 5 | JOHNNY | LOLLOBRIGIDA | 29 | | 6 | BETTE | NICHOLSON | 20 | | 7 | GRACE | MOSTEL | 30 | | 8 | MATTHEW | JOHANSSON | 20 | | 9 | JOE | SWANK | 25 | | 10 | CHRISTIAN | GABLE | 22 | | 11 | ZERO | CAGE | 25 | | 12 | KARL | BERRY | 31 | | 13 | UMA | WOOD | 35 | | ... | ... | ... | ... | | 194 | MERYL | ALLEN | 22 | | 195 | JAYNE | SILVERSTONE | 27 | | 196 | BELA | WALKEN | 30 | | 197 | REESE | WEST | 33 | | 198 | MARY | KEITEL | 40 | | 199 | JULIA | FAWCETT | 15 | | 200 | THORA | TEMPLE | 20 | +----------+-------------+--------------+------------+ 200 rows in set (0.005 sec)
-
[37] - Wie viele Filme gibt es pro Kategorie?
Tabellen: category, film_category
+----------------+--------------+ | Film_Kategorie | Anzahl_Filme | +----------------+--------------+ | Sports | 74 | | Foreign | 73 | | Family | 69 | | Documentary | 68 | | Animation | 66 | | Action | 64 | | New | 63 | | Drama | 62 | | Games | 61 | | Sci-Fi | 61 | | Children | 60 | | Comedy | 58 | | Classics | 57 | | Travel | 57 | | Horror | 56 | | Music | 51 | +----------------+--------------+ 16 rows in set (0.001 sec)
-
[38] - Finden Sie die Top 3 der am häufigsten ausgeliehenen Filmkategorien.
Tabellen: rental, inventory, film_category, category
+-------------+-----------+-------------+ | category_id | name | num_rentals | +-------------+-----------+-------------+ | 15 | Sports | 1179 | | 2 | Animation | 1166 | | 1 | Action | 1112 | +-------------+-----------+-------------+ 3 rows in set (0.024 sec)
-
[39] - Finden Sie die Top 3 der am wenigsten ausgeliehenen Filmkategorien.
Tabellen: rental, inventory, film_category, category
+-------------+--------+-------------+ | category_id | name | num_rentals | +-------------+--------+-------------+ | 12 | Music | 830 | | 16 | Travel | 837 | | 11 | Horror | 846 | +-------------+--------+-------------+ 3 rows in set (0.018 sec)
-
[40] - Für Premiumkunden soll eine Werbeaktion durchgeführt werden. Finden Sie diese Premiumkunden (das sind die Kunden, die bisher mehr als 30 Filme ausgeliehen haben).
Tabellen: rental, customer
+-------------+-------------+--------------+-------------+ | customer_id | first_name | last_name | num_rentals | +-------------+-------------+--------------+-------------+ | 148 | ELEANOR | HUNT | 46 | | 526 | KARL | SEAL | 45 | | 144 | CLARA | SHAW | 42 | | 236 | MARCIA | DEAN | 42 | | 75 | TAMMY | SANDERS | 41 | | 197 | SUE | PETERS | 40 | | 469 | WESLEY | BULL | 40 | | 137 | RHONDA | KENNEDY | 39 | | 468 | TIM | CARY | 39 | | 178 | MARION | SNYDER | 39 | | 410 | CURTIS | IRBY | 38 | | 295 | DAISY | BATES | 38 | | 5 | ELIZABETH | BROWN | 38 | | ... | ... | ... | ... | | 27 | SHIRLEY | ALLEN | 31 | | 251 | VICKIE | BREWER | 31 | | 279 | DIANNE | SHELTON | 31 | | 309 | CHRISTOPHER | GRECO | 31 | | 260 | CHRISTY | VARGAS | 31 | | 416 | JEFFERY | PINSON | 31 | | 446 | THEODORE | CULP | 31 | +-------------+-------------+--------------+-------------+ 134 rows in set (0.020 sec)
-
[41] - Finden Sie heraus, wieviele Filme jeder deutsche Kunde bisher ausgeliehen hat.
Tabellen: customer, address, city, country, rental
+-------------+------------+------------+--------------------+ | customer_id | first_name | last_name | Ausgeliehene_Filme | +-------------+------------+------------+--------------------+ | 114 | GRACE | ELLIS | 33 | | 195 | VANESSA | SIMS | 19 | | 196 | ALMA | AUSTIN | 35 | | 201 | VICKI | FIELDS | 25 | | 227 | COLLEEN | BURTON | 24 | | 251 | VICKIE | BREWER | 31 | | 448 | MIGUEL | BETANCOURT | 29 | +-------------+------------+------------+--------------------+ 7 rows in set (0.001 sec)
-
[42] - Finden Sie heraus, wieviel jeder deutsche Kunde bisher an Ausleih-Gebühren bezahlt hat.
Tabellen: customer, address, city, country, payment
+-------------+------------+------------+-------------------+ | customer_id | first_name | last_name | Ausleih_Gebühren | +-------------+------------+------------+-------------------+ | 114 | GRACE | ELLIS | 139.67 | | 195 | VANESSA | SIMS | 86.81 | | 196 | ALMA | AUSTIN | 151.65 | | 201 | VICKI | FIELDS | 108.75 | | 227 | COLLEEN | BURTON | 87.76 | | 251 | VICKIE | BREWER | 120.69 | | 448 | MIGUEL | BETANCOURT | 135.71 | +-------------+------------+------------+-------------------+ 7 rows in set (0.001 sec)
-
[43] - Was ist die Lieblings-Filmkategorie von deutschen Kunden?.
Tabellen: customer, address, city, country, rental, inventory, film, film_category, category
+-----------+-------------+ | Kategorie | Häufigkeit | +-----------+-------------+ | Animation | 21 | +-----------+-------------+ 1 row in set (0.138 sec)
-
[44] - Finden Sie die 3 Kunden, die bisher das meiste Geld für das Ausleihen von Filmen bezahlt haben.
Tabellen: customer, payment
+-------------+------------+-----------+----------------+ | customer_id | first_name | last_name | bisher_bezahlt | +-------------+------------+-----------+----------------+ | 526 | KARL | SEAL | 221.55 | | 148 | ELEANOR | HUNT | 216.54 | | 144 | CLARA | SHAW | 195.58 | +-------------+------------+-----------+----------------+ 3 rows in set (0.021 sec)
-
[45] - Wählen Sie alle Filme aus, die von deutschen Kunden ausgeliehen wurden.
Tabellen: customer, address, city, country, rental, inventory, film
+---------+-------------------------+ | film_id | title | +---------+-------------------------+ | 8 | AIRPORT POLLOCK | | 11 | ALAMO VIDEOTAPE | | 21 | AMERICAN CIRCUS | | 29 | ANTITRUST TOMATOES | | 30 | ANYTHING SAVANNAH | | 42 | ARTIST COLDBLOODED | | 43 | ATLANTIS CAUSE | | 51 | BALLOON HOMEWARD | | 60 | BEAST HUNCHBACK | | 63 | BEDAZZLED MARRIED | | 68 | BETRAYED REAR | | 72 | BILL OTHERS | | 77 | BIRDS PERDITION | | ... | ... | | 932 | VALLEY PACKER | | 936 | VANISHING ROCKY | | 944 | VIRGIN DAISY | | 956 | WANDA CHAMBER | | 964 | WATERFRONT DELIVERANCE | | 972 | WHISPERER GIANT | | 989 | WORKING MICROCOSMOS | +---------+-------------------------+ 179 rows in set (0.002 sec)
-
[46] - Finden Sie heraus, wie viele Kunden aus jedem Land kommen, absteigend nach der Anzahl der Kunden sortiert.
Tabellen: customer, address, city, country
+---------------------------------------+---------------+ | Land | Anzahl_Kunden | +---------------------------------------+---------------+ | India | 60 | | China | 53 | | United States | 36 | | Japan | 31 | | Mexico | 30 | | Brazil | 28 | | Russian Federation | 28 | | Philippines | 20 | | Turkey | 15 | | Indonesia | 14 | | Argentina | 13 | | Nigeria | 13 | | South Africa | 11 | | Taiwan | 10 | | United Kingdom | 9 | | Iran | 8 | | Poland | 8 | | Venezuela | 7 | | Germany | 7 | | Italy | 7 | | Vietnam | 6 | | Colombia | 6 | | Ukraine | 6 | | Egypt | 6 | | Netherlands | 5 | | South Korea | 5 | | Pakistan | 5 | | Canada | 5 | | Saudi Arabia | 5 | | Spain | 5 | | France | 4 | | Yemen | 4 | | Peru | 4 | | Israel | 4 | | Algeria | 3 | | Chile | 3 | | Austria | 3 | | Paraguay | 3 | | Ecuador | 3 | | Dominican Republic | 3 | | Malaysia | 3 | | Switzerland | 3 | | Bangladesh | 3 | | Mozambique | 3 | | United Arab Emirates | 3 | | Morocco | 3 | | Thailand | 3 | | Tanzania | 3 | | Latvia | 2 | | Bolivia | 2 | | Romania | 2 | | Kenya | 2 | | Greece | 2 | | Oman | 2 | | Belarus | 2 | | Puerto Rico | 2 | | Myanmar | 2 | | Cameroon | 2 | | Kazakstan | 2 | | Congo, The Democratic Republic of the | 2 | | Sudan | 2 | | Yugoslavia | 2 | | Cambodia | 2 | | Angola | 2 | | Bulgaria | 2 | | French Polynesia | 2 | | Azerbaijan | 2 | | French Guiana | 1 | | Tuvalu | 1 | | Holy See (Vatican City State) | 1 | | Iraq | 1 | | Réunion | 1 | | Afghanistan | 1 | | Nepal | 1 | | Turkmenistan | 1 | | Chad | 1 | | Kuwait | 1 | | Armenia | 1 | | Greenland | 1 | | Finland | 1 | | Nauru | 1 | | Slovakia | 1 | | Czech Republic | 1 | | Malawi | 1 | | Sweden | 1 | | Zambia | 1 | | Faroe Islands | 1 | | Tunisia | 1 | | Senegal | 1 | | Anguilla | 1 | | North Korea | 1 | | Madagascar | 1 | | Ethiopia | 1 | | Tonga | 1 | | Gambia | 1 | | Lithuania | 1 | | Sri Lanka | 1 | | Bahrain | 1 | | Hungary | 1 | | Estonia | 1 | | Saint Vincent and the Grenadines | 1 | | American Samoa | 1 | | New Zealand | 1 | | Liechtenstein | 1 | | Hong Kong | 1 | | Virgin Islands, U.S. | 1 | | Moldova | 1 | | Brunei | 1 | +---------------------------------------+---------------+ 108 rows in set (0.002 sec)
-
[47] - Gleiche Abfrage wie zuvor, aber zeigen Sie nur die Länder, aus denen mindestens 10 Kunden stammen.
Tabellen: customer, address, city, country
+--------------------+---------------+ | Land | Anzahl_Kunden | +--------------------+---------------+ | India | 60 | | China | 53 | | United States | 36 | | Japan | 31 | | Mexico | 30 | | Brazil | 28 | | Russian Federation | 28 | | Philippines | 20 | | Turkey | 15 | | Indonesia | 14 | | Argentina | 13 | | Nigeria | 13 | | South Africa | 11 | | Taiwan | 10 | +--------------------+---------------+ 14 rows in set (0.003 sec)
-
[48] - In der country Tabelle gibt es ein Land, aus dem kein Kunde kommt. Finden Sie dieses Land.
Tabellen: customer, address, city, country
+------------+-----------+ | country_id | country | +------------+-----------+ | 8 | Australia | +------------+-----------+ 1 row in set (0.002 sec)
-
[49] - Finden Sie die Städte, in denen keine Kunden wohnen.
Tabellen: customer, address, city, country
+---------+------------+-----------+ | city_id | city | country | +---------+------------+-----------+ | 576 | Woodridge | Australia | | 300 | Lethbridge | Canada | | 313 | London | Canada | +---------+------------+-----------+ 3 rows in set (0.002 sec)
-
[50] - Für eine Werbeaktion sollen Sie alle Kunden finden, die noch nie einen Film aus der Kategorie Horror ausgeliehen haben.
Subquery verwenden: Subqueries
Tabellen: customer, rental, inventory, film_category, category+-------------+-------------+-------------+ | customer_id | first_name | last_name | +-------------+-------------+-------------+ | 1 | MARY | SMITH | | 2 | PATRICIA | JOHNSON | | 6 | JENNIFER | DAVIS | | 16 | SANDRA | MARTIN | | 17 | DONNA | THOMPSON | | 20 | SHARON | ROBINSON | | 27 | SHIRLEY | ALLEN | | 31 | BRENDA | WRIGHT | | 37 | PAMELA | BAKER | | 41 | STEPHANIE | MITCHELL | | 45 | JANET | PHILLIPS | | 47 | FRANCES | PARKER | | 48 | ANN | EVANS | | ... | ... | ... | | 574 | JULIAN | VEST | | 576 | MORRIS | MCCARTER | | 581 | VIRGIL | WOFFORD | | 582 | ANDY | VANHORN | | 590 | SETH | HANNON | | 591 | KENT | ARSENAULT | | 592 | TERRANCE | ROUSH | +-------------+-------------+-------------+ 148 rows in set (0.005 sec)