URjuML8m

1###############
2###############
3### Blatt 06 ##
4###############
5###############
6
7#######################
8# Blatt 06 Aufgabe 01 #
9#######################
10
11# Welche Vorlesungen hält Prof. Sokrates 
12
13select v.Titel from vorlesungen v, professoren p
14where v.gelesenVon = p.persNr
15and p.name = 'Sokrates';
16
17# Wieviel Vorlesungen hält Prof. Sokrates?
18select count(*) from vorlesungen v, professoren p
19where v.gelesenVon = p.persNr
20and p.name = 'Sokrates'
21group by p.name;
22  
23# Welche Studenten hören Vorlesungen bei Prof. Sokrates?
24# In welchen Semester befinden sich die Studenten, die Prof. Kant Vorlesungen hören?
25select distinct s.name, s.semester from studenten s, professoren p, hoeren h, vorlesungen v
26where h.matrNr = s.MatrNr
27and h.vorlNr = v.vorlNr
28and v.gelesenVon = p.persNr
29and p.name = 'Sokrates';
30
31# Welche Vorlesungen Hört der Student Carnap?
32select v.titel from studenten s, vorlesungen v, hoeren h
33where h.matrNr = s.matrnr
34and h.vorlNr = v.vorlNr
35and s.name = 'Carnap';
36
37# Und Wieviel SWS ergibt das wöchentlich für Carnap?
38select sum(v.sws) from studenten s, vorlesungen v, hoeren h
39where h.matrNr = s.matrnr
40and h.vorlNr = v.vorlNr
41and s.name = 'Carnap';
42
43# Welche C4 Professoren leisten mehr als 4 SWS an Vorlesungen in der Woche?
44select count(*) from (select p.Name, sum(v.SWS) from professoren p, vorlesungen v
45where v.gelesenVon = p.persNr
46and p.rang = 'C4'
47group by p.persNr) tbl;
48
49#######################
50# Blatt 06 Aufgabe 02 #
51#######################
52
53# Erstellen Sie eine neue Tabelle Studentenauslastung (MatrNr, Name, SummeSWS)
54CREATE TABLE IF NOT EXISTS uni_db_klein.Studentenauslastung (
55  MatrNr INT NOT NULL,
56  Name VARCHAR(45) NOT NULL,
57  SummeSWS INT NOT NULL,
58  PRIMARY KEY (MatrNr)
59);
60
61# Fügen Sie Sie per geschachtelter Abfrage die Werte für alle Studenten in die neue Tabelle ein 
62insert into studentenauslastung (matrNr, name, summeSws) 
63select s.matrNr, s.Name, sum(v.sws) from studenten s, hoeren h, vorlesungen v
64	where h.matrNr = s.matrNr
65	and h.vorlNr = v.vorlNr
66	group by s.matrNr;
67    
68###############
69###############
70### Blatt 07 ##
71###############
72###############
73
74#######################
75# Blatt 07 Aufgabe 01 #
76#######################
77
78# Geben Sie die Namen der Professoren alphabetisch sortiert an, die der Student Theophrastos aus Vorlesungen kennt! 
79select distinct p.name from studenten s, vorlesungen v, hoeren h, professoren p
80where h.matrNr = s.matrNr
81and h.vorlNr = v.vorlNr
82and v.gelesenVon = p.persNr
83and s.name = 'Theophrastos'
84order by p.name;
85
86#######################
87# Blatt 07 Aufgabe 02 #
88#######################
89
90# Welche Vorlesungen werden von Studenten im Grundstudium (1.-4. Semester) gehört? Geben Sie die Titel dieser Vorlesungen an und zwar aufsteigend nach Vorlesungsnummer?
91select distinct v.Titel from studenten s, vorlesungen v, hoeren h
92where h.matrNr = s.matrNr
93and h.vorlNr = v.vorlNr
94and s.semester between 1 and 4
95order by v.vorlNr;
96
97#######################
98# Blatt 07 Aufgabe 03 #
99#######################
100
101# Ermitteln Sie das durchschnittliche Semester der Studenten der Universität
102select avg(semester) from studenten;
103
104#######################
105# Blatt 07 Aufgabe 04 #
106#######################
107
108# Ermitteln Sie das durchschnittliche Semester der Studenten, die mindestens eine Vorlesung bei
109# Sokrates hören. Beachten Sie, dass Sie das Semester von Studenten, die mehr als eine Vorlesung
110# bei Sokrates hören, nicht doppelt zählen dürfen
111
112select avg(semester) from (select semester from studenten s, vorlesungen v, hoeren h, professoren p
113						   where h.matrNr = s.matrNr
114						   and h.vorlNr = v.vorlNr
115						   and p.name = 'Sokrates'
116						   group by s.MatrNr) as tbl;
117
118#######################
119# Blatt 07 Aufgabe 05 #
120#######################
121
122# Ermitteln Sie, wie viele Vorlesungen im Schnitt pro Student gehört werden. Beachten Sie,
123# dass Studenten, die keine Vorlesung hören, in das Ergebnis auch einfließen müssen
124
125select hoererNum / studiNum from (select count(*) as studiNum from studenten) as tbl0, (select count(*) as hoererNum from hoeren) as tbl1;
126
127#######################
128# Blatt 07 Aufgabe 06 #
129#######################
130
131# Ermitteln Sie mit Hilfe einer SQL-Anfrage den Umfang des Prüfungsstoffes jedes Studenten. Es
132# sollen der Name des Studenten und die Summe der Semesterwochenstunden der
133# Prüfungsvorlesungen ausgegeben werden.
134
135select s.name, sum(v.sws) from studenten s, vorlesungen v, pruefen p
136where p.matrNr = s.matrNr
137and p.vorlNr = v.vorlNr
138group by s.matrNr;
139
140#######################
141# Blatt 07 Aufgabe 07 #
142#######################
143
144# Bestimmen Sie die Titel der Vorlesungen, deren Umfang der durchschnittlichen Vorlesungsdauer
145# entspricht.
146
147select titel from vorlesungen v where
148sws = (select avg(sws) as avgSws from vorlesungen);
149
150#######################
151# Blatt 07 Aufgabe 08 #
152#######################
153
154# Bestimmen Sie die Prüfer, die im Durchschnitt eine Prüfungsnote vergeben, die selbst auch schon
155# mal als Einzelnote vergeben wurde.
156select p.persNr, p.name from pruefen pf, professoren p
157where pf.persNr = p.persNr
158and pf.note = (select avg(note) from pruefen);
159
160#######################
161# Blatt 07 Aufgabe 09 #
162#######################
163
164# Alle Studenten müssen ab sofort alle Vorlesungen von Sokrates hören.
165# Formulieren Sie einen SQL-Befehl, der diese Operation durchführt.
166# Achtung: Berücksichtigen Sie, dass Studenten bereits Sokrates’s Vorlesungen hören und dürfen in
167# Folge dessen nicht doppelt eingetragen werden
168
169insert into hoeren (matrNr, vorlnr)
170Select s.matrNr as matrNr, v.vorlNr as vorlNr from professoren p, vorlesungen v, studenten s
171	where v.gelesenVon = p.persNr
172    and p.name = 'Sokrates'
173    and (s.matrNr, v.vorlNr) not in (Select * from hoeren);
174    
175#######################
176# Blatt 07 Aufgabe 10 #
177#######################
178
179# a) Selektiere die Studenten (Name, Semester) mit den längsten Studienzeiten
180Select s.name, max(semester) from studenten s
181where s.semester = (Select max(semester) from studenten);
182
183# b) Selektiere die Studenten (Name, Semester) mit den kürzesten Studienzeiten
184Select s.name, min(semester) from studenten s
185where s.semester = (Select min(semester) from studenten);
186
187#######################
188# Blatt 07 Aufgabe 11 #
189#######################
190
191# Finden Sie die Studenten, die mit je mindestens einem Kommilitonen aus demselben Semester eine
192# Vorlesung besuchen
193select distinct s0.Name from studenten s0, studenten s1, hoeren h0, hoeren h1
194where s0.matrNr != s1.matrNr
195and s0.semester = s1.semester
196and h0.matrNr = s0.matrNr
197and h1.matrNr = s1.matrNr
198and h0.vorlNr = h1.vorlNr;
199
200#######################
201# Blatt 07 Aufgabe 12 #
202#######################
203# Bestimmen Sie die Prüfer, deren durchschnittlich vergebene Note dem Durchschnitt der
204# Prüfungsergebnisse eines Studenten entspricht.
205
206Select distinct tbl.Name from pruefen pf, (Select p.name as name,avg(note) as avgNote from professoren p, pruefen pf
207						   where pf.persNr = p.persNr
208						   group by p.persNr) tbl
209where pf.Note = tbl.avgNote;
210
211#######################
212# Blatt 07 Aufgabe 13 #
213#######################
214
215# Formulieren Sie eine SQL-Anfrage, um den Bekanntheitsgrad von Studenten zu ermitteln.
216# Gehen Sie dabei davon aus, dass Studenten sich aus gemeinsam besuchten Vorlesungen kennen.
217# Sortieren Sie das Ergebnis absteigend nach Bekanntheitsgrad!
218# Zunächst formuliert man eine View, die für jeden Studenten alle seine Bekannten auflistet.
219# Anschließend müssen diese Bekannte nur noch gezählt werden, um den Bekanntheitsgrad der
220# Studenten zu ermitteln
221
222select tbl.name0, count(*) from (select distinct s0.Name as name0, s1.Name from studenten s0, studenten s1, hoeren h0, hoeren h1
223where s0.MatrNr != s1.MatrNr
224and s0.MatrNr = h0.MatrNr
225and s1.MatrNr = h1.MatrNr
226and h0.vorlNr = h1.vorlNr) as tbl
227group by tbl.name0;
228
229
230###############
231###############
232### Blatt 08 ##
233###############
234###############
235
236#######################
237# Blatt 08 Aufgabe 01 #
238#######################
239
240#
241#   !!!! TODO: ersetzen durch c# code
242#
243
244# c) Erweitern Sie die Ausgaben um folgende Abfragen:
245#		1. Durchschnittliche Semesterzahl aller Studenten
246Select avg(semester) from studenten;
247
248#		2. MatrNr, Name und Semester des ältesten Studenten (bezogen auf Semesterzahl)
249Select matrNr, name, semester from studenten s
250where s.semester = (Select max(semester) from studenten);
251
252#		3. Liste aller Studenten mit Ihren besuchten Vorlesungen und zugehörigen Professoren
253Select s.name, p.name, v.titel from studenten s, hoeren h, professoren p, vorlesungen v
254where h.matrNr = s.matrNr
255and h.vorlNr = v.vorlNr
256and v.gelesenVon = p.persNr;
257
258#		4. Liste der Vorlesungen, zuständiger Prof., Anzahl Zuhörer des Professors „X“. X ist dabei
259#		   als Variable in der Form durch den Benutzer einzugeben
260# !!!! TODO: hier für c# profname durch X ersetzen & liste muss noch ausgegeben werden
261Select count(distinct h.matrNr) from hoeren h 
262where h.vorlNr in (select vorlNr from professoren p, vorlesungen v
263				   where p.name = 'Sokrates'
264				   and v.gelesenVon = p.persNr);
265
266#		5. Selektiere die Studenten (Name, Semester) mit den kürzesten Studienzeiten
267select s.name, s.semester from studenten s
268where s.semester = (Select min(semester) from studenten);
269
270#		6. Formulieren Sie eine SQL-Anfrage, um den Bekanntheitsgrad von Studenten zu
271#		   ermitteln.
272#		   Gehen Sie dabei davon aus, dass Studenten sich aus gemeinsam besuchten
273#		   Vorlesungen kennen. Sortieren Sie das Ergebnis absteigend nach Bekanntheitsgrad!
274
275# siehe Blatt 07 Aufgabe 13
276
277###############
278###############
279### Blatt 09 ##
280###############
281###############
282
283#######################
284# Blatt 09 Aufgabe 01 #
285#######################
286
287# b) Exportieren Sie alle Studenten aus „welt_uni, die im 3. oder 4. Semester sind unter Anwendung des Befehls SELECT … INTO OUTFILE …
288# &
289# c) Es dürfen nur die Studenten exportiert werden, die nicht in dem Schema uni_db_klein.studenten bereits vorliegen
290# &
291# d) Die exportierten Daten sollen in die datei Studis.csv geschrieben werden!
292# on timeout:
293# https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query
294# For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600 -> set to 6000
295# TODO: SHIT GEHT NICHT... glaub in den folien stand was, ab er cba
296Select distinct s0.matrNr, s0.name, s0.semester into outfile 'C:\\Users\\pc\\Documents\\Studis.csv'
297fields terminated by ','
298optionally enclosed by '"'
299lines terminated by '\n'
300from studenten s0, uni_db_klein.studenten s1
301where s0.semester between 3 and 4
302and s0.matrNr != s1.matrNr;
303# e) Wieviel Records haben Sie jetzt exportiert?
304# 8673231
305
306# f) Importieren Sie jetzt in das Schema uni_db_klein.studenten die Studentendaten aus dieser Datei unter Nutzung des Befehls LOAD DATA INFILE …
307load data infile 'C:\\Users\\pc\\Documents\\Studis.csv'
308into table studenten
309fields terminated by ','
310optionally enclosed by '"'
311lines terminated by '\n'
312# g) Wie lange dauert dieser Prozess?
313# TODO: SHIT GEHT NICHT... glaub in den folien stand was, ab er cba
314
315#######################
316# Blatt 09 Aufgabe 02 #
317#######################
318
319# Es sollen neue Vorlesungen manuell erfasst und in das Schema „uni_db_klein“ importiert werden:
320# a) Erstellen Sie eine neue Excell-Datei und erfassen Sie 5 neue Vorlesungen unter Angabe von Vorlesungsnummer, Titel, SWS und Vorlesender Professor
321# b) Exportieren Sie die Daten in eine neue CSV-Datei (Vorl.csv)
322# c) Importieren Sie die neuen Vorlesungen aus der Vorl.csv in das Schema uni_db_klein
323# selbe Spiel wie in Aufgabe 01
324
325#######################
326# Blatt 09 Aufgabe 03 #
327#######################
328
329# Der Import aus Aufgabe 2 zwei soll über einen anderen Weg erneut importiert werden:
330# a) Löschen Sie die 5 neuen Vorlesungen aus uni_db_klein.Vorlesungen
331# b) Führen Sie eine Copy & Paste von Excel in die mySQL Workbench durch
332# c) Importieren Sie nun die Daten in das Schema uni_db_klein.Vorlesungen
333# d) Haben Sie denselben Stand wie zuvor in Aufgabe 2?
334# nach tabelle selecten, Werte einfügen und apply drücken