--1--
SELECT * FROM angajati
WHERE LOWER(substr(nume, -1, 1)) = 'e';
--2--
UPDATE rand_comenzi SET
pret = 0.9 * pret
WHERE nr_comanda IN (SELECT nr_comanda FROM comenzi WHERE EXTRACT(MONTH FROM DATA) = 1 AND EXTRACT(YEAR FROM DATA) = 2000);

--3--
SELECT denumire_departament, d.id_departament, MIN(salariul), COUNT(id_angajat)
FROM departamente d, angajati a
WHERE d.id_departament = a.id_departament
GROUP BY denumire_departament, d.id_departament  --doar pt functiile de grup)
HAVING COUNT(salariul) > 5;

--4--
SELECT EXTRACT(YEAR FROM DATA), COUNT(nr_comanda)
FROM comenzi
WHERE EXTRACT(YEAR FROM DATA) BETWEEN 1997 AND 1999
GROUP BY EXTRACT(YEAR FROM DATA);

--5--
SELECT p.id_produs, denumire_produs, nr_comanda
FROM produse p, rand_comenzi n
WHERE n.id_produs (+) = p.id_produs;

SELECT c.nr_comanda, SUM(pret*cantitate), DATA
FROM rand_comenzi r, comenzi c
WHERE r.nr_comanda = c.nr_comanda AND EXTRACT(MONTH FROM DATA) = 7
GROUP BY c.nr_comanda, DATA;

--6--
SELECT a.id_functie, denumire_functie, COUNT(id_angajat) FROM angajati a, functii f
WHERE a.id_functie = f.id_functie
GROUP BY a.id_functie, denumire_functie
HAVING avg(salariul) BETWEEN 5000 AND 10000;