/*
-- ex1
CREATE TABLE departamente AS
SELECT
	deptno id_dep,
	dname den_dep,
	loc locatie
FROM dept;

-- ex2
CREATE TABLE angajati AS
SELECT
	empno id_ang,
	ename nume,
	job functie,
	mgr id_sef,
	hiredate data_ang,
	sal salariu,
	comm comision,
	deptno id_dep
FROM emp;

-- ex3
SELECT * FROM angajati;

-- ex4
SELECT id_dep, den_dep FROM departamente;

-- ex5
SELECT id_ang||'-'||nume angajat,
	functie,
	data_ang
FROM angajati
ORDER BY id_ang DESC;

-- ex6
SELECT id_ang||'-'||nume angajat,
	functie,
	salariu+nvl(comision, 0) AS "venit lunar",
	'         ' AS semnatura
FROM angajati
ORDER BY id_dep;

-- ex7
SELECT nume, 'cu functie', functie
FROM angajati;

-- ex8
SELECT den_dep||' are codul '||id_dep "Lista departamente"
FROM departamente
ORDER BY den_dep;



-- ex9
SELECT
	a.id_ang ecuson,
	a.nume,
	a.data_ang "Data Angajarii",
	a.salariu
FROM angajati a
WHERE id_dep = 10;

-- ex10
SELECT
	id_dep "Nr. departament",
	nume,
	functie,
	salariu,
	data_ang "Data Angajarii"
FROM angajati
WHERE LOWER(functie) = 'manager'
ORDER BY id_dep;

-- ex11
SELECT
	id_dep "Nr. departament",
	nume,
	functie,
	salariu,
	data_ang "Data Angajarii"
FROM angajati
WHERE data_ang BETWEEN '01-MAY-1981' AND '01-DEC-1981'
ORDER BY 1, 2 DESC;


-- ex12
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu+nvl(comision, 0) AS "venit lunar"
FROM angajati
WHERE id_ang IN (7499, 7902, 7876)
ORDER BY nume;


-- ex13
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	data_ang AS "DATA ANGAJARII"
FROM angajati
WHERE data_ang LIKE '%80';



-- ex14
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	data_ang AS "DATA ANGAJARII"
FROM angajati
WHERE nume LIKE 'F%' AND functie LIKE '_______';


-- ex15
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	comision
FROM angajati
WHERE
	(comision = 0 OR comision is NULL) AND
	id_dep = 20
ORDER BY nume;


-- ex16
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	comision
FROM angajati
WHERE
	(comision != 0 OR comision is NOT NULL) AND
	functie = UPPER('salesman')
ORDER BY nume;



-- ex17
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	id_dep departament
FROM angajati
WHERE
	salariu >= 1500 AND
	functie = UPPER('manager') OR
	functie = UPPER('analyst')
ORDER BY nume;
*/


/*
exercitiu: selectati toti angajatii cu functia analyst care au
salariu peste 2000 si toti managerii al caror nume incepe cu j
iar a 4 litera este e. se vor sorta alfabetic
*/



SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	id_dep departament
FROM angajati
WHERE
	(functie = UPPER('analyst') AND salariu >= 2000) OR
	(functie = UPPER('manager') AND nume LIKE 'J__E%')
ORDER BY nume ASC;








/*
-- ex1
CREATE TABLE departamente AS
SELECT
	deptno id_dep,
	dname den_dep,
	loc locatie
FROM dept;

-- ex2
CREATE TABLE angajati AS
SELECT
	empno id_ang,
	ename nume,
	job functie,
	mgr id_sef,
	hiredate data_ang,
	sal salariu,
	comm comision,
	deptno id_dep
FROM emp;

-- ex3
SELECT * FROM angajati;

-- ex4
SELECT id_dep, den_dep FROM departamente;

-- ex5
SELECT id_ang||'-'||nume angajat,
	functie,
	data_ang
FROM angajati
ORDER BY id_ang DESC;

-- ex6
SELECT id_ang||'-'||nume angajat,
	functie,
	salariu+nvl(comision, 0) AS "venit lunar",
	'         ' AS semnatura
FROM angajati
ORDER BY id_dep;

-- ex7
SELECT nume, 'cu functie', functie
FROM angajati;

-- ex8
SELECT den_dep||' are codul '||id_dep "Lista departamente"
FROM departamente
ORDER BY den_dep;



-- ex9
SELECT
	a.id_ang ecuson,
	a.nume,
	a.data_ang "Data Angajarii",
	a.salariu
FROM angajati a
WHERE id_dep = 10;

-- ex10
SELECT
	id_dep "Nr. departament",
	nume,
	functie,
	salariu,
	data_ang "Data Angajarii"
FROM angajati
WHERE LOWER(functie) = 'manager'
ORDER BY id_dep;

-- ex11
SELECT
	id_dep "Nr. departament",
	nume,
	functie,
	salariu,
	data_ang "Data Angajarii"
FROM angajati
WHERE data_ang BETWEEN '01-MAY-1981' AND '01-DEC-1981'
ORDER BY 1, 2 DESC;


-- ex12
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu+nvl(comision, 0) AS "venit lunar"
FROM angajati
WHERE id_ang IN (7499, 7902, 7876)
ORDER BY nume;


-- ex13
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	data_ang AS "DATA ANGAJARII"
FROM angajati
WHERE data_ang LIKE '%80';



-- ex14
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	data_ang AS "DATA ANGAJARII"
FROM angajati
WHERE nume LIKE 'F%' AND functie LIKE '_______';


-- ex15
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	comision
FROM angajati
WHERE
	(comision = 0 OR comision is NULL) AND
	id_dep = 20
ORDER BY nume;


-- ex16
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	comision
FROM angajati
WHERE
	(comision != 0 OR comision is NOT NULL) AND
	functie = UPPER('salesman')
ORDER BY nume;



-- ex17
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	id_dep departament
FROM angajati
WHERE
	salariu >= 1500 AND
	functie = UPPER('manager') OR
	functie = UPPER('analyst')
ORDER BY nume;
*/


