Lösungen SoS 3b (SQL)
a3b1
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;
a3b2
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;
a3b3
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;
a3b4
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;
a3b5
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;
a3b6
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;
a3b7
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;
a3b8
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;