***** Übungen: MySQL - Subselects/Subqueries - LÖSUNGEN *****

MySQL13_1: Subquery erzeugt Wert - Buchladen

In dieser Übung werden Subselects verwendet, die einen einzelnen Wert zurückgeben. Manche Aufgaben könnten Sie auch auf andere (einfachere) Art und Weise erledigen, aber es geht hier darum, Subqueries zu üben und zu verwenden.

Tipp: Sie können für diesen Wert erst einmal einen beliebigen Wert annehmen, um die äußere Abfrage zu formulieren (... WHERE einkaufspreis>100); dann ersetzen Sie diesen Wert (im Beispiel 100) durch eine SELECT-Abfrage, die den gewünschten Wert aus der Datenbank holt.

Benutzen Sie für diese Übung diese Datenbank:
*LINK 07mysql/_dumps/buchladen/buchladen.sql LINK*
Hier das Model (ERD) als Bilddatei:
*LINK 07mysql/_dumps/buchladen/buchladen_ERD.png LINK*

A1) Welches ist das teuerste Buch in der Datenbank?

SELECT 
    *
FROM
    buecher
WHERE
    verkaufspreis = (SELECT 
            MAX(verkaufspreis)
        FROM
            buecher)

A2) Welches ist das billigste Buch in der Datenbank?

B) Lassen Sie sich alle Bücher ausgeben, deren Einkaufspreis über dem durchschnittlichen Einkaufspreis aller Bücher in der Datenbank liegt.

SELECT 
    *
FROM
    buecher
WHERE
    einkaufspreis > (SELECT 
            AVG(einkaufspreis)
        FROM
            buecher)
            
            

C) Lassen Sie sich alle Bücher ausgeben, deren Einkaufspreis über dem durchschnittlichen Einkaufspreis der Thriller liegt.

SELECT 
    *
FROM
    buecher
WHERE
    einkaufspreis > (SELECT 
            AVG(einkaufspreis)
        FROM
            buecher b,
            sparten s,
            buecher_has_sparten bhs
        WHERE
            bhs.buecher_buecher_id = b.buecher_id
                AND bhs.sparten_sparten_id = s.sparten_id
                AND s.bezeichnung = 'Thriller')

D) Lassen Sie sich alle Thriller ausgeben, deren Einkaufspreis über dem durchschnittlichen Einkaufspreis der Thriller liegt.

SELECT 
    *
FROM
    buecher b,
    sparten s,
    buecher_has_sparten bhs
WHERE
    bhs.buecher_buecher_id = b.buecher_id
        AND bhs.sparten_sparten_id = s.sparten_id
        AND s.bezeichnung = 'Thriller'
        AND b.einkaufspreis > (SELECT 
            AVG(b.einkaufspreis)
        FROM
            buecher b,
            sparten s,
            buecher_has_sparten bhs
        WHERE
            bhs.buecher_buecher_id = b.buecher_id
                AND bhs.sparten_sparten_id = s.sparten_id
                AND s.bezeichnung = 'Thriller')

E) Lassen Sie sich alle Bücher ausgeben, bei denen der Gewinn überdurchschnittlich ist; bei der Berechnung des Gewinndurchschnitts berücksichtigen Sie NICHT das Buch mit der id 22.


SELECT 
    titel, (verkaufspreis - einkaufspreis) AS gewinn
FROM
    buecher
HAVING gewinn > (SELECT 
        (ROUND(AVG(b.verkaufspreis - b.einkaufspreis), 2))
    FROM
        buecher b
    WHERE
        b.buecher_id <> 22)
        
        
@@@

MySQL13_2: Subselects, bei denen der Subselect eine Tabelle zurückgibt - Buchladen (mit Tipps)

In dieser Übung werden Subselects verwendet, die eine Tabelle zurückgeben.

Benutzen Sie für diese Übung diese Datenbank:
*LINK 07mysql/_dumps/buchladen/buchladen.sql LINK*
Hier das Model (ERD) als Bilddatei:
*LINK 07mysql/_dumps/buchladen/buchladen_ERD.png LINK*

