DQL – Data Query Language: The SELECT statement

Subject: Information Technology – Database Systems School level: 11th grade – HTL Informatik Prerequisites: Introduction to SQL, SCOTT Schema, Oracle SQL Developer Author: HTL Pinkafeld – IF/IT

1. Structure of the SELECT statement

1.1 Basic structure

The SELECT statement is the most important SQL statement. It queries data from one or more tables:

SELECT spalte1, spalte2, ...   -- Which columns?
FROM   tabellenname             -- From which table?
WHERE  bedingung                -- Which lines? (optional)
ORDER BY spalte;                -- Sorting (optional)

1.2 Processing order

Important: SQL clauses are not evaluated in the order in which they are written:

Schreibreihenfolge:       Auswertungsreihenfolge (intern):
1. SELECT                 1. FROM      -- Which table?
2. FROM                   2. WHERE     -- Which lines?
3. WHERE                  3. GROUP BY  -- Form groups
4. GROUP BY               4. HAVING    -- Filter groups
5. HAVING                 5. SELECT    -- Calculate columns
6. ORDER BY               6. ORDER BY  -- Sortieren

1.3 All columns and all rows

-- All columns with * (star)
SELECT * FROM EMP;

-- All columns explicit (better for production – more robust to schema changes)
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM   EMP;

Result (excerpt):

EMPNO  ENAME   JOB        MGR   HIREDATE     SAL   COMM  DEPTNO
-----  ------  ---------  ----  -----------  ----  ----  ------
7369   SMITH   CLERK      7902  17.12.1980    800         20
7499   ALLEN   SALESMAN   7698  20.02.1981   1600   300   30
7839   KING    PRESIDENT        17.11.1981   5000         10

2. Select columns and aliases

2.1 Select specific columns

-- Just name and salary
SELECT ENAME, SAL
FROM   EMP;

-- Order determines the output order
SELECT DEPTNO, ENAME, JOB
FROM   EMP;

2.2 DISTINCT – Remove duplicates

-- All different job titles
SELECT DISTINCT JOB
FROM   EMP;

-- Ergebnis:
-- JOB
-- ---------
-- ANALYST
-- CLERK
-- MANAGER
-- PRESIDENT
-- SALESMAN

-- DISTINCT applies to the entire column selection
SELECT DISTINCT JOB, DEPTNO
FROM   EMP;

2.3 Column Aliases

Aliases give columns their own names in the output:

-- Mit AS (empfohlen)
SELECT ENAME AS "Mitarbeiter",
       SAL   AS "Gehalt in USD"
FROM   EMP;

-- Without AS (shorter but less readable)
SELECT ENAME "Mitarbeiter",
       SAL   Monatsgehalt
FROM   EMP;

Rule: Aliases containing special characters, spaces, or case sensitivity must be enclosed in double quotes. Without quotation marks, Oracle automatically writes the alias in capital letters.

2.4 Table Aliases

-- Table alias (particularly important for multiple tables/joins)
SELECT e.ENAME, e.SAL, e.DEPTNO
FROM   EMP e;

3. Arithmetic expressions

3.1 Calculations in SELECT

Oracle allows arithmetic expressions directly in the SELECT list:

-- Jahresgehalt berechnen
SELECT ENAME,
       SAL,
       SAL * 12          AS "Jahresgehalt",
       SAL * 12 + 1000   AS "Jahresgehalt + Bonus"
FROM   EMP;

Operators: +, -, *, / (brackets possible)

3.2 Operator Priority

-- Multiplikation vor Addition (wie in Mathematik)
SELECT SAL + SAL * 0.2   AS falsch_weil_mehrdeutig,
       SAL * 1.2          AS richtig,
       (SAL + 500) * 12   AS mit_klammern
FROM   EMP;

3.3 ZERO in calculations

-- NULL in einer Berechnung ergibt immer NULL!
SELECT ENAME,
       SAL,
       COMM,
       SAL + COMM   AS "Gesamt (fehlerhaft)"
FROM   EMP;

-- Result for SMITH (COMM is NULL):
-- ENAME  SAL   COMM  Gesamt
-- SMITH 800 ZERO ZERO <-- not 800!

Note: Any arithmetic operation involving NULL results in NULL. Solution: NVL function (see Chapter 9).

4. WHERE – filter rows

4.1 Simple conditions

-- Employees of Department 20
SELECT ENAME, JOB, DEPTNO
FROM   EMP
WHERE  DEPTNO = 20;

