***** Übungen: MySQL - SELECT: Mehrere Tabellen mit JOINs abfragen ***** MySQL06_1: Kartesisches Produkt Erklären Sie in eigenen Worten, warum diese Abfrage kein sinnvolles Ergebnis gibt: --- MySQL-Code Start --- SELECT * FROM kunden, orte --- Code Ende --- @@@ MySQL06_2: Einfache Abfragen über zwei Tabellen - DB Kunden-einfach Benutzen Sie für diese Übung diese Datenbank: *LINK 07mysql/_dumps/kunden/kunden-einfach.sql LINK* Hier das zugehörige ER-Diagramm: *LINK 07mysql/_dumps/kunden/kunde_einfach_ERD.png LINK* A) Geben Sie Name, Postleitzahl und Wohnort aller Kunden aus. Die Liste enthält den Kundennamen und den Namen des Orts, wo er wohnt. B) Geben Sie Name und Wohnort aller Kunden aus, die die Postleitzahl 79312 haben. C) Geben Sie Name und Wohnort aller Kunden aus, die in Emmendingen wohnen (Einschränkungskriterium ist NICHT die Postleitzahl, sondern 'Emmendingen'). D) Geben Sie Name, Wohnort und Einwohnerzahl für alle Kunden aus, die in einem Ort mit mehr als 70000 Einwohnern wohnen. E) Geben Sie alle Orte aus, die weniger als 1000000 Einwohner haben. F) Geben Sie Kundename und Ortname aus für alle Kunden, die in Orten mit einer Einwohnerzahl zwischen 100.000 und 1.500.000 leben. G) Geben Sie Kundename, Postleitzahl und Ortname aus für alle Kunden, deren Name ein "e" enthält und alle Orte, die ein "u" oder ein "r" enthalten (frEd aus stUden wird also genau so angezeigt wie jEssE aus bRnz, frEd aus salzen aber nicht und martin aus hambURg auch nicht). @@@ MySQL06_2a: Übungen zu Abfragen über zwei Tabellen: Buchladen Verwenden Sie für diese Übung diese Datenbank: *LINK 07mysql/_dumps/buchladen/buchladen.sql LINK* Das ER-Diagramm: *LINK 07mysql/_dumps/buchladen/buchladen_ERD.png LINK* A) Geben Sie alle Lieferanten aus, die in Reute wohnen. Ausgabe: Lieferantename, Ortname, Postleitzahl B) Geben Sie eine Liste aller Verlage aus, deren Name mit L beginnt. Ausgabe: Verlagname, Ortname; bitte nach Ortsname absteigend sortieren. C) Geben Sie eine Liste aller Lieferanten aus, deren Name mit Schus... oder Loe.. beginnt und die in Emmendingen sitzen. Ausgabe: Lieferantename, Ortname, sortiert nach Lieferantenname aufsteigend. D) Geben Sie eine Liste aller Orte aus, aber nur die Orte, in denen ein Verlag sitzt, der im Namen ein n hat. E) Geben Sie eine Liste aller Bücher und ihrer Lieferanten aus (Achtung: 3 Tabellen abfragen!) F) Geben Sie eine Liste aller Bücher aus, die mehr als 10 Euro kosten (Ausgabe: Lieferantenname, Buchtitel, Verkaufspreis) - Achtung: 3 Tabellen abfragen G) Geben Sie eine Liste aller Bücher aus (Ausgabe: Buchtitel, Sparte) - Achtung: 3 Tabellen abfragen @@@ MySQL06_3: JOIN vs. LEFT JOIN Benutzen Sie für diese Übung diese Datenbank: *LINK 07mysql/_dumps/kunden/kunden-einfach.sql LINK* Hier das zugehörige ER-Diagramm: *LINK 07mysql/_dumps/kunden/kunde_einfach_ERD.png LINK* Betrachten Sie die beiden folgenden Statements und erklären Sie, worin die Ergebnistabellen sich unterscheiden werden. Probieren Sie danach die Befehle aus und überprüfen Sie Ihre Antwort. --- MySQL-Code Start --- -- Statement 1: LEFT JOIN SELECT k.kunde_id, k.name, o.name FROM kunden AS k LEFT JOIN orte AS o ON o.postleitzahl = k.ort_postleitzahl ORDER BY k.kunde_id -- Statement 2: EQUI-JOIN SELECT k.kunde_id, k.name, o.name FROM kunden AS k, orte AS o WHERE o.postleitzahl = k.ort_postleitzahl ORDER BY k.kunde_id --- Code Ende --- @@@ MySQL06_4: Übungen zu Abfragen über bis zu 5 Tabellen: Buchladen Verwenden Sie für diese Übung diese Datenbank: *LINK 07mysql/_dumps/buchladen/buchladen.sql LINK* Das ER-Diagramm: *LINK 07mysql/_dumps/buchladen/buchladen_ERD.png LINK* A) Gesucht sind alle Lieferanten, die ihren Sitz in Freiburg haben. Geben Sie Lieferantenname, Lieferantenort und Postleitzahl aus. (Lösungshinweis: Es werden drei Lieferanten ausgegeben.) B) Gesucht sind alle Verlage, die ihren Sitz in München haben. Geben Sie Verlagsname und Verlagsort aus. (Lösungshinweis: Es werden drei Verlage ausgegeben.) C) Gesucht sind alle Bücher, die im Verlag Assal erschienen sind. Geben Sie Buchtitel, Erscheinungsjahr und Verlagsname aus, absteigend sortiert nach Erscheinungsjahr. (Lösungshinweis: Es werden vier Bücher ausgegeben.) D) Gesucht sind alle Bücher des Lieferanten Schustermann. Geben Sie Buchtitel und Lieferantenname aus. (Lösungshinweis: Es werden drei Bücher ausgegeben.) E) Gesucht sind alle Thriller. Geben Sie Buchtitel und Sparte aus, sortiert nach Buchtitel. (Lösungshinweis: Es werden fünf Bücher ausgegeben, das erste ist "Ein letzter Kuss".) F) Gesucht sind alle Liebesromane. Geben Sie Buchtitel, Sparte und Verlagsname aus, sortiert nach Buchtitel aufsteigend. (Lösungshinweis: Es werden sieben Datensätze ausgegeben, "Der Landarzt und seine Sekretärin" zuerst.) G) Gesucht sind alle Bücher von Sabrina Müller. Geben Sie Autorennachname, Autorenvorname und Buchtitel aus, sortiert nach Buchtitel absteigend. (Lösungshinweis: Es werden vier Datensätze ausgegeben, "Wilde Jagd auf John Smith - Die Vergeltung" zuerst.) H) Gesucht sind alle Thriller von Sabrina Müller. Geben Sie Autorenname, Buchtitel und Sparte aus. (Lösungshinweis: Es werden zwei Datensätze ausgegeben.) (Achtung: Das können Sie eigentlich noch nicht!) Gesucht sind alle Bücher von Sabrina Müller, die in die Sparten Thriller oder Humor eingeordnet werden können. Geben Sie Autorenname, Buchtitel und Sparte aus. Damit das Buch "Wilde Jagd auf John Smith - Die Rache" nicht zweimal auftaucht, müssen Sie das Ergebnis nach Buchtitel gruppieren, indem Sie vor ORDER BY schreiben: --- MySQL-Code Start --- GROUP BY titel --- Code Ende --- Damit nun auch noch für dieses Buch beide Sparten in der Spalte "bezeichnung" angezeigt werden, benutzen Sie für die Ausgabe der Sparte nicht einfach den Feldnamen "bezeichnung", sondern --- MySQL-Code Start --- GROUP_CONCAT(bezeichnung) --- Code Ende --- Damit werden für Bücher, die in mehreren Sparten eingeordnet sind, alle Sparten mit Komma getrennt in einem Feld ausgegeben. (Lösungshinweis: Es werden drei Datensätze ausgegeben.)