Übung zu SQL DB anlegen + Abfragen

22. 11. 2013
Übung zu SQL
DB anlegen + Abfragen
1. Lege folgendes Schema in einer neuen Datenbank an.
Abbildung 1: Schema
1
SQL
SQL
22. 11. 2013
Achte auf die richtige Reihenfolge, in der die Tabellen erstellt werden, ebenso auf die korrekte Setzung der Primär- und Fremdschlüssel (vor allem in Tabelle reparatur). Folgende
Datentypen sind zu beachten:
• automatisch befüllt werden:
– hnr in hersteller,
– mnr in modell,
– knr in kunden,
– wnr in werkstatt,
– anr in ausleihe
• In hersteller sind alle restlichen Felder vom Typ character.
• In modell ist name vom Typ character, alle anderen beinhalten ganze Zahlen.
• In pkw ist
– name vom Typ character,
– erstzul und abmeld beinhalten ein Datum,
– pschiebedach und pklima beinhalten nur 1 Zeichen (Y oder N).
• In werkstatt tragen alle übrigen Felder beliebige Zeichen.
• In reparatur ist
– redatum ein Datum,
– reart trägt beliebige Zeichen
– redauer gibt die Reparaturdauer in ganzen Tagen an.
• In ausleihe sind
– leihdat und ruckdat ein Datum,
– anfangkm und endkm beinhalten ganze Zahlen.
• In kunden sind
– gebdat und datfschein ein Datum,
– del trägt entweder eine 0 oder eine 1 (andere Werte sind verboten),
– alle anderen Felder tragen beliebige Zeichen.
• Füge folgende Werte ein
select * from hersteller
hnr |
name
|
strasse
|
plz
|
ort |
telefon
|
kontakt
-----+-----------------+----------------------+----------+------------+----------------------+---------------------2 | VW
| Berliner Ring 2
| 38840
| Wolfsburg | +49-5361-9-0
| +49 1802 8655792436
3 | Daimler AG
| Mercedesstrasse 38
| 70327
| Stuttgart | +49 7 11 17-0
| [email protected]
4 | Toyota AG
| 1 Toyota Cho
| 471-8571 | Tokio
| (03) 3817-7111
| (052) 552-2111
select * from modell
mnr |
name
| hnr | leistung | hubraum | laenge | breite
-----+-------------------------------------+-----+----------+---------+--------+-------3 | prius hybrid
|
4 |
99 |
1800 |
4480 |
1745
4 | auris
|
4 |
99 |
1300 |
4280 |
1760
5 | golf
|
2 |
80 |
1400 |
4200 |
1800
6 | phaeton
|
2 |
240 |
3000 |
5000 |
1900
7 | verso
|
4 |
100 |
2000 |
4440 |
1790
select * from pkw;
pkennzeichen | mnr | erstzul
| abmeld | pschiebedach | pklima
--------------+-----+------------+--------+--------------+-------TA 81AZ
|
5 | 2011-01-01 |
| N
| Y
TA 82AZ
|
7 | 2012-01-01 |
| N
| Y
2
SQL
22. 11. 2013
select * from werkstatt;
wnr |
strasse
| plz |
ort
|
telefon
|
kontakt
-----+-----------------+-------+-----------------+----------------------+---------------------4 | Markt 117
| 5570 | Mauterndorf
| 06472/7318
| franz rainer
3 | Markt 230
| 5570 | Mauterndorf
| +43 (0)6472 7288-0
| karl graggaber
select * from kunden;
knr |
name
|
vorname | strasse
|
plz
|
ort
| telefon
|
gebdat
| datfschein | del
-----+------------+------------+------------+----------+-----------------+------------+------------+------------+----5 | gürtler
| christian | Markt 18
| 5570
| Mauterndorf
| 08154711
| 1965-05-29 | 1998-03-01 |
0
6 | gürtler
| andrea
| Markt 18
| 5570
| Mauterndorf
| 08154712
| 1971-10-25 | 1989-03-01 |
0
select * from ausleihe;
anr | pkennzeichen | knr | leihdat
| anfangkm | ruckdat
| endkm
-----+--------------+-----+------------+----------+------------+------12 | TA 82AZ
|
5 | 2013-10-10 |
55555 | 2013-10-11 | 56000
13 | TA 82AZ
|
5 | 2013-11-01 |
56000 | 2013-11-02 | 56100
14 | TA 82AZ
|
5 | 2013-11-19 |
56100 |
|
select * from reparatur;
pkennzeichen | wnr | redatum
|
reart
| redauer
--------------+-----+------------+--------------------------------+--------TA 82AZ
|
3 | 2013-10-01 | Klimaanlage
|
1
TA 82AZ
|
3 | 2013-10-08 | Service
|
1
2. Löse folgende Abfragen
• Gib die Hersteller aus, aufsteigend geordnet nach dem Namen.
• Gib die Hersteller aus, von denen es Modelle gibt.
• Gib die Hersteller aus, von denen es keine Modelle gibt.
• Gib die Hersteller aus und die Anzahl ihrer Modelle. Hersteller ohne Modell (hier
Daimler) sollen mit einer 0 aufscheinen.
name
| anzahl
-------------------------------------+-------Toyota AG
|
3
Daimler AG
|
0
VW
|
2
• Gib die Kunden und ihre ausgeliehenen Modelle aus.
• Gib den Leihvorgang aus, der noch nicht abgeschlossen ist.
• Gib aus, wie viele Kilometer bisher das Fahrzeug »TA 82AZ« in der Ausleihe zurückgelegt hat.
• Gib die Werkstatt aus, in der noch keine Reparatur durchgeführt wurde.
• Gib die PKW aus und die Summe ihrer Reparaturtage. Es sollen auch Autos ohne Reparatur mit ausgegeben werden.
pkennzeichen | Summe Reparaturtage
--------------+--------------------TA 82AZ
|
3
TA 81AZ
|
• Gib den Kunden aus, der den Führerschein schon am längsten besitzt.
name
| vorname
|
?column?
-----------------+-----------------+--------------------------gürtler
| andrea
| 9030 days 21:13:09.707106
• Gib den Kunden und die Fahrt aus, bei der die meisten Kilometer gefahren wurden.
name
|
vorname
| anfangkm | endkm | Differenz | pkennzeichen
------------------------+----------------------+----------+-------+-----------+-------------gürtler
| christian
|
55555 | 56000 |
445 | TA 82AZ
3