SQL-Aufgaben zur eigenen Leistungskontrolle

Werbung

SQL-Aufgaben zur eigenen Leistungskontrolle

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

Zugehörige Unterlagen

WS11/12 Data-Warehouse-Technologien Blatt 5 PERSONAL ID

WS11/12 Data-Warehouse-Technologien Blatt 5 PERSONAL ID

Massenimport von Daten in MySQL

Massenimport von Daten in MySQL

Einführung - Till Hänisch

Einführung - Till Hänisch

titel_dupdaten

1. Zahlenbereiche - Fakultät für Mathematik

1. Zahlenbereiche - Fakultät für Mathematik

Beispiele - Comelio Medien

Beispiele - Comelio Medien

Pressemitteilung - Universität Tübingen

Pressemitteilung - Universität Tübingen

Tabellen mit verschiedene Spaltentypen erzeugen

Tabellen mit verschiedene Spaltentypen erzeugen

Word

Lieferantenselbstauskunft Supplier self assessment Deckblatt

Lieferantenselbstauskunft Supplier self assessment Deckblatt

Schematische Ausnahmebehandlung in relationalen Datenbanken

Schematische Ausnahmebehandlung in relationalen Datenbanken

Computergestützte Verifikation

Computergestützte Verifikation