-- Employees with salaries over 2000
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > 2000;

-- Exactly one employee by name
SELECT *
FROM   EMP
WHERE  ENAME = 'KING';    -- Strings in SINGLE quotes!
                           -- Oracle: Pay attention to upper/lower case!

Important: In Oracle, strings are case-sensitive. 'king' and 'KING' are different!

4.2 Date comparisons

-- Eingestellt nach dem 01.01.1982
SELECT ENAME, HIREDATE
FROM   EMP
WHERE  HIREDATE > TO_DATE('01.01.1982', 'DD.MM.YYYY');

-- Im Jahr 1981 eingestellt
SELECT ENAME, HIREDATE
FROM   EMP
WHERE  HIREDATE >= TO_DATE('01.01.1981', 'DD.MM.YYYY')
  AND  HIREDATE <  TO_DATE('01.01.1982', 'DD.MM.YYYY');

5. Comparison and logical operators

5.1 Comparison operators

5.2 Logical operators

-- AND: both conditions must be fulfilled
SELECT ENAME, JOB, SAL
FROM   EMP
WHERE  JOB = 'SALESMAN'
  AND  SAL > 1400;

-- OR: at least one condition must be fulfilled
SELECT ENAME, JOB
FROM   EMP
WHERE  JOB = 'PRESIDENT'
   OR  JOB = 'ANALYST';

-- NOT: Bedingung umkehren
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  NOT DEPTNO = 20;
-- entspricht: WHERE DEPTNO <> 20

5.3 Operator Priority

Priority (descending):
1. Arithmetische Operatoren (* / + -)
2. Vergleichsoperatoren (= <> < > <= >=)
3. NOT
4. AND
5. OR

-- Attention: AND binds more strongly than OR!
SELECT ENAME, JOB, DEPTNO
FROM   EMP
WHERE  JOB = 'CLERK' AND DEPTNO = 20
    OR JOB = 'ANALYST';
-- = (JOB='CLERK' AND DEPTNO=20) OR (JOB='ANALYST')

-- Mit Klammern explizit steuern:
WHERE  JOB = 'CLERK' AND (DEPTNO = 20 OR DEPTNO = 10)

6. Special WHERE operators

6.1 BETWEEN ... AND

Checks a value for a range (inclusive):

-- Gehalt zwischen 1000 und 2000 (inklusiv)
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL BETWEEN 1000 AND 2000;

-- Equivalent to:
WHERE  SAL >= 1000 AND SAL <= 2000

-- Also for date:
SELECT ENAME, HIREDATE
FROM   EMP
WHERE  HIREDATE BETWEEN TO_DATE('01.01.1981','DD.MM.YYYY')
               AND     TO_DATE('31.12.1981','DD.MM.YYYY');

-- NOT BETWEEN: outside the range
WHERE  SAL NOT BETWEEN 1000 AND 2000

6.2 IN (...)

Checks whether a value is contained in a list:

-- Specific jobs
SELECT ENAME, JOB
FROM   EMP
WHERE  JOB IN ('CLERK', 'ANALYST', 'MANAGER');

-- Equivalent to:
WHERE  JOB = 'CLERK' OR JOB = 'ANALYST' OR JOB = 'MANAGER'

-- Specific departments
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO IN (10, 20);

-- NOT IN: not in the list
SELECT ENAME, JOB
FROM   EMP
WHERE  JOB NOT IN ('CLERK', 'SALESMAN');

6.3 LIKE – pattern comparison

Searches for character patterns with wildcards:

-- Name beginnt mit 'S'
SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%';
-- SMITH, SCOTT

-- Name endet auf 'N'
SELECT ENAME FROM EMP WHERE ENAME LIKE '%N';
-- ALLEN, MARTIN

-- Name contains 'AR'
SELECT ENAME FROM EMP WHERE ENAME LIKE '%AR%';
-- WARD, MARTIN, CLARK

-- Exactly 4 characters long
SELECT ENAME FROM EMP WHERE ENAME LIKE '____';
-- WARD, FORD, KING

-- Zweiter Buchstabe ist 'L'
SELECT ENAME FROM EMP WHERE ENAME LIKE '_L%';
-- ALLEN, CLARK, BLAKE

-- NOT LIKE
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE 'S%';

Case sensitive: LIKE is case-sensitive in Oracle. LIKE 's%' cannot find an employee because all names are stored in capital letters.

6.4 IS NULL / IS NOT NULL

