· 8 years ago · Jun 11, 2018, 05:48 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema Holzinger_gesamtuebung
9-- -----------------------------------------------------
10DROP SCHEMA IF EXISTS `Holzinger_gesamtuebung` ;
11
12-- -----------------------------------------------------
13-- Schema Holzinger_gesamtuebung
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `Holzinger_gesamtuebung` DEFAULT CHARACTER SET utf8 ;
16USE `Holzinger_gesamtuebung` ;
17
18-- -----------------------------------------------------
19-- Table `Holzinger_gesamtuebung`.`Kunde`
20-- -----------------------------------------------------
21DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Kunde` ;
22
23CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Kunde` (
24 `KundenNr` INT NOT NULL,
25 `Vorname` VARCHAR(45) NULL,
26 `Nachname` VARCHAR(45) NULL,
27 `Straße` VARCHAR(40) NULL,
28 `PLZ` INT(4) NULL,
29 `Ort` VARCHAR(45) NULL,
30 PRIMARY KEY (`KundenNr`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `Holzinger_gesamtuebung`.`Mitarbeiter`
36-- -----------------------------------------------------
37DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Mitarbeiter` ;
38
39CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Mitarbeiter` (
40 `Persnr` INT NOT NULL,
41 `Vorname` VARCHAR(45) NULL,
42 `Nachname` VARCHAR(45) NULL,
43 `GebDat` DATE NULL,
44 `Eintritt` DATE NULL,
45 PRIMARY KEY (`Persnr`))
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `Holzinger_gesamtuebung`.`Verleih`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Verleih` ;
53
54CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Verleih` (
55 `VerleihNr` INT NOT NULL,
56 `Dauer` VARCHAR(10) NULL,
57 `KundenNr` INT NOT NULL,
58 `RückgabeDatum` DATE NULL,
59 `Preis` VARCHAR(45) NULL,
60 `VerleihDatum` DATE NULL,
61 `Mitarbeiter_Persnr` INT NOT NULL,
62 PRIMARY KEY (`VerleihNr`, `KundenNr`, `Mitarbeiter_Persnr`),
63 INDEX `fk_Verleih_Kunde1_idx` (`KundenNr` ASC),
64 INDEX `fk_Verleih_Mitarbeiter1_idx` (`Mitarbeiter_Persnr` ASC),
65 CONSTRAINT `fk_Verleih_Kunde1`
66 FOREIGN KEY (`KundenNr`)
67 REFERENCES `Holzinger_gesamtuebung`.`Kunde` (`KundenNr`)
68 ON DELETE CASCADE
69 ON UPDATE CASCADE,
70 CONSTRAINT `fk_Verleih_Mitarbeiter1`
71 FOREIGN KEY (`Mitarbeiter_Persnr`)
72 REFERENCES `Holzinger_gesamtuebung`.`Mitarbeiter` (`Persnr`)
73 ON DELETE CASCADE
74 ON UPDATE CASCADE)
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `Holzinger_gesamtuebung`.`Fahrradtyp`
80-- -----------------------------------------------------
81DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Fahrradtyp` ;
82
83CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Fahrradtyp` (
84 `FahrradTyp_Nr` INT NOT NULL,
85 `FahrradTyp` VARCHAR(45) NULL,
86 PRIMARY KEY (`FahrradTyp_Nr`))
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `Holzinger_gesamtuebung`.`Fahrrad`
92-- -----------------------------------------------------
93DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Fahrrad` ;
94
95CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Fahrrad` (
96 `FahrradNr` INT NOT NULL,
97 `Zoll` VARCHAR(45) NULL,
98 `Fahrradtyp_FahrradTyp_Nr` INT NOT NULL,
99 PRIMARY KEY (`FahrradNr`),
100 INDEX `fk_Fahrrad_Fahrradtyp1_idx` (`Fahrradtyp_FahrradTyp_Nr` ASC),
101 CONSTRAINT `fk_Fahrrad_Fahrradtyp1`
102 FOREIGN KEY (`Fahrradtyp_FahrradTyp_Nr`)
103 REFERENCES `Holzinger_gesamtuebung`.`Fahrradtyp` (`FahrradTyp_Nr`)
104 ON DELETE CASCADE
105 ON UPDATE CASCADE)
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `Holzinger_gesamtuebung`.`Fahrradzubehör`
111-- -----------------------------------------------------
112DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Fahrradzubehör` ;
113
114CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Fahrradzubehör` (
115 `ZubehörNr` INT NOT NULL,
116 `Beschreibung` VARCHAR(45) NULL,
117 PRIMARY KEY (`ZubehörNr`))
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `Holzinger_gesamtuebung`.`Verleihzelle`
123-- -----------------------------------------------------
124DROP TABLE IF EXISTS `Holzinger_gesamtuebung`.`Verleihzelle` ;
125
126CREATE TABLE IF NOT EXISTS `Holzinger_gesamtuebung`.`Verleihzelle` (
127 `Fahrrad_FahrradNr` INT NOT NULL,
128 `Fahrradzubehör_ZubehörNr` INT NOT NULL,
129 `Verleih_VerleihNr` INT NOT NULL,
130 INDEX `fk_Verleihzelle_Fahrrad_idx` (`Fahrrad_FahrradNr` ASC),
131 INDEX `fk_Verleihzelle_Fahrradzubehör1_idx` (`Fahrradzubehör_ZubehörNr` ASC),
132 INDEX `fk_Verleihzelle_Verleih1_idx` (`Verleih_VerleihNr` ASC),
133 CONSTRAINT `fk_Verleihzelle_Fahrrad`
134 FOREIGN KEY (`Fahrrad_FahrradNr`)
135 REFERENCES `Holzinger_gesamtuebung`.`Fahrrad` (`FahrradNr`)
136 ON DELETE CASCADE
137 ON UPDATE CASCADE,
138 CONSTRAINT `fk_Verleihzelle_Fahrradzubehör1`
139 FOREIGN KEY (`Fahrradzubehör_ZubehörNr`)
140 REFERENCES `Holzinger_gesamtuebung`.`Fahrradzubehör` (`ZubehörNr`)
141 ON DELETE CASCADE
142 ON UPDATE CASCADE,
143 CONSTRAINT `fk_Verleihzelle_Verleih1`
144 FOREIGN KEY (`Verleih_VerleihNr`)
145 REFERENCES `Holzinger_gesamtuebung`.`Verleih` (`VerleihNr`)
146 ON DELETE CASCADE
147 ON UPDATE CASCADe)
148ENGINE = InnoDB;
149
150
151SET SQL_MODE=@OLD_SQL_MODE;
152SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
153SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
154
155
156
157
158-- DML
159
160
161
162/*
163Uebung: DML - Fahrradverleih
164Datum: 19.04.2018
165Name: Florian Holzinger
166*/
167
168USE Holzinger_gesamtuebung;
169
170
171
172INSERT INTO Fahrradtyp VALUES
173(1, 'Citybike'),
174(2, 'BMX'),
175(3, 'Mountainbike'),
176(4, 'Tandem'),
177(5, 'E-Bike'),
178(6, 'Rennrad'),
179(7, 'Dreirad');
180
181
182INSERT INTO Fahrrad VALUES
183(1, 28, 1),
184(2, 22, 2),
185(3, 28, 3),
186(4, 24, 4),
187(5, 27, 5),
188(6, 28, 6),
189(7, 18, 7);
190
191
192INSERT INTO Mitarbeiter VALUES
193(1, 'Ervvin', 'Katecovic', '1522-04-20', '1522-04-20'),
194(2, 'Florian', 'Holzinger', '2000-07-19', '2018-01-14'),
195(3, 'Armin', 'Penzenauer', '2001-03-13', '2015-10-08'),
196(4, 'Markus', 'Sieghartsleitner', '1999-02-06', '2012-05-12'),
197(5, 'Alexander', 'Crupenschi', '1998-11-15', '2010-07-11'),
198(6, 'Tarkan', 'Ayhan', '0002-02-02', '0001-02-02'),
199(7, 'Serkan', 'Güner', '199-08-22', '2005-11-22');
200
201
202INSERT INTO Kunde VALUES
203(1, 'Faruk', 'Kevrö', 'Bahnhofstraße 89', '4050', 'Traun'),
204(2, 'Wil', 'Helm', 'Gapstraße 567', '4501', 'Neuhofen'),
205(3, 'Dennis', 'Schläger', 'Tennisstraße 2', '4021', 'Linz'),
206(4, 'Sergej', 'Fährlich', 'Bauernstraße 12222', '6520', 'Eisenstadt'),
207(5, 'No', 'You', 'Feldweg 3', '1220', 'Wien'),
208(6, 'Ger', 'Hard', 'Waldstraße 22', '2301', 'Sattled'),
209(7, 'Her', 'Bert', 'Damnbach 23', '4505', 'Damnbach');
210DESC Kunde;
211
212
213INSERT INTO Fahrradzubehör VALUES
214(1, 'Helm'),
215(2, 'Licht'),
216(3, 'Ketten'),
217(4, 'Klingel'),
218(5, 'Pumpe'),
219(6, 'Warnweste'),
220(7, 'Handschuhe');
221
222
223INSERT INTO Verleihzelle VALUES
224(1, 1, 2),
225(2, 2, 3),
226(3, 3, 1),
227(4, 4, 5),
228(5, 5, 7),
229(6, 6, 6),
230(7, 7, 7);
231
232
233INSERT INTO Verleih VALUES
234(1, 2, 1, '2015-12-02', 5, '2018-04-24', 1),
235(2, 8, 2, '2016-11-08', 12, '2018-03-12', 2),
236(3, 240, 2, '2018-01-12', 120, '2017-11-11', 3),
237(4, 24, 2, '2018-03-31', 20, '2018-02-15', 4),
238(5, 15, 5, '2016-05-29', 90, '2018-04-24', 5),
239(6, 48, 6, '2019-10-18', 40, '2018-04-19', 6),
240(7, 72, 7, '2018-09-07', 30, '2018-01-05', 7);
241
242
243-- 1) Welche Kunden haben sich gerade ein Fahrrad ausgeborgt? (aktuelles Datum)
244SELECT KundenNr AS 'KundenNr', RückgabeDatum AS 'Rückgabe Datum'
245FROM Verleih
246WHERE VerleihDatum = curdate();
247
248
249-- 2) Welche Kunden haben sich öfters als ein mal ein Rad ausgeborgt? Kunde + Anzahl der Verleihungen ausgeben.
250SELECT Verleih.KundenNr AS 'KundenNr', Vorname, Nachname, COUNT(Verleih.KundenNr) AS 'Anzahl Ausgeborgt'
251FROM Verleih
252INNER JOIN
253 Kunde
254USING(KundenNr)
255GROUP BY Verleih.KundenNr ASC
256HAVING COUNT(Verleih.KundenNr) > 1;
257
258
259-- 3. Preis welche jeder Kunde Zahlen muss.
260SELECT Verleih.KundenNr AS 'KundenNr', Vorname, Nachname, SUM(Preis) AS 'Preis'
261FROM Verleih
262INNER JOIN
263 Kunde
264USING(KundenNr)
265GROUP BY Verleih.KundenNr ASC;
266
267
268-- 4. Nachname von Mitarbeiter 5 wird auf Maier geändert.
269UPDATE Mitarbeiter
270SET Nachname = 'Maier'
271WHERE Persnr = 5;
272
273
274-- 5. Ganze Rechnung anzeigen.
275SELECT Nachname, Vorname,
276 CONCAT('Zu zahlender Betrag: ', Preis),
277 CONCAT('Rückgabe am: ', RückgabeDatum) AS 'Rückgabe Datum',
278 CONCAT('Ausgeborgt am: ', VerleihDatum) AS 'Verleih Datum'
279FROM Kunde
280INNER JOIN
281 Verleih
282USING(KundenNr)
283ORDER BY Preis ASC;
284
285
286-- 6. Auf Anfrage wird Kunde 7 gelöscht.
287DELETE
288FROM Kunde
289WHERE KundenNr = 7;
290DESC Verleih;
291
292-- 7. Preis von VARCHAR auf FLOAT ändern.
293ALTER TABLE Verleih MODIFY Preis FLOAT;
294
295
296-- 8. Welche Mitarbeiter sind nach 2000 eingetreten?
297SELECT Vorname, Nachname, Eintritt
298FROM Mitarbeiter
299WHERE Eintritt > '2000-01-01';
300
301
302
303-- 9. Alle Kunden deren Nachname mit H anfängt.
304SELECT Vorname, Nachname, KundenNr AS 'Kunden Nummer'
305FROM Kunde
306WHERE LEFT(Nachname, 1) = 'H';
307
308
309-- 10. Fahrrad Ketten werden aus dem Zubehörsortiment genommen und Unterbodenbeleuchtung hinzugefügt
310UPDATE Fahrradzubehör
311SET Beschreibung = 'Unterbodenbeleuchtung'
312WHERE ZubehörNr = 3;