Oracal v3 - 15 noiembrie

15 noiembrie

Suma salariilor din fiecare departament (CU GROUP BY)
select sum(salary), department_id from employees group by department_id

select sum(salary), department_id, first_name from employees group by department_id, first_name

Suma salariilor din fiecare departament (cu numele departamentului)
Sintaxa ANSI
select sum(salary), department_name from employees join departments using(department_id) group by department_name


Sintaxa Oracle
select sum(salary) "Fond de salarii", department_name from employees e,departments d where e.department_id=d.department_id group by department_name
(e si d - aliasurile tabelelor)


Self Joining (Angajati - Sefi)
ANSI: select e.first_name "angajat", m.first_name "sef" from employees e join employees m on(m.employee_id=e.manager_id)

select e.first_name "angajat", m.first_name "sef" from employees e join employees m on(m.employee_id=e.manager_id) where m.first_name='Steven' order by 2

ORACLE: select e.first_name, m.first_name from employees e, employees m where m.employee_id=e.manager_id order by 2


select e.first_name, m.first_name from employees e, employees m where m.employee_id=e.manager_id and m.first_name='Steven' order by 2

(2 - sef, 1 - angajat)