-- Employee without commission (COMM is ZERO)
SELECT ENAME, COMM
FROM   EMP
WHERE  COMM IS NULL;

-- Employees with commission (COMM is not NULL)
SELECT ENAME, COMM
FROM   EMP
WHERE  COMM IS NOT NULL;

-- INCORRECT! = NULL never results in TRUE
-- WHERE COMM = NULL -- finds no lines!
-- WHERE COMM <> NULL -- finds no lines!

7. ORDER BY – Sort

7.1 Basic sorting

-- Aufsteigend (ASC ist Standard, kann weggelassen werden)
SELECT ENAME, SAL
FROM   EMP
ORDER BY SAL;

-- Absteigend
SELECT ENAME, SAL
FROM   EMP
ORDER BY SAL DESC;

-- Nach Name alphabetisch
SELECT ENAME, JOB
FROM   EMP
ORDER BY ENAME ASC;

7.2 Multiple sorting columns

-- Primarily by department, descending by salary within the same department
SELECT DEPTNO, ENAME, SAL
FROM   EMP
ORDER BY DEPTNO ASC, SAL DESC;

-- Ergebnis:
-- DEPTNO  ENAME   SAL
-- ------  ------  ----
-- 10      KING    5000
-- 10      CLARK   2450
-- 10      MILLER  1300
-- 20      FORD    3000
-- 20      SCOTT   3000
-- ...

7.3 Sorting by column number

-- Sorting by the 2nd and 3rd columns of the SELECT list
SELECT ENAME, DEPTNO, SAL
FROM   EMP
ORDER BY 2, 3 DESC;
-- = ORDER BY DEPTNO, SAL DESC

7.4 NULL sorting

-- In Oracle, NULLs are placed at the end (ASC) or at the beginning (DESC) by default.
SELECT ENAME, COMM
FROM   EMP
ORDER BY COMM;           -- NULLs kommen zuletzt

-- NULLS FIRST / NULLS LAST explizit steuern
ORDER BY COMM NULLS FIRST;   -- NULLs zuerst
ORDER BY COMM NULLS LAST;    -- NULLs zuletzt

7.5 Sorting by Alias

SELECT ENAME,
       SAL * 12 AS jahresgehalt
FROM   EMP
ORDER BY jahresgehalt DESC;   -- Use alias from SELECT

8. Single-line functions

Single-line functions process one row and return a value.

8.1 String functions

-- UPPER / LOWER / INITCAP
SELECT UPPER('hello'),           -- HELLO
       LOWER('WORLD'),           -- world
       INITCAP('hello world')    -- Hello World
FROM   DUAL;

-- Suche case-insensitiv
SELECT ENAME FROM EMP
WHERE  UPPER(ENAME) = 'SMITH';

-- LENGTH: Length of a character string
SELECT ENAME, LENGTH(ENAME) AS laenge
FROM   EMP
ORDER BY laenge DESC;

-- SUBSTR(string, start, laenge)
-- Counting starts at 1!
SELECT SUBSTR('Oracle SQL', 1, 6),   -- Oracle
       SUBSTR('Oracle SQL', 8),      -- SQL (bis Ende)
       SUBSTR('Oracle SQL', -3)      -- SQL (von hinten)
FROM   DUAL;

-- INSTR(string, suchstring): Position des ersten Vorkommens
SELECT INSTR('Oracle SQL', 'SQL')   -- 8
FROM   DUAL;

-- CONCAT or ||: Connect character strings
SELECT CONCAT(ENAME, ' ist ' ) || JOB AS beschreibung
FROM   EMP;
-- SMITH ist CLERK

-- TRIM / LTRIM / RTRIM: Leerzeichen entfernen
SELECT TRIM('  Hello  '),    -- 'Hello'
       LTRIM('  Hello  '),   -- 'Hello  '
       RTRIM('  Hello  ')    -- '  Hello'
FROM   DUAL;

-- LPAD / RPAD: Padding to specific length
SELECT LPAD(SAL, 10, '*'),   -- *****3000
       RPAD(ENAME, 12, '.')  -- SMITH.......
FROM   EMP;

-- REPLACE: replace characters
SELECT REPLACE('HELLO WORLD', 'O', '0')   -- HELL0 W0RLD
FROM   DUAL;

8.2 Numerical functions

-- ROUND: Runden
SELECT ROUND(123.456, 2),   -- 123.46
       ROUND(123.456, 0),   -- 123
       ROUND(123.456, -1)   -- 120
