Eine Gemeinde möchte eine Übersicht über die verschiedenen lokalen Imkereibetriebe bekommen und auch verstehen wie diese mit den verschiedenen Landwirtschaftsbetrieben interagieren. Hierfür wurden Sie als ein spezieller Bienenkoordinator beauftragt. Sie begannen damit ein konzeptionelles Modell zu erstellen, und beauftragten ihre Assistenten die nötigen Daten zu beschaffen. Die Struktur dieser Datenbank ist wie folgt. Jeder Imker und jede Imkerin ist eindeutig identifizierbar über eine Nummer (ImkerNR) und es wird zusätzlich der Name und das Geburtsdatum gespeichert. Jeder Imker und jede Imkerin kann auch eine Reihe von Hilfsarbeitende anstellen, die eindeutig identifizierbar sind über eine eigene Arbeiternummer (ArbeiterNR) in Kombination mit der Imkernummer ihres Anstellers (es ist erlaubt das verschiedene Imkereibetriebe die gleiche Arbeiternummer vergeben). Jeder Imker und jede Imkerin ist zuständig für eine Reihe von Bienenstöcken, die eindeutig über den Typ des Bienenstocks (Typ) und die Stocknummer (StockNr) gekennzeichnet sind. Für jeden Bienenstock ist auch der Ertrag in Kilogramm (Honigertrag) eingetragen. In jedem Bienenstock befinden sich auch mindestens ein Brutnest, das eine eigene Nummer (NestNr) hat, diese ist abhängig vom Typ und der Stocknummer des zugehörigen Bienenstocks. In jedem Bienenstock muss sich genau eine Königin befinden, und mindestens 10 Arbeiterinnen. Demnach kann jede Königin auch nur in einem Bienenstock leben, und jede Arbeiterin auch nur für einen Bienenstock arbeiten. Königinnen und Arbeiterinnen sind über eine eindeutige Kennzahl (Kennzahl) erkennbar, zusätzlich ist auch die Gattung bemerkt. Jede Arbeiterin muss auch mindestens ein Feld bestäuben (ist allerdings nicht eingeschränkt wie viele es bestäuben kann). Die für den Koordinator relevanten Landwirtschaftsbetriebe sind eindeutig über ihre Betriebsform (Betriebsform) und ihre Betriebsnummer (BetriebsNr) einzuordnen. Die Felder, die von Landwirtschaftsbetrieben verwendet werden können, sind eindeutig durch das Paar des Ortes (Ort) und einer Feldkennzahl (Feldkennzahl) gekennzeichnet. Zu allen verwendeten Felder wird auch festgestellt welcher Anteil (Flaechenanteil) vom jeweiligen Betrieb verwendet wird. Zuletzt bezahlen Landwirtschaftsbetriebe auch die jeweiligen Imker und Imkerinnen für die Bestäubung der Felder, die sie verwenden. Hierfür wird auch protokolliert wie viel sie bezahlen (Betrag).
1)
Geben Sie die Betriebsnummer und den Namen aller Landwirtschaftsbetriebe an, die als Haupterzeugnis 'Mais' haben und von der Betriebsform 'GmbH' sind. Sortieren Sie die Ausgabe aufsteigend nach dem Namen des Landwirtschaftsbetriebes. <br/><br/> English:<br/> List the number ('Betriebsnummer') and the name ('Namen') of all farms ('Landwirtschaftsbetriebe') that mainly produce corn ('Haupterzeugnis' is 'Mais') and whose type of business ('Betriebsform') is 'GmbH'. Sort the list ascending by the names of the farms.
SELECT BetriebsNr, Name FROM Landwirtschaftsbetrieb WHERE Haupterzeugnis = 'Mais' AND Betriebsform = 'GmbH' ORDER BY Name;
2)
Geben Sie für alle Typen von Bienenstöcken den durchschnittlichen Honigertrag aus, wobei nur jene Bienenstöcke berücksichtigt werden sollen, die mehr als 30 Arbeiterinnen vorweisen. Sortieren Sie das Ergebnis absteigend nach dem durchschnittlichen Ertrag. <br/><br/> English:<br/> List for all types of beehives ('Bienenstöcke') the average amount of honey harvested ('Honigertrag'), but consider only beehives that contain more than 30 worker bees ('Arbeiterin'). Sort the result descending by the average harvest.
SELECT Typ, AVG(Honigertrag) FROM Bienenstock WHERE (SELECT count(*) FROM Arbeiterin WHERE arbeitetInStockNr = StockNr AND arbeitetInTyp = Typ ) > 30 GROUP BY Typ ORDER BY AVG(Honigertrag) DESC;
SELECT Typ, AVG(Honigertrag) FROM Bienenstock NATURAL JOIN (SELECT arbeitetinStockNr as stockNr, arbeitetintyp as typ, count(kennzahl) as beeCount FROM Arbeiterin GROUP BY arbeitetinstocknr, arbeitetinTyp) as numbBees WHERE beeCount > 30 GROUP BY Typ ORDER BY AVG(Honigertrag) DESC;
SELECT Typ, AVG(Honigertrag) FROM Bienenstock NATURAL JOIN (SELECT arbeitetinStockNr as stockNr, arbeitetintyp as typ, count(kennzahl) as beeCount FROM Arbeiterin GROUP BY arbeitetinstocknr, arbeitetinTyp HAVING count(kennzahl) > 30) as numbBees GROUP BY Typ ORDER BY AVG(Honigertrag) DESC;
3)
Geben Sie den Namen und das Geburtsdatum aller Imker und Imkerinnen aus, die keine Hilfsarbeitende haben und für keine Bienenstöcke vom Typ "Magazin" zuständig sind. Ordnen Sie die Ergebnisse absteigend nach dem Geburtsdatum. <br/><br/> English:<br/> List the names ('Name') and the date of birth ('Geburtsdatum', attribute: 'geborenam') of all beekeepers ('Imker') that have no unskilled laborers ('Hilfsarbeiter') working for them and that are not responsible ('zuständing') for any beehives ('Bienenstöcke') of type "Magazin". Sort the list descending by the date of birth.
SELECT Name, GeborenAm FROM Imker WHERE (ImkerNr) NOT IN (SELECT stelltAn FROM Hilfsarbeiter) AND (ImkerNr) NOT IN (SELECT zustaendigFuer FROM Bienenstock WHERE Typ = 'Magazin') ORDER BY GeborenAm DESC;
SELECT Name, GeborenAm FROM Imker WHERE NOT EXISTS (SELECT * FROM Hilfsarbeiter WHERE stelltAn = ImkerNr) AND (ImkerNr) NOT IN (SELECT zustaendigFuer FROM Bienenstock WHERE Typ = 'Magazin') ORDER BY GeborenAm DESC;
SELECT Name, GeborenAm FROM Imker WHERE (ImkerNr) NOT IN (SELECT stelltAn FROM Hilfsarbeiter WHERE stelltAn = ImkerNr) AND (ImkerNr) NOT IN (SELECT zustaendigFuer FROM Bienenstock WHERE Typ = 'Magazin') ORDER BY GeborenAm DESC;
4)
Geben Sie die Imkernummer und Namen des Imkers bzw. der Imkerin an, der bzw. die durchschnittlich am meisten für angestellte Hilfsarbeitende zahlt. <br/><br/> English:<br/> List the number ('Imkernummer') and name ('Name') of all beekeepers that, on average, pay their employed ('angestellt') unskilled laborers best.
SELECT imkernr, imker.name FROM imker JOIN hilfsarbeiter ON (imkernr=stelltan) GROUP BY imkernr, imker.name HAVING AVG(lohn) >= all (SELECT AVG(lohn) FROM imker JOIN hilfsarbeiter ON (imkernr=stelltan) GROUP BY imkernr, imker.name);
SELECT imkernr, imker.name FROM imker JOIN hilfsarbeiter ON (imkernr=stelltan) GROUP BY imkernr, imker.name HAVING AVG(lohn) >= (SELECT max(l) FROM (SELECT AVG(lohn) AS l FROM imker JOIN hilfsarbeiter ON (imkernr=stelltan) GROUP BY imkernr, imker.name) as tmp);
SELECT ImkerNr,Name FROM Imker WHERE ImkerNr IN (SELECT stelltAn FROM Hilfsarbeiter GROUP BY stelltAn HAVING AVG(Lohn) >= all (SELECT AVG(Lohn) FROM Hilfsarbeiter GROUP BY stelltAn))
5)
Geben Sie die Namen und das Geburtsdatum der Imker und Imkerinnen aus, deren Bienen alle Felder bestäuben. <br/><br/> English:<br/> List the names ('Name') and the date of birth ('Geburtsdatum'; attribute: 'geborenam') of those beekeepers whose bees pollinate ('bestaeuben') all fields ('Felder').
SELECT Name,GeborenAm FROM Imker WHERE NOT EXISTS( SELECT * FROM Feld WHERE (Feldkennzahl,Ort) NOT IN (SELECT Feldkennzahl, Ort FROM bestaeubt NATURAL JOIN Arbeiterin JOIN Bienenstock ON arbeitetInStockNr = StockNr AND arbeitetInTyp = Typ WHERE zustaendigFuer = ImkerNr ))
SELECT name, geborenam FROM Imker i WHERE NOT EXISTS (SELECT * FROM Feld f WHERE NOT EXISTS (SELECT * FROM (bestaeubt NATURAL JOIN arbeiterin) a JOIN bienenstock b ON (a.arbeitetintyp = b.typ AND a.arbeitetinstocknr = b.stocknr) WHERE a.ort=f.ort AND a.feldkennzahl=f.feldkennzahl AND b.zustaendigfuer = i.imkernr));
SELECT name, geborenam FROM Imker i1, (SELECT tmp.imkernr, COUNT(*) as numb FROM ( SELECT DISTINCT i.imkernr, Feldkennzahl, Ort FROM imker i, bienenstock b, arbeiterin a, bestaeubt best WHERE i.imkernr = b.zustaendigfuer AND b.typ = a.arbeitetintyp AND b. stocknr = a.arbeitetinstocknr AND a.kennzahl = best.kennzahl ) as tmp GROUP By imkernr ) as fc WHERE i1.imkernr = fc.imkernr AND numb = (SELECT COUNT(*) FROM Feld);
6)
Geben Sie die Namen alle Imkerei-Meister und Meisterinnen und die Namen all derer Lehrlinge aus, die selber auch einen Lehrling gelehrt haben. Falls keine passenden Lehrlinge existieren, soll stattdessen in der Spalte für Lehrling "kein Lehrling vorhanden" ausgegeben werden. Dies gilt natürlich auch für Imker und Imkerinnen, die überhaupt keine Lehrlinge gehabt haben. Ordnen Sie die Ergebnisse aufsteigend nach den Namen der Meister und Meisterinnen. <br/><br/> English:<br/> List the names ('Name') of all beekeepers together with the names of all their trainees ('Lehrlinge') who already taught a trainee themselves (the relation 'gelerntVon(meister, lehrling)' means that the 'lehrling' learned from the 'meister'). If for some beekeeper no such trainee (or no trainee at all) exists, provide an entry in the list where instead of a name the column for the trainee contains the string "kein Lehrling vorhanden". Sort the list ascending by the name of the masters.
SELECT Name, COALESCE(Lehrlingsname, 'kein Lehrling vorhanden') FROM Imker LEFT JOIN ( SELECT Meister,Lehrling,Name as Lehrlingsname FROM gelerntVon JOIN Imker ON Lehrling = ImkerNr WHERE Lehrling IN (SELECT Meister From gelerntVon)) as tmp ON ImkerNr = Meister ORDER BY Name;
SELECT i.name, COALESCE(g1.name, 'kein Lehrling vorhanden') FROM Imker i LEFT OUTER JOIN (gelerntvon JOIN Imker ON (gelerntvon.lehrling=Imker.imkernr)) g1 JOIN gelerntvon g2 ON (g1.lehrling = g2.meister) ON (i.imkernr=g1.meister) ORDER BY i.name ASC
7)
Geben Sie die Namen aller Imker und ihrer Bienenstock Typen und Stocknummern aus, falls diese Bienenstöcke einen Honigertrag von 300kg oder mehr haben und mindestens 3 Brutnester besitzen. <br/><br/> English:<br/> List the names ('Name') of all beekeepers together with the types ('Typen') and numbers ('Nummern') of all their beehives ('Bienenstoecke') that give at least 300kg of honey ('Honigertrag') and that contain at least 3 brood nests ('Brutnester').
SELECT Name, Typ, StockNr FROM Imker JOIN Bienenstock ON zustaendigFuer = ImkerNr WHERE Honigertrag >= 300 AND (SELECT COUNT(*) FROM Brutnest WHERE liegtInTyp = Typ AND liegtInStockNr = StockNr) >= 3
SELECT name, typ, stocknr FROM imker JOIN bienenstock ON (imkernr = zustaendigfuer) JOIN brutnest on stocknr=liegtinstocknr AND typ = liegtintyp WHERE honigertrag >= 300 GROUP BY imkernr, name, typ, stocknr HAVING count (DISTINCT nestnr) >= 3;
8)
Geben Sie für jedes Feld, gekennzeichnet durch die Feldkennzahl und den Ort, aus von wie vielen Landwirtschaftsbetrieb es verwendet wird, welchen Flächenanteil ein Betrieb im Durchschnitt verwendet und was durchschnittlich für die Bestäubung dieses Feldes an Imker gezahlt wird. Auch soll für jedes Feld angegeben werden wie viele Arbeiterinnen es bestäuben. <br/><br/> English:<br/> For each field ('Feld'), marked by its number ('Feldkennzahl') and the place ('Ort'), list the number of farms ('Landwirtschaftsbetriebe') using it, the average share of the field ('Flaechenanteil') used by a farm, and how much, in average, is paid to beekeepers for pollinating the field. In addition, also state for each field the number of worker bees ('Arbeiterinnen') pollinating this field.
SELECT Feldkennzahl, Ort, (SELECT COUNT(*) FROM verwendetVon AS v WHERE f.Feldkennzahl = v.Feldkennzahl AND v.Ort = f.Ort ) as AnzahlBetriebe, (SELECT AVG(Flaechenanteil) FROM verwendetVon AS v WHERE f.Feldkennzahl = v.Feldkennzahl AND v.Ort = f.Ort ) as Durchschnittsverwendung, (SELECT AVG(Betrag) FROM bezahltFuer AS v WHERE v.Feldkennzahl = f.Feldkennzahl AND f.Ort = v.Ort) as Durschnittsbezahlung, (SELECT COUNT(*) FROM bestaeubt AS v WHERE v.Feldkennzahl = f.Feldkennzahl AND f.Ort = v.Ort) as AnzahlBienen FROM Feld as f
9)
Geben Sie den Namen eines Imkers bzw. einer Imkerin, einen Ortsnamen und einen Betrag aus, die folgende Bedingung erfüllen: Der Imker oder die Imkerin muss für alle Felder in diesem Ort als Summe genannten Betrag bezahlt bekommen haben und dieser muss höher sein als den Betrag den jeder andere Imker oder jede andere Imkerin in diesem Ort bekommen hat ("Betrag" ist hier verstanden als die Summe, die von allen Landwirtschaftsbetriebe die Felder dieses Ortes verwenden, bezahlt wird). Ordnen Sie die Ergebnisse absteigend nach dem Betrag. <br/><br/> English:<br/> For each place ('Ort'), list the beekeepers ('Imker') who (at this place) were paid the most for pollinating ('bestaeuben') of fields ('Felder'). Provide the name of the beekeeper, the name of the place ('Ortsname'), and the amount ('Betrag') the beekeeper was paid.
SELECT Name, Ort, SUM(Betrag) FROM (Imker NATURAL JOIN bezahltFuer) AS t GROUP BY ImkerNr,Name, Ort HAVING SUM(Betrag) >=all ( SELECT SUM(Betrag) FROM (Imker NATURAL JOIN bezahltFuer) AS tt WHERE t.Ort = tt.Ort GROUP BY ImkerNr,Name, Ort ) ORDER BY SUM(Betrag) DESC
SELECT i.name, b.ort, SUM(betrag) as betrag FROM imker i JOIN bezahltFuer b ON (i.imkernr = b.imkernr) GROUP BY i.imkernr, i.name, b.ort HAVING SUM(Betrag) >= all (SELECT SUM(betrag) as betrag FROM imker i1 JOIN bezahltFuer b1 ON (i1.imkernr = b1.imkernr) WHERE b1.ort=b.ort GROUP BY i1.imkernr, i1.name, b1.ort) ORDER BY betrag DESC;
SELECT a.name, a.ort, a.betrag FROM (SELECT i.name, b.ort, SUM(betrag) as betrag FROM imker i JOIN bezahltFuer b ON (i.imkernr = b.imkernr) GROUP BY i.imkernr, i.name, b.ort) a JOIN (SELECT ort, MAX(betrag) as maxi FROM (SELECT i1.imkernr, b1.ort, SUM(betrag) as betrag FROM imker i1 JOIN bezahltFuer b1 ON (i1.imkernr = b1.imkernr) GROUP BY i1.imkernr, i1.name, b1.ort) o GROUP BY ort) s ON (a.ort=s.ort AND a.betrag=s.maxi) ORDER BY a.betrag DESC
SELECT a.name, a.ort, a.betrag FROM (SELECT i.name, b.ort, SUM(betrag) as betrag FROM imker i JOIN bezahltFuer b ON (i.imkernr = b.imkernr) GROUP BY i.imkernr, i.name, b.ort) a JOIN (SELECT ort, MAX(betrag) as maxi FROM (SELECT i1.name, b1.ort, SUM(betrag) as betrag FROM imker i1 JOIN bezahltFuer b1 ON (i1.imkernr = b1.imkernr) GROUP BY i1.imkernr, i1.name, b1.ort) o GROUP BY ort) s ON (a.ort=s.ort AND a.betrag=s.maxi) ORDER BY a.betrag DESC
10)
Geben Sie alle Bienenstöcke (StockNr und Typ) aus, deren Arbeiterinnen weniger als 10 Felder bestäuben und die maximal 2 Brutnester besitzen, deren Honigertrag aber durchschnittlich oder besser (im Vergleich zu allen Bienenstöcken) ist. Ordnen Sie die aufsteigend Ergebnisse nach der Stocknummer. <br/><br/> English:<br/> List all beehives ('Bienenstoecke') -- provide the number ('StockNr') and type ('Type') -- whose worker bees ('Arbeiterinnen') pollinated ('bestaeuben') less than 10 field ('Felder') and that contain at most 2 brood nests ('Brutnester'), but where the amoung of honey harvested ('Hoingertrag') is not worse than the average amount of harvest (compared to all beehives). Sort the list ascending by the number of the beehives ('Stocknummer').
SELECT stocknr, typ FROM bienenstock NATURAL JOIN (SELECT typ, stocknr, COUNT(*) as fs FROM (SELECT DISTINCT arbeitetintyp as typ, arbeitetinstocknr as stocknr, feldkennzahl, ort FROM Feld NATURAL JOIN bestaeubt NATURAL JOIN arbeiterin) as tmp GROUP BY typ, stocknr) as feldProStock NATURAL JOIN (SELECT liegtintyp as typ, liegtinstocknr as stocknr, COUNT(*) as bn FROM brutnest GROUP BY liegtintyp, liegtinstocknr) as BrutnestProStock WHERE fs < 10 AND bn <= 2 AND honigertrag >= (SELECT AVG(honigertrag) FROM bienenstock);
SELECT StockNr, Typ FROM Bienenstock WHERE (SELECT COUNT(*) OVER () FROM Arbeiterin NATURAL JOIN bestaeubt WHERE arbeitetInTyp = Typ AND arbeitetInStockNr = StockNr GROUP BY Feldkennzahl, Ort LIMIT 1) < 10 AND (SELECT COUNT (*) FROM Brutnest WHERE liegtInTyp = Typ AND liegtInStockNr = StockNr) <=2 AND Honigertrag >=(SELECT AVG(Honigertrag) FROM Bienenstock);
select distinct b.stocknr as stocknr, b.typ as typ from bienenstock b natural join (select liegtinstocknr as stocknr, liegtintyp as typ, count(*) as bnc from brutnest bn group by bn.liegtintyp, bn.liegtinstocknr having count(*) <=2) tmp natural join (select arbeitetintyp as typ, arbeitetinstocknr as stocknr, count(distinct feldkennzahl) as feldc from arbeiterin natural join bestaeubt natural join feld group by arbeitetintyp, arbeitetinstocknr having count(distinct feldkennzahl) < 10) tmp2 where honigertrag >=(select avg(honigertrag) from bienenstock) order by stocknr asc
11)
Geben Sie für jede Gattung Biene an, wie viele Arbeiterinnen und Königinnen vorhanden sind, und wie viele Felder von der Arbeiterinnen der jeweiligen Gattung durchschnittlich bestäubt werden, zusätzlich dazu auch die minimale und maximal Anzahl Felder die pro Biene der jeweiligen Gattung bestäubt werden.
SELECT Gattung, (SELECT COUNT(*) FROM Arbeiterin WHERE Gattung=Biene.gattung) as anzArb, (SELECT COUNT(*) FROM Koenigin WHERE Gattung=Biene.gattung) as anzQueens, (SELECT AVG(fc) FROM (SELECT COUNT(*) as fc FROM bestaeubt NATURAL JOIN Arbeiterin WHERE Gattung=Biene.Gattung GROUP BY Kennzahl) as h1) as avgFeld, (SELECT MIN(fc) FROM (SELECT COUNT(*) as fc FROM bestaeubt NATURAL JOIN Arbeiterin WHERE Gattung=Biene.Gattung GROUP BY Kennzahl) as h2) as minFeld, (SELECT MAX(fc) FROM (SELECT COUNT(*) as fc FROM bestaeubt NATURAL JOIN Arbeiterin WHERE Gattung=Biene.Gattung GROUP BY Kennzahl) as h3) as maxFeld FROM BIENE GROUP BY GATTUNG;
SELECT gattung, arbeitcount, queencount, maxf, minf, avgf FROM (SELECT Gattung, count(*) AS arbeitcount FROM Arbeiterin GROUP BY Gattung) as t1 NATURAL JOIN (SELECT Gattung, count(*) AS queencount FROM Koenigin GROUP BY Gattung) as t2 NATURAL JOIN (SELECT gattung, MAX(fc) maxf, MIN(fc) minf, AVG(fc) avgf FROM (SELECT Gattung, Biene.kennzahl, COUNT(*) as fc FROM bestaeubt RIGHT OUTER JOIN Biene ON (Biene.kennzahl=bestaeubt.kennzahl) GROUP BY Biene.Kennzahl, Biene.Gattung) as fcT NATURAL JOIN Arbeiterin GROUP BY Gattung) as MinMaxArbeiter;
12)
Geben Sie die Kennzahl und den Ort aller Felder aus, die von allen Landwirtschaftsbetrieben verwendet werden. (In anderen Worten, es soll keinen Landwirtschaftsbetrieb geben, der von diesen Feldern nicht einen gewissen Anteil verwendet) Ordnen Sie das Ergebnis aufsteigend nach der Kennzahl.
SELECT feldkennzahl, ort FROM Feld f WHERE NOT EXISTS (SELECT * FROM Landwirtschaftsbetrieb l WHERE NOT EXISTS (SELECT * FROM verwendetVon v WHERE v.feldkennzahl=f.feldkennzahl AND v.ort = f.ort AND v.betriebsform=l.betriebsform AND v.betriebsnr = l.betriebsnr));
SELECT Feldkennzahl,Ort FROM Feld as f WHERE NOT EXISTS( SELECT Betriebsform, BetriebsNr FROM Landwirtschaftsbetrieb WHERE (Betriebsform,BetriebsNr) NOT IN( SELECT Betriebsform, BetriebsNr FROM verwendetVon as v WHERE v.Feldkennzahl = f.Feldkennzahl AND v.Ort = f.Ort))
13)
Geben Sie die Kennzahl und Gattung aller Arbeiterinnen an, die entweder in Bienenstöcken mit mehr als 60 Bienen (inklusive Königin!) arbeiten, und/oder die in Bienenstöcken mit 5 oder mehr Brutnestern arbeiten. Es genügt wenn jeweils eine Bedingung erfüllt ist.
(SELECT Kennzahl, Gattung FROM Arbeiterin JOIN Bienenstock ON arbeitetInTyp = Typ AND arbeitetInStockNr = StockNr WHERE (SELECT COUNT(*) FROM Arbeiterin WHERE arbeitetInStockNr = StockNr AND arbeitetInTyp = Typ ) +(SELECT COUNT(*) FROM Koenigin WHERE lebtInStockNr = StockNr AND lebtInTyp = Typ )> 60 ) UNION (SELECT Kennzahl, Gattung FROM Arbeiterin JOIN Bienenstock ON arbeitetInTyp = Typ AND arbeitetInStockNr = StockNr WHERE (SELECT COUNT(*) FROM Brutnest WHERE liegtInStockNr = StockNr AND liegtInTyp = Typ ) >= 5 ) ORDER BY kennzahl ASC;
SELECT Kennzahl, Gattung FROM Arbeiterin a WHERE (SELECT count(*) FROM arbeiterin a2 WHERE a2.arbeitetintyp=a.arbeitetintyp AND a2.arbeitetinstocknr=a.arbeitetinstocknr) + (SELECT count(*) FROM Koenigin k WHERE k.lebtinstocknr=a.arbeitetinstocknr AND k.lebtintyp=a.arbeitetintyp) > 60 OR (SELECT count(*) FROM Brutnest b WHERE b.liegtintyp=a.arbeitetintyp AND b.liegtinstocknr=a.arbeitetinstocknr) >= 5 ORDER BY kennzahl ASC;
14)
Geben Sie neben dem Namen aller Imker und Imkerinnen auch den Betrag aus, den sie von Landwirtschaftsbetrieben in Summe ausgezahlt bekommen. Daneben soll auch eine Spalte “Bewertung” ausgegeben werden, die wie folgt definiert ist: • Falls in Summe mehr als 2000 verdient wird, soll hier “hoher Ertrag” stehen • Falls in Summe zwischen 2000 und 1000 verdient wird, soll “mäßiger Ertrag” ausgeben werden. • Und für Summen unter 1000 soll “geringer Ertrag” ausgeben werden.
SELECT Name, CASE WHEN SUM(Betrag) IS NULL THEN 0 ELSE SUM(Betrag) END AS Betrag, CASE WHEN SUM(Betrag) > 2000 Then 'hoher Ertrag' WHEN SUM(Betrag) >= 1000 THEN 'mäßiger Ertrag' ELSE 'geringer Ertrag' END as Bewertung FROM Imker as i LEFT JOIN bezahltFuer as b ON b.ImkerNr = i.ImkerNr GROUP BY i.ImkerNr,Name
SELECT name, COALESCE(SUM(Betrag),0) as summe, CASE WHEN SUM(Betrag) > 2000 THEN 'hoher Ertrag' WHEN SUM(Betrag) >=1000 THEN 'mäßiger Ertrag' ELSE 'geringer Ertrag' END as Bewertung FROM Imker LEFT OUTER JOIN bezahltfuer ON (Imker.imkernr = bezahltFuer.imkernr) GROUP BY Imker.name, Imker.imkernr;
15)
Geben Sie die Imkernummer und den Namen des ältesten Imkers bzw. der ältesten Imkerin aus, der oder die für zumindest einen Bienenstock mit einem Brustnest mit einer Größe über 100 cm<up>3</up> zuständig ist und allen Hilfsarbeitenden einen ungeraden Lohn auszahlt.
SELECT ImkerNr, Name FROM Imker as i1 WHERE ImkerNr IN (SELECT zustaendigFuer FROM Bienenstock JOIN Brutnest ON liegtInTyp = Typ AND liegtInStockNr = StockNr WHERE Groesse>=100) AND NOT EXISTS (SELECT * FROM Hilfsarbeiter WHERE stelltAn = ImkerNr AND ((Lohn) / 2) *2 = Lohn ) AND NOT EXISTS (SELECT ImkerNr, Name FROM Imker as i2 WHERE ImkerNr IN (SELECT zustaendigFuer FROM Bienenstock JOIN Brutnest ON liegtInTyp = Typ AND liegtInStockNr = StockNr WHERE Groesse >=100) AND NOT EXISTS (SELECT * FROM Hilfsarbeiter WHERE stelltAn = ImkerNr AND ((Lohn) / 2) *2 = Lohn ) AND i2.GeborenAm < i1.GeborenAm )
SELECT imkernr, name FROM Imker i WHERE NOT EXISTS (SELECT * FROM Hilfsarbeiter h WHERE i.imkernr = h.stelltan AND h.lohn%2 = 0) AND EXISTS (SELECT * FROM Brutnest bn JOIN Bienenstock bs ON (bn.liegtintyp=bs.typ AND bn.liegtinstocknr=bs.stocknr) WHERE bs.zustaendigfuer = i.imkernr AND groesse > 100) AND geborenam <= all (SELECT geborenam FROM Imker i1 WHERE NOT EXISTS (SELECT * FROM Hilfsarbeiter h1 WHERE i1.imkernr = h1.stelltan AND h1.lohn%2 = 0) AND EXISTS (SELECT * FROM Brutnest bn1 JOIN Bienenstock bs1 ON (bn1.liegtintyp=bs1.typ AND bn1.liegtinstocknr=bs1.stocknr) WHERE bs1.zustaendigfuer = i1.imkernr AND groesse > 100));