-- Numer zestawu..............: D
-- Imię i nazwisko............: Marcin Chwedziak
-- ---------------------------------------------

-- ZADANIE 1

SELECT
first_name, last_name, salary
FROM employees
WHERE
  (TO_CHAR(hire_date, 'yyyy') = 1987 AND salary < 1000) OR
  (TO_CHAR(hire_date, 'yyyy') > 1993)

-- ZADANIE 2

SELECT
first_name, last_name,
(CASE
  WHEN salary>6000 THEN 'TAK'
  ELSE 'NIE'
  END) AS salary_info
FROM employees;

-- ZADANIE 3

SELECT
d.department_id, round(avg(e.salary), 2) AS avg_salary, j.job_title
FROM departments d
LEFT JOIN employees e ON
  e.department_id = e.department_id
LEFT JOIN jobs j ON
  j.job_id = e.job_id
GROUP BY d.department_id, j.job_id, j.job_title
ORDER BY d.department_id, j.job_id ASC

-- ZADANIE 4

SELECT
d.department_id, j.job_title,  ROUND(MIN(e.salary),2) AS min_salary, ROUND(MAX(e.salary),2) AS max_salary
FROM departments d
LEFT JOIN employees e ON
  e.department_id = d.department_id
RIGHT JOIN jobs j ON
  j.job_id = e.job_id
GROUP BY
  d.department_id, j.job_title
ORDER BY
  j.job_title, d.department_id

-- ZADANIE 5

SELECT
last_name
FROM employees
WHERE
  hire_date > (
    SELECT hire_date
    FROM employees
    WHERE
      first_name = 'Alexander' AND
      last_name = 'Hunold'
    )

-- ZADANIE 6

SELECT
last_name
FROM employees
WHERE
  job_id = (
    SELECT job_id
    FROM employees
    WHERE
      first_name = 'David' AND
      last_name = 'Austin'
    )

-- ZADANIE 7

SELECT
e.last_name, e1.last_name
FROM employees e
LEFT JOIN employees e1 ON
  e1.job_id = e.job_id
WHERE
  e1.last_name != e.last_name
GROUP BY
  e.last_name, e1.last_name
ORDER BY
  e.last_name ASC

-- ZADANIE 8

SELECT e.first_name, e.last_name, e.hire_date, l.city
FROM employees e
LEFT JOIN departments d ON
  d.department_id = e.department_id
LEFT JOIN locations l ON
  l.location_id = d.location_id
WHERE
  d.department_id IN (
    SELECT department_id
    FROM employees
    WHERE
      first_name LIKE '%k%' OR
      last_name LIKE '%k%'
    )

-- ZADANIE 9

SELECT
first_name, last_name, salary
FROM employees
WHERE
  salary < (
    SELECT salary
    FROM employees
    WHERE
      last_name = 'Davies'
    )

-- ZADANIE 10

SELECT e.first_name, e.last_name, e.salary, l.country_id
FROM employees e
LEFT JOIN departments d ON
  d.department_id = e.department_id
LEFT JOIN locations l ON
  l.location_id = d.location_id
WHERE
  e.salary = (
    SELECT MIN(e1.salary)
    FROM employees e1
    LEFT JOIN departments d1 ON
      d1.department_id = e1.department_id
    LEFT JOIN locations l1 ON
      l1.location_id = d1.location_id
    WHERE
      l1.country_id = l.country_id
    )

-- ZADANIE 11

UPDATE employees SET commission_pct = 0.15
WHERE commission_pct IS NULL AND
  (employee_id IN (
    SELECT e.employee_id FROM employees e
    LEFT JOIN departments d ON d.department_id = e.department_id
    LEFT JOIN locations l ON l.location_id = d.location_id
    WHERE l.country_id = 'CA'
  ))

-- ZADANIE 12

UPDATE employees SET salary = NVL((
  SELECT MAX(e.salary)
    FROM employees e
    LEFT JOIN departments d ON
      d.department_id = e.department_id
    LEFT JOIN locations l ON
      l.location_id = d.location_id
    WHERE
    l.city = 'Seattle' AND e.job_id = 'IT_PROG'
  ),salary)