====== Lösungen SoS 3b (SQL) ======

===== a3b1 =====
<code sql>
SELECT produktbeschreibung
FROM produkt p
JOIN bestellung_produkt bp ON bp.artikelnummer = p.artikelnummer
JOIN bestellung b ON b.id = bp.bestellung
JOIN kunde k ON k.kreditkartennummer = b.kunde
WHERE k.stadt = 'Bamberg'
ORDER BY produktbeschreibung ASC;
EXIT;
</code>

===== a3b2 =====
<code sql>
SELECT produktbeschreibung, p.artikelnummer, SUM(anzahl) AS bestellungen
FROM produkt p
JOIN bestellung_produkt bp ON bp.artikelnummer = p.artikelnummer
GROUP BY p.artikelnummer, p.produktbeschreibung
ORDER BY bestellungen ASC, p.artikelnummer ASC;
EXIT;
</code>

===== a3b3 =====
<code sql>
SELECT firma.firmenname, firma.strasse, firma.hausnummer, firma.stadt, 
firma.plz, COUNT(firmenname) As produkte
FROM firma
JOIN produkt ON produkt.strasse = firma.strasse AND produkt.hausnummer = 
firma.hausnummer AND produkt.stadt = firma.stadt AND produkt.plz = 
firma.plz
HAVING COUNT(firmenname) > 1
GROUP BY firma.firmenname, firma.strasse, firma.hausnummer, firma.stadt, 
firma.plz
ORDER BY COUNT(firmenname) ASC, firma.firmenname ASC, firma.strasse ASC, 
firma.hausnummer ASC, firma.stadt ASC, firma.plz ASC;
EXIT;
</code>

===== a3b4 =====
<code sql>
CREATE VIEW eink AS
SELECT kunde.kreditkartennummer, SUM((produkt.preis - produkt.einkaufspreis) * bp.anzahl) AS gewinn
FROM kunde
JOIN bestellung ON bestellung.kunde = kunde.kreditkartennummer
JOIN bestellung_produkt bp ON bp.bestellung = bestellung.id
JOIN produkt ON produkt.artikelnummer = bp.artikelnummer
GROUP BY kunde.kreditkartennummer;

CREATE VIEW werb AS
SELECT kk.werber AS kreditkartennummer, SUM(eink.gewinn * 0.05) AS gewinn
FROM eink
JOIN kunde_kunde kk ON kk.geworbener = eink.kreditkartennummer
GROUP BY kk.werber;

SELECT kreditkartennummer AS kunde, SUM(gewinn) AS gewinn
FROM ((SELECT * FROM eink UNION SELECT * FROM werb) 
	UNION SELECT kreditkartennummer, 0 AS gewinn FROM kunde)
GROUP BY kreditkartennummer
ORDER BY gewinn ASC, kreditkartennummer ASC;

DROP VIEW eink;
DROP VIEW werb;

EXIT;
</code>

===== a3b5 =====
<code sql>
CREATE VIEW best AS
SELECT firma.firmenname, firma.strasse, firma.hausnummer, firma.stadt, 
firma.plz, SUM(produkt.einkaufspreis * bp.anzahl) AS einnahmen
FROM bestellung_produkt bp
JOIN produkt ON bp.artikelnummer = produkt.artikelnummer
JOIN firma ON produkt.strasse = firma.strasse AND produkt.hausnummer = 
firma.hausnummer AND produkt.stadt = firma.stadt
AND produkt.plz = firma.plz
GROUP BY firma.firmenname, firma.strasse, firma.hausnummer, 
firma.stadt, firma.plz;

CREATE VIEW lag AS
SELECT firma.firmenname, firma.strasse, firma.hausnummer, firma.stadt, 
firma.plz, SUM(produkt.einkaufspreis * plb.anzahl) AS einnahmen
FROM produkt_lager_lagerbestellung plb
JOIN produkt ON produkt.artikelnummer = plb.artikelnummer
JOIN firma ON produkt.strasse = firma.strasse AND produkt.hausnummer = 
firma.hausnummer AND produkt.stadt = firma.stadt AND produkt.plz = 
firma.plz
GROUP BY firma.firmenname, firma.strasse, firma.hausnummer, firma.stadt, 
firma.plz;