FROM   DUAL;

-- TRUNC: Abschneiden (kein Runden!)
SELECT TRUNC(123.456, 2),   -- 123.45
       TRUNC(123.456, 0),   -- 123
       TRUNC(123.456, -1)   -- 120
FROM   DUAL;

-- MOD: Modulo (Rest der Ganzzahldivision)
SELECT MOD(17, 5),    -- 2
       MOD(10, 2)     -- 0 (gerade Zahl)
FROM   DUAL;

-- ABS: Absolutwert
SELECT ABS(-250)   -- 250
FROM   DUAL;

-- CEIL / FLOOR: Aufrunden / Abrunden auf ganze Zahl
SELECT CEIL(4.1),     -- 5
       FLOOR(4.9)     -- 4
FROM   DUAL;

-- POWER / SQRT
SELECT POWER(2, 10),   -- 1024
       SQRT(144)       -- 12
FROM   DUAL;

8.3 Date functions

-- Aktuelles Datum
SELECT SYSDATE FROM DUAL;             -- 15.06.2025 10:30:00
SELECT TRUNC(SYSDATE) FROM DUAL;     -- 06/15/2025 00:00:00 (date only)

-- Datumsarithmetik (Einheit: Tage)
SELECT SYSDATE + 7          AS naechste_woche,
       SYSDATE - 30         AS vor_30_tagen,
       HIREDATE + 365       AS nach_einem_jahr
FROM   EMP;

-- Differenz zwischen Daten (Ergebnis in Tagen)
SELECT ENAME,
       SYSDATE - HIREDATE   AS tage_im_unternehmen,
       ROUND((SYSDATE - HIREDATE) / 365, 1) AS jahre
FROM   EMP;

-- MONTHS_BETWEEN: Differenz in Monaten
SELECT ENAME,
       ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE), 1) AS monate
FROM   EMP;

-- ADD_MONTHS: Monate addieren
SELECT HIREDATE,
       ADD_MONTHS(HIREDATE, 6)    AS plus_6_monate,
       ADD_MONTHS(HIREDATE, -3)   AS minus_3_monate
FROM   EMP;

-- LAST_DAY: Letzter Tag des Monats
SELECT SYSDATE,
       LAST_DAY(SYSDATE)   AS letzter_tag
FROM   DUAL;

-- NEXT_DAY: Next day of the week
SELECT NEXT_DAY(SYSDATE, 'MONDAY')   AS naechster_montag
FROM   DUAL;

-- EXTRACT: Teile extrahieren
SELECT EXTRACT(YEAR  FROM HIREDATE) AS jahr,
       EXTRACT(MONTH FROM HIREDATE) AS monat,
       EXTRACT(DAY   FROM HIREDATE) AS tag
FROM   EMP;

8.4 Data type conversion

-- TO_CHAR: Zahl / Datum → Zeichenkette
SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY')          AS datum,
       TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI')  AS datum_zeit,
       TO_CHAR(SYSDATE, 'DAY')                  AS wochentag,
       TO_CHAR(SYSDATE, 'Month YYYY')           AS monat_jahr
FROM   DUAL;

SELECT TO_CHAR(SAL, '99,999.00')    AS formatiertes_gehalt,
       TO_CHAR(SAL, 'L99G999D99')   AS mit_waehrung
FROM   EMP;

-- TO_DATE: Zeichenkette → Datum
SELECT TO_DATE('25.12.2025', 'DD.MM.YYYY')       AS weihnachten,
       TO_DATE('2025-12-25', 'YYYY-MM-DD')        AS iso_datum
FROM   DUAL;

-- TO_NUMBER: Zeichenkette → Zahl
SELECT TO_NUMBER('1.234,56', '9G999D99')   AS zahl
FROM   DUAL;

Important format masks:

9. ZERO treatment

9.1 NVL – replace ZERO

-- NVL(ausdruck, ersatzwert): Falls NULL → Ersatzwert
SELECT ENAME,
       COMM,
       NVL(COMM, 0)             AS comm_ohne_null,
       SAL + NVL(COMM, 0)       AS gesamtverguetung
FROM   EMP;

-- Mit Zeichenketten
SELECT ENAME,
       NVL(TO_CHAR(COMM), 'keine Provision')   AS provision
FROM   EMP;

9.2 NVL2 – case distinction at ZERO

