Oracle Academy 1 (moje)

/* ------------------------------ QUIZ 1 ----------------------------------*/

/* 6 */
SELECT employee_id, last_name, job_id, hire_date "STARTDATE"
FROM employees;

/* 7 */
SELECT DISTINCT job_id
FROM employees;

/* 8 */
SELECT employee_id "Emp #", last_name "Employee", job_id "Job", hire_date "Hire Date"
FROM employees;

/* 9 */
SELECT last_name || ',' || job_id AS "Employee and Title"
FROM employees;

/* 10 */
SELECT employee_id || ',' || first_name || ',' || last_name || ',' || email || ',' || phone_number
                   || ',' || job_id || ',' || hire_date
                   || ',' || salary || ',' || department_id
AS "THE_OUTPUT"
FROM employees;

/* ------------------------------ PART 2 ----------------------------------*/
/* WHERE */

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

-- Wielkosc liter ma znaczenie
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen';

-- Jak wpiszemy '17-FEB-96' to potraktuje to jako stringa, wiec musimy wpisywac zgodnie z formatem
SELECT last_name
FROM employees
WHERE hire_date = '96/02/17'; --'96/02/17'


-- Przedzial obustronnie domkniety, wiec 2500 i 3500 tez wliczy
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

SELECT employee_id, last_name, manager_id
FROM employees;

-- IN wymienia
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100,101,201);

-- NOT zaprzeczenie / dopelnienie zbioru
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id NOT IN (100,101,201);

-- LIKE naklada maske.
-- % oznacza to samo, co * w windowsie (dowolny ciag znakow)
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

-- _ oznacza dowolny JEDEN znak
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

-- ESCAPE definiuje znak ucieczki. Tak jak w latechu aby napisac $ musimy dac \$, gdzie ESCAPE '\'
SELECT job_id
FROM employees
WHERE job_id LIKE '__\_%' ESCAPE '\';