CREATE VIEW ein AS
SELECT firmenname, strasse, hausnummer, stadt, plz, SUM(einnahmen) AS 
einnahmen
FROM ((SELECT * FROM best UNION SELECT * FROM lag)
	UNION SELECT firmenname, strasse, hausnummer, stadt, plz, 0 AS 
einnahmen FROM firma)
GROUP BY firmenname, strasse, hausnummer, stadt, plz;

SELECT COUNT(*) AS rang, a.firmenname, a.einnahmen
FROM ein a, ein b
WHERE a.einnahmen <= b.einnahmen
HAVING COUNT(*) <= 10
GROUP BY a.firmenname, a.einnahmen, a.strasse, a.hausnummer, a.stadt, 
a.plz
ORDER BY rang;

DROP VIEW best;
DROP VIEW lag;
DROP VIEW ein;

EXIT;
</code>

===== a3b6 =====
<code sql>
CREATE VIEW fk AS
SELECT firma.strasse, firma.hausnummer, firma.stadt, firma.plz, 
firma.firmenname
FROM firma
JOIN kunde ON kunde.strasse = firma.strasse AND kunde.hausnummer = 
firma.hausnummer AND kunde.stadt = firma.stadt AND kunde.plz = 
firma.plz;

CREATE VIEW xauf_lager AS
SELECT fk.firmenname, produkt.artikelnummer, COUNT(*) AS auf_lager
FROM fk
JOIN produkt ON produkt.strasse = fk.strasse AND produkt.hausnummer = 
fk.hausnummer AND produkt.stadt = fk.stadt AND produkt.plz = fk.plz
JOIN produktexemplar ex ON ex.artikelnummer = produkt.artikelnummer
JOIN produktexemplar_lager exla ON exla.artikelnummer = ex.artikelnummer 
AND exla.seriennummer = ex.seriennummer
GROUP BY fk.firmenname, fk.strasse, fk.hausnummer, 
fk.stadt, fk.plz, produkt.artikelnummer;

CREATE VIEW xlagerbestellt AS
SELECT fk.firmenname, produkt.artikelnummer, SUM(lb.anzahl) AS 
lagerbestellt 
FROM fk
JOIN produkt ON produkt.strasse = fk.strasse AND produkt.hausnummer = 
fk.hausnummer AND produkt.stadt = fk.stadt AND produkt.plz = fk.plz
JOIN produkt_lager_lagerbestellung lb ON lb.artikelnummer = 
produkt.artikelnummer
GROUP BY fk.firmenname, fk.strasse, fk.hausnummer, 
fk.stadt, fk.plz, produkt.artikelnummer;

CREATE VIEW xversendet AS
SELECT fk.firmenname, produkt.artikelnummer, COUNT(*) AS versendet
FROM fk
JOIN produkt ON produkt.strasse = fk.strasse AND produkt.hausnummer =
fk.hausnummer AND produkt.stadt = fk.stadt AND produkt.plz = fk.plz
JOIN paket_produktexemplar pex ON pex.artikelnummer = produkt.artikelnummer
GROUP BY fk.firmenname, fk.strasse, fk.hausnummer,
fk.stadt, fk.plz, produkt.artikelnummer;

CREATE VIEW xbestellt AS
SELECT fk.firmenname, produkt.artikelnummer, SUM(bp.anzahl) AS bestellt
FROM fk
JOIN produkt ON produkt.strasse = fk.strasse AND produkt.hausnummer =
fk.hausnummer AND produkt.stadt = fk.stadt AND produkt.plz = fk.plz
JOIN bestellung_produkt bp ON bp.artikelnummer = produkt.artikelnummer
GROUP BY fk.firmenname, fk.strasse, fk.hausnummer,
fk.stadt, fk.plz, produkt.artikelnummer;

