SQL CASE OUTER JOIN SUBSELECT

ALTER SESSION SET current_schema=uchinook;

-- CASE bedingte Ausgabe
-- Klassifizieren von Rechnungen in geringe (unter 5), mittlere (5 bis 10) und hohe Rechnungssummen (über 10)
SELECT invoiceid, invoicedate, total,
    CASE
        WHEN total < 5 THEN 'geringe'
        WHEN total >=5 AND total <=10 THEN 'mittlere'
        WHEN total > 10 THEN 'hohe'
    ELSE
        'ausserhalb Spezifikation'
    END AS Klassifizierung_Summe
FROM invoice;

--Mitarbeiter betreut Kunden ODER Kunde wird von Mitarbeiter betreut
--Welche Mitarbeiter gibt es? (Gesamtmenge aus der Referenztabelle)
SELECT * from employee;

-- Welche Mitarbeiter sind Vertriebsmitarbeiter? (Menge der Fremdschlüsselwerte)
SELECT DISTINCT supportrepid from customer;

--Komplette Ausgabe Namen der Mitarbeiter und Namen der Kunden:
SELECT DISTINCT MA.FirstName, MA.LastName, MA.EmployeeId, KU.SupportRepId
FROM employee MA
    JOIN customer KU ON KU.SupportRepId=MA.EmployeeId;

-- Zeige mir alle Mitarbeiter und die Kunden der Vertriebsmitarbeiter
SELECT MA.FirstName, MA.LastName, MA.EmployeeId, KU.SupportRepId, KU.FirstName, KU.LastName
FROM employee MA
    JOIN customer KU ON KU.SupportRepId=MA.EmployeeId;
    
SELECT MA.EmployeeId, MA.FirstName, MA.LastName, KU.SupportRepId, KU.FirstName, KU.LastName
FROM employee MA
    LEFT JOIN customer KU ON KU.SupportRepId=MA.EmployeeId
    ORDER BY MA.EmployeeId;

-- welche Mitarbeiter sind keine Vertriebsmitarbeiter?
--über OUTER JOIN
SELECT MA.EmployeeId, MA.FirstName, MA.LastName
FROM employee MA
    LEFT JOIN customer KU ON KU.SupportRepId=MA.EmployeeId
    WHERE KU.SupportRepId is NULL
    ORDER BY MA.EmployeeId;

--MINUS
SELECT MA.EmployeeId FROM employee MA
MINUS
SELECT DISTINCT KU.SupportRepId from customer KU;

--UNION
SELECT MA.EmployeeId FROM employee MA
UNION
SELECT DISTINCT KU.SupportRepId FROM customer KU;

--Mengenvergleich NOT IN mit Unterabfrage/Subselect =  Differenzmenge
SELECT * FROM employee WHERE EmployeeId NOT IN (SELECT DISTINCT SupportRepId from customer);

--Welche Tracks wurden verkauft?
--INNER JOIN
SELECT DISTINCT TR.TrackId, TR.Name
FROM Track TR
    JOIN InvoiceLine IL ON IL.TrackId=TR.TrackId;


--Wieviel Tracks gibt es? 
SELECT COUNT(TrackId) FROM Track;


--Welche Tracks gibt es insgesamt und welche davon wurden verkauft?
-- mit outer join
SELECT TR.TrackId, TR.Name, IL.InvoiceLineId
FROM Track TR
    lEFT OUTER JOIN InvoiceLine IL ON IL.TrackId=TR.TrackId;


--Welche Tracks wurden noch nicht verkauft?
-- mit outer join
SELECT TR.TrackId, TR.Name, IL.InvoiceLineId
FROM Track TR
    lEFT OUTER JOIN InvoiceLine IL ON IL.TrackId=TR.TrackId
    WHERE IL.InvoiceLineId IS NULL;

-- mit NOT IN / Unterselect
SELECT * FROM Track WHERE TrackId NOT IN (SELECT TrackID FROM InvoiceLine);

-- weitere Übungsmöglichkeit: 
-- Welche Tracks sind keinem Album zugeordnet?
-- Welche Tracks sind keiner Playlist zugeordnet?