Lab 4 [PDF]

Zitiervorschau

1. a) Functiile grup includ valorile NULL in calcule? b) Care este deosebirea dintre clauzele WHERE si HAVING? -------------------------2. Sa se afiseze cel mai mare salariu, cel mai mic salariu, suma si media salariilor tuturor angajatilor. Etichetati coloanele Maxim, Minim, Suma, respectiv Media. Sa se rotunjeasca rezultatele. SELECT MAX(salary) maxim,MIN(salary) minim, SUM(salary) suma, AVG(salary) media FROM employees; -------------------------3. Sa se afiseze minimul, maximul, suma si media salariilor pentru fiecare job. SELECT MAX(salary) maxim,MIN(salary) minim, SUM(salary) suma, AVG(salary) media,job_id FROM employees GROUP BY job_id; -------------------------4. Sa se afiseze numarul de angajati pentru fiecare job. SELECT COUNT(employee_id),job_id FROM employees GROUP BY job_id; -------------------------5. Sa se determine numarul de angajati care sunt sefi. Etichetati coloana �Nr. manageri�. SELECT COUNT(DISTINCT manager_id) FROM employees; -------------------------6. Sa se afiseze diferenta dintre cel mai mare si cel mai mic salariu. Etichetati coloana. SELECT MAX(salary)-MIN(salary) FROM employees; -------------------------7. Scrieti o cerere pentru a se afisa numele departamentului, locatia, numarul de angajati si salariul mediu pentru angajatii din acel departament. Coloanele vor fi etichetate corespunzator. SELECT department_name,location_id, count(employee_id),avg(salary) FROM employees JOIN departments USING (department_id) GROUP BY department_name, location_id; ------------------------8. Sa se afiseze codul si numele angajatilor care c�stiga mai mult dec�t salariul mediu din firma. Se va sorta rezultatul �n ordine descrescatoare a salariilor. SELECT employee_id, last_name,salary FROM employees GROUP BY employee_id, last_name, salary HAVING salary>(SELECT avg(salary) FROM employees) ORDER BY salary DESC; ------------------------9. Pentru fiecare sef, sa se afiseze codul sau si salariul celui mai prost platit

subordonat. Se vor exclude cei pentru care codul managerului nu este cunoscut. De asemenea, se vor exclude grupurile �n care salariul minim este mai mic de 1000$. Sortati rezultatul �n ordine descrescatoare a salariilor. SELECT ang.manager_id, ang.last_name, ang.salary FROM employees ang JOIN employees sef ON ang.manager_id=sef.employee_id WHERE ang.salary>=1000 and ang.salary=1000 and ceva.salary9000; ------------------------11. Care este salariul mediu minim al job-urilor existente? Salariul mediu al unui job va fi considerat drept media arirmetica a salariilor celor care �l practica. SELECT min(avg(salary)) FROM employees GROUP BY job_id; ------------------------12. Sa se afiseze codul, numele departamentului si suma salariilor pe departamente. SELECT department_id, department_name, sum(salary) FROM employees JOIN departments USING (department_id) GROUP BY department_id, department_name; ------------------------13. Sa se afiseze maximul salariilor medii pe departamente. SELECT max(avg(salary)) FROM employees GROUP BY department_id; ------------------------14. Sa se obtina codul, titlul si salariul mediu al job-ului pentru care salariul mediu este minim. SELECT job_id, job_title,avg(salary) FROM employees JOIN jobs USING (job_id) GROUP BY job_id,job_title HAVING avg(salary)2500; ------------------------16. Sa se afiseze suma salariilor pe departamente si, �n cadrul acestora, pe joburi. SELECT department_id,job_id, sum(salary) FROM employees GROUP BY department_id,job_id; ------------------------17. Sa se afiseze numele departamentului si cel mai mic salariu din departamentul avand cel mai mare salariu mediu. SELECT department_id, department_name,min(salary) FROM employees join departments using (department_id) GROUP BY department_id, department_name HAVING avg(salary)>=(SELECT max(avg(salary)) FROM employees GROUP BY department_id); ------------------------18. Sa se afiseze codul, numele departamentului si numarul de angajati care lucreaza in acel departament pentru: a) departamentele in care lucreaza mai putin de 4 angajati; b) departamentul care are numarul maxim de angajati. a) SELECT department_id, department_name,count(employee_id) FROM employees RIGHT JOIN departments USING (department_id) GROUP BY department_id, department_name HAVING count(employee_id)=(SELECT max(count(employee_id)) FROM employees GROUP BY department_id); ------------------------AICI AM RAMAS: 19. Sa se afiseze salariatii care au fost angajati �n aceeasi zi a lunii �n care cei mai multi dintre salariati au fost angajati. SELECT * FROM employees WHERE TO_CHAR(hire_date,'dd')IN (SELECT to_char(hire_date,'dd') FROM employees GROUP BY to_char(hire_date,'dd') HAVING count(employee_id) IN (SELECT max(count(employee_id)) FROM employees GROUP BY to_char(hire_date,'dd')));

