CREATE TABLE DEPART
(
    ID_DEPART NUMBER(3) PRIMARY KEY,
    DEN_DEPART VARCHAR2(30),
    ID_SEF_DEP NUMBER(3)
);

CREATE TABLE ANGAJATI
(
    ID_ANG NUMBER(3),
    NUME VARCHAR2(30),
    ID_DEPART NUMBER(2),
    SALARIU NUMBER(7,2),
    FUNCTIE VARCHAR2(30),
    CONSTRAINT PK_ANGAJATI PRIMARY KEY (ID_ANG),
    CONSTRAINT FK_ANGAJATI FOREIGN KEY (ID_DEPART) REFERENCES DEPART(ID_DEPART)
);

CREATE TABLE PLAFOANE
(
    DEN_PLAFON VARCHAR2(30),
    SAL_MIN NUMBER(7,2),
    SAL_MAX NUMBER(7,2)
);

INSERT INTO DEPART VALUES (1, 'HR', 10);
INSERT INTO DEPART VALUES (2, 'RESEARCH', 20);
INSERT INTO DEPART VALUES (3, 'IMPLEMENTATION', 30);
INSERT INTO DEPART VALUES (4, 'FRECAT MENTA', 40);


INSERT INTO ANGAJATI VALUES (10, 'BOSSINIO', 1, 99990, 'BOSS');
INSERT INTO ANGAJATI VALUES (11, 'MIHAI', 1, 2, 'PROGRAMATOR');
INSERT INTO ANGAJATI VALUES (12, 'ANCA', 1, 120, 'GINGER');

INSERT INTO ANGAJATI VALUES (20, 'EMI', 2, 990, 'BOSS');
INSERT INTO ANGAJATI VALUES (21, 'ALEX', 2, 720, 'PROGRAMATOR');
INSERT INTO ANGAJATI VALUES (22, 'ALIN', 2, 4420, 'PROGRAMATOR');

INSERT INTO ANGAJATI VALUES (30, 'BOSSULTAN', 3, 99990, 'BOSS');
INSERT INTO ANGAJATI VALUES (31, 'ANDREEA', 3, 80, 'MENAJERA');
INSERT INTO ANGAJATI VALUES (32, 'FLORICA', 3, 9900, 'SECRETARA');

INSERT INTO PLAFOANE VALUES('PLAFON1', 1, 5000);
INSERT INTO PLAFOANE VALUES('PLAFON2', 5001, 99999);

alter table angajati
add
	(bonus number(7,2));

update angajati
set bonus = salariu * 0.5
where
	mod(id_ang,2) = 0;

--1.cate zile au mai ramas pana pe 28 aprilie (paste) -> nr intreg
--2. sa se citeasca o denumire departament si sa se afiseze toti angajatii care nu fac parte din
-- acel departament afisand: nume, venit (sal+bonus), den_depart, den plafon salarial
--3. sa se selecteze de la tastatura un nume de angajat si sa se afiseze toti angajatii care castiga
-- mai mult decat seful sau ( venit > venit sef), afisare: nume, venit, nume_dep
--4. to_char, round, last_day, next_day

--1.
	select round(to_date('28-APR-19', 'dd-mm-yyyy') - to_date(sysdate, 'dd-mm-yyyy'))
	from dual;


--2.

	SELECT A.NUME, round(A.SALARIU + nvl(A.BONUS, 0)), D1.DEN_DEPART, P.DEN_PLAFON
	FROM ANGAJATI A, DEPART D, PLAFOANE P, DEPART D1
	WHERE
    		A.ID_DEPART != D.ID_DEPART
    		AND
    		D.DEN_DEPART = '&DEP'
		AND
    		A.SALARIU >= P.SAL_MIN
    		AND
    		A.SALARIU <= P.SAL_MAX
		AND
		D1.ID_DEPART = A.ID_DEPART;

--3.
	SELECT A2.NUME, round(A2.SALARIU + nvl(A2.BONUS, 0)), D2.DEN_DEPART
	FROM ANGAJATI A, ANGAJATI A2, ANGAJATI S, DEPART D, DEPART D2
	WHERE
    		round(A2.SALARIU + nvl(A2.BONUS, 0)) > round(S.SALARIU + nvl(S.BONUS, 0))
    		AND
    		A.NUME='&NUME'
		AND
		A.ID_DEPART = D.ID_DEPART
		AND
		D.ID_SEF_DEP = S.ID_ANG
		AND
		D2.ID_DEPART = A2.ID_DEPART;

--4.
	select last_day('28-APR-2019') from dual;
	select NEXT_DAY('24-MAR-2014', 'MONDAY') from dual;
	select round(8.89) from dual;
	select to_char(sysdate, 'YYYY-MM-DD') from dual;



DROP TABLE PLAFOANE;
DROP TABLE ANGAJATI;
DROP TABLE DEPART;