3SQL
Exercice 1:
```sql
-- a) Nom employé et département
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- b) Nom, numéro et nom département
-- Version JOIN ON
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- Version JOIN USING
SELECT e.last_name, department_id, d.department_name
FROM employees e
JOIN departments d USING (department_id);
-- c) Nom, département et manager du département
SELECT e.last_name, e.department_id, d.manager_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- d) Nom, département et manager de l'employé
SELECT e.last_name, d.department_name, e.manager_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- e) Nom et département (incluant sans département)
SELECT e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- f) Employés du département Executive
SELECT e.last_name, e.job_id, [Link], YEAR(e.hire_date)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Executive';
-- g) Nom et ville
-- SQL 92
SELECT e.last_name, [Link]
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
-- SQL 99
SELECT e.last_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;
```
Exercice 2:
```sql
-- a) Salaire minimum
SELECT MIN(salary) FROM employees;
-- b) Stats salaires
SELECT MIN(salary), MAX(salary), SUM(salary), AVG(salary)
FROM employees;
-- c) Nombre total employés
SELECT COUNT(*) FROM employees;
-- d) Nombre employés avec commission
SELECT COUNT(*) FROM employees WHERE commission_pct IS NOT NULL;
-- e) Moyenne commissions
SELECT AVG(commission_pct) FROM employees;
-- f) Somme salaires par département
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;
-- g) Nom département et somme salaires
SELECT d.department_name, SUM([Link])
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
-- h) Départements somme > 19000
SELECT d.department_name, SUM([Link])
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING SUM([Link]) > 19000
ORDER BY d.department_name;
-- i) Nombre employés par fonction et département
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;
```
Exercice 3:
```sql
-- a) Employés comme Fay
SELECT last_name, salary, job_id
FROM employees
WHERE salary = (SELECT salary FROM employees WHERE last_name = 'Fay');
-- b) Employés location 1700
SELECT last_name, department_id, job_id,
DATE_FORMAT(hire_date, '%W %d %M %Y')
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
);
-- c) Employé mieux payé
SELECT last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- d) Non programmeurs gagnant plus qu'un programmeur
SELECT last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary > ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');
-- e) Non programmeurs gagnant plus que tous les programmeurs
SELECT last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');
```
Exercice 4:
```sql
-- a) Employés et leurs managers
SELECT e.employee_id "Emp_Id", e.last_name "Employee",
m.employee_id "Mgr_Id", m.last_name "Manager"
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- b) Même année d'embauche que Davies
SELECT last_name, hire_date
FROM employees
WHERE YEAR(hire_date) = (
SELECT YEAR(hire_date)
FROM employees
WHERE last_name = 'Davies'
);
-- c) Nom et grade
SELECT e.last_name, j.grade_level
FROM employees e
JOIN job_grades j ON [Link] BETWEEN j.lowest_sal AND j.highest_sal;
```
