Du befindest dich hier: FSI Informatik » jahrgaenge » 2006 » Lösungen SoS 3b (SQL)   (Übersicht)

no way to compare when less than two revisions

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen der Seite angezeigt.


—jahrgaenge:2006:sos3b [22.02.2008 23:10] (aktuell) – angelegt Noya
Zeile 1: Zeile 1:
 +====== 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>