Bonjour, Habr !
Cela fait plus de 3 ans que j'enseigne SQL dans différents centres de formation, et l'une de mes observations est que les étudiants maîtrisent et comprennent mieux SQL lorsqu'on leur pose des problèmes à résoudre plutôt que de simplement leur expliquer les possibilités et les bases théoriques.
Dans cet article, je vais partager avec vous ma liste de tâches que je donne aux étudiants comme devoirs et sur lesquelles nous réalisons divers types de brainstormings, ce qui conduit à une compréhension approfondie et claire de SQL.
SQL (ˈɛsˈkjuˈɛl ; en anglais, structured query language — « langage de requête structuré ») est un langage de programmation déclaratif utilisé pour créer, modifier et gérer des données dans une base de données relationnelle, contrôlée par le système de gestion de bases de données approprié.
On peut lire sur SQL dans divers .
Cet article n’a pas pour but de vous apprendre SQL depuis le début.
Alors, c'est parti.
Utilisons la dans Oracle avec ses tables ():
Je précise que nous allons examiner uniquement les tâches sur SELECT. Il n’y a pas de tâches sur DML et DDL.
Objectifs
Restriction et tri des données
Table Employees. Obtenir la liste de toutes les informations sur les employés
Solution
SELECT * FROM employees
Table Employees. Obtenir la liste de tous les employés portant le nom ‘David’
Solution
SELECT *
FROM employees
WHERE first_name = 'David';
Table Employees. Obtenir la liste de tous les employés avec job_id égal à ‘IT_PROG’
Solution
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Table Employees. Obtenir la liste de tous les employés du 50ème département (department_id) avec un salaire (salary) supérieur à 4000
Solution
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Table Employees. Obtenir la liste de tous les employés des départements 20 et 30 (department_id)
Solution
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Table Employees. Obtenir la liste de tous les employés dont la dernière lettre du prénom est ‘a’
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Table Employees. Obtenir la liste de tous les employés des départements 50 et 80 (department_id) qui ont un bonus (valeur dans la colonne commission_pct non vide)
Solution
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Table Employees. Obtenir la liste de tous les employés dont le prénom contient au moins 2 lettres ‘n’
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Table Employees. Obtenir la liste de tous les employés dont la longueur du prénom est supérieure à 4 lettres
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Table Employees. Obtenir la liste de tous les employés dont le salaire est compris entre 8000 et 9000 (inclus)
Solution
SÉLECTIONNER *
DE employés
OÙ salaire ENTRE 8000 ET 9000;
Table Employees. Obtenir la liste de tous les employés dont le nom contient le caractère ‘%’
Solution
SÉLECTIONNER *
DE employés
OÙ first_name LIKE '%%%' ESCAPE '';
Table Employees. Obtenir la liste de tous les ID des managers
Solution
SÉLECTIONNER DISTINCT manager_id
DE employés
OÙ manager_id EST NON NULL;
Table Employees. Obtenir la liste des employés avec leurs postes au format : Donald(sh_clerk)
Solution
SÉLECTIONNER first_name || '(' || LOWER (job_id) || ')' employé DE employés;
Utilisation des fonctions à une seule ligne pour personnaliser la sortie
Table Employees. Obtenir la liste de tous les employés dont la longueur du nom est supérieure à 10 lettres
Solution
SÉLECTIONNER *
DE employés
OÙ LENGTH (first_name) > 10;
Table Employees. Obtenir la liste de tous les employés dont le nom contient la lettre ‘b’ (sans tenir compte de la casse)
Solution
SÉLECTIONNER *
DE employés
OÙ INSTR (LOWER (first_name), 'b') > 0;
Table Employees. Obtenir la liste de tous les employés dont le nom contient au moins 2 lettres ‘a’
Solution
SÉLECTIONNER *
DE employés
OÙ INSTR (LOWER (first_name),'a',1,2) > 0;
Table Employees. Obtenir la liste de tous les employés dont le salaire est un multiple de 1000
Solution
SÉLECTIONNER *
DE employés
OÙ MOD (salaire, 1000) = 0;
Table Employees. Obtenir le premier numéro de téléphone à 3 chiffres de l'employé si son numéro est au format XXX.XXX.XXXX
Solution
SÉLECTIONNER phone_number, SUBSTR (phone_number, 1, 3) nouveau_numéro_de_téléphone
DE employés
OÙ phone_number LIKE '___.___.____';
Table Departments. Obtenir le premier mot du nom du département pour ceux dont le nom contient plus d'un mot
Solution
SÉLECTIONNER department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
premier_mot
DE départements
OÙ INSTR (department_name, ' ') > 0;
Table Employees. Obtenir les prénoms des employés sans la première et la dernière lettre du prénom
Solution
SÉLECTIONNER first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) nouveau_nom
DE employés;
Table Employees. Obtenir la liste de tous les employés dont la dernière lettre du prénom est ‘m’ et dont la longueur du prénom est supérieure à 5
Solution
SÉLECTIONNER *
DE employés
OÙ SUBSTR (first_name, -1) = 'm' ET LENGTH(first_name) > 5;
Table Dual. Obtenir la date du prochain vendredi
Solution
SÉLECTIONNER NEXT_DAY (SYSDATE, 'VENDREDI') prochain_vendredi DE DUAL;
Table Employees. Obtenir la liste de tous les employés qui travaillent dans l'entreprise depuis plus de 17 ans
Solution
SÉLECTIONNER *
DE employés
OÙ MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Table Employees. Obtenir la liste de tous les employés dont le dernier chiffre du numéro de téléphone est impair et se compose de 3 chiffres séparés par un point
Solution
SÉLECTIONNER *
DE employés
OÙ MOD (SUBSTR (phone_number, -1), 2) != 0
ET INSTR (phone_number,'.',1,3) = 0;
Table Employees. Obtenir la liste de tous les employés dont la valeur de job_id après le signe ‘_’ contient au moins 3 caractères mais celui-ci n'est pas ‘CLERK’
Solution
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Table Employees. Obtenez la liste de tous les employés en remplaçant dans la valeur PHONE_NUMBER tous les ‘.’ par ‘-‘
Solution
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Utilisation des fonctions de conversion et des expressions conditionnelles
Table Employees. Obtenez la liste de tous les employés qui ont commencé à travailler le premier jour d'un mois (quel qu'il soit)
Solution
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Table Employees. Obtenez la liste de tous les employés qui ont commencé à travailler en 2008
Solution
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Table DUAL. Afficher la date de demain au format : Tomorrow is Second day of January
Solution
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Table Employees. Obtenez la liste de tous les employés et la date de leur embauche au format : 21st of June, 2007
Solution
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Table Employees. Obtenez la liste des employés avec des augmentations de salaire de 20 %. Montrez le salaire avec un signe dollar
Solution
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Table Employees. Obtenez la liste de tous les employés qui ont commencé à travailler en février 2007.
Solution
SELECT *
FROM employees
WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')
AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));
SELECT *
FROM employees
WHERE to_char(hire_date,'MM.YYYY') = '02.2007';
Table DUAL. Afficher la date actuelle, + seconde, + minute, + heure, + jour, + mois, + année
Solution
SELECT SYSDATE now,
SYSDATE + 1 / (24 * 60 * 60) plus_second,
SYSDATE + 1 / (24 * 60) plus_minute,
SYSDATE + 1 / 24 plus_hour,
SYSDATE + 1 plus_day,
ADD_MONTHS (SYSDATE, 1) plus_month,
ADD_MONTHS (SYSDATE, 12) plus_year
FROM DUAL;
Table Employees. Obtenez la liste de tous les employés avec des salaires complets (salary + commission_pct(%)) au format : $24,000.00
Solution
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Table Employees. Obtenez la liste de tous les employés et des informations sur la présence de primes de salaire (Oui/Non)
Solution
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Table Employees. Obtenez le niveau de salaire de chaque employé : Moins de 5000 est considéré comme un niveau bas, 5000 ou plus et moins de 10000 est considéré comme un niveau normal, 10000 ou plus est considéré comme un niveau élevé
Solution
SELECT first_name,
salary,
CASE
WHEN salary = 5000 AND salary < 10000 THEN 'Normal'
ELSE 'High'
END salary_level
FROM employees;
Table Countries. Pour chaque pays, montrez la région dans laquelle il se trouve : 1-Europe, 2-Amérique, 3-Asie, 4-Afrique (sans Join)
Solution
SÉLECTIONNER country_name pays,
DECODE (region_id,
1, 'Europe',
2, 'Amérique',
3, 'Asie',
4, 'Afrique',
'Inconnu')
region
DE countries;
SÉLECTIONNER country_name
pays,
CASE region_id
QUAND 1 ALORS 'Europe'
QUAND 2 ALORS 'Amérique'
QUAND 3 ALORS 'Asie'
QUAND 4 ALORS 'Afrique'
SINON 'Inconnu'
FIN
region
DE countries;
Rapport de données agrégées à l'aide des fonctions de groupe
Table Employés. Obtenir un rapport par department_id avec le salaire minimum et maximum, la date d'embauche la plus ancienne et la plus récente, et le nombre d'employés. Trier par nombre d'employés (ordre décroissant)
Solution
SÉLECTIONNER department_id,
MIN (salary) min_salary,
MAX (salary) max_salary,
MIN (hire_date) min_hire_date,
MAX (hire_date) max_hire_Date,
COUNT (*) count
DE employees
GROUP BY department_id
ORDER BY count(*) DESC;
Table Employés. Combien d'employés ont un prénom commençant par la même lettre ? Trier par quantité. Afficher seulement ceux où le nombre est supérieur à 1
Solution
SÉLECTIONNER SUBSTR (first_name, 1, 1) first_char, COUNT (*)
DE employees
GROUP BY SUBSTR (first_name, 1, 1)
HAVING COUNT (*) > 1
ORDER BY 2 DESC;
Table Employés. Combien d'employés travaillent dans le même département et ont le même salaire ?
Solution
SÉLECTIONNER department_id, salary, COUNT (*)
DE employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Table Employés. Obtenir un rapport du nombre d'employés embauchés chaque jour de la semaine. Trier par quantité
Solution
SÉLECTIONNER TO_CHAR (hire_Date, 'Day') jour, COUNT (*)
DE employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Table Employés. Obtenir un rapport du nombre d'employés embauchés par année. Trier par quantité
Solution
SÉLECTIONNER TO_CHAR (hire_date, 'YYYY') année, COUNT (*)
DE employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Table Employés. Obtenir le nombre de départements où il y a des employés
Solution
SÉLECTIONNER COUNT (COUNT (*)) department_count
DE employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Table Employés. Obtenir la liste des department_id où plus de 30 employés travaillent
Solution
SÉLECTIONNER department_id
DE employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Table Employés. Obtenir la liste des department_id et le salaire moyen arrondi des employés dans chaque département.
Solution
SÉLECTIONNER department_id, ROUND (AVG (salary)) avg_salary
DE employees
GROUP BY department_id;
Table Pays. Obtenir la liste des region_id où la somme de tous les caractères de country_name dépasse 60
Solution
SÉLECTIONNER region_id
DE countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Table Employés. Obtenir la liste des department_id où des employés ayant plusieurs (>1) job_id travaillent
Solution
SÉLECTIONNER department_id
DE employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Table des Employés. Obtenir la liste des manager_id ayant plus de 5 subordonnés et dont la somme des salaires de tous les subordonnés dépasse 50000
Solution
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Table des Employés. Obtenir la liste des manager_id dont le salaire moyen de tous les subordonnés est compris entre 6000 et 9000 et qui ne reçoivent pas de primes (commission_pct vide)
Solution
SELECT manager_id, AVG (salary) avg_salary
FROM employees
WHERE commission_pct IS NULL
GROUP BY manager_id
HAVING AVG (salary) BETWEEN 6000 AND 9000;
Table des Employés. Obtenir le salaire maximum parmi tous les employés dont le job_id se termine par le mot ‘CLERK’
Solution
SELECT MAX (salary) max_salary
FROM employees
WHERE job_id LIKE '%CLERK';
SELECT MAX (salary) max_salary
FROM employees
WHERE SUBSTR (job_id, -5) = 'CLERK';
Table des Employés. Obtenir le salaire maximum parmi toutes les moyennes de salaires par département
Solution
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Table des Employés. Obtenir le nombre d'employés ayant le même nombre de lettres dans leur prénom. Ne montrer que ceux dont la longueur du prénom est supérieure à 5 et le nombre d'employés avec ce prénom est supérieur à 20. Trier par longueur de prénom
Solution
SELECT LENGTH (first_name), COUNT (*)
FROM employees
GROUP BY LENGTH (first_name)
HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20
ORDER BY LENGTH (first_name);
SELECT LENGTH (first_name), COUNT (*)
FROM employees
WHERE LENGTH (first_name) > 5
GROUP BY LENGTH (first_name)
HAVING COUNT (*) > 20
ORDER BY LENGTH (first_name);
Affichage des données provenant de plusieurs tables à l'aide de jointures
Table des Employés, Départements, Lieux, Pays, Régions. Obtenir la liste des régions et le nombre d'employés dans chaque région
Solution
SELECT region_name, COUNT (*)
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
GROUP BY region_name;
Table des Employés, Départements, Lieux, Pays, Régions. Obtenir des informations détaillées sur chaque employé :
Prénom, Nom de famille, Département, Poste, Rue, Pays, Région
Solution
SELECT First_name,
Last_name,
Department_name,
Job_id,
street_address,
Country_name,
Region_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id);
Table des Employés. Montrer tous les managers ayant plus de 6 employés sous leur responsabilité
Solution
SELECT man.first_name, COUNT (*)
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
GROUP BY man.first_name
HAVING COUNT (*) > 6;
Table des Employés. Montrer tous les employés qui n'ont de subordonnés
Solution
SELECT emp.first_name
FROM employees emp
LEFT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE man.FIRST_NAME IS NULL;
SELECT first_name
FROM employees
WHERE manager_id IS NULL;
Table des employés, historique des emplois. La table des employés contient tous les employés. La table historique des emplois contient les employés qui ont quitté l'entreprise. Obtenir un rapport sur tous les employés et leur statut dans l'entreprise (Travaillant ou quitté avec date de départ)
Exemple :
first_name | status
Jennifer | A quitté l'entreprise le 31 décembre 2006
Clara | Actuellement en poste
Solution
SELECT first_name,
NVL2 (
end_date,
TO_CHAR (end_date, 'fm""A quitté l'entreprise le"" DD ""de"" Month, YYYY'),
'Actuellement en poste')
status
FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);
Table des employés, départements, emplacements, pays, régions. Obtenir la liste des employés vivant en Europe (region_name)
Solution
SELECT first_name
FROM employees
JOIN departments USING (department_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
JOIN regions USING (region_id)
WHERE region_name = 'Europe';
SELECT first_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
WHERE region_name = 'Europe';
Table des employés, départements. Montrer tous les départements où plus de 30 employés travaillent
Solution
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Table des employés, départements. Montrer tous les employés qui ne font partie d'aucun département
Solution
SELECT first_name
FROM employees e
LEFT JOIN departments d ON (e.department_id = d.department_id)
WHERE d.department_name IS NULL;
SELECT first_name
FROM employees
WHERE department_id IS NULL;
Table des employés, départements. Montrer tous les départements où il n'y a aucun employé
Solution
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Table des employés. Montrer tous les employés qui n'ont personne sous leur responsabilité
Solution
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Table des employés, emplois, départements. Montrer les employés au format : First_name, Job_title, Department_name.
Exemple :
First_name | Job_title | Department_name
Donald | Expédition | Employé en expédition
Solution
SELECT first_name, job_title, department_name
FROM employees e
JOIN jobs j ON (e.job_id = j.job_id)
JOIN departments d ON (d.department_id = e.department_id);
Table des employés. Obtenir la liste des employés dont les managers ont été embauchés en 2005, mais qui eux-mêmes ont été embauchés avant 2005
Solution
SELECT emp.*
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE TO_CHAR (man.hire_date, 'YYYY') = '2005'
AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');
Table des employés. Obtenez la liste des employés dont les managers ont été embauchés en janvier d'une année donnée et dont la longueur du job_title est supérieure à 15 caractères.
Solution
SELECT emp.*
FROM employees emp
JOIN employees man ON (emp.manager_id = man.employee_id)
JOIN jobs j ON (emp.job_id = j.job_id)
WHERE TO_CHAR(man.hire_date, 'MM') = '01' AND LENGTH(j.job_title) > 15;
Utilisation de sous-requêtes pour résoudre des requêtes
Table des employés. Obtenez la liste des employés avec le prénom le plus long.
Solution
SELECT *
FROM employees
WHERE LENGTH(first_name) =
(SELECT MAX(LENGTH(first_name)) FROM employees);
Table des employés. Obtenez la liste des employés dont le salaire est supérieur à la moyenne de tous les employés.
Solution
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Tables Employees, Departments, Locations. Obtenez la ville où les employés gagnent le moins en total.
Solution
SELECT city
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city
HAVING SUM(salary) =
(SELECT MIN(SUM(salary))
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city);
Table des employés. Obtenez la liste des employés dont le manager a un salaire supérieur à 15000.
Solution
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Table des employés, départements. Montrer tous les départements où il n'y a aucun employé
Solution
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Table des employés. Montrez tous les employés qui ne sont pas des managers
Solution
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Table des Employés. Montrer tous les managers ayant plus de 6 employés sous leur responsabilité
Solution
SELECT *
FROM employees e
WHERE (SELECT COUNT(*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Table des employés, départements. Montrez les employés qui travaillent dans le département IT
Solution
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Table des employés, emplois, départements. Montrer les employés au format : First_name, Job_title, Department_name.
Exemple :
First_name | Job_title | Department_name
Donald | Expédition | Employé en expédition
Solution
SELECT first_name,
(SELECT job_title
FROM jobs
WHERE job_id = e.job_id) job_title,
(SELECT department_name
FROM departments
WHERE department_id = e.department_id) department_name
FROM employees e;
Table des employés. Obtenir la liste des employés dont les managers ont été embauchés en 2005, mais qui eux-mêmes ont été embauchés avant 2005
Solution
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') = '2005')
AND hire_date < TO_DATE('01012005', 'DDMMYYYY');
Table des employés. Obtenez la liste des employés dont les managers ont été embauchés en janvier d'une année donnée et dont la longueur du job_title est supérieure à 15 caractères.
Solution
SELECT *
FROM employees e
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR(hire_date, 'MM') = '01')
AND (SELECT LENGTH(job_title)
FROM jobs
WHERE job_id = e.job_id) > 15;
C'est tout pour l'instant.
J'espère que les tâches étaient intéressantes et captivantes.
J'ajouterai ce que je peux à cette liste de tâches.
Je suis également ouvert à tous commentaires et suggestions.
P.S. : Si quelqu'un a une idée de tâche intéressante sur SELECT, écrivez dans les commentaires, je l'ajouterai à la liste.
Merci.
Source : habr.com