------------------------20. Sa se obtina numarul departamentelor care au cel putin 15 angajati. SELECT count(*) FROM (SELECT department_id, count(employee_id) FROM employees GROUP BY department_id HAVING count(employee_id)>=15); ------------------------21. Sa se obtina codul departamentelor si suma salariilor angajatilor care lucreaza �n acestea, �n ordine crescatoare. Se considera departamentele care au mai mult de 10 angajati si al caror cod este diferit de 30. SELECT department_id, sum(salary), count(employee_id) FROM employees WHERE department_id30 GROUP BY department_id HAVING count(employee_id)>10; ------------------------22. Sa se afiseze codul, numele departamentului, numarul de angajati si salariul mediu din departamentul respectiv, impreuna cu numele, salariul si jobul angajatilor din acel departament. Se vor afisa si departamentele fara angajati (outer join). SELECT d.department_id, department_name,count(coleg.employee_id), round(avg(coleg.salary),2),ang.last_name,ang.salary,ang.job_id FROM employees ang JOIN employees coleg ON ang.department_id=coleg.department_id RIGHT JOIN departments d ON ang.department_id=d.department_id GROUP BY d.department_id,department_name,ang.last_name,ang.salary,ang.job_id; -----------------------23. Scrieti o cerere pentru a afisa, pentru departamentele avand codul > 80, salariul total pentru fiecare job din cadrul departamentului. Se vor afisa orasul, numele departamentului, jobul si suma salariilor. Se vor eticheta coloanele corespunzator. SELECT department_id,department_name,city,job_id,sum(salary) FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) --WHERE department_id>80 GROUP BY department_id,department_name, job_id,city HAVING department_id>80; -----------------------24. Care sunt angajatii care au mai avut cel putin doua joburi? SELECT employee_id FROM job_history GROUP BY employee_id HAVING count(job_id)>=2; -----------------------25. Sa se calculeze comisionul mediu din firma, lu�nd �n considerare toate liniile din tabel.

SELECT avg(commission_pct), count(commission_pct) FROM employees; ????Asta????? -----------------------26. Analizati cele 2 exemple prezentate mai sus (III � IV), referitor la operatorii ROLLUP si CUBE. -----------------------27. Scrieti o cerere pentru a afisa job-ul, salariul total pentru job-ul respectiv pe departamente si salariul total pentru job-ul respectiv pe departamentele 30, 50, 80. Se vor eticheta coloanele corespunzator. Rezultatul va aparea sub forma de mai jos: Job Dep30 Dep50 Dep80 Total -----------------------------------------------------------------------------����. -----------------------28. Sa se creeze o cerere prin care sa se afiseze numarul total de angajati si, din acest total, numarul celor care au fost angajati �n 1997, 1998, 1999 si 2000. Denumiti capetele de tabel in mod corespunzator. SELECT COUNT(*) total, (SELECT COUNT(*) FROM employees WHERE to_char(hire_date,'yyyy')=1997) "1997" , (SELECT COUNT(*) FROM employees WHERE to_char(hire_date,'yyyy')=1998) "1998" , (SELECT COUNT(*) FROM employees WHERE to_char(hire_date,'yyyy')=1999) "1999" , (SELECT COUNT(*) FROM employees WHERE to_char(hire_date,'yyyy')=2000) "2000" FROM employees GROUP BY 'mancare'; -----------------------29. Rezolvati problema 22 cu ajutorul subcererilor specificate �n clauza SELECT. SELECT d.department_id, department_name ,ang.last_name,ang.salary,ang.job_id, (select count(employee_id) FROM employees WHERE department_id=ang.department_id) NUMAR, (select round(avg(salary),2) FROM employees WHERE department_id=ang.department_id) medie FROM employees ang RIGHT JOIN departments d ON ang.department_id=d.department_id;

-----------------------30. Sa se afiseze codul, numele departamentului si suma salariilor pe departamente. SELECT department_id, department_name, sum(salary) FROM employees JOIN departments USING (department_id) GROUP BY department_id, department_name -----------------------31. Sa se afiseze numele, salariul, codul departamentului si salariul mediu din departamentul respectiv. SELECT last_name,salary,e.department_id, (SELECT avg(salary) FROM employees WHERE department_id=d.department_id) medie FROM employees e join departments d ON e.department_id=d.department_id; -----------------------32. Modificati cererea anterioara, pentru a determina si listarea numarului de angajati din departamente. SELECT last_name,salary,e.department_id, (SELECT avg(salary) FROM employees WHERE department_id=d.department_id) medie, (SELECT count(employee_id) FROM employees WHERE department_id=d.department_id) nr FROM employees e join departments d ON e.department_id=d.department_id; -----------------------33. Pentru fiecare departament, sa se afiseze numele acestuia, numele si salariul celor mai prost platiti angajati din cadrul sau. SELECT last_name, salary, department_id FROM employees e WHERE salary = (SELECT min(salary) FROM employees WHERE department_id=e.department_id); ----------------------34. Rezolvati problema 22 cu ajutorul subcererilor specificate �n clauza FROM. SELECT d.department_id, department_name ,ang.last_name,ang.salary,ang.job_id,nr,medie FROM employees ang RIGHT JOIN departments d ON ang.department_id=d.department_id LEFT JOIN (SELECT department_id, count(employee_id) nr ,round(avg(salary),2) medie FROM employees GROUP BY department_id) aux ON aux.department_id=ang.department_id; -----------------------