Oracle Academy 2

FUNKCJE GRUPOWE */

/*
AVG - srednia
COUNT - zliczanie wierszy z zapytania
STDEV
SUM
VARIANCE


Funkcje grupowe tylko w klauzuli SELECT (jak narazie)
*/

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;


/* COUNT */

-- COUNT(*) zliczy wszystko
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

-- COUNT(expr) zliczy tylko rozne od null
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;

SELECT COUNT(commission_pct)
FROM employees;

SELECT COUNT(DISTINCT department_id) -- NULLA pomija
FROM employees;

SELECT AVG(commission_pct), SUM(commission_pct)/COUNT(*), SUM(commission_pct)/COUNT(commission_pct) -- AVG przeklamuje, bo pomija NULLA
FROM employees;

SELECT AVG(NVL(commission_pct, 0))
FROM employees;

/* GRUPOWANIE DANYCH */

-- jak grupujemy po column, to column moze sie pojawic w SELECT, czyli
-- SELECT column, group_func(column)

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

SELECT job_id, MAX(salary)
FROM employees
GROUP BY job_id;

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id
ORDER BY department_id;

SELECT department_id, job_id, SUM(salary)
FROM employees
HAVING SUM(salary) > 10000 AND department_id > 40
GROUP BY department_id, job_id
-- HAVING sluzy do tego, by umieszczac warunki na funkcjach grupowych, czyli SUM(salary) > 10000 w WHERE nie zadziala
-- z WHERE do HAVING mozna
-- z HAVING do WHERE NIE MOZNA
ORDER BY department_id;

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id; -- group by tyczy sie avg(salary)

SELECT COUNT(employee_id), SUM(DECODE((TO_CHAR(hire_date,'yyyy')),'1995',1,0)) AS "1995" -- decode zwraca 1 lub 0
FROM employees;

------------------------------ QUIZ 5 -------------------------------------

-- 1
-- T,

SELECT MAX(salary) "Maximum", MIN(salary) "Minimum", SUM(salary) "Sum", ROUND(AVG(salary)) "Average"
FROM employees;

SELECT job_id, MAX(salary) "Maximum", MIN(salary) "Minimum", SUM(salary) "Sum", ROUND(AVG(salary)) "Average"
FROM employees
GROUP BY job_id;

SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;

SELECT job_id, COUNT(*)
FROM employees
WHERE job_id = '&jobid'
GROUP BY job_id;

SELECT COUNT(DISTINCT manager_id) AS "Number of Managers"
FROM employees;

SELECT (MAX(salary)-MIN(salary)) AS DIFFERENCE
FROM employees;

SELECT manager_id, MIN(salary)
FROM employees
HAVING MIN(salary)>6000 AND manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY MIN(salary) DESC;

SELECT COUNT(employee_id) TOTAL,
       SUM(DECODE((TO_CHAR(hire_date,'yyyy')),'1995',1,0)) AS "1995",
       SUM(DECODE((TO_CHAR(hire_date,'yyyy')),'1996',1,0)) AS "1996",
       SUM(DECODE((TO_CHAR(hire_date,'yyyy')),'1997',1,0)) AS "1997",
       SUM(DECODE((TO_CHAR(hire_date,'yyyy')),'1998',1,0)) AS "1998"
FROM employees;

SELECT job_id "Job", SUM(DECODE(department_id,20,salary,NULL)) AS "Dept 20",
                     SUM(DECODE(department_id,50,salary,NULL)) AS "Dept 50",
                     SUM(DECODE(department_id,80,salary,NULL)) AS "Dept 80",
                     SUM(DECODE(department_id,90,salary,NULL)) AS "Dept 90",
                     SUM(salary) AS "Total"
FROM employees
GROUP BY job_id;

----------------------------------- PRACTICE 6 ---------------------------------

/*
NATURAL JOIN (laczy kolumny z dwoch tabel, ktore maja takie same nazwy), jak NAZWY SA TAKIE SAME ALE TYP DANYCH JEST INNY TO BLAD
*/

SELECT department_id, department_name, location_id, city
FROM departments
NATURAL JOIN locations;

SELECT last_name, department_name
FROM employees
NATURAL JOIN departments;

