Ściągawka — Cała Składnia

-- ROLLUP — hierarchia sum (dół do góry)
SELECT department_id, job_id, SUM(salary)
FROM hr.employees
GROUP BY ROLLUP(department_id, job_id);
-- wiersze: per (dept+job) + per dept + grand total

-- CUBE — wszystkie możliwe kombinacje
GROUP BY CUBE(department_id, job_id);
-- dodaje też: per job (bez dept)

-- GROUPING SETS — ręcznie wybierz kombinacje
GROUP BY GROUPING SETS(
  (department_id, job_id),
  (department_id),
  ()
);

-- Rozróżnij NULL-rollup od NULL-z-danych
SELECT
  CASE WHEN GROUPING(department_id) = 1
       THEN 'WSZYSTKIE' ELSE TO_CHAR(department_id) END AS dept,
  SUM(salary)
FROM hr.employees
GROUP BY ROLLUP(department_id);

-- Drzewo od góry w dół: PRIOR na kolumnie rodzica
SELECT
  LPAD(' ', (LEVEL-1)*2) || last_name AS tree,
  LEVEL,
  SYS_CONNECT_BY_PATH(last_name, '/') AS path
FROM hr.employees
START WITH manager_id IS NULL          -- korzenie = bez menedżera
CONNECT BY PRIOR employee_id = manager_id;  -- PRIOR na rodzicu
-- Czytaj: "employee_id RODZICA = manager_id DZIECKA"

-- Filtrowanie:
WHERE  job_id = 'CLERK'          -- usuwa wiersze, dzieci zostają
AND    job_id = 'CLERK'          -- w CONNECT BY: przycina całą gałąź
HAVING LEVEL <= 3                -- głębokość max 3

-- Ranking
RANK()        OVER (PARTITION BY dept ORDER BY sal DESC)  -- luki po remisie: 1,1,3
DENSE_RANK()  OVER (PARTITION BY dept ORDER BY sal DESC)  -- bez luk: 1,1,2
ROW_NUMBER()  OVER (PARTITION BY dept ORDER BY sal DESC)  -- unikalne: 1,2,3

-- TOP N per group:
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
  FROM hr.employees e
) WHERE rn = 1;   -- najlepiej zarabiający w każdym dziale

-- LAG / LEAD
LAG(salary, 1, 0)  OVER (ORDER BY hire_date)  -- poprzednia pensja (default=0)
LEAD(salary, 1)    OVER (ORDER BY hire_date)  -- następna pensja

-- Suma kumulacyjna
SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Grand total w każdym wierszu
SUM(salary) OVER ()

-- LAST_VALUE — UWAGA NA PUŁAPKĘ!
-- Domyślna ramka z ORDER BY: RANGE UNBOUNDED PRECEDING TO CURRENT ROW
-- "Ostatnia wartość" = bieżący wiersz! Naprawienie:
LAST_VALUE(salary) OVER (
  PARTITION BY dept ORDER BY salary
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- ← wymagane!
)

-- INSERT ALL — jeden wiersz może trafić do wielu tabel
INSERT ALL
  WHEN salary > 10000 THEN INTO high_sal (id, sal) VALUES (employee_id, salary)
  WHEN department_id = 50 THEN INTO dept50 (id)   VALUES (employee_id)
  ELSE INTO others (id) VALUES (employee_id)
SELECT employee_id, salary, department_id FROM hr.employees;

-- INSERT FIRST — zatrzymuje się po pierwszym dopasowaniu
INSERT FIRST
  WHEN salary > 10000 THEN INTO tier_a VALUES (employee_id, salary)
  WHEN salary > 5000  THEN INTO tier_b VALUES (employee_id, salary)
  ELSE                     INTO tier_c VALUES (employee_id, salary)
SELECT employee_id, salary FROM hr.employees;

-- CTAS — kopiuje strukturę + dane, NIE kopiuje PK/FK/CHECK
CREATE TABLE emp_copy AS
SELECT * FROM hr.employees WHERE department_id = 50;

-- Pusta kopia (tylko struktura):
CREATE TABLE emp_empty AS SELECT * FROM hr.employees WHERE 1=2;

-- Pełny MERGE
MERGE INTO hr.employees t
USING (
  SELECT employee_id, salary, department_id FROM staging_updates
) s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET t.salary        = s.salary,
             t.department_id = s.department_id
  -- DELETE WHERE t.salary < 1000   ← opcjonalne: usuń przy spełnieniu warunku
WHEN NOT MATCHED THEN
  INSERT (employee_id, salary, department_id)
  VALUES (s.employee_id, s.salary, s.department_id);

-- SCD Typ 2 — zachowaj historię (nowy wiersz + zamknij stary)
MERGE INTO dim_employee t
USING staging s ON (t.employee_id = s.employee_id AND t.is_current = 'Y')
WHEN MATCHED THEN
  UPDATE SET t.valid_to = SYSDATE, t.is_current = 'N'
WHEN NOT MATCHED THEN
  INSERT (employee_id, name, salary, valid_from, valid_to, is_current)
  VALUES (s.employee_id, s.name, s.salary, SYSDATE, DATE '9999-12-31', 'Y');

-- Krok 1: MV LOG na tabeli bazowej (wymagany dla FAST)
CREATE MATERIALIZED VIEW LOG ON hr.employees
  WITH ROWID, PRIMARY KEY, SEQUENCE
  INCLUDING NEW VALUES;

-- Krok 2: Tworzenie MV
CREATE MATERIALIZED VIEW mv_dept_stats
  BUILD IMMEDIATE          -- od razu oblicz (DEFERRED = przy pierwszym REFRESH)
  REFRESH FAST ON COMMIT   -- FAST wymaga MV LOG
  ENABLE QUERY REWRITE     -- Oracle używa MV automatycznie
AS
  SELECT department_id, COUNT(*) cnt, SUM(salary) total_sal
  FROM hr.employees
  GROUP BY department_id;

-- Ręczne odświeżanie
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_STATS', 'C');  -- C=COMPLETE, F=FAST

-- Słowniki danych
SELECT * FROM user_mviews;
SELECT * FROM user_mview_logs;

LOAD DATA
INFILE 'pracownicy.csv'
BADFILE  'pracownicy.bad'
DISCARDFILE 'pracownicy.dsc'
INTO TABLE hr.employees_load
APPEND                        -- INSERT | REPLACE | TRUNCATE | APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
  employee_id,
  first_name,
  last_name,
  hire_date DATE "YYYY-MM-DD"
)

-- Wywołanie:
-- sqlldr userid=hr/hr control=load.ctl log=load.log

CREATE TABLE ext_employees (
  employee_id  NUMBER,
  first_name   VARCHAR2(50),
  last_name    VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
  )
  LOCATION ('pracownicy.csv')
)
REJECT LIMIT UNLIMITED;

-- Tylko SELECT — INSERT/UPDATE/DELETE → ORA-30657

-- Eksport schematu HR
expdp hr/hr DIRECTORY=dpump_dir DUMPFILE=hr_export.dmp SCHEMAS=HR LOGFILE=hr.log

-- Import do innego schematu
impdp system/sys DIRECTORY=dpump_dir DUMPFILE=hr_export.dmp
      REMAP_SCHEMA=HR:HR_TEST LOGFILE=import.log

-- Eksport tylko tabeli
expdp hr/hr DIRECTORY=dpump_dir DUMPFILE=emp.dmp TABLES=HR.EMPLOYEES