-- Aby napisac ' nalezy napisac podwojnie ''
-- Ewentualnie SELECT q'[ czyli ' zamieniamy na [
/*

SELECT 'Plutal's' FROM dual'; - blad
SELECT 'Plutal''s' FROM dual'; -- '' wyswietli '
SELECT q'< Plural's;
*/


SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NOT NULL;

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id LIKE '%MAN%';

-- NOT W DWOCH MIEJSCACH MOZE BYC ( patrz na 2 przyklady nizej)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id NOT LIKE '%MAN%';

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND NOT job_id LIKE '%MAN%';

-- Zmiana AND na OR
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

/*
KOLEJNOSC WYKONYWANIA OPERATOROW
1. Arytmetyczne
2. Konkatenacji (string)
3. Porownywanie warunkow
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. Nie rowne
7. NOT warunek logiczny
8. AND -||-
9. OR -||-
*/

-- najpierw wykona sie AND, potem OR zgodnie z powyzszym
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_PRES'
AND salary > 15000

-- do sprawdzenia
SELECT last_name, job_id, salary
FROM employees
WHERE (job_id = 'SA_REP'
OR job_id = 'AD_PRES')
AND salary > 15000;


/* SORTOWANIE */
/* ASC - rosnaco (domyslnie)
   DESC - malejaco */

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

-- annsal jak obojetna wielkosc, 'annsal' jak sie liczy
SELECT employee_id, last_name, salary*12 annsal
FROM employees
WHERE salary*12>1000 -- jak tu damy annsal to nie zadziala!!!
-- W WHERE NIE MOZNA STOSOWAC ALIASOW!!!!
ORDER BY salary*12;

-- Sortuje po 3ciej kolumnie
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY 3;

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY department_id, salary DESC;

-- domyslnie NULLE beda na koncu (traktowane jako najwieksze)
SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC;

SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS FIRST; -- NULLE NA POCZATKU

SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST; -- NULLE NA KONCU

/* ZASTEPOWANIE ZMIENNYCH PRZY POMOCY & */

-- &nazwa definiuje zmienna, ktora w sposob interantywny zostanie zwrocona do operatora z prosba o wstawienie wartosc z klawiatury
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num;


-- PRZY STRINGACH MUSIMY DAC '' po przypisaniu, bo musielibysmy wpisywac 'nazzwa' w okienku

-- && nie pyta drugi raz, &
SELECT employee_id, last_name, job_id, &&column_name1
FROM employees
ORDER BY &column_name1;

-- wyswietla zmienne
DEFINE
-- definiuje zmienne
DEFINE employee_num = 200;

UNDEFINE column_name1;
DEFINE

SET VERIFY ON

SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id = &employee_num;

/* ------------------------------ QUIZ 2 ----------------------------------*/

/* 1 */
SELECT last_name, salary
FROM employees
WHERE salary > 12000;

/* 2 */
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;

/* 3 */
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

/* 4 */
SELECT last_name, job_id, hire_date
FROM employees
WHERE last_name IN('Matos','Taylor')
ORDER BY hire_date;

/* 5 */
SELECT last_name, department_id
FROM employees
WHERE department_id IN(20,50)
ORDER BY last_name;

/* 6 */
SELECT last_name Employee, salary "Monthly Salary"
FROM employees
WHERE salary BETWEEN 5000 AND 12000
      AND department_id IN(20,50);

/* 7 */
SELECT last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '94/01/01' AND '94/12/31';

/* 8 */
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

/* 9 */
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;

/* 10 */
SELECT last_name, salary
FROM employees
WHERE salary > &salary_num;

/* 11 */
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE manager_id = &manager_id_num
ORDER BY &order_condition;

/* 12 */
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

/* 13 */
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

/* 14 */
SELECT last_name, job_id, salary
FROM employees
WHERE (job_id='ST_CLERK' OR job_id='SA_REP')
  AND salary NOT IN(2500,3500,7000);

/* 15 */
SELECT last_name Employee, salary "Monthly Salary", commission_pct
FROM employees
WHERE salary BETWEEN 5000 AND 12000
AND department_id IN(20,50) AND commission_pct = 0.2;



/* ------------------------------ PART 3 ----------------------------------*/
/* FUNKCJE JEDNOWIERSZOWE ( zwracaja jeden wynik dla jednego wiersza)      */

SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins'; -- NIE ZWROCI WYNIKU BO WIELKOSC LITER MA ZNACZENIE

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';

-- Dziala tez dla zmiennych
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = LOWER('&Dane');

/* Funkcje znakowe
CONCAT('Hello, 'World')       HelloWorld
SUBSTR('HelloWorld',1,5)      Hello
LENGHT('HelloWorld')          10
INSTR('HelloWorld','W')       6 (wyszukuje pozycje litery)
LPAD(salary,10,'*')           *****24000 (wyrownanie do lewej)
RPAD                          24000***** (wyrownanie do prawej)
REPLACE('JACK and JUE','J','BL')    BLACK and BLUE
LTRIM usuwanie literki z lewej
RTRIM
*/

SELECT employee_id, CONCAT(first_name, last_name) NAME,
       job_id,
       LENGTH (last_name),
       INSTR (last_name,'a') "Contains 'a'?",
       LPAD(salary,10,'*'),
       RPAD(salary,10,'*')
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';


/* Funkcje numeryczne
ROUND(45.926, 2)            45.93 (Zaokragla do okreslonej liczby cyfr po przecinku)
TRUNC(45.926, 2)            45.92 (Ucina)
MOD(1600, 300)              100 (Modulo)
*/

SELECT ROUND (45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1)
FROM dual;

SELECT TRUNC (45.923, 2), TRUNC(45.923), TRUNC(45.923, -1)
FROM dual;

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';

/* DATY

RR - format przypisuje WIEK
YY - format 19YY

*/
SELECT sysdate FROM dual;

SELECT hire_date
FROM employees;

-- teraz zmieniamy format
SELECT sysdate FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS';
ALTER SESSION SET NLS_LANGUAGE = 'ENGLISH';

SELECT hire_date, hire_date+1, hire_date+5/24, hire_date+(20/(24*60)) -- doda 1 dzien, 20/(24*60) dodaje 20 min
FROM employees;

SELECT last_name, ROUND((SYSDATE-hire_date)/7,2) AS WEEKS
FROM employees
WHERE department_id = 90;

/* Funkcje manipulacji data
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY         ost dzien miesiaca
ROUND            zaokraglenie daty
TRUNC            przyciecie
*/

SELECT MONTHS_BETWEEN(sysdate,hire_date)
FROM employees;

SELECT ADD_MONTHS(sysdate,1), NEXT_DAY(sysdate, 'FRIDAY'), LAST_DAY(sysdate)
FROM dual;

SELECT ROUND(sysdate,'MONTH'),ROUND(sysdate,'YEAR'),TRUNC(sysdate,'MONTH'),TRUNC(sysdate,'YEAR')
FROM dual;

-- z powrotem na stary format
ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD';
ALTER SESSION SET NLS_LANGUAGE = 'ENGLISH';

/*
sysdate - czas SERWERA
current_date - czas klienta
*/
SELECT sysdate, systimestamp, CURRENT_DATE
FROM dual;


/* Funkcje konwersji
CHARACTER -> TO_DATE
DATE -> TO_CHAR

CHARACTER -> TO_NUMBER
NUMBER -> TO_CHAR

YYYY
YEAR (slownie)
MM
MONTH (pelna nazwa)
MON
DY
DAY
DD (liczba)
HH
HH24
MI
SS
SSSSS - sekund do polnocy
*/

-- TO_CHAR dla daty
SELECT sysdate, TO_CHAR(sysdate,'J')
FROM dual;

SELECT hire_date, TO_CHAR(hire_date, 'fmDD HH24:MI:SS Month YYYY') AS HIREDATE
FROM employees;

-- TO_CHAR dla liczb
/*
9 Reprezentuje liczbe
0 Zmusza do wyswietlenia 0
$
L lokalny symbol waluty
. miejsca dziesietne
, przecinek jako dzielnik tysiecy
*/

SELECT salary, TO_CHAR(salary, '$99,999.00') AS SALARY,  TO_CHAR(salary, '99.999EEEE') AS SALARY2
FROM employees;

-- z CHAR na NUMBER/DATE
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

-- ZAGNIEZDZANIE FUNKCJI
SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employees
WHERE department_id = 60;

/* Funkcje pracujace na NULLACH (ogolne)
NVL(commission_pct,0)     Jak napotka Nulla w commission_pct to wstawi tam 0 (zwraca sama siebie lub 0 )
NVL2(commission_pct,'SAL+COMM','SAL') income    Jak napotka NULL to zwroci SAL+COMM, jak nie to SAL, przy czym dwa ost. arrg musza byc tego samego typu
NULLIF(war1,war2)      zwraca NULL jezeli sa takie same wartosci, jezeli rozne to zwraca 1 wartosc
COALESCE(war1,war2,do_zwrocenia)    TESTUJE wartosc i jak jest NULL to TESTUJE DALSZE REKURENCYJNIE


WSZEDZIE TAKIE SAME TYPY ARGUMENTOW!!!!!!!!!!!!!!

*/

SELECT last_name, salary,commission_pct,  NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct, NVL2(commission_pct,salary+commission_pct,salary) income
FROM employees WHERE department_id IN (50,80);

SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) RESULT
FROM employees;

