bd lab 2 r

http://193.226.51.37/down/SQL/Laborator2_SQL_an2.pdf
--lab2
--ex1
SELECT first_name || ' ' || last_name || ' castiga ' || salary
|| ' lunar, dar doreste ' || salary*3 AS "Salariu ideal"
FROM employees;

--ex2
SELECT first_name, UPPER(last_name), LENGTH(last_name)
FROM employees
WHERE last_name LIKE 'J%' OR last_name LIKE 'M%' OR last_name LIKE '___a%'
ORDER BY LENGTH(last_name) DESC;

--ex2b
SELECT first_name, UPPER(last_name), LENGTH(last_name)
FROM employees
WHERE substr(last_name,1,1) = 'J'
OR substr(last_name,1,1) = 'M'
OR substr(last_name,3,1) = 'a'
ORDER BY LENGTH(last_name) DESC;

--ex3
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE LOWER(TRIM(first_name)) = 'steven';

--ex4
SELECT employee_id AS "ID", last_name AS "Nume", department_id AS "Dep. ID", LENGTH(last_name) AS "Lungime nume", instr(last_name,'a') AS "Pozitie"
FROM employees
WHERE last_name LIKE '%e';

--ex5
SELECT employee_id, first_name, last_name, round(sysdate - hire_date)
FROM employees
WHERE MOD(round(sysdate - hire_date),7) = 0;

--ex6 nu merge fmm
SELECT employee_id, last_name, salary + 0.0
FROM employees;

--ex7
SELECT last_name AS "Nume angajat", rpad(hire_date,12) AS "Data angajarii"
FROM employees
WHERE commission_pct > 0;

--ex8
SELECT sysdate + 30, to_char(sysdate + 30, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

--ex9
SELECT last_day('01-DEC-2018') - sysdate
FROM dual;

--ex10
SELECT to_char(sysdate + 0.5, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

SELECT to_char(sysdate + 1/24/12, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

--ex11
SELECT last_name || ' ' || first_name AS "Angajat", hire_date, salary, next_day(add_months(hire_date,6),'Monday') AS "Negociere"
FROM employees;

--ex12
SELECT last_name, round(months_between('25-NOV-2018',hire_date))
FROM employees
ORDER BY months_between('25-NOV-2018',hire_date) DESC ;

--ex13
SELECT last_name, hire_date, to_char(trunc(hire_date), 'day')
FROM employees;

--ex14
SELECT last_name, decode(commission_pct,NULL,'Fara comision',commission_pct) AS "Comision"
FROM employees;

--ex15
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary * (1 + nvl(commission_pct,0)) > 10000;

--ex16
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN salary * 1.20
WHEN 'SA_REP' THEN salary * 1.25
WHEN 'SA_MAN' THEN salary * 1.35
ELSE salary
END AS "Salariu renegociat"
FROM employees;

--ex17
SELECT emp.last_name, emp.department_id, dep.department_name
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id;

-ex18
SELECT job_title, department_id
FROM departments JOIN jobs
ON department_id = 30;

--ex19
SELECT emp.last_name, dep.department_name, loc.city
FROM employees emp, departments dep, locations loc
WHERE emp.commission_pct > 0;

--ex19 v2
SELECT emp.last_name, dep.department_name, loc.city
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id
JOIN locations loc
ON dep.location_id = loc.location_id
WHERE commission_pct >0;

--ex20
SELECT last_name, department_name
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id
WHERE UPPER(last_name) LIKE '%A%';

--ex21
SELECT emp.last_name, j.job_title, emp.department_id, dep.department_name, loc.city
FROM employees emp
JOIN departments dep ON emp.department_id = dep.department_id
JOIN jobs j ON j.job_id = emp.job_id
JOIN locations loc ON loc.location_id = dep.location_id
WHERE loc.city = 'Oxford';

--ex22
SELECT emp.employee_id AS "Ang#", emp.last_name AS "Angajat",
man.employee_id AS "Mgr#", man.last_name AS "Manager"
FROM employees emp
JOIN employees man
ON emp.manager_id = man.employee_id;

--ex23
SELECT emp.employee_id AS "Ang#", emp.last_name AS "Angajat",
man.employee_id AS "Mgr#", man.last_name AS "Manager"
FROM employees emp
LEFT JOIN employees man
ON emp.manager_id = man.employee_id;

-ex24
SELECT emp.last_name AS "Angajat" , emp.department_id AS "Departamanet",
col.last_name AS "Colegi"
FROM employees emp
JOIN employees col
ON emp.department_id = col.department_id
WHERE emp.employee_id < col.employee_id;

-ex25
DESC jobs;
SELECT emp.last_name, j.job_id, j.job_title, dep.department_name, emp.salary
FROM employees emp
JOIN jobs j ON emp.job_id = j.job_id
LEFT JOIN departments dep
ON emp.department_id = dep.department_id;

--ex26
SELECT emp.last_name, emp.hire_date
FROM employees emp
JOIN employees gates
ON emp.hire_date > gates.hire_date
WHERE gates.last_name = 'Gates';

--ex27
SELECT emp.last_name AS "Angajat", emp.hire_date AS "Data_ang",
man.last_name AS "Manager", man.hire_date AS "Data_mgr"
FROM employees emp
JOIN employees man ON emp.manager_id = man.employee_id
WHERE emp.hire_date < man.hire_date;