4. KOMBINIERTE ABFRAGEN
• Gib alle Dezemberhefte aus (Nummer, Titel, Monat)
select nummer,titel,monat from mosaik1
where monat <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 12
• Gib alle Dezemberhefte der Heftnummern 20 bis 80 aus (Nummer, Titel, Monat)
select nummer,titel,monat from mosaik1
where monat <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 12 and nummer <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 20 <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect nummer <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 80
• Gib alle Dezemberhefte der Jahre 1960 bis 1970 aus (Jahr, Monat, Nummer)
select jahr,monat,nummer from mosaik1
where monat <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 12 and jahr <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 1960 and jahr <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 1970
5. AGGREGATFUNKTIONEN
• Letztes Heft
select titel,<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(jahr) from mosaik1
• Erstes Heft
select titel,<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(jahr) from mosaik1
• Durchschnittsjahr
select <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(jahr) from mosaik1
• Durchschnittsjahr gerundet auf 1 Stelle
select <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(jahr),1) from mosaik1
• Anzahl der Hefte (Heftnummern zählen)
select <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(nummer) from mosaik1
• Anzahl der Dezemberhefte
select <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) from mosaik1
where monat = <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect
• Anzahl der Dezemberhefte und Januarhefte
select <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) from mosaik1
where monat = <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect monat = <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect
VERSCHACHTELTE SQL-ANWEISUNGEN:
• Titel und Monat aller Dezemberhefte (Hinweis: Maximalwert der Monatszahlen)
select titel,monat from mosaik1
where monat = (<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) from mosaik1)
• Titel und Monat aller Novemberhefte
select titel,monat from mosaik1
where monat = (<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) -<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect from mosaik1)
• Titel und Monat aller Januarhefte und Märzhefte (Hinweis: Minimalwert der Monatszahlen...)
select titel,monat from mosaik1
where monat = (<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) from mosaik1)
<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect monat = (<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) +<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect from mosaik1)
6. GRUPPIEREN
• Anzahl der Hefte pro Jahr
select <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect(monat) as Anzahl, <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect from mosaik1
<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect
• Anzahl der Hefte pro Monat (Wieviel Januarhefte, Februarhefte, ...)
select count(nummer) <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect Anzahl, monat from mosaik1
<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect
• Gruppierung und Filterung nach den Heften der 50-er Jahre und der 70-er Jahre
select count(monat),jahr from mosaik1
<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect
having jahr <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 1949 <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 1960
<=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect jahr <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 1969 <=>==1122andasavgbetweenbycountgroupjahrmaxminmonatorroundselect 1980