***** Übungen: MySQL - GROUP BY und HAVING *****

@@@

MySQL: Beispieldatenbank zum Video-Tutorial:
Verwenden Sie für dieses Video diese Datenbank:
*LINK 07mysql/_dumps/kunden/kunden-normal.sql LINK*

Das ER-Diagramm:
*LINK 07mysql/_dumps/kunden/kunden-normal-ERD.png LINK*


@@@

MySQL11_1: GROUP BY & HAVING - Schule-DB

Verwenden Sie für diese Übung diese Datenbank:
*LINK 07mysql/_dumps/schuleDB/uebungSchuleDB.sql LINK*

Das ER-Diagramm:
*LINK 07mysql/_dumps/schuleDB/uebungSchuleModel.png LINK*

A1) Geben Sie eine Liste der Durchschnittsnoten (Deutsch, Mathe) aller Schüler aus; es werden aber nur die Schüler ausgegeben, deren Durchschnitt besser als 4 ist. Ausgabe: Schülername, Durchschnittsnote


A2) Runden Sie in der vorigen Aufgabe die Durchschnittsnote auf eine Dezimale und sortieren Sie die Ausgabe nach der Durchschnittsnote aufsteigend.


B) Geben Sie eine Liste aller Lehrer und ihres Nettogehalts (gehalt * 0.7) aus. Wir wollen nur die Lehrer sehen, deren Nettogehalt mehr als 3000 Euro beträgt.


C) Wir wollen herausbekommen, in welchen Klassenzimmern zu wenig Schüler unterrichtet werden. Geben Sie eine Liste der Klassenzimmer und die in diesen Klassenzimmern unterrichteten Schüler aus (Spalten "Klassenzimmer", "Anzahl"). Dabei interessieren uns nur die Klassenzimmer, in denen weniger als 10 Schüler sitzen.


D1) Wie viele Schüler mit russischer Herkunft (nationalitaet: "RU") wohnen in den einzelnen Orten? Geben Sie eine Liste aus mit "Anzahl" und "Ort-Name". Bitte nach Ort-Name aufsteigend sortieren.


D2) Erweitern Sie die Aufgabe D1 so, dass nur die Orte ausgegeben werden, in denen 10 oder mehr russischstämmige Schüler wohnen.


@@@

MySQL11_2: HAVING oder WHERE?

Verwenden Sie für diese Übung diese Datenbank:
*LINK 07mysql/_dumps/schuleDB/uebungSchuleDB.sql LINK*

Das ER-Diagramm:
*LINK 07mysql/_dumps/schuleDB/uebungSchuleModel.png LINK*

A) 
Zeige den Durchschnitt der Mathenoten aller Schüler an, deren Name mit einem B beginnt.
Berücksichtigte dabei nur die Schüler, die eine Note besser als 3 haben.

WHERE oder HAVING - Welche Lösung ist richtig?

(A1) SELECT 
    AVG(s.noteMathe) AS `Durchschnitt Mathe`
FROM
    schueler s
WHERE s.name LIKE 'M%'
	AND
	s.noteMathe < 3

oder

(A2)
SELECT 
    AVG(s.noteMathe) AS `Durchschnitt Mathe`
FROM
    schueler s
WHERE s.name LIKE 'M%'
HAVING s.noteMathe < 3



B) 
Zeige eine Liste aller Smartphonemarken und für jede Marke die Anzahl der Schüler, die ein solches Smartphone besitzen.
Die Marke Apple wollen wir nicht berücksichtigen.
Außerdem interessieren uns nur die Marken, von denen mindestens 15 Schüler ein Smartphone haben.


WHERE oder HAVING - Welche Lösung ist richtig?

(B1)
SELECT 
    COUNT(s.id) AS anzahl, sp.marke
FROM
    schueler s,
    smartphones sp
WHERE
    s.idSmartphones = sp.id
		AND anzahl > 15
        AND sp.marke != 'Apple'
GROUP BY sp.id


ODER

(B2) SELECT 
    COUNT(s.id) AS anzahl, sp.marke
FROM
    schueler s,
    smartphones sp
WHERE
    s.idSmartphones = sp.id
        AND sp.marke != 'Apple'
GROUP BY sp.id
HAVING anzahl > 15


C)
Wie viele Lehrer, die mehr als 2000 Euro verdienen, sind Angestellter / Beamter?
Bitte nur diejenigen berücksichtigen, die im Ort Nr. 4 wohnen.

WHERE oder HAVING - Welche Lösung ist richtig?

(C1)
SELECT 
    COUNT(l.id) AS anzahl, l.status
FROM
    lehrer l
WHERE
    l.gehalt > 2000
    AND
    l.idOrte = 4
GROUP BY l.status

(C2)
SELECT 
    COUNT(l.id) AS anzahl, l.status
FROM
    lehrer l
WHERE
    l.idOrte = 4
GROUP BY l.status
HAVING l.gehalt > 2000

D)
Wir wollen eine Liste aller Fächer, die mit R oder M beginnen.
Für jedes Fach brauchen wir die Anzahl der Lehrer, die es unterrichten, außerdem das Durchschnittsgehalt der Lehrer dieses Fachbereichs.
Da uns nur die Fächer interessieren, in denen man richtig viel verdient, lassen wir uns nur die Einträge anzeigen, bei denen das Durchschnittsgehalt über 2800 Euro liegt.

WHERE oder HAVING - Welche Lösung ist richtig?

(D1)
SELECT 
    COUNT(l.id) AS anzahlLehrer,
    AVG(l.gehalt) AS durchschnittsgehalt,
    f.fachbezeichnung
FROM
    faecher f,
    lehrer l,
    lehrer_hat_faecher lhf
WHERE
    lhf.idFaecher = f.id
        AND lhf.idLehrer = l.id
        AND durchschnittsgehalt > 2800
GROUP BY f.id
HAVING (f.fachbezeichnung LIKE 'm%'
		OR f.fachbezeichnung LIKE 'r%')


(D2)
SELECT 
    COUNT(l.id) AS anzahlLehrer,
    AVG(l.gehalt) AS durchschnittsgehalt,
    f.fachbezeichnung
FROM
    faecher f,
    lehrer l,
    lehrer_hat_faecher lhf
WHERE
    lhf.idFaecher = f.id
        AND lhf.idLehrer = l.id
        AND COUNT(l.gehalt) > 2800
        AND 
			(f.fachbezeichnung LIKE 'm%'
			OR f.fachbezeichnung LIKE 'r%')
GROUP BY f.id

(D3)

SELECT 
    COUNT(l.id) AS anzahlLehrer,
    AVG(l.gehalt) AS durchschnittsgehalt,
    f.fachbezeichnung
FROM
    faecher f,
    lehrer l,
    lehrer_hat_faecher lhf
WHERE
    lhf.idFaecher = f.id
        AND lhf.idLehrer = l.id
        AND 
			(f.fachbezeichnung LIKE 'm%'
			OR f.fachbezeichnung LIKE 'r%')
GROUP BY f.id
HAVING durchschnittsgehalt > 2800