Jeder BAUSTEIN-Typ besitzt eine eindeutige Nummer (NR) und eine Beschreibung (BESCHR). Baustein-Typen gibt es in verschiedenen Farben. Jede FARBE hat einen eindeutigen Namen (NAME) sowie einen korrespondierenden RGB-, CMYK- und PANTONE-Farbwert. Es wird für jeden Baustein-Typ vermerkt, in welchen Farben dieser bisher überhaupt produziert worden ist. Außerdem wird die Anzahl (ANZAHL) der Baustein-Typen pro Farbe gespeichert, die im Besitz des Sammlers sind (z.B. ist in der Sammlung der Baustein-Typ Nummer "1" dreimal in "grün", fünfmal in "rot" und einmal in "blau" enthalten). Baustein-Typen, die der Sammler besitzt, werden in KISTEN mit eindeutigen Nummern (NR), einer BESCHRIFTUNG und einem bestimmten Fassungsvermögen (FASSUNGSVER) aufbewahrt, wobei jeder Baustein-Typ genau einer Kiste zugeordnet ist, eine Kiste aber verschiedene Baustein-Typen beinhalten kann. Baustein-Typen werden gewöhnlich in Form von LEGOSETS vertrieben, die alle eine eindeutige Produktnummer (PNR), eine Bezeichnung (BEZ) und ein Erscheinungsjahr (JAHR) haben. Für jedes Set wird vermerkt, ob es im Besitz des Sammlers ist oder nicht (BESITZT). Besitzt der Sammler ein Set, so ist der Wert von besitzt 1. Es wird auch vermerkt, welche Baustein-Typen in welcher Farbe wie oft (ANZAHL) in einem Set enthalten sind. Neben Legosets, die im Handel einzeln erwerblich sind und von denen der Listenpreis (LPREIS) gespeichert werden soll, gibt es auch Legosets, die einzeln nicht gekauft werden können, sondern nur im Zusammenhang mit Produkten anderer Firmen erhältlich sind (z.B. als "gratis Beigaben" zu Frühstücksflocken). Speichern Sie bei dieser Art von "Werbesets" den Namen der Firma (FIRMA), die diese vertreibt. Weiters ist jedes Set genau einem bestimmten SPIELTHEMA zugeordnet, wobei ein Spielthema natürlich mehrere Sets umfassen kann. Spielthemen werden durch ihre Bezeichnung (BEZ) eindeutig identifiziert und sind hierarchisch organisiert, d.h. zu jedem Spielthema kann es mehrere untergeordnete Spielthemen geben, wobei ein bestimmtes Spielthema aber höchstens ein direkt übergeordnetes Spielthema hat. Jedes Set wird mit einer oder mehreren BAUANLEITUNGEN ausgeliefert. Bauanleitungen haben eine pro Set eindeutige Nummer (NR), eine Seitenanzahl (SEITEN), ein bestimmtes Papierformat (FORMAT) und werden - sofern der Sammler sie im Original besitzt - in speziellen MAPPEN aufgehoben. Mappen haben eine eindeutige Nummer (NR), eine BESCHRIFTUNG und können maximal zwanzig Bauanleitungen beinhalten. Für Bauanleitungen, die der Sammler in Form von PDF-Dateien besitzt, soll der Dateiname (DATEI) und der Speicherpfad (PFAD) eingetragen werden. Lego-Modelle, die der Sammler selbst entwirft, sollen ebenfalls in der Datenbank gespeichert werden. Gehen Sie davon aus, dass jedes MODELL einen Namen (NAME), ein Erstellungsdatum (DATUM), ein Beschreibung (TEXT) und einen Schwierigkeitsgrad (GRAD) besitzt, wobei die Kombination aus Namen und Datum eindeutig ist. Modelle können einem oder mehreren Spielthemen zugeordnet sein.
1)
Geben Sie die Produktnummer, die Bezeichnung und das Erscheinungsjahr aller Legosets aus.
SELECT pnr, bez, jahr FROM Legoset;
2)
Geben Sie die Produktnummer, die Bezeichnung, das Erscheinungsjahr und den Listenpreis aller Lego-Verkaufssets aus. Sortieren Sie die Liste absteigend nach dem Listenpreis.
SELECT pnr, bez, jahr, lpreis FROM Verkaufsset NATURAL JOIN Legoset ORDER BY lpreis DESC;
3)
Geben Sie die Namen derjenigen Firmen aus, deren Werbesets im Besitz des Sammlers sind. Vermeiden Sie dabei, dass eine Firma doppelt angefuehrt wird.
SELECT DISTINCT firma FROM Werbeset NATURAL JOIN Legoset WHERE besitzt = 1;
4)
Geben Sie Name, Datum und Text aller Modelle aus, die seit dem 1.1.2009 gebaut wurden und nicht schwieriger als Kategorie 4 sind. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY.
SELECT name, datum, text
FROM Modell
WHERE datum >= TO_DATE('01-01-2009','dd-mm-yyyy') AND grad > 4;
select NAME, DATUM, TEXT
from MODELL
where DATUM >= TO_DATE('01-01-2009', 'DD-MM-YYYY')
and GRAD > 4
5)
In der Mappe mit der Beschriftung 'Zum Nachbauen' werden die Bauanleitungen welcher Legosets (Produktnummer und Bezeichnung) aufgehoben? Vermeiden Sie doppelte Eintraege.
SELECT DISTINCT l.pnr, l.bez FROM Legoset l JOIN in_Mappe i ON (l.pnr = i.pnr) JOIN Mappe m ON (i.mappe = m.nr) WHERE beschriftung = 'Zum Nachbauen';
6.1)
Geben Sie Produktnummer, Nummer und Format der Bauanleitungen aus zusammen mit der Nummer der Mappe, in der sie aufgehoben werden.
SELECT pnr, nr, format, mappe FROM Bauanleitung NATURAL JOIN in_Mappe;
6.2)
Geben Sie Produktnummer, Nummer und Format ALLER Bauanleitungen aus zusammen mit der Nummer der Mappe, in der sie aufgehoben werden. Fuer Bauanleitungen, die nicht in Mappen aufgehoben werden, soll die Zahl '999' ausgegeben werden. (Stichwort: coalesce).
SELECT b.pnr, b.nr, b.format, COALESCE(i.mappe, 999) AS Mappe FROM Bauanleitung b LEFT OUTER JOIN in_Mappe i ON (b.pnr = i.pnr AND b.nr = i.nr);
6.3)
Geben Sie Produktnummer, Nummer und Format ALLER Bauanleitungen aus zusammen mit der Beschriftung der Mappe, in der sie aufgehoben werden. Fuer Bauanleitungen, die nicht in Mappen aufgehoben werden, soll 'keine Mappe vorhanden' ausgegeben werden. (Stichwort: coalesce).
SELECT b.pnr, b.nr, b.format, COALESCE(m.beschriftung, 'keine Mappe vorhanden') AS Mappe FROM Bauanleitung b LEFT OUTER JOIN (in_Mappe i JOIN Mappe m ON (i.mappe = m.nr)) ON (b.pnr = i.pnr AND b.nr = i.nr);
7.1)
Geben Sie das Datum des zuletzt gebauten Modells aus.
SELECT MAX(datum) AS Datum FROM Modell;
7.2)
Geben Sie Name, Datum und Text der zuletzt gebauten Modelle aus.
SELECT name, datum, text FROM Modell WHERE datum = (SELECT MAX(datum) FROM Modell);
SELECT name, datum, text FROM Modell WHERE datum >= ALL (SELECT datum FROM Modell);
SELECT m.name, m.datum, m.text FROM Modell m WHERE NOT EXISTS (SELECT * FROM Modell WHERE datum > m.datum);
7.3)
Geben Sie Name, Datum und Text des zuletzt gebauten Modells mit einem Schwierigkeitsgrad von 3 aus.
SELECT name, datum, text FROM Modell WHERE grad = 3 AND datum = (SELECT MAX(datum) FROM Modell WHERE grad = 3);
SELECT name, datum, text FROM Modell WHERE grad = 3 AND datum >= ALL (SELECT datum FROM Modell WHERE grad = 3);
SELECT m.name, m.datum, m.text FROM Modell m WHERE m.grad = 3 AND NOT EXISTS (SELECT * FROM Modell WHERE grad = 3 AND datum > m.datum);
8)
Geben Sie ALLE Bausteine (Nummer, Beschreibung) aus und wenn die Kiste, in der sie aufgehoben werden, sehr gross ist (das Fassungsvermoegen liegt ueber 3 Liter), auch die Beschriftung der Kiste, ansonsten: 'keine grosse Kiste'.
SELECT b.nr, b.beschr, coalesce(k.beschriftung, 'keine grosse Kiste') AS Kiste FROM Baustein b LEFT OUTER JOIN (in_Kiste i JOIN Kiste k ON (i.kiste = k.nr AND k.fassungsver > 3)) ON (b.nr = i.baustein);
9.1)
Wieviele Spielthemen sind dem Modell 'Krokodil', das am 22.8.2009 erbaut wurde, zugeordnet?
SELECT COUNT(*) AS Anzahl
FROM zugeordnet
WHERE name = 'Krokodil' AND datum = TO_DATE('22-08-2009','dd-mm-yyyy');
9.2)
Geben Sie Name, Text und Schwierigkeitsgrad aller Modelle aus zusammen mit der Anzahl der zugeordneten Spielthemen.
SELECT name, text, grad, COUNT(*) AS Anzahl FROM Modell NATURAL JOIN zugeordnet GROUP BY name, datum, text, grad;
9.3)
Geben Sie Name, Text und Schwierigkeitsgrad jener Modelle aus, denen am meisten Spielthemen zugeordnet sind.
SELECT name, text, grad FROM Modell NATURAL JOIN zugeordnet GROUP BY name, datum, text, grad HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM zugeordnet GROUP BY name, datum);
SELECT name, text, grad FROM Modell NATURAL JOIN zugeordnet GROUP BY name, datum, text, grad HAVING COUNT(*) = (SELECT MAX(ANZ) FROM (SELECT COUNT(*) AS ANZ FROM zugeordnet GROUP BY name, datum))
10)
Geben Sie zu jedem Legoset (Bezeichnung und Spielthema), das im Besitz des Sammlers ist, die Anzahl der in ihnen enthaltenen Einzelbausteine aus.
SELECT l.bez, l.spielthema, SUM(i.anzahl) AS Anzahl FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) WHERE l.besitzt = 1 GROUP BY l.pnr, l.bez, l.spielthema;
11.1)
Gesucht sind jene Lego-Verkaufssets (Produktnummer, Bezeichnung und Erscheinungsjahr), deren Listenpreis unter 20 Euro liegt und die nicht im Jahr 2003 erschienen sind.
SELECT pnr, bez, jahr FROM Legoset WHERE pnr IN (SELECT pnr FROM Verkaufsset WHERE lpreis < 20) AND jahr != 2003;
SELECT pnr, bez, jahr FROM Legoset NATURAL JOIN Verkaufsset WHERE lpreis < 20 AND jahr != 2003;
11.2)
Gesucht sind jene Lego-Verkaufssets (Produktnummer, Bezeichnung und Erscheinungsjahr), deren Listenpreis unter 20 Euro liegt und die keinen weissen Baustein enthalten.
SELECT pnr, bez, jahr FROM Legoset WHERE pnr IN (SELECT pnr FROM Verkaufsset WHERE lpreis < 20) AND pnr NOT IN (SELECT legoset FROM in_Set WHERE farbe = 'Weiss');
SELECT pnr, bez, jahr FROM Legoset NATURAL JOIN Verkaufsset WHERE lpreis < 20 AND pnr NOT IN (SELECT legoset FROM in_Set WHERE farbe = 'Weiss');
SELECT l.pnr, l.bez, l.jahr FROM Legoset l WHERE EXISTS (SELECT * FROM Verkaufsset v WHERE v.lpreis < 20 AND v.pnr =l.pnr) AND NOT EXISTS (SELECT * FROM in_Set i WHERE i.farbe = 'Weiss' AND i.legoset = l.pnr);
12)
Gesucht sind jene Bausteintypen (Nummer und Beschreibung), die in allen gelisteten Farben produziert worden sind, aber nicht im Besitz des Sammlers sind.
SELECT b.nr, b.beschr FROM Baustein b JOIN produziert_in i ON (b.nr = i.baustein) WHERE b.nr NOT IN (SELECT baustein FROM besitzt) GROUP BY b.nr, b.beschr HAVING COUNT(i.farbe) = (SELECT COUNT(*) FROM Farbe);
SELECT b.nr, b.beschr FROM Baustein b JOIN produziert_in i ON (b.nr = i.baustein) WHERE NOT EXISTS (SELECT * FROM besitzt WHERE baustein = b.nr) GROUP BY b.nr, b.beschr HAVING COUNT(i.farbe) = (SELECT COUNT(*) FROM Farbe);
SELECT * FROM Baustein b WHERE NOT EXISTS (SELECT * FROM FARBE WHERE name NOT IN (SELECT farbe FROM produziert_in WHERE baustein = b.nr)) AND b.nr NOT IN (SELECT baustein FROM besitzt)
13)
Geben Sie fuer jedes Erscheinungsjahr das Legoset (Bezeichnung) mit der hoechsten Anzahl an Einzelbausteinen aus.
SELECT l.jahr, l.bez FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) GROUP BY l.jahr, l.pnr, l.bez HAVING SUM(i.anzahl) >= ALL (SELECT SUM(n.anzahl) FROM in_Set n JOIN Legoset e ON (n.legoset = e.pnr) WHERE e.jahr = l.jahr GROUP BY e.pnr);
SELECT l.jahr, l.bez FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) GROUP BY l.jahr, l.pnr, l.bez HAVING SUM(i.anzahl) = (SELECT MAX(anz) FROM (SELECT e.jahr, SUM(n.anzahl) AS anz FROM in_Set n JOIN Legoset e ON (n.legoset = e.pnr) GROUP BY e.pnr, e.jahr) WHERE jahr = l.jahr);
14)
Geben Sie eine Liste ALLER Farben aus (Name und Pantone-Farbwert) und vermerken Sie in einer zusaetzlichen Spalte: a) 'keine Kiste', falls die Farbe in keiner Kiste vorkommt, b) 'eine Kiste', falls die Farbe in genau einer Kiste vorkommt und c) 'mehrere Kisten', falls die Farbe in mehr als einer Kiste vorkommt.
SELECT f.name, f.pantone, 'keine Kiste' AS Kisten FROM Farbe f WHERE NOT EXISTS (SELECT * FROM in_Kiste i JOIN Baustein b ON (i.baustein = b.nr) JOIN besitzt t ON (b.nr = t.baustein) WHERE t.farbe = f.name) UNION SELECT f.name, f.pantone, 'eine Kiste' AS Kisten FROM Farbe f WHERE EXISTS (SELECT * FROM in_Kiste i JOIN Baustein b ON (i.baustein = b.nr) JOIN besitzt t ON (b.nr = t.baustein) WHERE t.farbe = f.name GROUP BY t.farbe HAVING COUNT(distinct i.kiste) = 1) UNION SELECT f.name, f.pantone, 'mehrere Kisten' AS Kisten FROM Farbe f WHERE EXISTS (SELECT * FROM in_Kiste i JOIN Baustein b ON (i.baustein = b.nr) JOIN besitzt t ON (b.nr = t.baustein) WHERE t.farbe = f.name GROUP BY t.farbe HAVING COUNT(distinct i.kiste) > 1);
SELECT f.name, f.pantone, 'keine Kiste' AS Kisten FROM Farbe f WHERE f.name NOT IN (SELECT t.farbe FROM in_Kiste i JOIN Baustein b ON (i.baustein = b.nr) JOIN besitzt t ON (b.nr = t.baustein)) UNION SELECT f.name, f.pantone, 'eine Kiste' AS Kisten FROM Farbe f WHERE f.name IN (SELECT t.farbe FROM in_Kiste i JOIN Baustein b ON (i.baustein = b.nr) JOIN besitzt t ON (b.nr = t.baustein) GROUP BY t.farbe HAVING COUNT(distinct i.kiste) = 1) UNION SELECT f.name, f.pantone, 'mehrere Kisten' AS Kisten FROM Farbe f WHERE f.name IN (SELECT t.farbe FROM in_Kiste i JOIN Baustein b ON (i.baustein = b.nr) JOIN besitzt t ON (b.nr = t.baustein) GROUP BY t.farbe HAVING COUNT(distinct i.kiste) > 1);
SELECT f.name, f.pantone, 'keine Kiste' AS Kisten FROM Farbe f WHERE NOT EXISTS (SELECT * FROM in_Kiste i JOIN besitzt t ON (i.baustein = t.baustein) WHERE t.farbe = f.name) UNION SELECT f.name, f.pantone, 'eine Kiste' AS Kisten FROM Farbe f WHERE EXISTS (SELECT * FROM in_Kiste i JOIN besitzt t ON (i.baustein = t.baustein) WHERE t.farbe = f.name GROUP BY t.farbe HAVING COUNT(distinct i.kiste) = 1) UNION SELECT f.name, f.pantone, 'mehrere Kisten' AS Kisten FROM Farbe f WHERE EXISTS (SELECT * FROM in_Kiste i JOIN besitzt t ON (i.baustein = t.baustein) WHERE t.farbe = f.name GROUP BY t.farbe HAVING COUNT(distinct i.kiste) > 1);
SELECT f.name, f.pantone, 'keine Kiste' AS Kisten FROM Farbe f WHERE f.name NOT IN (SELECT farbe FROM in_Kiste NATURAL JOIN besitzt) UNION SELECT f.name, f.pantone, 'eine Kiste' AS Kisten FROM Farbe f WHERE f.name IN (SELECT farbe FROM in_Kiste NATURAL JOIN besitzt GROUP BY farbe HAVING COUNT(distinct kiste) = 1) UNION SELECT f.name, f.pantone, 'mehrere Kisten' AS Kisten FROM Farbe f WHERE f.name IN (SELECT farbe FROM in_Kiste NATURAL JOIN besitzt GROUP BY farbe HAVING COUNT(distinct kiste) > 1);
SELECT name, pantone, 'keine Kiste' FROM Farbe f WHERE 0 = ( SELECT COUNT (DISTINCT i.kiste) FROM In_Kiste i NATURAL JOIN Besitzt b WHERE b.farbe = f.name ) UNION SELECT name, pantone, 'eine Kiste' FROM Farbe f WHERE 1 = ( SELECT COUNT (DISTINCT i.kiste) FROM In_Kiste i NATURAL JOIN Besitzt b WHERE b.farbe = f.name ) UNION SELECT name, pantone, 'mehrere Kisten' FROM Farbe f WHERE 1 < ( SELECT COUNT (DISTINCT i.kiste) FROM In_Kiste i NATURAL JOIN Besitzt b WHERE b.farbe = f.name );
15)
Geben Sie ALLE Mappen (Beschriftung) aus und dazu die Anzahl der Legosets zum Spielthema 'Baufahrzeuge', deren Bauanleitungen sich in dieser Mappe befinden.
SELECT m.beschriftung, COUNT(DISTINCT l.pnr) AS anz_legosets FROM Mappe m LEFT OUTER JOIN (in_Mappe i JOIN Legoset l ON (i.pnr = l.pnr AND l.spielthema = 'Baufahrzeuge')) ON (m.nr = i.mappe) GROUP BY m.nr, m.beschriftung;
16)
Um wieviel kosten die Verkaufssets, die dem Spielthema 'Zuege' zugeordnet sind, durchschnittlich mehr als die anderer Spielthemen?
SELECT a.preis - b.preis AS preis_differenz FROM (SELECT AVG(lpreis) AS preis FROM Legoset NATURAL JOIN Verkaufsset WHERE spielthema = 'Zuege') a, (SELECT AVG(lpreis) AS preis FROM Legoset NATURAL JOIN Verkaufsset WHERE spielthema != 'Zuege') b;
17)
Geben Sie eine Liste ALLER Spielthemen aus zusammen mit der Anzahl der zugeordneten Werbesets, der Anzahl der zugeordneten Verkaufssets und der Anzahl der zugeordneten Modelle.
SELECT s.bez, coalesce(w.anz, 0) AS Werbesets, coalesce(v.anz, 0) AS Verkaufssets, coalesce(m.anz, 0) AS Modelle FROM Spielthema s LEFT OUTER JOIN (SELECT spielthema, COUNT(pnr) AS anz FROM Legoset NATURAL JOIN Werbeset GROUP BY spielthema) w ON (s.bez = w.spielthema) LEFT OUTER JOIN (SELECT spielthema, COUNT(pnr) AS anz FROM Legoset NATURAL JOIN Verkaufsset GROUP BY spielthema) v ON (s.bez = v.spielthema) LEFT OUTER JOIN (SELECT spielthema, COUNT(*) AS anz FROM zugeordnet GROUP BY spielthema) m ON (m.spielthema = s.bez);
SELECT s.bez, COUNT(DISTINCT w.pnr) AS Werbesets, COUNT(DISTINCT v.pnr) AS Verkaufssets, COUNT(DISTINCT CONCAT(z.name, z.datum)) AS Modelle FROM Spielthema s LEFT OUTER JOIN (SELECT pnr, spielthema FROM Legoset NATURAL JOIN Werbeset) w ON (s.bez = w.spielthema) LEFT OUTER JOIN (SELECT pnr, spielthema FROM Legoset NATURAL JOIN Verkaufsset) v ON (s.bez = v.spielthema) LEFT OUTER JOIN zugeordnet z ON (z.spielthema = s.bez) GROUP BY s.bez;
SELECT s.bez, werbe.anz AS werbesets, verkauf.anz AS verkaufsets, modell.anz AS modelle FROM Spielthema s JOIN ( SELECT spielthema, COUNT (*) AS anz FROM zugeordnet GROUP BY spielthema UNION SELECT bez, 0 AS anz FROM Spielthema WHERE bez NOT IN ( SELECT DISTINCT spielthema FROM zugeordnet ) ) modell ON ( s.bez = modell.spielthema ) JOIN ( SELECT spielthema, COUNT (*) AS anz FROM Werbeset NATURAL JOIN Legoset GROUP BY spielthema UNION SELECT bez, 0 AS anz FROM Spielthema WHERE bez NOT IN ( SELECT DISTINCT spielthema FROM Werbeset NATURAL JOIN Legoset ) ) werbe ON ( s.bez = werbe.spielthema ) JOIN ( SELECT spielthema, COUNT (*) AS anz FROM Verkaufsset NATURAL JOIN Legoset GROUP BY spielthema UNION SELECT bez, 0 AS anz FROM Spielthema WHERE bez NOT IN ( SELECT DISTINCT spielthema FROM Verkaufsset NATURAL JOIN Legoset ) ) verkauf ON ( s.bez = verkauf.spielthema ) ;
18)
Geben Sie jene Legosets (Produktnummer, Bezeichnung und Erscheinungsjahr) aus, die einem Spielthema zugeordnet sind, dem auch mindestens 20 Prozent der Modelle mit einem Schwierigkeitsgrad von unter 4 zugeordnet sind.
SELECT pnr, bez, jahr FROM Legoset WHERE spielthema IN (SELECT spielthema FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4 GROUP BY spielthema HAVING COUNT(*) >= (SELECT COUNT(*) * 0.2 FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4));
SELECT l.pnr, l.bez, l.jahr FROM Legoset l WHERE EXISTS (SELECT * FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4 AND spielthema = l.spielthema GROUP BY spielthema HAVING COUNT(*) >= (SELECT COUNT(*) * 0.2 FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4));
SELECT pnr, bez, jahr FROM Legoset WHERE spielthema IN (SELECT spielthema FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4 GROUP BY spielthema HAVING COUNT(*) >= (SELECT COUNT(*) / 5 FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4));
SELECT l.pnr, l.bez, l.jahr FROM Legoset l WHERE EXISTS (SELECT * FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4 AND spielthema = l.spielthema GROUP BY spielthema HAVING COUNT(*) >= (SELECT COUNT(*) / 5 FROM zugeordnet NATURAL JOIN Modell WHERE grad < 4));
19)
Welche Verkaufssets (Produktnummer und Bezeichnung) haben ein neueres Erscheinungsjahr als das zuletzt erschienene Werbeset?
SELECT pnr, bez FROM Verkaufsset NATURAL JOIN Legoset WHERE jahr > (SELECT MAX(jahr) FROM Legoset NATURAL JOIN Werbeset);
20)
Gesucht sind jene Legosets (Produktnummer, Bezeichnung und Erscheinungsjahr), die Bausteine mit dem Pantone-Farbwert '2728 C' und/oder '165 C' enthalten, aber keine mit dem Pantone-Farbwert 'CoolGrey 1 C'.
SELECT DISTINCT l.pnr, l.bez, l.jahr FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) JOIN Farbe f ON (i.farbe = f.name) WHERE f.pantone = '2728 C' OR f.pantone = '165 C' AND l.pnr NOT IN (SELECT legoset FROM in_Set JOIN Farbe ON (in_Set.farbe = Farbe.name AND pantone = 'CoolGrey 1 C'));
21)
Geben Sie alle Legosets (Produktnummer, Bezeichnung und Erscheinungsjahr) aus, von denen mindestens eine Bauanleitung in einer Mappe eingeordnet ist und die ausserdem einen Bausteintyp enthalten, der in mindestens 3 verschiedenen Farben in diesem Set vorkommt.
SELECT DISTINCT l.pnr, l.bez, l.jahr FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) WHERE l.pnr IN (SELECT pnr FROM in_Mappe) GROUP BY l.pnr, l.bez, l.jahr, i.baustein HAVING COUNT(i.farbe) >= 3;
SELECT DISTINCT l.pnr, l.bez, l.jahr FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) WHERE EXISTS (SELECT * FROM in_Mappe WHERE pnr = l.pnr) GROUP BY l.pnr, l.bez, l.jahr, i.baustein HAVING COUNT(i.farbe) >= 3;
SELECT DISTINCT l.pnr, l.bez, l.jahr FROM Legoset l JOIN in_Set i ON (l.pnr = i.legoset) JOIN in_Mappe m ON (l.pnr = m.pnr) GROUP BY l.pnr, l.bez, l.jahr, i.baustein HAVING COUNT(DISTINCT i.farbe) >= 3;
22)
In welcher kleinen Kiste (Fassungsvermoegen betraegt weniger als 3 Liter) ist die Anzahl der Einzelbausteine, die in ihnen gelagert werden, am hoechsten? Geben Sie die Beschriftung der Kiste zusammen mit der Anzahl der in ihnen enthaltenen Einzelbausteine aus.
SELECT k.beschriftung, SUM(b.anzahl) AS Anzahl FROM Kiste k JOIN in_Kiste i ON (k.nr = i.kiste) JOIN besitzt b ON (i.baustein = b. baustein) WHERE k.fassungsver < 3 GROUP BY k.nr, k.beschriftung HAVING SUM(b.anzahl) >= ALL(SELECT SUM(b.anzahl) FROM Kiste k JOIN in_Kiste i ON (k.nr = i.kiste) JOIN besitzt b ON (i.baustein = b. baustein) WHERE k.fassungsver < 3 GROUP BY k.nr);
SELECT k.beschriftung, SUM(b.anzahl) AS Anzahl FROM Kiste k JOIN in_Kiste i ON (k.nr = i.kiste) JOIN besitzt b ON (i.baustein = b. baustein) WHERE k.fassungsver < 3 GROUP BY k.nr, k.beschriftung HAVING SUM(b.anzahl) = ( SELECT MAX(anz) FROM (SELECT SUM(b.anzahl) AS anz FROM Kiste k JOIN in_Kiste i ON (k.nr = i.kiste) JOIN besitzt b ON (i.baustein = b. baustein) WHERE k.fassungsver < 3 GROUP BY k.nr));
23)
Geben Sie eine Liste ALLER Spielthemen aus und dazu den Namen, das Datum und den Schwierigkeitsgrad desjenigen Modells, das diesem Spielthema zuletzt zugeordnet wurde.
SELECT s.bez, m.name, m.datum, m.grad FROM (zugeordnet z JOIN Modell m ON (z.name = m.name AND z.datum = m.datum) JOIN (SELECT spielthema, MAX(datum) as dat FROM zugeordnet GROUP BY spielthema) t ON (t.spielthema = z.spielthema AND t.dat = z.datum)) RIGHT OUTER JOIN Spielthema s ON (z.spielthema = s.bez);
24)
Geben Sie eine Liste ALLER Verkaufssets (Produktnummer, Bezeichnung und Erscheinungsjahr) aus und dazu, wie viele andere Verkaufssets es gibt, deren Listenpreis hoeher ist.
SELECT l.pnr, l.bez, l.jahr, (SELECT COUNT(*) FROM Verkaufsset WHERE lpreis > v.lpreis) AS Anzahl FROM Verkaufsset v JOIN Legoset l ON (v.pnr = l.pnr);