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

===== Ausgabeformatierungen =====

  * ''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:<code sql>
SELECT DISTINCT job_id from EMPLOYEES WHERE job_id = 'FI_ACCOUNT';
  JOB_ID    |
  ----------+
  FI_ACCOUNT|
</code>Im Gegensatz zu:<code sql>
SELECT job_id from EMPLOYEES WHERE job_id = 'FI_ACCOUNT';
  JOB_ID    |
  ----------+
  FI_ACCOUNT|
  FI_ACCOUNT|
</code>

----

===== Filter, Sortierung & Formatieren =====

  * Viele Formatierungen können in der ''SELECT'' und in der ''WHERE'' Klausel verwendet werden.
  * Sortieren im ''WHERE''\\ Beispiel:<code sql>
... WHERE SALARY > 12000 ORDER BY SALARY DESC;
</code>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.<code sql>
WHERE SALARY < 5000 OR SALARY > 12000
WHERE SALARY_MIN < 5000 AND SALARY_MAX > 12000
</code>
  * Bei Zahlenwerten kann man auch mit ''BETWEEN'' arbeiten.<code sql>
WHERE NOT (SALARY BETWEEN 5000 AND 12000) -- Durch das 'NOT' wird der Bereich 5000 bis 12000 ausgelassen.
</code>Hier findet auch eine Negation statt.
  * Sucht man mehrere bestimmte Werte, kann man auch den ''IN'' Operator nutzen.<code sql>
select distinct last_name from employees where last_name in ('King', 'Tobias', 'Weiss') ;

LAST_NAME    
____________ 
King         
Tobias       
Weiss
</code>

==== Operatoren, die im SELECT & WHERE verwendet werden ====

  * Mit ''TO_CHAR'' kann man in einem Datumsfeld die Schreibweise definieren, mit der man bestimmte Daten sucht.\\ Beispiel:<code sql>
SELECT last_name, hire_date FROM EMPLOYEES WHERE to_char(HIRE_DATE, 'YYYY-MM') = '2000-04';
</code>
  * ''EXTRACT'' funktioniert ähnlich wie ''TO_CHAR'' und ist dabei performanter.\\ Beispiel:<code sql>
SELECT last_name, hire_date FROM EMPLOYEES WHERE EXTRACT(YEAR FROM HIRE_DATE) = 2000;
</code>Man kann hier nach einer bestimmten Komponente des Datums suchen.\\ Folgende Zeitsuchbegriffe gibt es:

^ Feld ^ Beispiel ^ Rückgabe ^
| YEAR | EXTRACT(YEAR FROM hire_date) | 2000 |
| MONTH | EXTRACT(MONTH FROM hire_date) | 6 |
| DAY | EXTRACT(DAY FROM hire_date) | 17 |
| HOUR | EXTRACT(HOUR FROM SYSTIMESTAMP) | 14 |
| MINUTE | EXTRACT(MINUTE FROM SYSTIMESTAMP) | 30 |
| SECOND | EXTRACT(SECOND FROM SYSTIMESTAMP) | 45.123 |
| TIMEZONE_HOUR | EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) | 2 |
| TIMEZONE_MINUTE | EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) | 0 |
| TIMEZONE_REGION | EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) | Europe/Berlin |
| TIMEZONE_ABBR | EXTRACT(TIMEZONE_ABBR FROM SYSTIMESTAMP) | CET |

**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:<code sql>
…WHERE MANAGER_ID = NULL; -- FALSCH: Ergibt immer false!
…WHERE MANAGER_ID IS NULL;
-- oder bei einer Negierung:
…WHERE commission_pct IS NOT NULL;
</code>
  * 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:<code sql>
…WHERE LAST_NAME LIKE '__a%'
</code>Hier wird alles zurückgegeben, das an der dritten Stelle ein 'a' hat.
    * ''INSTR'' Liefert die Position von Stringelementen in einem String.\\ Beispiel:<code sql>
-- 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
</code>
    * ''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:<code sql>
SUBSTR('Copilot', 4)     -- Liefert 'ilot'
SUBSTR('Copilot', 4, 2)  -- Liefert 'il'

-- Beispiel in einer WHERE Klausel
…WHERE substr(LAST_NAME, 7, 1) = 'a'
</code>
    * ''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:<code sql>
select initcap('den haag') from dual; -- liefert 'Den Haag'
</code>
    * ''UPPER'' oder ''LOWER'' Wandelt alle Buchstaben in einem String in Groß- oder Kleinbuchstaben um.<code sql>
select upper('den haag') from dual; -- liefert 'DEN HAAG'
select lower('RIP') from dual;      -- liefert 'rip'
</code>
    * ''LENGTH'' zählt die Zeichen in einem String.<code sql>
select length('den haag') from dual; -- liefert 8
</code>
    * ''REGEXP_COUNT'' liefert Zähler, die über einen regulären Ausdruck definiert werden.<code sql>
-- Zählen von Worten in einem String:
select regexp_count('den haag', '\S+') from dual; -- liefert 2
</code>
    * ''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:<code sql>
select lpad('mein Text', 15, '+') from dual;
-- liefert: ++++++mein Text

select rpad('mein Text', 15, '+-*_') from dual;
-- liefert: mein Text+-*_+-
</code>
  * **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:<code sql>
SELECT last_name AS name, job_id AS job ...
</code>In diesem Fall werden die Überschriften komplett in Großbuchstaben geschrieben.<code sql>
SELECT last_name "Last Name", job_id "JobID" ...
</code>Das 'AS' ist immer optional außer bei Tabellen-Alias, da **MUSS** es weggelassen werden:<code sql>
-- Funktioniert:
SELECT e.last_name FROM employees e;

-- Fehler in Oracle!
SELECT e.last_name FROM employees AS e;
</code>
  * ''CONCAT'': (Concatenation) bedeutet das Zusammenfassen (Verketten) von Strings und Spaltenwerten zu einem einzigen Wert.\\ Der Operator hierzu ist ein Doppelpipe ''%%||%%''.\\ Beispiel:<code sql>
SELECT last_name || ', ' || job_id AS "Employee and Title" 
  from EMPLOYEES
  WHERE employee_id = 100;

Employee and Title|
------------------+
King, AD_PRES     |
</code>
  * **Zahlen-Formatierung**
    * ''TRUNC'' schneidet alle Nachkommastellen ab.<code sql>
-- Es wird nicht gerundet
select trunc(3.14) from dual;

TRUNC(3.14) 
______________ 
          3

select trunc(3.6) from dual;

  TRUNC(3.6) 
_____________ 
            3
</code>
    * ''ROUND'' Rundet Zahlen nach der angegebenen Nachkommastelle.\\ Beispiel:<code sql>
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" …
</code>

----

===== Prompting =====

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:
<code sql>
SELECT last_name, salary from EMPLOYEES WHERE SALARY > &min_sal ;
</code>
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:
<code sql>
SELECT last_name, salary from EMPLOYEES WHERE SALARY > :min_sal ;
</code>
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:
<code sql>
VARIABLE min_sal NUMBER;
exec :min_sal := 14000;
SELECT last_name, salary from EMPLOYEES WHERE SALARY > :min_sal ;
</code>
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:
<code sql>
SELECT last_name FROM employees WHERE LAST_NAME LIKE upper('&first_letter%');
</code>

[[opendocks:oracle-sql-nutshell-doku:start|Zurück]] - [[opendocks:oracle-sql-nutshell-doku:nutshell-doku-02|Weiter]]