SQL 2 Subselects
Zeichenkettenfunktionen[edit]
Wichtige Zeichenkettenfunktionen[edit]
Funktion | Beispiel | Ergebnis |
---|---|---|
DECODE
|
DECODE(GRADE,'A',4,'B',3,'C',2,'D',1,0
|
Übersetzt die Char-Werte von GRADE in numerische Werte
|
INITCAP
|
INITCAP(ENAME)
|
Erster Buchstabe von ENAME groß
|
INSTR
|
INSTR(LOC,' ')
|
Ermittelt die Position des ersten Blanks in LOC
|
LENGTH
|
LENGTH(ENAME
|
Anzahl von Zeichen in ENAME
|
LOWER
|
LOWER(ENAME)
|
ENAME in Kleinbuchstaben
|
SOUNDEX
|
SOUNDEX('SMYTH')
|
Ermittelt einen Wert, der wie 'SMYTH' klingt
|
SUBSTR
|
SUBSTR(ENAME,1,2)
|
Ergibt 2 Zeichen von ENAME beginnend beim ersten Zeichen
|
UPPER
|
UPPER(ENAME)
|
ENAME in Großbuchstaben
|
Beispiele[edit]
Beispiel 1[edit]
SELECT INITCAP(ENAME) FROM EMP WHERE UPPER(ENAME) = ’WARD‘;
Beispiel 2[edit]
SELECT ENAME,JOB,DECODE(JOB,‘CLERK‘,1,‘MANAGER’,3,’PRESIDENT‘,5,2) JOB_CLASS FROM EMP;
ENAME | JOB | JOB_CLASS |
---|---|---|
SMITH | CLERK | 1 |
ALLEN | SALESMAN | 2 |
WARD | SALESMAN | 2 |
JONES | MANAGER | 3 |
MARTIN | SALESMAN | 2 |
BLAKE | MANAGER | 3 |
CLARK | MANAGER | 3 |
SCOTT | ANALYST | 2 |
KING | PRESIDENT | 5 |
TURNER | SALESMAN | 2 |
ADAMS | CLERK | 1 |
JAMES | CLERK | 1 |
FORD | ANALYST | 2 |
MILLER | CLERK | 1 |
Arbeiten mit Datum und Uhrzeit[edit]
Datumswerte[edit]
Mit der Funktion TO_CHAR(DATEVARIABLE,'MM/DD/YY')
kann man sich ein Datum im gewünschten Datumsformat ausgeben lassen.
SELECT ENAME, TO_CHAR(HIREDATE,‘MM/DD/YY‘) HIRE_DATE FROM EMP WHERE DEPTNO = 20;
Liste der Datumsformate[edit]
Element | Bedeutung |
---|---|
YYY, YY oder Y
|
Letzten 3,2 oder 1 Zahl(en) des Jahres |
SYEAR oder YEAR
|
Jahr ausgeschrieben; S ersetzt BC durch -
|
Q
|
Quartal des Jahres |
MM
|
Monat |
MONTH oder MON
|
Name des Monats oder Abkürzung mit 3 Zeichen |
DDD, DD oder D
|
Tag des Jahres, des Monats oder der Woche |
DAY oder DY
|
Name des Tages oder Abkürzung mit 3 Zeichen |
AM oder PM
|
Anzeige von AM oder PM
|
A.M. oder P.M.
|
Anzeige von AM oder PM mit Perioden
|
HH oder HH12
|
Stunde im 12 Stunden Format |
HH24
|
Stunde im 24 Stunden Format |
MI
|
Minuten |
SS
|
Sekunde |
Beispiele[edit]
Format | Ergebnis |
---|---|
ohne Formatangabe
|
12-JAN-86
|
MM/DD/YY
|
01/12/86
|
DD.MM.YYYY
|
12.01.86
|
Month DD,YYYY
|
January 12,1986
|
DY DD MON YY
|
WED 12 JAN 86
|
Day Mon DD
|
Wednesday Jan 12
|
DY "the" ddth "of" Month YYYY
|
WED the 12th of January 1986
|
MONTH DD, YYYY HH:MI PM
|
JANUARY 12, 1986 05:30 PM
|
Für jeden Mitarbeiter das Datum der 1-jährigen Firmenzugehörigkeit.
SELECT ENAME,HIREDATE,HIREDATE+365 ONEYEAR FROM EMP;
Besser:
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,12) ONEYEAR FROM EMP;
Datumsfunktionen[edit]
Funktion | Beispiel | Ergebnis |
---|---|---|
ADD_MONTHS
|
ADD_MONTHS(D,N)
|
Datum D + N Monate
|
GREATEST
|
GREATEST(D1,D2)
|
Späteres Datum von D1 und D2
|
LEAST
|
LEAST(D1,D2)
|
Früheres Datum von D1 und D2
|
LAST_DAY
|
LAST_DAY(D)
|
Letzter Tag des Monats aus D
|
MONTHS_BETWEEN
|
MONTHS_BETWEEEN(D1,D2)
|
Anzahl der Monate zwischen D1 und D2
|
NEXT_DAY
|
NEXT_DAY(D,'FRIDAY')
|
Datum des ersten Freitags nach D
|
ROUND
|
ROUND(D)
|
Rundet D auf einen ganzen Tag
|
TO_CHAR
|
TO_CHAR(D,'MM/DD/YY'
|
Umwandlung des Datumswertes D in Char Wert, wobei das 2. Argument das Format angibt.Fehlt das 2. Argument, wird das Default Format verwendet. |
TO_DATE
|
TO_DATE(CHARDATE,'MM/DD/YY')
|
Umwandlung eines Char Wertes in einen Datumswert, wobei der Char Wert entsprechend dem angegebenen Format interpretiert wird. Fehlt das 2. Argument, wird das Default Format verwendet. |
Standardeinstellungen[edit]
Die Standardeinstellung kann geändert werden mit:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS‘;
SELECT TO_CHAR(SYSDATE) Today FROM DUAL;
Ergebnis:
TODAY ------------------- 1997 08 12 14:25:56
Änderung des Datumsformats 🡪 Französisch
ALTER SESSION SET NLS_DATE_LANGUAGE = French;
Ergebnis:
Mardi 28 Février 1997
Datum und Uhrzeit[edit]
Wird ein Datum von einem anderen abgezogen, so ist das Ergebnis eine Kommazahl, falls die Tageszeit der beiden Werte unterschiedlich ist.
Datum1: 3-JAN-87 11:00 AM Datum2: 5-JAN-87 05:00 PM Datum2 - Datum1: 2.25 (2 Tage und 6 Stunden)
Datum1: 3-Jan-87 05:00 PM Datum2: 5-JAN-87 11:00 AM Datum2-Datum1: 1.75 (1 Tag und 18 Stunden)
Pseudocolumn SYSDATE[edit]
SYSDATE
liefert das aktuelle Datum. Da SYSDATE
keine aktuelle Spalte in
einer Tabelle ist, werden solche Spalten auch als Pseudo – Columns
bezeichnet.
Ausgabe des aktuellen Datums:
SELECT SYSDATE FROM EMP WHERE ENAME = 'SMITH';
Warum einschränkende WHERE
Bedingung? Braucht man eigentlich nicht.
SELECT TO_CHAR(SYSDATE,‘DD-MON-YY HH:MM PM‘) TODAY FROM EMP WHERE ENAME = ‘SMITH‘;
Besser: Verwendung der Tabelle DUAL
SELECT SYSDATE FROM DUAL;
EXTRACT[edit]
Entnimmt aus einem datetime Feld einen spezifizierten Teilstring
EXTRACT ((year | month | day | hour | minute | second) FROM expression)
Beispiel
SELECT ename, empno , hiredate FROM emp WHERE EXTRACT(YEAR FROM TO_DATE (hiredate, 'DD-MM-YYYY')) > 81 ORDER BY hiredate;
Beispiel[edit]
In welchem Monat gab es mehr als 2 Neuaufnahmen?
select extract (month from hiredate) Monat , count(*) Anzahl from emp group by extract (month from hiredate) having count(*) > 2
Ergebnis:
MONAT ANZAHL -------------------------------------- 12 4
Null Values und NVL[edit]
Null Values[edit]
Anzeige aller Mitarbeiter, die nicht auf Provisionsbasis arbeiten.
SELECT ENAME,SAL,COMM,JOB FROM EMP WHERE COMM IS (NOT) NULL;
Alle Mitarbeiter, deren Provision von 500.- verschieden ist. (auch NULL ist von 500 verschieden)
SELECT ENAME,SAL,COMM,JOB FROM EMP WHERE COMM != 500;
Mitarbeiter, deren Feld COMM
einen Null-Wert enthalten, werden nicht ausgegeben,
obwohl eigentlich die Suchbedingung (!=500
) erfüllt wäre. Um diese Mitarbeiter mit
auszugeben, müsste die Suchbedingung COMM != 500 OR COMM IS NULL
lauten.
NVL Funktion[edit]
Ausgabe des gesamten Verdienstes der Mitarbeiter in Abteilung 30.
SELECT ENAME,JOB,SAL,COMM,SAL+NVL(COMM,0) FROM EMP WHERE DEPTNO = 30;
Ein NULL – Value in der Spalte COMM
wird durch den Wert 0
ersetzt.
Subselect / Subquery[edit]
Einführung Subqueries[edit]
Liste alle Mitarbeiter, die den gleichen Job wie JONES
haben:
Ermittlung des Jobs von JONES:
SELECT JOB FROM EMP WHERE ENAME = 'JONES';
Ergebnis: MANAGER
Ermittlung aller Manager:
SELECT ENAME,JOB FROM EMP WHERE JOB = 'MANAGER';
Wir wollen diese Abfrage jedoch mit einem Statement durchführen.
Für diese Fälle kann man SELECT
-Statements verschachteln.
SELECT ENAME, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = ‚JONES‘);
Beispiele[edit]
Beispiel 1[edit]
Alle Mitarbeiter, die mehr verdienen als irgendein Mitarbeiter aus Abteilung 30
:
SELECT SAL,JOB,ENAME,DEPTNO FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);
… die mehr verdienen als jeder ….
…. (SELECT MAX(SAL)…….
Beispiel 2[edit]
Ermittlung aller Mitarbeiter mit gleichem Job und Gehalt wie FORD
:
SELECT ENAME,JOB,SAL FROM EMP WHERE (JOB,SAL) = (SELECT JOB,SAL FROM EMP WHERE ENAME = ‘FORD‘);
Beispiel 3[edit]
Ermittlung aller Mitarbeiter, die entweder den gleichen Job wie JONES
haben oder deren Gehalt größer gleich dem von FORD
ist:
SELECT ENAME,JOB,DEPTNO,SAL FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'JONES‘) OR SAL >= (SELECT SAL FROM EMP WHERE ENAME = 'FORD‘) ORDER BY JOB,SAL;
Beispiel 4[edit]
Alle Mitarbeiter aus Abteilung 10
, die einen Job haben wie irgendein Mitarbeiter aus Abteilung 30
:
SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 10 AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = 30);
Beispiel 5[edit]
Liste der Mitarbeiter aus Abteilung 10
mit einem Job, der auch von einem Mitarbeiter aus der Abteilung mit dem Namen „SALES“
ausgeübt wird
SELECT ENAME,JOB FROM EMP WHERE DEPTNO = 10 AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES‘));
Beispiel 6[edit]
Zeige diejenigen Mitarbeiter, die den gleichen Job haben wie ein Mitarbeiter aus CHICAGO
SELECT ENAME,JOB FROM EMP WHERE JOB IN (SELECT JOB FROM EMP,DEPT WHERE LOC = ‚CHICAGO‘ AND EMP.DEPTNO = DEPT.DEPTNO);
Abhängige Subqueries[edit]
Alle Mitarbeiter, deren Gehalt höher ist als der Durchschnittsgehalt ihrer Abteilung.
Dazu benötigt man ein Hauptquery, um die Zeilen aus der Tabelle EMP auszuwählen:
SELECT DEPTNO,ENAME,SAL FROM EMP WHERE SAL > (Abteilungsdurchschnittsgehalt für die DEPTNO der aktuellen Zeile);
Das Subquery soll also das Durchschnittsgehalt der Abteilung, die zur momentan ausgewählten Zeile gehört, berechnen:
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = (momentaner Wert von DEPTNO));
Lösung:
SELECT DEPTNO,ENAME,SAL FROM EMP X WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = X.DEPTNO) ORDER BY DEPTNO;
Wir benennen die benötigte Tabelle mit dem Namen X
Subqueries zur Prüfung auf Existenz[edit]
Gesucht sind diejenigen Abteilungen, in denen mindestens ein Mitarbeiter beschäftigt ist
SELECT * FROM DEPT WHERE EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO);
Der logische Ausdruck EXISTS(Subquery)
ist wahr, wenn das Subquery mindestens eine Zeile als Ergebnis liefert, sonst falsch. Es wird üblicherweise an der Stelle verwendet, an der ein IN
mit einem Subquery verwendet werden könnte, aber mit folgendem Unterschied: Es wird üblicherweise nur mit einem correlated Subquery verwendet.
Äquivalent dazu ist folgendes Statement ohne EXISTS:
select * from dept where dept.deptno in (select deptno from emp);
Dieses Ergebnis könnte auch mit einem Join zwischen EMP
und DEPT
erzielt werden. Ein Join ist aber im Gegensatz zur Lösung mit EXISTS laufzeitintensiver
Subselect oder Join?
Äquivalent zu den beiden vorher beschrieben Statements ist auch:
select distinct d.deptno, d.dname, d.loc from dept d, emp where emp.deptno = d.deptno
Beispiele[edit]
Beispiel 1[edit]
Auswahl aller Abteilungen, die einen Mitarbeiter aufweisen, der über 1000.- verdient.
SELECT * FROM DEPT WHERE EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL > 1000);
Beispiel 2[edit]
Ausgabe derjenigen Abteilungen, in der jeder Mitarbeiter über 1000.- verdient. Entspricht: ‚Kein Mitarbeiter verdient weniger als 1000.- oder gleich 1000.-.
SELECT * FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL <= 1000);
Da die Abteilung 40 keinen Mitarbeiter hat, liefert das ‚innere‘ SELECT
keine Zeile, NOT EXISTS
ist daher auch für Abteilung 40 wahr. Um schließlich das gewünschte Ergebnis zu erhalten, muss NOT EXISTS
und EXISTS
in folgender Weise verwendet werden:
SELECT * FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL <= 1000) AND EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO);
Subquery in der FROM Klausel[edit]
Gesucht ist der Durchschnitt aller Abteilungsgehaltssummen, die über 9000,-- liegen.
select avg(abteilungsgehaltssum) from (select deptno, sum(sal) as abteilungsgehaltssum from emp group by deptno having sum(sal) > 9000 );
Flashback Query[edit]
Nachdem ein schreibender Benutzer seine Änderungen mit COMMIT bestätigt hat, wird das before image nicht mehr benötigt. Aus Performancegründen wird dieses Image allerdings erst dann gelöscht, wenn wieder Platz im Undo Tablespace benötigt wird. Bis dahin kann mittels Flashback Query auf diese ‘alten’ Daten zugegriffen werden.
select * from emp as of timestamp systimestamp - interval '5' minute
(hier wird der Zustand von vor 5 Minuten angezeigt)