a) Wir brauchen die Summe der durchschnittlichen Einkaufspreise der einzelnen Sparten. Allerdings wollen wir dabei nicht die Sparte Humor berücksichtigen, ebensowenig die Sparten, in denen der durchschnittliche Einkaufspreis 10 Euro oder weniger beträgt.

Tipp: Erstellen Sie ein Subselect, dessen Ergebnis eine Tabelle ist, in der die gewünschten Sparten und ihre durchschnittlichen Einkaufspreise ausgegeben werden.
Von dieser Tabelle fragen Sie anschließend die Summe ab.

Lösung: 218.67

SELECT 
    (ROUND(SUM(schnitte.durchschnittlicherEinkaufspreis),
            2))
FROM
    (SELECT 
        s.bezeichnung,
            AVG(b.einkaufspreis) AS durchschnittlicherEinkaufspreis
    FROM
        buecher b, sparten s, buecher_has_sparten bhs
    WHERE
        bhs.buecher_buecher_id = b.buecher_id
            AND bhs.sparten_sparten_id = s.sparten_id
            AND s.bezeichnung != 'Humor'
    GROUP BY s.sparten_id
    HAVING durchschnittlicherEinkaufspreis >= 10) AS schnitte




b)
"Bekannte Autoren" definieren wir als Autoren, die mehr als 4 Bücher veröffentlicht haben. Wie viele solcher Autor/innen haben wir in der Datenbank?

Tipp: Erstellen Sie ein Subselect, das Ihnen die bekannten Autoren ausgibt. Um zu sehen, ob Ihr Ergebnis plausibel wirkt, lassen Sie sich ausgeben: Vorname, Nachname, Anzahl veröffentlichter Bücher
Über dieses Subselect machen Sie eine einfach COUNT-Abfrage.

SELECT 
    COUNT(*) AS `Anzahl Autoren mit mehr als 4 Büchern`
FROM
    (SELECT 
    COUNT(*) AS `Anzahl veröffentlichter Bücher`,
    a.vorname,
    a.nachname
FROM
    autoren a,
    autoren_has_buecher ab,
    buecher b
WHERE
    ab.autoren_autoren_id = a.autoren_id
        AND ab.buecher_buecher_id = b.buecher_id
GROUP BY a.autoren_id
HAVING `Anzahl veröffentlichter Bücher` > 4
ORDER BY `Anzahl veröffentlichter Bücher` DESC) AS `bekannte Autoren`
    

c) Ihr Chef sagt zu Ihnen: "Schauen Sie sich mal alle Verlage an, die im Durchschnitt weniger als 10 Euro Gewinn pro Buch machen. Ich glaube, die verdienen im Schnitt höchstens 7 Euro pro Buch."

Tipp: Erstellen Sie für den ersten Satz des Chefs ein Subselect, das Sie für die Überprüfung des zweiten Satzes verwenden (Ausgabe: 'durchschnittlicher Gewinn pro Buch der Verlage, die weniger als 10 Euro pro Buch verdienen')

SELECT 
    ROUND(AVG(schnitte.`durchschnittlicher Gewinn des Verlags`),
            2) AS `durchschnittlicher Gewinn pro Buch der Verlage, die weniger als 10 Euro pro Buch verdienen`
FROM
    (SELECT 
        v.name,
            ROUND(AVG(b.verkaufspreis), 2) AS `durchschnittlicher Verkaufspreis des Verlags pro Buch`,
            ROUND(AVG(b.einkaufspreis), 2) AS `durchschnittlicher Einkaufspreis des Verlags pro Buch`,
            ROUND((AVG(b.verkaufspreis) - AVG(b.einkaufspreis)), 2) AS `durchschnittlicher Gewinn des Verlags`
    FROM
        verlage v, orte o, buecher b
    WHERE
        v.orte_orte_id = o.orte_id
            AND b.verlage_verlage_id = v.verlage_id
    GROUP BY v.name
    HAVING `durchschnittlicher Gewinn des Verlags` < 10) AS schnitte