/* USING */


SELECT employee_id, last_name, location_id, department_id
FROM employees
JOIN departments USING (department_id);

/* ON */

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id);

SELECT employee_id, last_name, e.department_id, location_id -- PRZY KOLUMNACH KTORE SA WSPOLNE MUSI BYC LITERIA, NIE MA ZNACZENIA CZY BIERZEMY KLUCZ Z e czy z d
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id);

SELECT e.last_name, d.department_name, l.city
FROM employees e
JOIN departments d
ON (d.department_id = e.department_id)
JOIN locations l
ON d.location_id = l.location_id;

SELECT employee_id, last_name, manager_id
FROM employees;

SELECT e.last_name AS PRACOWNIK, m.last_name AS SZEF
FROM employees e JOIN employees m
ON (m.employee_id = e.manager_id)
ORDER BY e.last_name;

/* ZLACZENIA NIEROWNOSCIOWE */

SELECT e.last_name, e.salary, j.grade
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

/* OUTER JOIN */

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

-- ZADANIE
/* Wyswietl nazwy departamentow w ktorych nikt nie jest zatrudniony */

/* CROSS JOIN (Iloczyn kartezjanski) */

SELECT *
FROM employees
CROSS JOIN departments;

------------------------------------------------- QUIZ 6 -----------------------

SELECT location_id, street_address, city, state_province, country_name
FROM locations
NATURAL JOIN countries;

SELECT last_name, department_id, department_name
FROM employees
NATURAL JOIN departments;

SELECT last_name, job_id, department_id, department_name
FROM employees
NATURAL JOIN departments
NATURAL JOIN locations
WHERE city LIKE 'Toronto';

SELECT e.last_name Employee, e.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);

SELECT e.last_name Employee, e.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"
FROM employees e LEFT JOIN employees m
ON (e.manager_id = m.employee_id)
ORDER BY e.employee_id;

SELECT e.department_id "DEPARTMENT", c.last_name "EMPLOYEE", e.last_name "COLLEAGUE" -- do poprawy
FROM employees e JOIN employees c
ON (c.department_id = e.department_id)
WHERE e.last_name NOT LIKE c.last_name
ORDER BY c.department_id;

SELECT e.last_name, e.job_id, department_name, salary, g.grade "GRADE_LEVEL"
FROM employees e
NATURAL JOIN departments
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

SELECT e.last_name, e.hire_date, d.hire_date --97/01/29
FROM employees e
JOIN employees d
ON (e.hire_date < d.hire_date)
WHERE d.last_name LIKE 'Davies'
ORDER BY e.hire_date;

SELECT e.last_name, e.hire_date, m.last_name, m.hire_date
FROM employees e
JOIN employees m
ON (e.manager_id = m.employee_id)
WHERE e.hire_date < m.hire_date;

/* Wyswietl nazwy departamentow w ktorych nikt nie jest zatrudniony */
SELECT d.department_name, e.last_name
FROM departments d
LEFT JOIN employees e
ON (d.department_id = e.department_id)
WHERE last_name IS NULL;


-------------------------------  SKLADNIA ORACLE -------------------------------

/* zamiast ON jest WHERE */
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

SELECT employee_id, city, department_name
FROM employees e, departments d, locations l
WHERE (d.department_id = e.department_id)
  AND (d.location_id = l.location_id);

-- zamiast RIGHT OUTER JOIN     e.department_id (+)= d.department_id
-- + tam gdzie nulle

SELECT e.last_name, e.department_id, d.department_name
FROM employees e,departments d
WHERE (e.department_id (+)= d.department_id);

-- LEFT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e,departments d
WHERE (e.department_id = d.department_id (+));

-- UNIA (FULL OUTER JOIN)
SELECT e.last_name, e.department_id, d.department_name
FROM employees e,departments d
WHERE (e.department_id (+)= d.department_id);
UNION ------------------------------------------------------
SELECT e.last_name, e.department_id, d.department_name
FROM employees e,departments d
WHERE (e.department_id = d.department_id (+));

SELECT last_name, department_name
FROM employees, departments;

---------------------------- QUIZ 6 (skladnia Oraclowa) ------------------------

