SQL Lösungen HS10/11 2.11.2010 Aggregatfunktionen können

SQL Lösungen
1.a)
HS10/11
2.11.2010
SELECT * FROM reparaturauftrag
WHERE nummer=“SS625000“;
b)
SELECT * FROM kunde
WHERE nachname LIKE “A%”
ORDER BY nachname;
c)
SELECT * FROM reparaturauftrag
WHERE abschlussdatum BETWEEN “1999-05-01” AND “1999-05-10”;
d)
SELECT AVG(gesamtpreis) FROM reparaturauftrag
WHERE NOT ISNULL(abschlussdatum);
e)
SELECT COUNT(r.nummer), g.type
FROM reparaturauftrag r, gerät g
WHERE r.gerätid=g.gerätid
AND r.von>”1999-01-01”
GROUP BY g.type;
f)
SELECT @a:=AVG(gesamtpreis) FROM reparaturauftrag;
SELECT * FROM reparaturauftrag
WHERE gesamtpreis>@a;
Aggregatfunktionen können nicht im WHERE-Teil verwendet werden!
Hier wird im ersten SELECT Statement die Variable @a mit dem
Durchschnittswert des Gesamtpreises gefüllt und dann im zweiten Statement
als Bedingung verwendet.
Alternativ ab MySQL 4.1 (SUBSELECT):
SELECT * FROM reparaturauftrag
WHERE gesamtpreis>(SELECT AVG(gesamtpreis) FROM reparaturauftrag);
g)
SELECT gerät.gerätid, gerät.bezeichnung
FROM gerät, kunde, reparaturauftrag, mitarbeiter
WHERE kunde.kdnr=reparaturauftrag.kdnr
AND reparaturauftrag.svnr=mitarbeiter.svnr
AND reparaturauftrag.gerätid=gerät.gerätid
AND kunde.vorname LIKE „Alf%“
AND mitarbeiter.nachname=”Hasi”;
2.a)
mysql> select uni.name, count(stud.name) from universitaet uni, student stud
-> where stud.stammuni = uni.bezeichner group by uni.name;
+----------------------+------------------+
| name
| count(stud.name) |
+----------------------+------------------+
| Karl Franzens Uni Gr |
2 |
| TU Wien
|
1 |
| Universität Klagenfu |
2 |
+----------------------+------------------+
3 rows in set (0.15 sec)
b)
mysql> select stud.vorname, stud.name, studium.bezeichnung FROM
-> student stud, studium, inskribiert insk
-> where stud.matnr = insk.student
-> and insk.studium = studium.nr
-> and stud.vorname = "Petra" and stud.name = "Facchini";
+---------+----------+-------------------+
| vorname | name
| bezeichnung
|
+---------+----------+-------------------+
| Petra
| Facchini | Techn. Informatik |
+---------+----------+-------------------+
1 row in set (0.00 sec)
c) mysql> select distinct bezeichnung from
-> studium, inskribiert where inskribiert.studium = studium.nr;
+----------------------+
| bezeichnung
|
+----------------------+
| BWL
|
| Angew. Informatik
|
| Wirtschaftsinformati |
| Techn. Informatik
|
+----------------------+
4 rows in set (0.00 sec)
d)
mysql> select stammuni from student
-> where name like "F%";
+----------+
| stammuni |
+----------+
| KFU
|
| TUW
|
+----------+
2 rows in set (0.00 sec)
e)
mysql> select gebdatum from student, inskribiert, studium
-> where student.matnr = inskribiert.student
-> and inskribiert.studium = studium.nr
-> and studium.bezeichnung = "BWL";
+------------+
| gebdatum
|
+------------+
| 1973-07-16 |
| 1980-12-18 |
| 1978-04-23 |
+------------+
3 rows in set (0.00 sec)
3a) Wer ist Leiter der Abteilung Raumfahrt? (Name und Vorname)
select mitarbeiter.name, mitarbeiter.vorname
from mitarbeiter, abteilung
where abteilung.name=“raumfahrt“ and
leiter=mitarbeiter.id;
3b) Wie heissen die Abteilungsleiter? (Name und Vorname)
select mitarbeiter.name, mitarbeiter.vorname
from mitarbeiter, abteilung
where leiter=mitarbeiter.id;
3c) Welche Projekte gehören in die Abteilung Informatik?
select projekt.bezeichnung
from projekt, abteilung
where abteilung.name=“informatik“ and
abteilung.id=projekt.abt;
3d) Wer ist für das Explorer Projekt verantwortlich?
select mitarbeiter.name, mitarbeiter.vorname
from mitarbeiter, projekt
where projekt.bezeichnung=“explorer“ and
Projekt.verantw_ma=mitarbeiter.id;
3e) Wer arbeitet am Internet Projekt?
select mitarbeiter.name, mitarbeiter.vorname
from mitarbeiter, arbeitet_an, projekt
where projekt.bezeichnung=“internet“ and
projekt.id=arbeitet_an.projekt and
arbeitet_an.mitarbeiter=mitarbeiter.id;
3f) Wer arbeitet am Apollo13 Projekt und ist gleichzeitig Leiter einer Abteilung?
select mitarbeiter.name, mitarbeiter.vorname
from abteilung, arbeitet_an, mitarbeiter, projekt
where projekt.bezeichnung=“apollo13“ and
projekt.id=arbeitet_an.projekt and
arbeitet_an.mitarbeiter=abteilung.leiter and
arbeitet_an.mitarbeiter=mitarbeiter.id;
3g) Wer arbeitet am Fit-Projekt oder am Internet-Projekt?
select mitarbeiter.name, mitarbeiter.vorname
from mitarbeiter, arbeitet_an, projekt
where (projekt.bezeichnung=“fit“ or
projekt.bezeichnung=“internet“) and
projekt.id=arbeitet_an.projekt and
arbeitet_an.mitarbeiter=mitarbeiter.id;
3h) Welche Mitarbeiter liegen mit ihrem Salär über dem Durchnitt?
select @a:=avg(salaer) from mitarbeiter;
select name, vorname, salaer
from mitarbeiter
where salaer>@a;
3i) Erhöhen Sie alle Saläre, welche unter dem Schnitt liegen um 8% und jene, die über dem Schnitt liegen um 5%
(Jeweils auf ganze 100Fr. gerundet).
select @a:=avg(salaer) from mitarbeiter;
update mitarbeiter
set salaer=round(if(salaer>@a, salaer*1.05, salaer*1.08),-2);
4a) Ja
Fremdschlüssel Vertreter in Tabelle Kunde
4b) Kundennummer 1999-3 in Tabelle KUNDE
Primärschlüssel müssen UNIQUE sein!
4c) 1999-3
Maier
Villach
200
1999-5
Erkollar
Klagenfurt
212
Keine Primärschlüssel zum Fremdschlüssel existent!
4d) Nein, die referentielle Integrität bleibt erhalten.
(Der Vertreter 234 betreut im Moment keine Kunden mehr)
4e) Ja, die referentielle Integrität wird verletzt.
Der Vertreter Nr. 205 existiert (noch) nicht!