SELECT last_name, employee_id, COALESCE(commission_pct,manager_id,1)
FROM employees;

/* Wyrazenia warunkowe
CASE
DECODE
*/

-- Wersja CASE
SELECT last_name, job_id, salary,
        CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
                    WHEN 'ST_CLERK' THEN 1.15*salary
                    WHEN 'SA_REP' THEN 1.20*salary
        ELSE salary
        END AS "REVISED_SALARY"
FROM employees;

-- Wersja DECODE
SELECT last_name, job_id, salary,
        DECODE (job_id, 'IT_PROG' ,1.10*salary,
                        'ST_CLERK',1.15*salary,
                        'SA_REP'  ,1.20*salary,
                        salary) REVISED_SALARY
FROM employees;


/* ------------------------------ QUIZ 3 ----------------------------------*/

/* 1 */
SELECT sysdate AS "Date"
FROM dual;

/* 2,3 */
SELECT employee_id, last_name, salary, ROUND(salary+(salary*0.155)) "New Salary"
FROM employees;

/* 4 */
SELECT employee_id, last_name, salary, ROUND(salary+(salary*0.155)) "New Salary", ROUND(salary+(salary*0.155))-salary AS Increase
FROM employees;

/* 5 */
SELECT INITCAP(last_name) Name,LENGTH(last_name) LENGTH
FROM employees
--WHERE last_name LIKE ('J%' OR 'A%' OR 'M%')
WHERE last_name LIKE 'J%' OR last_name LIKE 'A%' OR last_name LIKE 'M%'
ORDER BY last_name;

