SQL-Übungen

einfache Listen

einfache Patientenliste, Name mit der Funktion SUBSTR() auf 10 Zeichen gekürzt, nur welche mit Geburtsdatum, aufsteigend nach Geburtsdatum geordnet

select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum
from PATIENT
where Geburtsdatum IS NOT NULL
order by Geburtsdatum
/

Die gleiche Liste, ergänzt um das Alter der Patienten. Die Funktion MONTHS_BETWEEN( Endedatum, Anfangsdatum ) berechnet die Differenz zwischen zwei Datumswerten in Monaten. Das Ergebnis geteilt durch 12 ist dann das Alter in Jahren. Anfangsdatum bei der Berechnung des Alters ist selbstverständlich das Geburtsdatum. Ist das Sterbedatum bekannt, ist dies das Endedatum für die Altersberechnung. Ist es das nicht, wird angenommen, daß der Patient noch lebt und es wird das aktuelle Datum verwendet. SYSDATE liefert das aktuelle Datum, mit Uhrzeit. Die Funktion NVL(Wert,Ersatz) gibt den Wert aus, wenn einer da ist, sonst den Ersatz. Hier kommt also das Sterbedatum heraus wenn bekannt, sonst das aktuelle Datum.

select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum,
       MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12 WieAlt
from PATIENT
where Geburtsdatum IS NOT NULL
order by Geburtsdatum
/

Liste ergänzt um die Funktion TRUNC() um den Ausdruck zur Altersberechnung herum. Diese schneidet die Nachkommastellen ab und es bleiben nur ganze Lebensjahre übrig.

select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum,
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt
from PATIENT
where Geburtsdatum IS NOT NULL
order by Geburtsdatum
/

Ordnung der Liste geändert, es wird jetzt das berechnete Alter benutzt. Im SQL könnte auch stehen order by TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12). Dieser Ausdruck ist der fünfte der Spaltenliste hinter SELECT, und in der order by - Klausel kann in vielen Fällen einfach die Position verwendet werden, hier als order by 5 .

select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum,
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt
from PATIENT
where Geburtsdatum IS NOT NULL
order by 5
/

Auswertung mit Summen- und Gruppenfunktionen

Die folgende Liste zät, mit welcher Anzahl ein bestimmtes Alter in der o.g. Patientenliste vorkommt. Dazu wird nach dem Auswertung, der das Alter berechnet, gruppiert ( group by). In den anderen Spalten des Ergebnisses können jetzt keine Aussagen mehr zu einzelnen Teilen stehen, sondern nur noch die Ergebnisse von Summen- und Gruppenfunktionen.

select MIN(Pat_ID),MIN( Geburtsdatum ),MIN(Sterbedatum),
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt,
       COUNT(*) Anzahl
from PATIENT
where Geburtsdatum IS NOT NULL
group by TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12)
order by TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12)
/

Der Versuch , gleichzeitig einzelne Zeilen darzustellen, funktioniert nicht :


select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum,
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt,
       COUNT(*)
from PATIENT
where Geburtsdatum IS NOT NULL
group by TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12)
/

Alle Bestandteile der SELECT - Liste, die keine Summen- oder Gruppenfunktion sind, müssen in die group by Klausel !

select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum,
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt,
       COUNT(*)
from PATIENT
where Geburtsdatum IS NOT NULL
group by Pat_ID,SUBSTR(Name,1,10),Geburtsdatum,Sterbedatum,  
         TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12)
/

Dieses Statement funktioniert, da jedoch jetzt auch nach den verschiedenen Feldern gruppiert wird, die zum einzelnen Patienten gehören, gibt es keine Zahlen mehr für die gesamt Altergsgruppe. Deutlicher wird das noch, wenn wir nach der Altersgruppe ordnen :

select Pat_ID,SUBSTR(Name,1,10) Name,Geburtsdatum,Sterbedatum,
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt,
       COUNT(*)
from PATIENT
where Geburtsdatum IS NOT NULL
group by Pat_ID,SUBSTR(Name,1,10),Geburtsdatum,Sterbedatum,
         TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12)
order by TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12)
/

Kombinierte Listen : Einzelne Zeilen plus Summenfunktionen

sind möglich auf zwei Weisen :

Liste mit einzelnen Zeilen, zusätzlich Verwendung der SQLPLUS - Anweisungen BREAK und COMPUTE
bei reinem SQL gibt es gewisse Möglichkeiten mit den Funktionen ROLLUP() und CUBE() innerhalb von group by

Beispiel für ROLLUP() :

 
select Pat_ID||' '||SUBSTR(Name,1,10)||' '||Geburtsdatum||' '||Sterbedatum,
       TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12) WieAlt,
       COUNT(*)
from PATIENT
where Geburtsdatum IS NOT NULL
group by ROLLUP( TRUNC( MONTHS_BETWEEN( NVL(Sterbedatum,SysDate), Geburtsdatum ) / 12),
                 Pat_ID||' '||SUBSTR(Name,1,10)||' '||Geburtsdatum||' '||Sterbedatum  )   
/