Eigene Oracle-SQL-Doku, die aus den Übungen entstanden ist

• DISTINCT: Wird in der SELECT Klausel verwendet und sorgt für eine eindeutige Ausgabe von Feldwerten, die bei der Abfrage mehrfach auftreten können.
  Beispiel:

  SELECT DISTINCT job_id FROM EMPLOYEES WHERE job_id = 'FI_ACCOUNT';
    JOB_ID    |
    ----------+
    FI_ACCOUNT|

  Im Gegensatz zu:

  SELECT job_id FROM EMPLOYEES WHERE job_id = 'FI_ACCOUNT';
    JOB_ID    |
    ----------+
    FI_ACCOUNT|
    FI_ACCOUNT|

• Viele Formatierungen können in der SELECT und in der WHERE Klausel verwendet werden.
• Sortieren im WHERE
  Beispiel:

  ... WHERE SALARY > 12000 ORDER BY SALARY DESC;

  Als ORDER BY können die folgenden Werte angegeben werden:

  • Name einer x-beliebigen Spalte der abgefragten Tabelle oder der View.
  • Die Position der Spalte in der Ausgabe. Z.B. ORDER BY 2
  • Ein im SELECT gesetzter Alias. Z.B. SELECT last_name AS name, salary from EMPLOYEES WHERE SALARY > 12000 ORDER BY name;
• Es gibt AND und OR Verknüpfungen.

  WHERE SALARY < 5000 OR SALARY > 12000
  WHERE SALARY_MIN < 5000 AND SALARY_MAX > 12000

• Bei Zahlenwerten kann man auch mit BETWEEN arbeiten.

  WHERE NOT (SALARY BETWEEN 5000 AND 12000) -- Durch das 'NOT' wird der Bereich 5000 bis 12000 ausgelassen.

  Hier findet auch eine Negation statt.

• Sucht man mehrere bestimmte Werte, kann man auch den IN Operator nutzen.

  SELECT DISTINCT last_name FROM employees WHERE last_name IN ('King', 'Tobias', 'Weiss') ;
   
  LAST_NAME    
  ____________ 
  King         
  Tobias       
  Weiss

• Mit TO_CHAR kann man in einem Datumsfeld die Schreibweise definieren, mit der man bestimmte Daten sucht.
  Beispiel:

  SELECT last_name, hire_date FROM EMPLOYEES WHERE to_char(HIRE_DATE, 'YYYY-MM') = '2000-04';

• EXTRACT funktioniert ähnlich wie TO_CHAR und ist dabei performanter.
  Beispiel:

  SELECT last_name, hire_date FROM EMPLOYEES WHERE EXTRACT(YEAR FROM HIRE_DATE) = 2000;

  Man kann hier nach einer bestimmten Komponente des Datums suchen.
  Folgende Zeitsuchbegriffe gibt es:

Wichtig: HOUR, MINUTE, SECOND und die Timezone-Felder funktionieren nur mit TIMESTAMP-Datentypen, nicht mit DATE.

• NULL Felder sind Felder, in denen NICHTS steht und werden somit als null bezeichnet.
  Wenn man nach NULL-Feldern filtern will, darf man nicht mit dem '=' als Operator arbeiten.
  Beispiel:

  …WHERE MANAGER_ID = NULL; -- FALSCH: Ergibt immer false!
  …WHERE MANAGER_ID IS NULL;
  -- oder bei einer Negierung:
  …WHERE commission_pct IS NOT NULL;

• Stringoperatoren
  • Mit LIKE kann man nach Stringelementen suchen.
    Hierbei steht ein Unterstrich '_' für ein x-beliebiges Zeichen und Prozent '%' für beliebig viele Zeichen.
    Beispiel:

    …WHERE LAST_NAME LIKE '__a%'

    Hier wird alles zurückgegeben, das an der dritten Stelle ein 'a' hat.

  • INSTR Liefert die Position von Stringelementen in einem String.
    Beispiel:

    -- Alle Nachnamen, die an dritter Stelle ein 'a' haben
    …WHERE instr(LAST_NAME, 'a') = 3
     
    -- Alle, in deren Nachnamen ein 'a' vorkommt
    …WHERE instr(LAST_NAME, 'a') > 0

  • SUBSTR extrahiert Zeichenfolgen aus einem String. Dabei ist im ersten Parameter der komplette String, im zweiten Parameter die Position, an der die Extraktion beginnen soll, und der dritte Parameter beschreibt, wie viele Zeichen extrahiert werden sollen. Der dritte Parameter ist optional und wenn er nicht angegeben ist, wird ab dem Startpunkt alles bis Stringende extrahiert.
    Beispiel:

    SUBSTR('Copilot', 4)     -- Liefert 'ilot'
    SUBSTR('Copilot', 4, 2)  -- Liefert 'il'
     
    -- Beispiel in einer WHERE Klausel
    …WHERE substr(LAST_NAME, 7, 1) = 'a'

  • INITCAP Schreibt den ersten Buchstaben eines jeden Wortes im String groß und alle weiteren Buchstaben klein und das unabhängig davon, wie die Buchstaben im ursprünglichen String geschrieben sind.
    Beispiel:

    SELECT initcap('den haag') FROM dual; -- liefert 'Den Haag'

  • UPPER oder LOWER Wandelt alle Buchstaben in einem String in Groß- oder Kleinbuchstaben um.

    SELECT UPPER('den haag') FROM dual; -- liefert 'DEN HAAG'
    SELECT LOWER('RIP') FROM dual;      -- liefert 'rip'

  • LENGTH zählt die Zeichen in einem String.

    SELECT LENGTH('den haag') FROM dual; -- liefert 8

  • REGEXP_COUNT liefert Zähler, die über einen regulären Ausdruck definiert werden.

    -- Zählen von Worten in einem String:
    SELECT regexp_count('den haag', '\S+') FROM dual; -- liefert 2

  • LPAD und RPAD werden benutzt, um ein Ausgabefeld auf eine bestimmte Länge zu definieren und die ungenutzten Zeichen durch eine definierte Zeichenkette links (LPAD) oder rechts (RPAD) aufzufüllen.
    Beispiel:

    SELECT lpad('mein Text', 15, '+') FROM dual;
    -- liefert: ++++++mein Text
     
    SELECT rpad('mein Text', 15, '+-*_') FROM dual;
    -- liefert: mein Text+-*_+-