SELECT INITCAP(last_name) Name ,LENGTH(last_name) LENGTH
FROM employees
--WHERE last_name LIKE ('J%' OR 'A%' OR 'M%')
WHERE last_name LIKE INITCAP('&pierwsza_litera%');
ORDER BY last_name;

/* 6 */
SELECT last_name, ROUND(MONTHS_BETWEEN(sysdate,hire_date)) AS MONTHS_WORKED
FROM employees;

/* 7 */
SELECT last_name, LPAD(salary,15,'$') AS SALARY
FROM employees;

/* 8 */
SELECT last_name || ' ' || LPAD(' ',salary/1000,'*') AS EMPLOYEES_AND_THEIR_SALARIES
FROM employees;

/* 9 */
SELECT last_name, TRUNC((sysdate-hire_date)/7,0) AS TENURE
FROM employees
ORDER BY TENURE DESC;

/* ------------------------------ QUIZ 4 ----------------------------------*/

/* 1 */
SELECT last_name || 'earns' || TO_CHAR(salary,'$00,000.00') || ' monthly but wants' || TO_CHAR(salary*3,'$00,000.00') AS "Dream Salaries"
FROM employees;

/* 2 */
SELECT last_name, hire_date, INITCAP(TO_CHAR((NEXT_DAY(ADD_MONTHS(hire_date,6),'MONDAY')),'DAY,')) || 'the ' ||
                            INITCAP(TO_CHAR((NEXT_DAY(ADD_MONTHS(hire_date,6),'MONDAY')),'DDspth')) || ' of ' ||
                            INITCAP(TO_CHAR((NEXT_DAY(ADD_MONTHS(hire_date,6),'MONDAY')),'MONTH'))
AS Review
FROM employees;

/* 3 */
SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAY
FROM employees;

/* 4 */
SELECT last_name, NVL(TO_CHAR(commission_pct),TO_CHAR('No Commission')) COMM
FROM employees;

/* 5 */
SELECT job_id, DECODE (job_id, 'AD_PRES' ,'A',
                        'ST_MAN','B',
                        'IT_PROG'  ,'C',
                        'SA_REP', 'D',
                        'ST_CLERK', 'E',
                        0) GRADE
FROM employees;

/* 6 */
SELECT job_id, CASE job_id WHEN 'IT_PROG' THEN 'A'
                    WHEN 'ST_MAN' THEN 'B'
                    WHEN 'IT_PROG' THEN 'C'
                    WHEN 'SA_REP' THEN 'D'
                    WHEN 'ST_CLERK' THEN 'E'
        ELSE '0'
        END AS GRADE
FROM employees;