/*
exercitiu: selectati toti angajatii cu functia analyst care au
salariu peste 2000 si toti managerii al caror nume incepe cu j
iar a 4 litera este e. se vor sorta alfabetic
*/



SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	id_dep departament
FROM angajati
WHERE
	(functie = UPPER('analyst') AND salariu >= 2000) OR
	(functie = UPPER('manager') AND nume LIKE 'J__E%')
ORDER BY nume ASC;








/*
-- ex1
CREATE TABLE departamente AS
SELECT
	deptno id_dep,
	dname den_dep,
	loc locatie
FROM dept;

-- ex2
CREATE TABLE angajati AS
SELECT
	empno id_ang,
	ename nume,
	job functie,
	mgr id_sef,
	hiredate data_ang,
	sal salariu,
	comm comision,
	deptno id_dep
FROM emp;

-- ex3
SELECT * FROM angajati;

-- ex4
SELECT id_dep, den_dep FROM departamente;

-- ex5
SELECT id_ang||'-'||nume angajat,
	functie,
	data_ang
FROM angajati
ORDER BY id_ang DESC;

-- ex6
SELECT id_ang||'-'||nume angajat,
	functie,
	salariu+nvl(comision, 0) AS "venit lunar",
	'         ' AS semnatura
FROM angajati
ORDER BY id_dep;

-- ex7
SELECT nume, 'cu functie', functie
FROM angajati;

-- ex8
SELECT den_dep||' are codul '||id_dep "Lista departamente"
FROM departamente
ORDER BY den_dep;



-- ex9
SELECT
	a.id_ang ecuson,
	a.nume,
	a.data_ang "Data Angajarii",
	a.salariu
FROM angajati a
WHERE id_dep = 10;

-- ex10
SELECT
	id_dep "Nr. departament",
	nume,
	functie,
	salariu,
	data_ang "Data Angajarii"
FROM angajati
WHERE LOWER(functie) = 'manager'
ORDER BY id_dep;

-- ex11
SELECT
	id_dep "Nr. departament",
	nume,
	functie,
	salariu,
	data_ang "Data Angajarii"
FROM angajati
WHERE data_ang BETWEEN '01-MAY-1981' AND '01-DEC-1981'
ORDER BY 1, 2 DESC;


-- ex12
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu+nvl(comision, 0) AS "venit lunar"
FROM angajati
WHERE id_ang IN (7499, 7902, 7876)
ORDER BY nume;


-- ex13
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	data_ang AS "DATA ANGAJARII"
FROM angajati
WHERE data_ang LIKE '%80';



-- ex14
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	data_ang AS "DATA ANGAJARII"
FROM angajati
WHERE nume LIKE 'F%' AND functie LIKE '_______';


-- ex15
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	comision
FROM angajati
WHERE
	(comision = 0 OR comision is NULL) AND
	id_dep = 20
ORDER BY nume;


-- ex16
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	comision
FROM angajati
WHERE
	(comision != 0 OR comision is NOT NULL) AND
	functie = UPPER('salesman')
ORDER BY nume;



-- ex17
SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	id_dep departament
FROM angajati
WHERE
	salariu >= 1500 AND
	functie = UPPER('manager') OR
	functie = UPPER('analyst')
ORDER BY nume;
*/


/*
exercitiu: selectati toti angajatii cu functia analyst care au
salariu peste 2000 si toti managerii al caror nume incepe cu j
iar a 4 litera este e. se vor sorta alfabetic
*/



SELECT
	id_ang AS ecuson,
	nume,
	functie,
	salariu,
	id_dep departament
FROM angajati
WHERE
	(functie = UPPER('analyst') AND salariu >= 2000) OR
	(functie = UPPER('manager') AND nume LIKE 'J__E%')
ORDER BY nume ASC;