SELECT l.location_id, l.street_address, l.city, l.state_province, c.country_name
FROM locations l, countries c
WHERE (l.country_id = c.country_id);

SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE (e.department_id = d.department_id);

SELECT e.last_name, e.job_id, e.department_id, d.department_name, city
FROM employees e, departments d, locations l
WHERE l.city LIKE 'Toronto';

SELECT e.last_name Employee, e.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"
FROM employees e, employees m
WHERE (e.manager_id = m.employee_id);

SELECT e.last_name Employee, e.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"
FROM employees e, employees m
WHERE (e.manager_id = m.employee_id (+))
ORDER BY e.employee_id;

SELECT e.department_id "DEPARTMENT", c.last_name "EMPLOYEE", e.last_name "COLLEAGUE" -- do poprawy
FROM employees e, employees c
WHERE (c.department_id = e.department_id)
AND e.last_name NOT LIKE c.last_name
ORDER BY c.department_id;

SELECT e.last_name, e.job_id, d.department_name, e.salary, g.grade "GRADE_LEVEL"
FROM employees e, departments d, job_grades g
WHERE(e.salary BETWEEN g.lowest_sal AND g.highest_sal);

SELECT e.last_name, e.hire_date --97/01/29
FROM employees e, employees d
WHERE(e.hire_date > d.hire_date)
AND d.last_name LIKE 'Davies'
ORDER BY e.hire_date;

SELECT e.last_name, e.hire_date, m.last_name, m.hire_date
FROM employees e, employees m
WHERE (e.manager_id = m.employee_id)
AND e.hire_date < m.hire_date;

/* Wyswietl nazwy departamentow w ktorych nikt nie jest zatrudniony */
SELECT d.department_name, e.last_name
FROM departments d, employees e
WHERE (d.department_id = e.department_id (+))
AND last_name IS NULL;

---------------------------------- PODZAPYTANIA --------------------------------
SELECT *
FROM (
  SELECT d.department_name, e.last_name
  FROM departments d, employees e
  WHERE (d.department_id = e.department_id (+))
  AND last_name IS NULL
);


SELECT last_name, salary
FROM employees
WHERE salary > 11000;

SELECT last_name, salary
FROM employees
WHERE salary > (
                SELECT salary
                FROM employees
                WHERE last_name = 'Abel'
                );


SELECT employee_id, last_name
FROM employees
WHERE salary IN (
                SELECT MIN(salary)
                FROM employees
                GROUP BY department_id
                );

/*
IN
ANY
ALL
*/

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
                SELECT salary
                FROM employees
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
                SELECT salary
                FROM employees
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';



-- PROBLEM WARTOSCI NULL W PODZAPYTANIACH

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN ( -- na NOT IN nie moze byc wartosc NULL)
                          SELECT mgr.manager_id
                          FROM employees mgr
                          WHERE mgr.manager_id IS NOT NULL);

SELECT last_name, salary, department_id
FROM employees
WHERE (salary,department_id) IN
              (
              SELECT MAX(salary),department_id
              FROM employees
              GROUP BY department_id
              )
ORDER BY department_id;

SELECT last_name, hire_date
FROM employees
WHERE (job_id, hire_date)
  IN (SELECT job_id, MAX(hire_date)
      FROM employees
      GROUP BY job_id);
  /*
SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE a. salary =
              (
              SELECT MAX(b.salary)
              FROM employees b
              WHERE a.department_id = b.department_id
              )
ORDER BY department_id;  */


------------------------------------- QUIZ 7 -----------------------------------

SELECT e.last_name, e.hire_date
FROM employees e
WHERE e.department_id = (
                      SELECT d.department_id
                      FROM employees d
                      WHERE d.last_name LIKE '&name'
                      )
                      AND e.last_name NOT LIKE '&name';

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
                SELECT AVG(salary)
                FROM employees
                )
ORDER BY salary;

SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
                      SELECT department_id
                      FROM employees
                      WHERE last_name LIKE '%u%'
                      );

SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
                        SELECT department_id
                        FROM departments
                        WHERE location_id = 1700
                        );

SELECT last_name, salary
FROM employees
WHERE manager_id IN (
                    SELECT employee_id
                    FROM employees
                    WHERE last_name LIKE 'King');