SELECT xauf_lager.firmenname, xauf_lager.artikelnummer, auf_lager, 
lagerbestellt, versendet, bestellt
FROM xauf_lager
JOIN xlagerbestellt ON xlagerbestellt.firmenname = xauf_lager.firmenname 
AND xlagerbestellt.artikelnummer = xauf_lager.artikelnummer
JOIN xversendet ON xversendet.firmenname = xauf_lager.firmenname 
AND xversendet.artikelnummer = xauf_lager.artikelnummer
JOIN xbestellt ON xbestellt.firmenname = xauf_lager.firmenname 
AND xbestellt.artikelnummer = xauf_lager.artikelnummer
ORDER BY xauf_lager.firmenname ASC, xauf_lager.artikelnummer;

DROP VIEW fk;
DROP VIEW xauf_lager;
DROP VIEW xlagerbestellt;
DROP VIEW xversendet;
DROP VIEW xbestellt;

EXIT;
</code>

===== a3b7 =====
<code sql>
CREATE VIEW best AS
SELECT bestellung.kunde, bestellung.id AS bestellung, bp.artikelnummer, 
bp.anzahl AS zahl
FROM bestellung
JOIN bestellung_produkt bp ON bp.bestellung = bestellung.id;

CREATE VIEW ges AS
SELECT bestellung.kunde, bestellung.id AS bestellung, pex.artikelnummer, 
COUNT(*) AS zahl
FROM bestellung
JOIN paket ON paket.bestellung = bestellung.id
JOIN paket_produktexemplar pex ON pex.paketnummer = paket.paketnummer
GROUP BY bestellung.kunde, bestellung.id, pex.artikelnummer;

SELECT kunde.vorname, kunde.nachname, kunde.kreditkartennummer, 
a.bestellung, a.artikelnummer, best.zahl AS bestellt, ges.zahl AS 
gesendet
FROM (SELECT * FROM ges MINUS SELECT * FROM best) a
LEFT JOIN best ON best.bestellung = a.bestellung AND best.artikelnummer 
= a.artikelnummer
LEFT JOIN ges ON ges.bestellung = a.bestellung AND ges.artikelnummer = 
a.artikelnummer
JOIN kunde ON kunde.kreditkartennummer = a.kunde
ORDER BY kunde.kreditkartennummer ASC, a.bestellung ASC, a.artikelnummer 
ASC;

DROP VIEW best;
DROP VIEW ges;

EXIT;
</code>

===== a3b8 =====
<code sql>
CREATE VIEW kp AS
SELECT kunde.kreditkartennummer, bp.artikelnummer
FROM kunde
JOIN bestellung ON bestellung.kunde = kunde.kreditkartennummer
JOIN bestellung_produkt bp ON bp.bestellung = bestellung.id
GROUP BY kunde.kreditkartennummer, bp.artikelnummer;

CREATE VIEW kpa AS
SELECT kp.kreditkartennummer, COUNT(*) AS panzahl
FROM kp
GROUP BY kp.kreditkartennummer;

CREATE VIEW gleichh AS
SELECT a.kreditkartennummer AS kunde1, b.kreditkartennummer AS kunde2, 
COUNT(*) * 
100 / 
kpa.panzahl AS 
gleichheit
FROM kp a
JOIN kp b ON b.kreditkartennummer != a.kreditkartennummer AND 
b.artikelnummer = a.artikelnummer
JOIN kpa ON kpa.kreditkartennummer = a.kreditkartennummer
GROUP BY a.kreditkartennummer, b.kreditkartennummer, kpa.panzahl;

CREATE VIEW max AS
SELECT gleichh.kunde1, MAX(gleichh.gleichheit) AS 
gleichheit
FROM gleichh
GROUP BY gleichh.kunde1;

SELECT gleichh.kunde1, gleichh.kunde2, gleichh.gleichheit
FROM gleichh
JOIN max ON max.kunde1 = gleichh.kunde1 AND max.gleichheit = 
gleichh.gleichheit
ORDER BY gleichheit ASC, kunde1 ASC, kunde2 ASC;

DROP VIEW kp;
DROP VIEW kpa;
DROP VIEW gleichh;
DROP VIEW max;

EXIT;
</code>