SQL - CERERI MULTITABEL, SUBCERERI

Atunci cand in clauza FROM a unei comenzi SELECT apar mai multe tabele se realizeaza produsul cartezian al acestora. De aceea numarul de linii rezultat creste considerabil, fiind necesara restrictionarea acestora cu o clauza WHERE.

Atunci cand este necesara obtinerea de informatii din mai multe tabele se utilizeaza conditii de join. In acest fel liniile dintr-un tabel pot fi puse in legatura cu cele din alt tabel conform valorilor comune ale unor coloane. Conditiile de corelare utilizeaza de obicei coloanele cheie primara si cheie externa.

Pentru claritatea si eficienta accesului la baza de date se recomanda prefixarea numelor coloanelor cu numele tabelelor din care fac parte (tabel.coloana). De asemenea, exista posibilitatea de a utiliza aliasuri pentru tabelele din clauza FROM si utilizarea lor in cadrul comenzii SELECT respective (alias.coloana). Aceasta identificare (prin 'tabel.coloana' sau 'alias.coloana') este obligatorie atunci cand se face referinta la o coloana ce apare in mai mult de un tabel din clauza FROM.

Tipuri de join:

equijoin (se mai numeste inner join sau simple join) - compunerea a doua relatii diferite dupa o conditie ce contine operatorul de egalitate.

SELECT last_name, department_name, location_id, e.department_id

FROM employees e, departments d
WHERE e.department_id = d.department_id;

nonequijoin - compunerea a doua relatii diferite dupa o conditie oarecare, ce NU contine operatorul de egalitate.

SELECT last_name, salary, grade
FROM employees, job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal;

outerjoin - compunerea externa a doua relatii diferite completand intr-una dintre relatii cu valori NULL acolo unde nu exista in aceasta nici un tuplu ce indeplineste conditia de corelare.

SELECT last_name, department_name,location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

selfjoin - compunerea externa a unui tabel cu el insusi dupa o conditie data.

SELECT sef.last_name, angajat.last_name
FROM employees sef, employees angajat
WHERE sef.employee_id = angajat.manager_id
ORDER BY sef.last_name;

Sa se afiseze numele salariatului, codul si numele departamentului pentru toti angajatii.

SELECT    last_name, e.department_id, department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

Sa se afiseze numele angajatului, numele departamentului pentru toti angajatii care castiga comision.

SELECT    last_name, commission_pct, department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND commission_pct IS NOT NULL;

Sa se listeze numele job-urile care exista in departamentul 30.

SELECT    DISTINCT job_title

FROM employees e, jobs j

WHERE e.job_id = j.job_id

AND department_id = 30;

Sa se afiseze numele, job-ul si numele departamentului pentru toti angajatii care lucreaza in Seattle.

SELECT    last_name, job_id, department_name

FROM employees e, departments d, locations s

WHERE e.department_id = d.department_id

AND d.location_id = s.location_id

AND city = Seattle

Sa se afiseze numele, salariul, data angajarii si numele departamentului pentru toti programatorii care lucreaza in America.

SELECT    last_name, salary, hire_date, department_name

FROM employees e, departments d, locations s, countries c, regions r, jobs j

WHERE e.department_id = d.department_id

AND d.location_id = s.location_id

AND s.country_id = c.country_id

AND c.region_id = r.region_id

AND e.job_id = j.job_id

AND region_name = Americas

AND job_title = Programmer

Sa se afiseze numele salariatilor si numele departamentelor in care lucreaza. Se vor afisa si salariatii care nu au asociat un departament. (right outher join).

SELECT    last_name, department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

Sa se afiseze numele departamentelor si numele salariatilor care lucreaza in ele. Se vor afisa si departamentele care nu au salariati. (left outher join).

SELECT    department_name, last_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

Sa se afiseze numele, job-ul, numele departamentului, salariul si grila de salarizare pentru toti angajatii.

SELECT    last_name, job_id, salary, department_name, grade_level

FROM employees e, departments d, job_grades

WHERE e.department_id = d.department_id

AND salary BETWEEN lowest_sal AND highest_sal;

Sa se afiseze codul angajatului si numele acestuia, impreuna cu numele si codul sefului sau direct. Se vor eticheta coloanele Ang#, Angajat, Mgr#, Manager. Sa se salveze instructiunea intr-un fisier numit p3_9.sql.

SELECT    a.employee_id Ang# , a.last_name Angajat , b.employee_id Mgr# , b.last_name Manager

FROM employees a, employees b

WHERE a.manager_id = b. employee_id;

Sa se modifice p3_9.sql pentru a afisa toti salariatii, inclusiv pe cei care nu au sef.

