DFfWTtf5

1-- -----------------------------------------------------
2-- A 1.
3-- -----------------------------------------------------
4CREATE TABLE IF NOT EXISTS `Trainer`(
5`MNr`int not null,
6`Vorname` varchar(45) not null,
7`Name` varchar(45) not null,
8`Vertretung`int,
9primary key(`MNr`),
10constraint `fk_trainer_trainer`
11foreign key(`Vertretung`)
12references `Trainer` (`MNr`)
13on delete set null
14on update cascade);
15
16alter table `trainingsplan`
17add constraint `fk_trainingsplan_trainer` 
18foreign key(`Trainer_MNr`) references `Trainer`(`MNr`);
19-- -----------------------------------------------------
20-- A 2.
21-- -----------------------------------------------------
22alter table `Geraet`
23add column `Hersteller`varchar(45) after `Bezeichnung`;
24 -- -----------------------------------------------------
25-- A 3.
26-- -----------------------------------------------------   
27alter table `Kunde`
28modify `Tel` varchar(20); 
29 -- -----------------------------------------------------
30-- B 1.
31-- ----------------------------------------------------- 
32SELECT k.KNr, k.Vorname, k.Nachname, k.Geburtsdatum FROM kunde k
33WHERE year(k.Geburtsdatum) = 1983 
34 -- -----------------------------------------------------
35-- B 2.
36-- ----------------------------------------------------- 
37SELECT k.Vorname, k.Nachname FROM kunde k 
38WHERE concat(left(k.Vorname, 1),left(k.Nachname, 1)) = 'FF'
39 -- -----------------------------------------------------
40-- B 3.
41-- ----------------------------------------------------- 
42SELECT t.TNr, COUNT(*) AS anzahlKunden FROM trainingsplan t 
43INNER JOIN zuweisung z ON z.Trainingsplan_TNr = t.TNr
44WHERE z.Anfangsdatum <= '2017-01-01' AND z.Enddatum >= '2017-03-31'
45GROUP BY t.TNr
46ORDER BY anzahlKunden desc
47 -- -----------------------------------------------------
48-- B 4.
49-- ----------------------------------------------------- 
50SELECT g.GNr, g.Bezeichnung, MAX(u.Gewicht) maxGewicht, round(AVG(u.Gewicht), 2) durchschnittGewicht FROM geraet g 
51INNER JOIN `übung` u ON g.GNr = u.Geraet_GNr
52GROUP BY g.GNr 
53 -- -----------------------------------------------------
54-- B 5.
55-- ----------------------------------------------------- 
56SELECT t.Vorname, t.NAME, COUNT(tp.TNr) anzahlPlaene FROM trainer t 
57left OUTER JOIN trainingsplan tp ON t.MNr = tp.Trainer_MNr
58GROUP BY t.MNr 
59 -- -----------------------------------------------------
60-- B 6.
61-- ----------------------------------------------------- 
62SELECT t.Vorname, t.NAME, CONCAT(t2.Vorname, ' ', t2.NAME) vertretungtrainer FROM trainer t 
63LEFT OUTER JOIN trainer t2 ON t.Vertretung = t2.MNr
64 -- -----------------------------------------------------
65-- B 7.
66-- ----------------------------------------------------- 
67SELECT t.Vorname, t.Name FROM trainer t
68WHERE t.Vertretung IS NULL 
69 -- -----------------------------------------------------
70-- B 8.
71-- ----------------------------------------------------- 
72SELECT distinct k.Vorname, k.Nachname, g.Bezeichnung FROM kunde k 
73INNER JOIN zuweisung z ON k.KNr = z.Kunde_KNr
74INNER JOIN trainingsplan t ON t.TNr = z.Trainingsplan_TNr
75INNER JOIN trainingsplan_zu_übung tue ON tue.Trainingsplan_TNr = t.TNr
76INNER JOIN übung u ON u.UNr = tue.`Übung_UNr`
77INNER JOIN geraet g ON g.GNr = u.Geraet_GNr
78ORDER BY k.KNr, g.Bezeichnung