SELECT department_id, last_name, job_id
FROM employees
WHERE department_id = (
                      SELECT department_id
                      FROM departments
                      WHERE department_name LIKE 'Executive');

SELECT employee_id, last_name, salary -- i ktorzy pracuja w departamencie z pracownikami ktorzy maja 'u' w nazwisku
FROM employees
WHERE salary > (
                SELECT AVG(salary)
                FROM employees
                )
AND department_id IN (
                      SELECT department_id
                      FROM employees
                      WHERE last_name LIKE '%u%'
                      );
ORDER BY salary;

------------------------------ OPERATORY ZBIOROWE ------------------------------

-- UNION bez duplikatow
SELECT  employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

-- UNION ALL z duplikatami (200sty pracownik)
SELECT  employee_id, job_id
FROM employees
UNION ALL
SELECT employee_id, job_id
FROM job_history
ORDER BY employee_id;

SELECT  employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history
ORDER BY employee_id;

SELECT  employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history
ORDER BY employee_id;

SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees;


-- kompatybilnosc naglowkow
SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehoude location"
FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", state_province
FROM locations;

SELECT employee_id, job_id, salary
FROM employees
UNION
SELECT employee_id, job_id, NULL
FROM job_history
ORDER BY employee_id; -- w ORDER tylko kolumny z pierwszego selecta

------------------------------------ JEZYK DML ---------------------------------
/*
INSERT INTO tabela [kolumna]
VALUES (value [, value...]);
*/

INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (280, 'Public Relations', 100, 1700);

SELECT *
FROM departments;

CREATE TABLE Test AS
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE last_name LIKE 'King';

DELETE Test;

SELECT *
FROM Test;

INSERT INTO Test(employee_id, last_name, salary, commission_pct)
  SELECT employee_id, last_name, salary, commission_pct
  FROM employees
  WHERE job_id LIKE '%REP%';

SELECT *
FROM employees
WHERE employee_id = 113;

UPDATE employees
SET department_id = 50
WHERE employee_id = 113;

UPDATE Test -- TERAZ MUSI BYC ZATWIERDZENIE, bo TYLKO MY TO WIDZIMY
SET salary = 50;

ROLLBACK;
COMMIT;

----------------------------------  DDL ---------------------------------------

CREATE TABLE hire_dates
            (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);

INSERT INTO hire_dates VALUES (10, to_date('10-10-2012','dd-mm-yyyy'));
SELECT *
FROM hire_dates;

INSERT INTO hire_dates VALUES (20, to_date('20-12-2002','dd-mm-yyyy'));
SELECT *
FROM hire_dates;

INSERT INTO hire_dates VALUES (100, NULL);
INSERT INTO hire_dates VALUES (150); -- err
INSERT INTO hire_dates VALUES (200,DEFAULT);
INSERT INTO hire_dates(id) VALUES (300);

CREATE TABLE employees_lab ( employee_id NUMBER(6) CONSTRAINT emp_employee_id_lab PRIMARY KEY
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
, email VARCHAR2(25)
  CONSTRAINT emp_email_lab NOT NULL
  CONSTRAINT emp_email_lab_1 UNIQUE
, phone_number VARCHAR2(20)
, hire_date DATE
  CONSTRAINT emp_hire_date_lab NOT NULL
, job_id VARCHAR2(10)
  CONSTRAINT emp_job_lab NOT NULL
, salary NUMBER(8,2)
  CONSTRAINT emp_salary_lab CHECK(salary>0)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
  CONSTRAINT emp_manager_fk_lab REFERENCES employees_lab (employee_id)
, department_id NUMBER(4)
  CONSTRAINT emp_dept_fk_lab REFERENCES
    departments(department_id));


INSERT INTO employees_lab
VALUES (1,'Jan','Kowalski','jankow@wp.pl','1234567',to_date('10-03-1992','dd-mm-yyyy'),5,6500,0.25,NULL,60);

INSERT INTO employees_lab
VALUES (2,'Krystian','Pudlik','kp@wp.pl','7777777',to_date('17-04-1993','dd-mm-yyyy'),2,12000,0.5,1,60);

SELECT * FROM employees_lab;