Oracle Abfragen

Aggregatfunktionen.sql

-- 2. Aggregatfunktionen

-- 1)
SELECT MIN(e.salary),
  MAX(e.salary)
FROM employees e

-- 2)
SELECT AVG(e.salary),
  SUM(e.salary)
FROM employees e

-- 2) Ohne lange Durchschnittszahl:
SELECT TRUNC(AVG(e.salary)),
  SUM(e.salary)
FROM employees e

-- 3)
SELECT TRUNC(AVG(e.salary)),
  SUM(e.salary),
  e.department_id
FROM employees e
GROUP BY e.department_id

-- 4)
SELECT(MAX(e.salary) -MIN(e.salary)) AS
"Unterschied"
FROM employees e

-- 5)
SELECT COUNT(1) AS "Anzahl"
FROM employees e
GROUP BY e.department_id

Mengenoperatoren.sql

-- 4)

-- 4. 
-- 1)
SELECT department_id
FROM departments minus
SELECT department_id
FROM employees
WHERE job_id = 'ST_MAN'

--- (oder:)
SELECT UNIQUE d.department_id
FROM departments d,
  job_history jh,
  jobs j
WHERE d.department_id != jh.department_id OR jh.job_id != j.job_id OR j.job_id != 'ST_MAN'
ORDER BY department_id

-- 2)
SELECT country_id,
  country_name
FROM countries minus
SELECT l.country_id,
  c.country_name
FROM locations l JOIN countries c ON(l.country_id = c.country_id) JOIN departments d ON d.department_id = l.location_id

---

SELECT UNIQUE c.country_id,
  c.country_name
FROM countries c,
  departments d,
  locations l
WHERE d.location_id != l.location_id OR l.country_id != c.country_id
ORDER BY country_id;

-- 3)
SELECT DISTINCT job_id,
  department_id
FROM employees
WHERE department_id = 10
UNION ALL
SELECT DISTINCT job_id,
  department_id
FROM employees
WHERE department_id = 20
UNION ALL
SELECT DISTINCT job_id,
  department_id
FROM employees
WHERE department_id = 50;

--- (oder:)
SELECT j.job_id,
  d.department_id
FROM departments d,
  employees e,
  jobs j
WHERE j.job_id = e.job_id
 AND e.department_id = d.department_id
 AND d.department_id IN(10,   20,   50);

-- 4)
SELECT UNIQUE e.last_name,
  e.department_id,
  to_char(NULL)
FROM employees e
UNION
SELECT UNIQUE to_char(NULL),
  d.department_id,
  d.department_name
FROM departments d;

Skalare Funktionen.sql

-- 1. Skalare Funktionen

-- 1)
select sysdate from dual

-- 2)
select e.employee_id, e.last_name, e.salary, (e.salary * 1.155) as "NeuesGehalt" from employees e

-- 3)
SELECT e.employee_id,
  e.first_name,
  e.last_name
FROM employees e
WHERE e.first_name LIKE 'H%' OR e.first_name LIKE 'I%' OR e.first_name LIKE 'J%' OR e.first_name LIKE 'K%'

-- 4)
SELECT e.*,
  TRUNC(sysdate -e.hire_date) AS
"Dauer der Anstellung"
FROM employees e

-- 5)
SELECT next_day('11.12.1990',   'fri')
FROM dual

Unterabfragen.sql

-- 3. Unterabfragen

-- 1)
SELECT e.employee_id,
  e.last_name,
  e.salary
FROM employees e
WHERE e.salary >
  (SELECT AVG(salary)
   FROM employees)

-- 2)
SELECT *
FROM employees e
WHERE e.manager_id IN
  (SELECT employee_id
   FROM employees
   WHERE last_name = 'King')

-- 3)
SELECT *
FROM departments d
WHERE d.location_id IN
  (SELECT l.location_id
   FROM locations l,
     countries co
   WHERE co.country_id = l.country_id
   AND co.country_name != 'USA')