SQL-Aufgaben zur eigenen Leistungskontrolle Basis der hier dargestellten Ergebnisse ist die Beispieldatenbank umfrage. Installation: 1. Sie können unter www.ralfadams.de im Themenpark DAB/SQL das Skript umfrage.sql herunterladen. 2. Starten des mysql-Servers bzw. überprüfen, ob er läuft. 3. Starten des mysql-clients mit root-Rechten; es reicht natürlich auch ein Benutzer mit den Rechten Datenbanken und Tabellen anlegen und löschen zu dürfen. 4. Im Client das umfrage.sql ausführen: SOURCE <verzeichnis>umfrage.sql Dies sollte ohne Fehlermeldung erfolgen. Geben Sie SHOW TABLES ein. Es müsste folgende Ausgabe erfolgen ++ | Tables_in_umfrage | ++ | antwort | | antwortxinfoquelle | | einsatzort | | infoquelle | | kunde | | logbuch | | nachricht | | qualifikation | | zufriedenheit | ++ Mit DESCRIBE <tabellenname> erhalten Sie Auskunft über die interne Struktur der Tabellen. Aufgaben: 1. Ermitteln Sie anhand der Tabellennamen und der Spaltennamen ein ERM! 2. Legen Sie eine neue Tabelle an. Sie soll Mitarbeiterstammdaten enthalten. Ein DESCRIBE liefert folgende Ausgabe: +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | mitarbeiter_id | int(11) | NO | PRI | NULL | auto_increment | | vorname | varchar(255) | YES | | NULL | | | nachname | varchar(255) | YES | | NULL | | | zweitname | varchar(255) | YES | | NULL | | | strasse | varchar(255) | YES | | NULL | | | hausnummer | char(5) | YES | | NULL | | | länderkennzeichen | char(2) | NO | | D | | | postleitzahl | char(5) | YES | | NULL | | | ort | varchar(255) | YES | | NULL | | +++++++ Seite 1 von 10 3. Fügen Sie der Tabelle mitarbeiter die Spalte eingestellt_am hinzu. +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | mitarbeiter_id | int(11) | NO | PRI | NULL | auto_increment | | vorname | varchar(255) | YES | | NULL | | | nachname | varchar(255) | YES | | NULL | | | zweitname | varchar(255) | YES | | NULL | | | strasse | varchar(255) | YES | | NULL | | | hausnummer | char(5) | YES | | NULL | | | länderkennzeichen | char(2) | NO | | D | | | postleitzahl | char(5) | YES | | NULL | | | ort | varchar(255) | YES | | NULL | | | eingestellt_am | date | YES | | NULL | | +++++++ 4. Verändern Sie den Datentyp der Spalte postleitzahl in ein CHAR(6). +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | mitarbeiter_id | int(11) | NO | PRI | NULL | auto_increment | | vorname | varchar(255) | YES | | NULL | | | nachname | varchar(255) | YES | | NULL | | | zweitname | varchar(255) | YES | | NULL | | | strasse | varchar(255) | YES | | NULL | | | hausnummer | char(5) | YES | | NULL | | | länderkennzeichen | char(2) | NO | | D | | | postleitzahl | char(6) | YES | | NULL | | | ort | varchar(255) | YES | | NULL | | | eingestellt_am | date | YES | | NULL | | +++++++ 5. Löschen Sie die Spalte zweitname. +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | mitarbeiter_id | int(11) | NO | PRI | NULL | auto_increment | | vorname | varchar(255) | YES | | NULL | | | nachname | varchar(255) | YES | | NULL | | | strasse | varchar(255) | YES | | NULL | | | hausnummer | char(5) | YES | | NULL | | | länderkennzeichen | char(2) | NO | | D | | | postleitzahl | char(6) | YES | | NULL | | | ort | varchar(255) | YES | | NULL | | | eingestellt_am | date | YES | | NULL | | +++++++ Seite 2 von 10 6. Fügen Sie einen neuen Mitarbeiter ein. +++++++ ++ | mitarbeiter_id | vorname | nachname | strasse | hausnummer | länderkennzeichen | postleitzahl | ort | +++++++ ++ | 1 | Helga | Fleißig | Nebenweg | 4a | D | 45454 | Kleinwusterhausen | +++++++ ++ 7. Ändern Sie den Ort in Großwusterhausen +++++++ ++ | mitarbeiter_id | vorname | nachname | strasse | hausnummer | länderkennzeichen | postleitzahl | ort | +++++++ ++ | 1 | Helga | Fleißig | Nebenweg | 4a | D | 45454 | Großwusterhausen | +++++++ ++ 8. Löschen Sie alle Mitarbeiter mit der mitarbeiter_id 1. Empty set (0.00 sec) 9. Ermitteln Sie den kompletten Inhalt der Tabelle kunde! +++++++ | kunde_id | nachname | vorname | ort | umsatz | geschlecht | +++++++ | 1 | Müller | HansHerbert | Bochum | 12000.00 | m | | 2 | müller | Pantaleon | Bochum | 321.02 | m | | 3 | Müller | Fiedhelm | Welper | 0.00 | m | | 4 | Meier | Wilhelma | Bochum | 5420.50 | w | | 5 | Schmidt | Werner | Bochum | 0.00 | m | | 6 | Schimmelpfennig | Ludowika | Essen | 21.30 | w | | 7 | Fassreiter | Bilbo | Erebor | 730.34 | m | | 8 | Eichenschild | Torin | Erebor | 659.00 | m | | 9 | Schibulsky | Jaqueline | Essen | 1773.00 | w | | 10 | Beutlin | Frodo | Beutelsend | 10.00 | m | | 11 | Gamdschie | Samweis | Beutelsend | 0.00 | m | +++++++ 10.Geben Sie nur den Nachnamen und den Vornamen aller Kunden aus. +++ | nachname | vorname | +++ | Müller | HansHerbert | | müller | Pantaleon | | Müller | Fiedhelm | | Meier | Wilhelma | | Schmidt | Werner | | Schimmelpfennig | Ludowika | | Fassreiter | Bilbo | | Eichenschild | Torin | | Schibulsky | Jaqueline | | Beutlin | Frodo | Seite 3 von 10 | Gamdschie | Samweis | +++ 11.Reduzieren Sie die Ausgabe aus 10. auf die Kunden mit einem Umsatz größer 100€! +++ | nachname | vorname | +++ | Müller | HansHerbert | | müller | Pantaleon | | Meier | Wilhelma | | Fassreiter | Bilbo | | Eichenschild | Torin | | Schibulsky | Jaqueline | +++ 12.Reduzieren Sie die Ausgabe aus 11. um alle Kunden, die aus Bochum kommen! +++ | nachname | vorname | +++ | Müller | HansHerbert | | müller | Pantaleon | | Meier | Wilhelma | +++ 13.Erweitern Sie die Ausgabe aus 12. um alle Kunden, die auch aus Essen kommen! +++ | nachname | vorname | +++ | Müller | HansHerbert | | müller | Pantaleon | | Meier | Wilhelma | | Schibulsky | Jaqueline | +++ 14.Reduzieren Sie die Ausgabe aus 13. auf alle Kunden, deren Nachname mit „Sch“ beginnen.! +++ | nachname | vorname | +++ | Schibulsky | Jaqueline | +++ 15.Sortieren Sie die Ausgabe aus 11. nach Nachname! +++ | nachname | vorname | +++ | Eichenschild | Torin | | Fassreiter | Bilbo | | Meier | Wilhelma | | Müller | HansHerbert | | müller | Pantaleon | | Schibulsky | Jaqueline | +++ Seite 4 von 10 16.Sortieren Sie die Ausgabe aus 15. nach Nachname und Vorname absteigend! +++ | nachname | vorname | +++ | Schibulsky | Jaqueline | | müller | Pantaleon | | Müller | HansHerbert | | Meier | Wilhelma | | Fassreiter | Bilbo | | Eichenschild | Torin | +++ 17.Geben Sie alle Orte aus, in denen Kunden wohnen! Vermeiden Sie dabei Mehrfachangaben! ++ | ort | ++ | Bochum | | Welper | | Essen | | Erebor | | Beutelsend | ++ 18.Geben Sie zu allen Kunden, die den Fragebogen ausgefüllt haben, an, welche Zufriedenheit vorliegt und die Kundennummer. Die Zufriedenheit soll als Text und nicht als Fremdschlüsselwert ausgegeben werden! Sortieren Sie die Ausgabe nach der Kudennummer! +++ | kunde_id | zufriedenheit | +++ | 1 | gut | | 2 | gut | | 4 | gut | | 6 | mittel | | 7 | mittel | | 8 | mäßig | | 9 | gar nicht | | 10 | mittel | +++ 19.Erweitern Sie die Ausgabe aus 18. um die Qualifikation! ++++ | kunde_id | zufriedenheit | qualifikation | ++++ | 1 | gut | Anfänger | | 2 | gut | Profi | | 4 | gut | Profi | | 6 | mittel | Standard | | 7 | mittel | Standard | | 8 | mäßig | Standard | | 9 | gar nicht | Profi | | 10 | mittel | Standard | ++++ Seite 5 von 10 20.Geben Sie zu allen Kunden die Informationsquelle aus! Die Informationsquelle soll als Text und nicht als Fremdschlüsselwert ausgegeben werden. Sortieren Sie die Ausgabe nach der Kundennummer. +++ | kunde_id | infoquelle | +++ | 1 | Internet | | 2 | Messe | | 4 | Internet | | 6 | Presse | | 6 | Internet | | 7 | Internet | | 8 | Presse | | 8 | Messe | | 8 | Broschüre | | 9 | Internet | | 10 | Internet | +++ 21.Erweitern Sie die Ausgabe aus 20. um den Kundennamen! +++++ | kunde_id | nachname | vorname | infoquelle | +++++ | 1 | Müller | HansHerbert | Internet | | 2 | müller | Pantaleon | Messe | | 4 | Meier | Wilhelma | Internet | | 6 | Schimmelpfennig | Ludowika | Presse | | 6 | Schimmelpfennig | Ludowika | Internet | | 7 | Fassreiter | Bilbo | Internet | | 8 | Eichenschild | Torin | Presse | | 8 | Eichenschild | Torin | Messe | | 8 | Eichenschild | Torin | Broschüre | | 9 | Schibulsky | Jaqueline | Internet | | 10 | Beutlin | Frodo | Internet | +++++ 22.Erweitern Sie die Ausgabe aus 21. um die Zufriedenheit und die Qualifikation! +++++++ | kunde_id | nachname | vorname | infoquelle | zufriedenheit | qualifikation | +++++++ | 1 | Müller | HansHerbert | Internet | gut | Anfänger | | 2 | müller | Pantaleon | Messe | gut | Profi | | 4 | Meier | Wilhelma | Internet | gut | Profi | | 6 | Schimmelpfennig | Ludowika | Presse | mittel | Standard | | 6 | Schimmelpfennig | Ludowika | Internet | mittel | Standard | | 7 | Fassreiter | Bilbo | Internet | mittel | Standard | | 8 | Eichenschild | Torin | Presse | mäßig | Standard | | 8 | Eichenschild | Torin | Messe | mäßig | Standard | | 8 | Eichenschild | Torin | Broschüre | mäßig | Standard | | 9 | Schibulsky | Jaqueline | Internet | gar nicht | Profi | | 10 | Beutlin | Frodo | Internet | mittel | Standard | +++++++ Seite 6 von 10 23.Erweitern Sie die Ausgabe aus 22. um alle Kunden, unabhängig davon, ob sie den Fragebogen ausgefüllt haben oder nicht! +++++++ | kunde_id | nachname | vorname | infoquelle | zufriedenheit | qualifikation | +++++++ | 1 | Müller | HansHerbert | Internet | gut | Anfänger | | 2 | müller | Pantaleon | Messe | gut | Profi | | 3 | Müller | Fiedhelm | NULL | NULL | NULL | | 4 | Meier | Wilhelma | Internet | gut | Profi | | 5 | Schmidt | Werner | NULL | NULL | NULL | | 6 | Schimmelpfennig | Ludowika | Presse | mittel | Standard | | 6 | Schimmelpfennig | Ludowika | Internet | mittel | Standard | | 7 | Fassreiter | Bilbo | Internet | mittel | Standard | | 8 | Eichenschild | Torin | Presse | mäßig | Standard | | 8 | Eichenschild | Torin | Messe | mäßig | Standard | | 8 | Eichenschild | Torin | Broschüre | mäßig | Standard | | 9 | Schibulsky | Jaqueline | Internet | gar nicht | Profi | | 10 | Beutlin | Frodo | Internet | mittel | Standard | | 11 | Gamdschie | Samweis | NULL | NULL | NULL | +++++++ 24.Schränken Sie die Ausgabe aus 23. um alle Kunden ein, die einen Umsatz größer 100 haben! +++++++ | kunde_id | nachname | vorname | infoquelle | zufriedenheit | qualifikation | +++++++ | 1 | Müller | HansHerbert | Internet | gut | Anfänger | | 2 | müller | Pantaleon | Messe | gut | Profi | | 4 | Meier | Wilhelma | Internet | gut | Profi | | 7 | Fassreiter | Bilbo | Internet | mittel | Standard | | 8 | Eichenschild | Torin | Presse | mäßig | Standard | | 8 | Eichenschild | Torin | Messe | mäßig | Standard | | 8 | Eichenschild | Torin | Broschüre | mäßig | Standard | | 9 | Schibulsky | Jaqueline | Internet | gar nicht | Profi | +++++++ 25.Geben Sie alle Kundennamen und -nummer aus, die nicht den Fragebogen ausgefüllt haben! Sortieren Sie die Ausgabe nach Nachname und Vorname. +++ | nachname | vorname | +++ | Gamdschie | Samweis | | Müller | Fiedhelm | | Schmidt | Werner | +++ 26.Geben Sie zu Antworten auf die Nachricht 2 aus. ++++++ +++++ | nachricht_id | nachricht | erstellt | von | antwort_auf | nachricht_id | nachricht | erstellt | von | antwort_auf | ++++++ +++++ | 3 | Stimmt, ich war dabei. | 20150303 09:15:00 | gesine | 2 | 2 | Ich bin doch schon fertig. | 20150303 09:00:00 | willi | 1 | | 4 | Ok, sorry. | 20150303 12:15:00 | boss | 2 | 2 | Ich bin doch schon fertig. | 20150303 09:00:00 | willi | 1 | ++++++ +++++ Seite 7 von 10 27.Geben Sie den durchschnittlichen Umsatz aller Kunden aus! ++ | Umsatzdurchschnitt | ++ | 1903.196364 | ++ 28.Schränken Sie die Durchschnittsbildung auf Kunden ein, die den Fragebogen ausgefüllt haben! ++ | Umsatzdurchschnitt | ++ | 2616.895000 | ++ 29.Schränken Sie die Durchschnittsbildung auf Kunden ein, die den Fragebogen ausgefüllt und einen Umsatz > 100 haben! ++ | Umsatzdurchschnitt | ++ | 3483.976667 | ++ 30.Geben Sie von allen Kunden, die den Fragebogen ausgefüllt haben, den maximalen und den minimalen Umsatz an! +++ | Umsatzminimum | Umsatzmaximum | +++ | 10.00 | 12000.00 | +++ 31.Geben Sie an, wieviele Kunden den Fragebogen ausgefüllt haben! ++ | AnzahlFragebögen | ++ | 8 | ++ 32.Erweitern Sie die Ausgabe aus 23. um die Anzahl der Kunden, die keinen Fragebogen ausgefüllt haben! +++ | AnzahlFragebögen | AnzahlKeineFragebögen | +++ | 8 | 3 | +++ Seite 8 von 10 33.Geben Sie den durchschnittlichen Umsatz pro Ort an! +++ | ort | Ortdurchschnittsumsatz | +++ | Beutelsend | 5.000000 | | Bochum | 4435.380000 | | Erebor | 694.670000 | | Essen | 897.150000 | | Welper | 0.000000 | +++ 34.Sortieren Sie die Ausgabe aus 33. nach dem Ortsumsatz absteigend. +++ | ort | Ortdurchschnittsumsatz | +++ | Bochum | 4435.380000 | | Essen | 897.150000 | | Erebor | 694.670000 | | Beutelsend | 5.000000 | | Welper | 0.000000 | +++ 35.Erweitern Sie die Ausgabe aus 34. um den maximalen Umsatz eines Ortes und die Summe des Umsatzes pro Ort! +++++ | ort | Ortdurchschnittsumsatz | Maximalumsatz | Umsatzsumme | +++++ | Bochum | 4435.380000 | 12000.00 | 17741.52 | | Essen | 897.150000 | 1773.00 | 1794.30 | | Erebor | 694.670000 | 730.34 | 1389.34 | | Beutelsend | 5.000000 | 10.00 | 10.00 | | Welper | 0.000000 | 0.00 | 0.00 | +++++ 36.Schränken Sie die Berechnungen aus 35. auf die Kunden mit einem Umsatz > 0 ein! +++++ | ort | Ortdurchschnittsumsatz | Maximalumsatz | Umsatzsumme | +++++ | Bochum | 5913.840000 | 12000.00 | 17741.52 | | Essen | 897.150000 | 1773.00 | 1794.30 | | Erebor | 694.670000 | 730.34 | 1389.34 | | Beutelsend | 10.000000 | 10.00 | 10.00 | +++++ 37.Geben Sie nur noch die Orte aus, die einen durchschnittlichen Umsatz > 700 haben! +++++ | ort | Ortdurchschnittsumsatz | Maximalumsatz | Umsatzsumme | +++++ | Bochum | 5913.840000 | 12000.00 | 17741.52 | | Essen | 897.150000 | 1773.00 | 1794.30 | +++++ Seite 9 von 10 38.Schränken Sie die Ausgabe aus 37. auf die Orte ein, deren Umsatzdurchschnitt über dem Gesamtdurchschnitt liegen! +++++ | ort | Ortdurchschnittsumsatz | Maximalumsatz | Umsatzsumme | +++++ | Bochum | 4435.380000 | 12000.00 | 17741.52 | +++++ 39.Geben Sie pro Ort den Umsatz der Männer und der Frauen aus. ++++ | ort | Umsatz Männer | Umsatz Frauen | ++++ | Beutelsend | 10.00 | NULL | | Bochum | 12321.02 | 5420.50 | | Erebor | 1389.34 | NULL | | Essen | NULL | 1794.30 | | Welper | 0.00 | NULL | ++++ Seite 10 von 10
© Copyright 2024 ExpyDoc
