mnta49z1

1drop database if exists DB_ScooTeq;
2create database DB_ScooTeq;
3use DB_ScooTeq;
4
5create table Abteilung(
6Kuerzel varchar(5) not null,
7Abteilung_Name varchar(30),
8primary key (Kuerzel));
9
10create table Mitarbeiter(
11ID int AUTO_INCREMENT unique not null,
12Nachname varchar(20),
13Vorname varchar(15),  
14Raum varchar(10),
15Abteilung varchar(5),
16primary key (ID),
17foreign key (Abteilung) references Abteilung(Kuerzel));
18
19create table Geraete(
20Geraet_ID int AUTO_INCREMENT unique not null,
21Geraet_Name varchar(30),
22OS varchar(10),
23OS_Version varchar(10),
24GerätLizenzen int unique not null,
25Raum varchar(10),
26Einkauf date,
27benutzung_seit date,
28benutzung_bis date,
29wartung date,
30BenutztVon int,
31primary key(Geraet_ID,GerätLizenzen),
32foreign key (BenutztVon) references Mitarbeiter(ID));
33
34create table Peripheriegeräte(
35Peripherie_ID int AUTO_INCREMENT unique not null,
36GeräteID int,
37Name varchar(20),
38wartung date,
39primary key (Peripherie_ID),
40foreign key (GeräteID) references Geraete(Geraet_ID));
41
42create table Lizenzen(
43Lizenz_ID int not null,
44Name varchar(10),
45Version varchar(30),
46seit_Gültig date,
47bis_Gültig date,
48primary key (Lizenz_ID));
49
50create table LizenzenGeräte(
51Lizenz_ID int auto_increment not null,
52GerätLizenzen int,
53foreign key (GerätLizenzen) references Geraete(GerätLizenzen),
54foreign key (Lizenz_ID) references Lizenzen(Lizenz_ID));
55
56use DB_ScooTeq;
57
58insert into Abteilung(Kuerzel,Abteilung_Name) values ('M','Marketing');
59insert into Abteilung(Kuerzel,Abteilung_Name) values ('G','Geschäftsführung');
60insert into Abteilung(Kuerzel,Abteilung_Name) values ('E','Entwicklung');
61insert into Abteilung(Kuerzel,Abteilung_Name) values ('HRuF','Human Resources und Finance');
62insert into Abteilung(Kuerzel,Abteilung_Name) values ('V','Vertrieb');
63
64insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('EInstein','ALbert', '109a','M');
65insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('Isaac','Newton', '104a','G');
66insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('TEst','TEst', '222','E');
67insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('Donald','Duck', '222','HRuF');
68insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('Dagobert','Duck', '123','HRuF');
69insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('ROccat','Razer', '444','V');
70insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('Merlin','Dobert', '109a','M');
71insert into Mitarbeiter(Nachname, Vorname,Raum,Abteilung) values ('logitech','silomat', '109a','M');
72
73insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('SchnittRechner 1','Windows','10','109a','1992-1-2','1992-2-1',null,'2019-4-2','1','1');
74insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Arbeitsrechner 1','Windows','8','104a','1996-1-2','1997-2-1',null,'2019-4-2','2','2');
75insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Laptop 1','Windows','7','222','2000-1-2','2000-2-1',null,'2019-4-2','3','3');
76insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Desktop 5','Windows','10','222','2006-1-5','2006-2-9',null,'2019-4-2','4','4');
77insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Desktop 6','Windows','10','123','2000-1-4','2000-2-1',null,'2019-4-2','5','5');
78insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Desktop 7','Windows','10','444','2000-1-3','2000-2-1',null,'2019-4-2','6','6');
79insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Laptop 2','Windows','10','3f','2012-1-3','2012-2-1',null,'2019-4-2','7','7');
80insert into Geraete(Geraet_Name,OS,OS_Version,Raum,Einkauf,benutzung_seit,benutzung_bis,wartung,BenutztVon,GerätLizenzen) values ('Laptop 3','Windows','10','987','2014-5-3','2014-2-1',null,'2019-4-2','8','8');
81
82insert into Peripheriegeräte(GeräteID,Name) values ('1','Monitor');
83insert into Peripheriegeräte(GeräteID,Name) values ('1','Monitor');
84insert into Peripheriegeräte(GeräteID,Name) values ('1','Maus');
85insert into Peripheriegeräte(GeräteID,Name) values ('1','Tastatur');
86insert into Peripheriegeräte(GeräteID,Name) values ('2','Monitor');
87insert into Peripheriegeräte(GeräteID,Name) values ('2','Boxen');
88insert into Peripheriegeräte(GeräteID,Name) values ('2','Maus');
89insert into Peripheriegeräte(GeräteID,Name) values ('3','Tastatur');
90insert into Peripheriegeräte(GeräteID,Name) values ('3','Drucker');
91insert into Peripheriegeräte(GeräteID,Name) values ('3','DockingStation');
92insert into Peripheriegeräte(GeräteID,Name) values ('4','Monitor');
93insert into Peripheriegeräte(GeräteID,Name) values ('4','Monitor');
94insert into Peripheriegeräte(GeräteID,Name) values ('4','Tastatur');
95insert into Peripheriegeräte(GeräteID,Name) values ('4','Maus');
96insert into Peripheriegeräte(GeräteID,Name) values ('5','Monitor');
97insert into Peripheriegeräte(GeräteID,Name) values ('5','Maus');
98insert into Peripheriegeräte(GeräteID,Name) values ('6','Boxen');
99insert into Peripheriegeräte(GeräteID,Name) values ('6','Drucker');
100insert into Peripheriegeräte(GeräteID,Name) values ('6','Tablet');
101insert into Peripheriegeräte(GeräteID,Name) values ('5','Tastatur');
102
103insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('1','Premiere','12','1997-05-01','2020-05-01');
104insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('2','Photoshop','12','2005-05-01','2020-05-01');
105insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('3','Office 365','Build:2019.05.02','2007-05-01','2020-05-01');
106insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('4','Spotify','n/a','2018-05-01','2020-05-01');
107insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('5','Photoshop','9','2007-05-01','2019-01-19');
108insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('6','Norten','123.908','2000-05-01','2015-01-19');
109insert into Lizenzen(Lizenz_ID,Name,Version,seit_Gültig,bis_Gültig) values ('7','Kaspersky','8932.12','2001-05-01','2014-01-19');
110 
111insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('1','1');
112insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('2','1');
113insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('3','1');
114insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('7','1');
115insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('4','2');
116insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('2','2');
117insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('3','3');
118insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('2','4');
119insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('3','4');
120insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('4','5');
121insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('7','5');
122insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('2','5');
123insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('2','6');
124insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('4','6');
125insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('3','6');
126insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('3','7');
127insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('4','7');
128insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('6','7');
129insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('5','7');
130insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('5','8');
131insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('4','8');
132insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('7','8');
133insert into LizenzenGeräte(Lizenz_ID,GerätLizenzen) values ('3','8');
134
135
136Geben Sie die Standorte aller PC-Systeme mit mehr als einem Monitor aus:
137
138select 
139	g.Raum,Count(p.NAme = 'Monitor') Anzahl_der_Monitore,g.Geraet_ID,m.Vorname, m.Nachname,a.Abteilung_Name
140from
141	geraete g 
142    join peripheriegeräte p on p.GeräteID = g.Geraet_ID
143    join mitarbeiter m on m.ID = g.BenutztVon
144    join abteilung a on a.Kuerzel = m.Abteilung
145group by 
146	p.Name,g.Raum
147having 
148	count(p.NAme = 'Monitor') > 1
149
150Wie viele Laptops werden von der Marketing-Abteilung genutzt:
151
152
153select 
154	Count(g.Geraet_Name)Laptops_Marketing
155from
156	geraete g 
157    join mitarbeiter m on m.ID = g.BenutztVon
158    join abteilung a on a.Kuerzel = m.Abteilung
159where
160	a.Abteilung_Name = 'Marketing' && g.Geraet_Name  like 'Laptop%'
161
162
163
164Welche Betriebssystemversionen sind auf den Rechnern der Human Resources and 
165Finance-Abteilung installiert:
166
167select 
168	g.Geraet_Name,g.Geraet_ID,OS,OS_Version
169from
170	geraete g 
171    join mitarbeiter m on m.ID = g.BenutztVon
172    join abteilung a on a.Kuerzel = m.Abteilung
173where
174	a.Abteilung_Name = 'Human Resources und Finance'
175
176Gültigkeit in Tag und jahren sowie die Version der Photoshop Lizenzen in der Marketing Abteilung:
177
178
179select 
180	l.Name,l.Version,l.bis_Gültig,round((datediff(l.bis_Gültig,CURRENT_DATE)/365),0)verbleibende_gesamt_Jahre,
181    datediff(l.bis_Gültig,CURRENT_DATE)verbleibende_gesamt_Tage,g.Geraet_ID
182from
183	geraete g 
184	join lizenzengeräte lg on lg.GerätLizenzen = g.GerätLizenzen
185    join lizenzen l On l.Lizenz_ID = lg.Lizenz_ID
186	join mitarbeiter m on m.ID = g.BenutztVon
187    join abteilung a on a.Kuerzel = m.Abteilung
188where
189	a.Abteilung_Name = 'Marketing' && l.Name = 'Photoshop'
190
191
192
193alle abgelaufenden Lizenzen in tagen:
194
195
196select 
197	l.Name,l.Version,l.bis_Gültig,datediff(l.bis_Gültig,CURRENT_DATE)Abgelaufen_seit_x_Tagen,g.Geraet_ID
198from
199	geraete g 
200	join lizenzengeräte lg on lg.GerätLizenzen = g.GerätLizenzen
201    join lizenzen l On l.Lizenz_ID = lg.Lizenz_ID
202
203where
204	datediff(l.bis_Gültig,CURRENT_DATE) < 1