-- NVL2(expression, value_if_not_null, value_if_null)
SELECT ENAME,
       NVL2(COMM, 'Hat Provision', 'Keine Provision')   AS status,
       NVL2(COMM, SAL + COMM, SAL)                       AS gesamtgehalt
FROM   EMP;

9.3 COALESCE – First non-NULL value

-- COALESCE returns the first non-NULL value
SELECT ENAME,
       COALESCE(COMM, SAL * 0.1, 0)   AS bonus
FROM   EMP;
-- If COMM is not NULL → COMM
-- If COMM NULL but SAL*0.1 not NULL → SAL*0.1
-- Wenn beide NULL → 0

9.4 NULLIF – NULL if equal

-- NULLIF(a, b): Wenn a = b → NULL, sonst a
SELECT ENAME,
       NULLIF(COMM, 0)   AS comm_bereinigt
FROM   EMP;
-- Provision von 0 wird zu NULL (Turner hatte COMM=0)

10. GROUP BY and aggregate functions

10.1 Aggregate functions

Aggregate functions calculate a value for multiple rows:

-- Without GROUP BY: across all lines
SELECT COUNT(*)          AS anzahl_mitarbeiter,
       SUM(SAL)          AS summe_gehaelter,
       AVG(SAL)          AS durchschnittsgehalt,
       MAX(SAL)          AS hoechstes_gehalt,
       MIN(SAL)          AS niedrigstes_gehalt,
       MAX(HIREDATE)     AS spaeteste_einstellung
FROM   EMP;

10.2 COUNT – count the number

-- Number of lines (including ZERO)
SELECT COUNT(*) FROM EMP;               -- 14

-- Number of non-null values ​​in COMM
SELECT COUNT(COMM) FROM EMP;            -- 4 (only Salesman have COMM)

-- Anzahl verschiedener Berufe
SELECT COUNT(DISTINCT JOB) FROM EMP;   -- 5

10.3 GROUP BY – Aggregate by groups

-- Average salary per department
SELECT DEPTNO,
       COUNT(*)      AS anzahl,
       AVG(SAL)      AS avg_gehalt,
       MAX(SAL)      AS max_gehalt,
       SUM(SAL)      AS sum_gehalt
FROM   EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

-- Ergebnis:
-- DEPTNO  ANZAHL  AVG_GEHALT  MAX_GEHALT  SUM_GEHALT
-- ------  ------  ----------  ----------  ----------
-- 10           3      2916.67        5000        8750
-- 20           5      2175.00        3000       10875
-- 30           6      1566.67        2850        9400

10.4 GROUP BY Rules

Important rule: Any column in SELECT that is not an aggregate function must be** in GROUP BY!

-- RICHTIG:
SELECT DEPTNO, JOB, COUNT(*), AVG(SAL)
FROM   EMP
GROUP BY DEPTNO, JOB;

-- FALSE: ENAME is not in GROUP BY and is not an aggregate function
SELECT DEPTNO, ENAME, COUNT(*)
FROM   EMP
GROUP BY DEPTNO;
-- ORA-00979: not a GROUP BY expression

10.5 GROUP BY with multiple columns

-- Per department and profession
SELECT DEPTNO,
       JOB,
       COUNT(*)   AS anzahl,
       SUM(SAL)   AS summe
FROM   EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

-- Ergebnis:
-- DEPTNO  JOB        ANZAHL  SUMME
-- ------  ---------  ------  -----
-- 10      CLERK           1   1300
-- 10      MANAGER         1   2450
-- 10      PRESIDENT       1   5000
-- 20      ANALYST         2   6000
-- ...

11. HAVING – Filter groups

11.1 Difference WHERE / HAVING

WHERE:   Filtert einzelne Zeilen VOR dem Gruppieren
         Darf keine Aggregatfunktionen enthalten
HAVING:  Filtert Gruppen NACH dem Gruppieren
         Darf Aggregatfunktionen enthalten

-- HAVING: Only departments with more than 3 employees
SELECT DEPTNO,
       COUNT(*)   AS anzahl,
       AVG(SAL)   AS avg_gehalt
FROM   EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3;

-- Kombination WHERE + GROUP BY + HAVING
-- Only CLERK and SALESMAN, then only groups with average salary > 1000
SELECT JOB,
       COUNT(*)   AS anzahl,
       AVG(SAL)   AS avg_gehalt
FROM   EMP
WHERE  JOB IN ('CLERK', 'SALESMAN')    -- Filter rows first
GROUP BY JOB                            -- Dann gruppieren
HAVING AVG(SAL) > 1000                 -- Then filter groups
ORDER BY avg_gehalt DESC;              -- Dann sortieren

