Oracle Academy 1

SELECT department_name
FROM departments;

SELECT *
FROM departments;

SELECT last_name, salary, salary+300
FROM employees;

SELECT last_name, job_id,salary, commission_pct,salary +(salary*commission_pct)
FROM employees;

SELECT last_name AS name, commission_pct comm
FROM employees;

/*" " gdy nazwa sklada sie z 2 slow*/
SELECT last_name AS name, salary*12 AS "Annual Salary"
FROM employees;

SELECT first_name, department_id
FROM employees;

/* Bez powtarzania tych samych ID */
SELECT DISTINCT department_id
FROM employees;

/*W I kolumnie laczy last_name i job_id np: KingAD_PRES*/
SELECT last_name || job_id AS "Employees"
FROM employees;

/* Wyswietla strukture tabeli */
DESCRIBE departments;
DESCRIBE employees;

SELECT employee_id, last_name, job_id, hire_date
FROM employees;

SELECT DISTINCT job_id
FROM employees;

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

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

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

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

SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen';

SELECT last_name
FROM employees
WHERE hire_date = '17-FEB-96';

SELECT hire_date
FROM employees;

SELECT last_name
FROM employees
WHERE hire_date = '96/02/17';

SELECT last_name ,salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

SELECT employee_id , last_name , manager_id
FROM employees;

/* IN - wyswietli tylko z manager ID 100,101,201 */
SELECT employee_id , last_name , salary, manager_id
FROM employees
WHERE manager_id IN (100,101,201);

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

/* Tworzy maske %- dowolny ciag znakow */
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

/* j/w _ - dowolna litera */
SELECT first_name
FROM employees
WHERE first_name LIKE '_o%';

SELECT job_id
FROM employees;

/* Mozna wpisywac specjalne znaki ESCAPE */
SELECT job_id
FROM employees
WHERE job_id LIKE '__\_%' ESCAPE '\';

/* Plural''s <- pierwszy apostrof rozkodowuje */

/* Blad */
/*SELECT 'Plural's' FROM dual; */

/*Poprawnie */
SELECT 'Plural''s' FROM dual;

/*IS NULL - porownanie z NULL */
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%';

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
OR job_id LIKE '%MAN%';

SELECT last_name , job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_PRES'
AND salary >15000;

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

/*Sortowanie*/
SELECT last_name, job_id, department_id , hire_date
FROM employees
ORDER BY hire_date;

/*ASC - rosnaca - domyslnie , DESC - odwrotnie */
SELECT last_name, job_id, department_id , hire_date
FROM employees
ORDER BY hire_date DESC;

/* annsal - bez AS zmiana nazwy , sortuje po polu */
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

/* W where nie mozna stosowac aliasow
SELECT employee_id, last_name, salary*12 annsal
FROM employees
WHERE annsal > 1000
ORDER BY annsal;
*/

/* Posortuje po 3 kolumnie*/
SELECT last_name,job_id,department_id,hire_date
FROM employees
ORDER BY 3;

/* Laczy 2 warunki - najpierw po department_id potem po salary*/
SELECT last_name, department_id , salary
FROM employees
ORDER BY department_id, salary DESC;

/*NULLE piertwsze */
SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS FIRST;

/* NULLE na koncu */
SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

/*Domyslnie - na poczatku */
SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC;

/*Wyswietla prosbe o wpisanie z klawiatury wartosc */
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num;

/*Proszac o stringa w cudzyslowiu*/
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE job_id = '&job_title';

/* &&- nie wyswietla nowego okienka - odwolanie do adresu innej zmiennej */
SELECT employee_id, last_name ,job_id , &&column_name1
FROM employees
ORDER BY &column_name1;

/* Wyswietla jakie zmienne sa zapisane w pamieci */
DEFINE

/*Dodaje zmiennea */
DEFINE employee_num = 200
DEFINE

/*Kasuje zmienna z pamieci */
UNDEFINE empluee_num
DEFINE

UNDEFINE column_name
DEFINE

SET VERIFY ON

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

SELECT last_name, salary
FROM employees
WHERE salary > 12000;

SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;

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

SELECT last_name , job_id, hire_date
FROM employees
WHERE last_name = 'Matos'
OR last_name = 'Taylor'
ORDER BY hire_date;

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

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

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

SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY salary DESC,commission_pct DESC;

SELECT last_name, salary
FROM  employees
WHERE salary>&condition;

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE manager_id = &managerID
ORDER BY &OrderBY;

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

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

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);

SELECT last_name AS Employee, salary AS "Monthly Salary", commission_pct
FROM employees
WHERE salary BETWEEN 5000 AND 12000
AND commission_pct = 0.2;