CREATE TABLE departament (
    id_depart NUMBER(2) PRIMARY KEY,
    den_depart VARCHAR2(10),
    id_responsabil NUMBER(4)
);

CREATE TABLE angajati (
    id_ang NUMBER(4) PRIMARY KEY,
    nume VARCHAR2(20),
    id_depart NUMBER(4),
    salariu NUMBER(7,2),
    functie VARCHAR2(20),
    CONSTRAINT FK_ANGAJATI FOREIGN KEY(id_depart) REFERENCES departament(id_depart)
);

CREATE TABLE plafoane (
    den_plafon VARCHAR(10),
    sal_min NUMBER(7,2),
    sal_max NUMBER(7,2)
);

SET LINE 600

INSERT INTO departament(id_depart, den_depart, id_responsabil) VALUES(10, 'IT', 1001);
INSERT INTO departament(id_depart, den_depart, id_responsabil) VALUES(20, 'HR', 2001);
INSERT INTO departament(id_depart, den_depart, id_responsabil) VALUES(30, 'Marketing', 3001);
INSERT INTO departament(id_depart, den_depart, id_responsabil) VALUES(40, 'Productie', 4001);
INSERT INTO departament(id_depart, den_depart, id_responsabil) VALUES(50, 'Empty', NULL);

INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(1001, 'Gigel', 10, 180.99, 'sef A');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(2001, 'Popescu', 20, 130.99, 'sef

B');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(3001, 'Pop', 30, 102.99, 'sef C');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(4001, 'Popa', 40, 135.99, 'sef D');

INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(1002, 'Dan', 10, 92.99, 'inginer');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(1003, 'Daniel', 20, 34.99, 'inginer');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(1004, 'Danut', 30, 100.99,

'inginer');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(1005, 'Dima', 40, 110.99,

'inginer');

INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(2002, 'Geo', 10, 92.99, 'ing');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(3002, 'George', 20, 180.99,

'inginer');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(4002, 'Costel', 30, 160.99, 'ing');
INSERT INTO angajati(id_ang, nume, id_depart, salariu, functie) VALUES(2003, 'Diana', 40, 110.99,

'inginer');

INSERT INTO plafoane(den_plafon, sal_min, sal_max) VALUES('mare', 150.00, 200.00);
INSERT INTO plafoane(den_plafon, sal_min, sal_max) VALUES('mic', 50.00, 150.00);

SELECT * FROM angajati;
--SELECT nume, den_depart, A.functie FROM angajati A, departament D WHERE A.id_depart=D.id_depart

AND D.den_depart='IT';
--SELECT A.nume, D.den_depart, A.functie FROM angajati A JOIN departament D ON

A.id_depart=D.id_depart WHERE D.den_depart='IT';
--SELECT nume, den_depart, functie FROM angajati NATURAL JOIN departament WHERE

den_depart='IT';
--SELECT A.nume, P.den_plafon FROM angajati A, plafoane P WHERE A.salariu>=P.sal_min AND

A.salariu<=P.sal_max;
--SELECT D.den_depart, A.nume, A.salariu FROM angajati A, departament D WHERE A.id_depart(+)

=D.id_depart;

--SELECT nume, salariu, D.den_depart FROM angajati A, departament D, plafoane P WHERE

A.id_depart=D.id_depart AND A.functie='inginer' AND P.den_plafon='mare' AND A.salariu>=P.sal_min

AND A.salariu<=P.sal_max;

--SELECT A.nume, A.salariu, D.den_depart FROM angajati A RIGHT OUTER JOIN departament D ON

A.id_depart=D.id_depart;
--ACCEPT nume_depart_citit PROMPT 'Introdu te rog denumirea departamentului:';
--SELECT A.nume, A.salariu, S.nume, S.salariu FROM angajati A, departament D, angajati S WHERE

A.id_depart=S.id_depart AND A.id_depart=D.id_depart AND D.id_responsabil=S.id_ang AND

D.den_depart='&nume_depart_citit' AND A.salariu > S.salariu;

--ACCEPT nume_ang_citit PROMPT 'Introdu te rog numele angajatului:';
--SELECT A.nume, D.den_depart, A.salariu, P.den_plafon FROM angajati A, departament D, angajati SOLO,

plafoane P WHERE SOLO.nume='&nume_ang_citit' AND D.id_depart!=SOLO.id_depart AND

A.id_depart=D.id_depart AND P.sal_min<A.salariu AND P.sal_max>A.salariu;

SELECT EXTRACT(DAY FROM SYSDATE) AS AZI FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;

--daca VAL1 e nenul il lasa neschimbat iar daca este null se inlocuieste cu Val2
--NVL(VAL1, VAL2)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE angajati ADD(prima NUMBER(7,2));
UPDATE angajati SET prima=0.5*salariu WHERE MOD(id_ang, 2)=0;
SELECT * FROM angajati;

ACCEPT nume_ang_citit PROMPT 'Introdu te rog numele angajatului:';
SELECT A.nume, D.den_depart, A.salariu, P.den_plafon FROM angajati A, departament D, angajati SOLO,

departament D_sef, angajati SEF, plafoane P WHERE SOLO.nume='&nume_ang_citit' AND

D_sef.id_depart=SOLO.id_depart AND SEF.id_ang=D_sef.id_responsabil AND D.id_depart=SEF.id_depart

AND A.id_depart=D.id_depart AND P.sal_min<A.salariu AND P.sal_max>A.salariu;

SELECT ROUND(SYSDATE-TO_DATE('25-12-2018', 'DD-MM-YYYY'), 'ZI') FROM DUAL;

SELECT A.nume, D.den_depart, A.salariu + A.prima, SEF.nume, SEF.salariu + NVL(SEF.prima, 0) FROM

angajati A, departament D, angajati SEF WHERE A.prima IS NOT NULL AND A.id_depart=D.id_depart

AND D.id_responsabil=SEF.id_ang;

SELECT LAST_DAY(SYSDATE) FROM dual;
SELECT TRANSLATE('a cd  efg hij', ' ', '_') FROM dual;
SELECT SUBSTR('123456789', 3, 2) FROM dual;
SELECT CEIL(23.6) FROM dual;

DROP TABLE plafoane;
DROP TABLE angajati;
DROP TABLE departament;