SELECT    a.employee_id Ang# , a.last_name Angajat , b.employee_id Mgr# , b.last_name Manager

FROM employees a, employees b

WHERE a.manager_id = b. employee_id (+);

Sa se afiseze numele salariatului si data angajarii impreuna cu numele si data angajarii sefului direct pentru salariatii care au fost angajati inaintea sefilor lor. Se vor eticheta coloanele Angajat, Data_ang, Manager si Data_mgr.

SELECT    a.last_name Angajat , a.hire_date Data_ang , b.last_name Manager b.hire_date Data_mgr

FROM employees a, employees b

WHERE a.manager_id = b. employee_id

AND a.hire_date<b.hire_date;

Pentru departamentele 20 si 30 sa afiseze numele angajatului, codul departamentului si toti salariatii care lucreaza in acelasi departament cu el. Se vor eticheta coloanele corespunzator.

SELECT    a.last_name Angajat , a.department_id Departament , b.last_name Coleg

FROM employees a, employees b

WHERE a.department_id = b.department_id

AND a.employee_id <> b.employee_id

AND a.department_id IN (20,30)

ORDER BY a.last_name;

Sa se afiseze numele si data angajarii pentru salariatii care au fost angajati dupa Fay.

SELECT    last_name, hire_date

FROM employees

WHERE hire_date > (SELECT hire_date

FROM employees

WHERE last_name Fay

Scrieti o cerere pentru a afisa numele si salariul pentru toti colegii (din acelasi departament) lui Fay. Se va exclude Fay

SELECT    last_name, salary

FROM employees

WHERE last_name <> Fay

AND department_id (SELECT department_id

FROM employees

WHERE last_name Fay

Sa se afiseze codul departamentului, codul si numele angajatilor care lucreaza in acelasi departament cu cel putin un angajat al carui nume contine litera T . Sa se ordoneze dupa codul departamentului.

SELECT    employee_id, last_name, department_id

FROM employees

WHERE department_id IN (SELECT DISTINCT department_id

FROM employees

WHERE UPPER(last_name) LIKE %T%

ORDER BY department_id;

Sa se afiseze numele si salariul angajatilor condusi direct de Steven King.

SELECT    last_name, salary

FROM     employees

WHERE manager_id (SELECT employee_id

FROM employees

WHERE UPPER(last_name) ='KING'

AND UPPER(first_name) ='STEVEN'

Sa se afiseze numele si job-ul tuturor angajatilor din departamentul Sales

SELECT    last_name, job_id

FROM     employees

WHERE department_id (SELECT department_id

FROM departments

WHERE department_name ='Sales');

Sa se afiseze numele angajatilor, numarul departamentului si job-ul tuturor salariatilor al caror departament este localizat in Seattle.

SELECT    last_name, job_id, department_id

FROM employees    

WHERE department_id IN (SELECT department_id

FROM departments

WHERE location_id = (SELECT location_id

FROM locations

WHERE city = Seattle

Sa se afle daca exista angajati care nu lucreaza in departamentul Sales si al caror salariu si comision coincid cu salariul si comisionul unui angajat din departamentul Sales

SELECT    last_name, salary, commission_pct, department_id

FROM employees

WHERE (salary, commission_pct) IN (SELECT salary, commission_pct

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND department_name Sales

AND department_id <> (SELECT department_id

FROM departments

WHERE department_name Sales

Scrieti o cerere pentru a afisa numele, numele departamentului si salariul angajatilor care nu castiga comision, dar al caror manager coincide cu managerul unui angajat care castiga comision.

SELECT    last_name, department_name, salary

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND e.manager_id IN (SELECT DISTINCT manager_id

FROM employees

WHERE commission_pct IS NOT NULL)

AND commission_pct IS NULL;

Scrieti o cerere pentru a afisa angajatii care castiga mai mult decat oricare functionar (PU_CLERK, SH_CLERK, ST_CLERK). Sortati rezultatele dupa salariu, in ordine descrescatoare.

SELECT    last_name, salary, job_id

FROM employees

WHERE salary > (SELECT MAX(salary)

FROM employees

WHERE job_id LIKE '%CLERK')

ORDER BY salary DESC;

Sa se afiseze numarul, numele si salariul tuturor angajatilor care castiga mai mult decat salariul mediu si lucreaza intr-un departament cu cel putin unul din angajatii al caror nume contine litera T

SELECT    employee_id, last_name, salary

FROM employees

WHERE salary > (SELECT AVG(salary)

FROM employees)

AND department_id IN (SELECT DISTINCT department_id

FROM employees

WHERE UPPER(last_name) LIKE '%T%