11.2 Typical errors and corrections

-- FALSE: Aggregate function in WHERE
SELECT DEPTNO, AVG(SAL)
FROM   EMP
WHERE  AVG(SAL) > 2000   -- ORA-00934: group function is not allowed here
GROUP BY DEPTNO;

-- RICHTIG: Aggregatfunktion in HAVING
SELECT DEPTNO, AVG(SAL)
FROM   EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 2000;

-- INCORRECT: Non-aggregate column without GROUP BY
SELECT DEPTNO, COUNT(*)
FROM   EMP;              -- ORA-00937: not a single-group group function

-- RICHTIG:
SELECT DEPTNO, COUNT(*)
FROM   EMP
GROUP BY DEPTNO;

11.3 Complete SELECT structure

SELECT   spalten / aggregatfunktionen    -- 5. Was ausgeben?
FROM     tabelle                          -- 1. Woher?
WHERE    zeilenbedingung                  -- 2. Which lines?
GROUP BY gruppierungsspalten             -- 3. Wie gruppieren?
HAVING   gruppenbedingung                -- 4. Which groups?
ORDER BY sortierspalten;                 -- 6. Wie sortieren?

12. Summary and outlook

DQL overview

-- Complete example:
-- Professions with more than 1 employee, without PRESIDENT,
-- sortiert nach Durchschnittsgehalt absteigend

SELECT   JOB,
         COUNT(*)        AS anzahl,
         ROUND(AVG(SAL)) AS avg_gehalt,
         MIN(SAL)        AS min_gehalt,
         MAX(SAL)        AS max_gehalt
FROM     EMP
WHERE    JOB <> 'PRESIDENT'
GROUP BY JOB
HAVING   COUNT(*) > 1
ORDER BY avg_gehalt DESC;

DQL checklist

SELECT-Grundstruktur und Auswertungsreihenfolge kennen
DISTINCT korrekt einsetzen
Aliase mit/ohne AS und Anführungszeichen
Arithmetische Ausdrücke und NULL-Problem
WHERE-Operatoren: =, <>, <, >, BETWEEN, IN, LIKE, IS NULL
AND / OR / NOT und Priorität (Klammern!)
ORDER BY: ASC/DESC, mehrere Spalten, NULLS FIRST/LAST
Zeichenkettenfunktionen: UPPER/LOWER, SUBSTR, LENGTH, ...
Numerische Funktionen: ROUND, TRUNC, MOD
Datumsfunktionen: SYSDATE, ADD_MONTHS, MONTHS_BETWEEN
Konvertierung: TO_CHAR, TO_DATE, TO_NUMBER
NULL-Funktionen: NVL, NVL2, COALESCE, NULLIF
Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN
GROUP BY: Regel – alle Nicht-Aggregat-Spalten in GROUP BY!
HAVING: Unterschied zu WHERE

Typical exam tasks

-- 1. All employees in department 20, sorted by salary in descending order
SELECT ENAME, JOB, SAL
FROM   EMP
WHERE  DEPTNO = 20
ORDER BY SAL DESC;

-- 2. Annual salary including commission for all employees
SELECT ENAME,
       SAL * 12 + NVL(COMM, 0) * 12   AS jahresverguetung
FROM   EMP
ORDER BY jahresverguetung DESC;

-- 3. Departments with average salary over 2500
SELECT DEPTNO, ROUND(AVG(SAL), 2) AS avg_sal
FROM   EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 2500;

-- 4. Number of employees per profession, only professions with more than 2 people
SELECT JOB, COUNT(*) AS anzahl
FROM   EMP
GROUP BY JOB
HAVING COUNT(*) > 2
ORDER BY anzahl DESC;

-- 5. Employees hired in 1981
SELECT ENAME, HIREDATE
FROM   EMP
WHERE  EXTRACT(YEAR FROM HIREDATE) = 1981
ORDER BY HIREDATE;

Outlook: Next topics

• Joins: Join multiple tables (INNER JOIN, OUTER JOIN, SELF JOIN)
• Subqueries: Subqueries in WHERE and FROM
• DML: INSERT, UPDATE, DELETE, MERGE
• DDL: CREATE TABLE, ALTER TABLE, Constraints
• PL/SQL: Procedures, functions, triggers

HTL Pinkafeld – IF/IT | Oracle SQL | 11th grade