Difference between revisions of "SQL 2 Subselects"

From Coders.Bay Wiki
Jump to navigation Jump to search
 
Line 560: Line 560:
where dept.deptno in (select deptno
where dept.deptno in (select deptno
                         from emp);
                         from emp);
<pre>
</pre>
Dieses Ergebnis könnte auch mit einem Join zwischen <code>EMP</code> und <code>DEPT</code> erzielt werden. Ein Join ist aber im Gegensatz zur Lösung mit EXISTS laufzeitintensiver
Dieses Ergebnis könnte auch mit einem Join zwischen <code>EMP</code> und <code>DEPT</code> erzielt werden. Ein Join ist aber im Gegensatz zur Lösung mit EXISTS laufzeitintensiver


Latest revision as of 09:09, 20 July 2023

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)

Retrieved from "http://wiki.streampy.at/index.php?title=SQL_2_Subselects&oldid=2331"