• Spaltenüberschrift: Bei einem 'SELECT job_id …' steht als Spaltenüberschrift der Name der selektierten Tabellenspalte. Um hier eine individuelle Überschrift zu setzen, schreibt man die Überschrift dahinter.
  Beispiel:

  SELECT last_name AS name, job_id AS job ...

  In diesem Fall werden die Überschriften komplett in Großbuchstaben geschrieben.

  SELECT last_name "Last Name", job_id "JobID" ...

  Das 'AS' ist immer optional außer bei Tabellen-Alias, da MUSS es weggelassen werden:

  -- Funktioniert:
  SELECT e.last_name FROM employees e;
   
  -- Fehler in Oracle!
  SELECT e.last_name FROM employees AS e;

• CONCAT: (Concatenation) bedeutet das Zusammenfassen (Verketten) von Strings und Spaltenwerten zu einem einzigen Wert.
  Der Operator hierzu ist ein Doppelpipe ||.
  Beispiel:

  SELECT last_name || ', ' || job_id AS "Employee and Title" 
    FROM EMPLOYEES
    WHERE employee_id = 100;
   
  Employee AND Title|
  ------------------+
  King, AD_PRES     |

• Zahlen-Formatierung
  • TRUNC schneidet alle Nachkommastellen ab.

    -- Es wird nicht gerundet
    SELECT trunc(3.14) FROM dual;
     
    TRUNC(3.14) 
    ______________ 
              3
     
    SELECT trunc(3.6) FROM dual;
     
      TRUNC(3.6) 
    _____________ 
                3

  • ROUND Rundet Zahlen nach der angegebenen Nachkommastelle.
    Beispiel:

    SELECT round(3.6) FROM dual;
     
      ROUND(3.6) 
    _____________ 
                4
     
    -- oder als Rechnung:
    SELECT round(2.3123 * 3.522, 10) AS zahl FROM DUAL;
     
            ZAHL 
    ____________ 
      8,1439206
     
    -- oder als Berechnung mit einem Datenfeld:
    SELECT round(salary * 1.155, 0) AS "New Salary" …

Selects mit Prompts an den User werden nicht von der Datenbank selber gepromptet. Vielmehr ist es so, dass hier der SQL-Client zunächst eine Abfrage an den User stellt und dann den eingegebenen Wert durch die zu promptende Variable ersetzt und dann erst den Select an die Datenbank schickt.
Hier ein Beispiel:

SELECT last_name, salary FROM EMPLOYEES WHERE SALARY > &min_sal ;

Beim Ausführen des Selects wird zunächst nach dem Wert von min_sal gefragt bevor der Select an die DB geschickt wird.
ACHTUNG! Diese Schreibweise beherrschen nur Oracle eigene SQL-Clients. Dies wären:

• SQL*Plus
• SQL Developer
• SQLcl

Alle anderen Nicht-Oracle-SQL-Clients beherrschen diese Schreibweise nicht und würden &min_sal als Teil der WHERE Klausel mitschicken und so in einen Fehler laufen.
Allerdings beherrscht DBeaver hier eine eigene Syntax:

SELECT last_name, salary FROM EMPLOYEES WHERE SALARY > :min_sal ;

Mit einem Doppelpunkt ':' wird hier min_sal ebenfalls gepromptet.
ACHTUNG! In einem Oracle-SQL-Client wird hier auch gepromptet, weil hier :min_sal als eine noch nicht definierte Variable erkannt wird.
Wird aber in einem Script die Variable zuvor definiert und gesetzt, dann wird nicht mehr gepromptet.
Beispiel:

VARIABLE min_sal NUMBER;
EXEC :min_sal := 14000;
SELECT last_name, salary FROM EMPLOYEES WHERE SALARY > :min_sal ;

Liefert als Script eine Rückgabe ohne vorherigen Prompt.
:min_sal kann hier auch an verschiedenen Stellen wiederverwendet werden.

Ein Prompt wird auch innerhalb eines Stringoperators ausgelesen und angezeigt:

SELECT last_name FROM employees WHERE LAST_NAME LIKE UPPER('&first_letter%');

Zurück - Weiter