📋 Podstawy SQL

-- UWAGA: ORDER BY musi być w podzapytaniu, nie na zewnątrz!
SELECT * FROM (
    SELECT employee_id, first_name, last_name, salary
    FROM hr.employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

SELECT employee_id, first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

-- Top 10% najlepiej zarabiających
SELECT * FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

-- LIKE: wzorce tekstowe (% = dowolna liczba znaków, _ = jeden znak)
SELECT first_name, last_name FROM hr.employees
WHERE last_name LIKE 'S%';          -- nazwisko zaczyna się na S
WHERE last_name LIKE '_a%';         -- druga litera to 'a'
WHERE last_name LIKE '%son';        -- kończy się na 'son'

-- BETWEEN: zakres włącznie (oba końce)
WHERE salary BETWEEN 5000 AND 9000; -- salary >= 5000 AND salary <= 9000

-- IN: lista wartości
WHERE job_id IN ('IT_PROG', 'SA_REP', 'FI_ACCOUNT');

-- Kombinacja:
SELECT first_name, last_name, salary, job_id
FROM hr.employees
WHERE last_name LIKE 'S%'
  AND salary BETWEEN 5000 AND 9000
  AND job_id IN ('IT_PROG', 'SA_REP', 'FI_ACCOUNT');

SELECT
    UPPER(last_name)              AS wielkie,      -- KOWALSKI
    LOWER(last_name)              AS male,         -- kowalski
    INITCAP(first_name)           AS pierwsza,     -- Jan
    SUBSTR(first_name, 1, 1)      AS inicjal,      -- J
    INSTR(LOWER(last_name), 'a')  AS poz_a,        -- pozycja litery 'a'
    LENGTH(last_name)             AS dlugosc,
    LPAD(' ', 4*(level-1))        AS wciecie,      -- dla drzew
    first_name || ' ' || last_name AS pelne_imie
FROM hr.employees;

SELECT
    TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date))   AS staz_mies,
    EXTRACT(YEAR FROM hire_date)                AS rok_zatrudnienia,
    TO_CHAR(hire_date, 'yyyy')                  AS rok_tekst,
    TO_CHAR(hire_date, 'DD-MON-YYYY')           AS data_format,
    NVL(commission_pct, 0)                      AS prowizja,
    salary + NVL(commission_pct, 0) * salary   AS pensja_z_premia,
    ROUND(AVG(salary), 2)                       AS zaokraglona_sr
FROM hr.employees;

-- Łączne wynagrodzenie i liczba pracowników w każdym dziale:
SELECT d.department_name,
       NVL(SUM(e.salary), 0)    AS laczne_wynagrodzenie,
       COUNT(e.employee_id)     AS liczba_pracownikow
FROM hr.departments d
LEFT JOIN hr.employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- Średnie wynagrodzenie w firmie (bez grupowania):
SELECT ROUND(AVG(salary), 2) AS srednia_firma
FROM hr.employees;

-- Działy ze średnią > 8000 (HAVING po agregacji):
SELECT d.department_name,
       ROUND(AVG(e.salary), 2) AS srednia
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING AVG(e.salary) > 8000;

-- Pracownicy z premią (commission_pct IS NOT NULL) wg stanowiska:
SELECT j.job_title,
       COUNT(e.employee_id) AS liczba_z_premia
FROM hr.employees e
JOIN hr.jobs j ON j.job_id = e.job_id
WHERE e.commission_pct IS NOT NULL      -- WHERE przed GROUP BY
GROUP BY j.job_title;

-- Średnia długość nazwiska wg działu:
SELECT d.department_name,
       TRUNC(AVG(LENGTH(e.last_name))) AS sr_dlugosc_nazwiska
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

-- Liczba podwładnych każdego managera (self JOIN + GROUP BY):
SELECT m.employee_id AS manager_id,
       m.first_name, m.last_name,
       COUNT(e.employee_id) AS liczba_podwladnych
FROM hr.employees e
JOIN hr.employees m ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.first_name, m.last_name;

-- Skalarne: pracownicy zarabiający więcej niż średnia firmy
SELECT first_name, last_name, salary
FROM hr.employees
WHERE salary > (SELECT AVG(salary) FROM hr.employees);

-- Działy w tym samym mieście co pracownik 100:
SELECT d.department_name
FROM hr.departments d
JOIN hr.locations l ON d.location_id = l.location_id
WHERE l.city = (
    SELECT l2.city
    FROM hr.employees e2
    JOIN hr.departments d2 ON e2.department_id = d2.department_id
    JOIN hr.locations l2 ON d2.location_id = l2.location_id
    WHERE e2.employee_id = 100
);

-- Pracownicy zarabiający więcej niż średnia w swoim dziale
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
WHERE e.salary > (
    SELECT AVG(salary)
    FROM hr.employees
    WHERE department_id = e.department_id  -- korelacja!
);

-- Pracownicy zatrudnieni w tym samym roku co Steven King
SELECT first_name, last_name, hire_date
FROM hr.employees
WHERE EXTRACT(YEAR FROM hire_date) = (
    SELECT EXTRACT(YEAR FROM hire_date)
    FROM hr.employees
    WHERE first_name = 'Steven' AND last_name = 'King'
);

-- Alternatywa z TO_CHAR:
WHERE TO_CHAR(hire_date, 'yyyy') = (
    SELECT TO_CHAR(hire_date, 'yyyy')
    FROM hr.employees WHERE first_name = 'Steven' AND last_name = 'King'
);

-- Pracownik i jego przełożony (LEFT JOIN = wszyscy, JOIN = tylko ci z szefem)
SELECT e.first_name || ' ' || e.last_name AS pracownik,
       m.first_name || ' ' || m.last_name AS przelozony
FROM hr.employees e
LEFT JOIN hr.employees m ON e.manager_id = m.employee_id;

-- Pracownicy zarabiający więcej niż ich bezpośredni przełożony
SELECT e.first_name, e.last_name, e.salary,
       m.salary AS pensja_szefa
FROM hr.employees e
JOIN hr.employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

-- Pracownicy z Europy
SELECT e.first_name, e.last_name, r.region_name
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
JOIN hr.locations l    ON d.location_id  = l.location_id
JOIN hr.countries c    ON l.country_id   = c.country_id
JOIN hr.regions r      ON c.region_id    = r.region_id
WHERE r.region_name = 'Europe';

-- Pracownicy z imieniem + nazwą działu + miastem:
SELECT e.first_name, e.last_name, d.department_name, l.city
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
JOIN hr.locations l ON d.location_id = l.location_id;

-- Działy bez pracowników
SELECT d.department_name, l.city
FROM hr.departments d
JOIN hr.locations l ON d.location_id = l.location_id
WHERE d.department_id NOT IN (
    SELECT department_id FROM hr.employees
    WHERE department_id IS NOT NULL  -- ważne! patrz pułapka poniżej
);

SELECT e.first_name, e.last_name, e.salary, j.min_salary, j.max_salary
FROM hr.employees e
JOIN hr.jobs j ON e.job_id = j.job_id
WHERE e.salary < j.min_salary OR e.salary > j.max_salary;