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


@@@

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

SELECT 
    s.name `Schülername`,
   (s.noteDeutsch + s.noteMathe) / 2 AS Durchschnittsnote
FROM
    schueler s
HAVING Durchschnittsnote < 4

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

SELECT 
    s.name `Schülername`,
    ROUND(((s.noteDeutsch + s.noteMathe) / 2), 1) AS Durchschnittsnote
FROM
    schueler s
HAVING Durchschnittsnote < 4
ORDER BY Durchschnittsnote ASC



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.

SELECT 
    l.name, (gehalt * 0.7) AS nettogehalt
FROM
    lehrer l
HAVING nettogehalt > 3000

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.

SELECT 
    s.klassenzimmer, COUNT(s.id) AS `Anzahl`
FROM
    schueler s
GROUP BY klassenzimmer
HAVING `Anzahl` < 15

D) 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.

SELECT 
    COUNT(s.id) AS `Anzahl`, o.name AS `Ort-Name`
FROM
    schueler s,
    orte o
WHERE
    s.idOrte = o.id
        AND s.nationalitaet = 'RU'
GROUP BY o.id
ORDER BY `Ort-Name`

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

SELECT 
    COUNT(s.id) AS `Anzahl`, o.name AS `Ort-Name`
FROM
    schueler s,
    orte o
WHERE
    s.idOrte = o.id
        AND s.nationalitaet = 'RU'
GROUP BY o.id
HAVING `Anzahl` >= 10
ORDER BY `Ort-Name`

@@@

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

LÖSUNG:
(A1)


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

LÖSUNG:
(B2)

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

LÖSUNG:
(C1)

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

LÖSUNG:
D3