Bonjour Habr!
Depuis plus de 3 ans j'enseigne le SQL dans différents centres de formation, et une de mes constatations est que les étudiants maîtrisent et comprennent mieux le SQL si on leur confie une tâche, et ne se contentent pas de parler des possibilités et des fondements théoriques.
Dans cet article, je vais partager avec vous ma liste de tâches que je donne aux étudiants en devoir et sur lesquelles nous menons différents types de brainstorms, ce qui conduit à une compréhension profonde et claire de SQL.
SQL (ˈɛsˈkjuˈɛl ; eng. 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 gérée par un système de gestion de base de données approprié.
Vous pouvez lire sur SQL à partir de différents .
Cet article n'est pas destiné à vous apprendre SQL à partir de zéro.
Alors, allons-y.
Nous utiliserons le bien connu dans Oracle avec ses tables ():
Je note que nous ne considérerons que les tâches sur SELECT. Il n'y a pas de tâches sur DML et DDL.
Tâches
Restriction et tri des données
Tableau des employés. Obtenez une liste avec des informations sur tous les employés
décision
SELECT * FROM employees
Tableau des employés. Obtenir une liste de tous les employés nommés 'David'
décision
SELECT *
FROM employees
WHERE first_name = 'David';
Tableau des employés. Obtenir une liste de tous les employés avec job_id égal à 'IT_PROG'
décision
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Tableau des employés. Obtenir une liste de tous les employés du 50ème département (department_id) avec un salaire (salary) supérieur à 4000
décision
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Tableau des employés. Obtenez une liste de tous les employés du 20e et du 30e département (department_id)
décision
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Tableau des employés. Obtenez une liste de tous les employés dont la dernière lettre de leur nom est 'a'
décision
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Tableau des employés. Obtenir une liste de tous les employés du 50ème et du 80ème département (department_id) qui ont un bonus (la valeur dans la colonne commission_pct n'est pas vide)
décision
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Tableau des employés. Obtenez une liste de tous les employés dont le nom contient au moins 2 lettres 'n'
décision
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Tableau des employés. Obtenez une liste de tous les employés dont le nom comporte plus de 4 lettres
décision
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Tableau des employés. Obtenez une liste de tous les employés dont le salaire est compris entre 8000 et 9000 (inclus)
décision
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Tableau des employés. Obtenir une liste de tous les employés dont le nom contient le symbole '%'
décision
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Tableau des employés. Obtenir une liste de tous les ID de gestionnaire
décision
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Tableau des employés. Obtenez une liste des employés avec leurs postes au format : Donald(sh_clerk)
décision
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Utilisation de fonctions à une seule ligne pour personnaliser la sortie
Tableau des employés. Obtenez une liste de tous les employés dont le nom comporte plus de 10 lettres
décision
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Tableau des employés. Obtenez une liste de tous les employés qui ont la lettre 'b' dans leur nom (insensible à la casse)
décision
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Tableau des employés. Obtenez une liste de tous les employés dont le nom contient au moins 2 lettres 'a'
décision
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Tableau des employés. Obtenez une liste de tous les employés dont le salaire est un multiple de 1000
décision
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Tableau des employés. Obtenez le premier numéro à 3 chiffres du numéro de téléphone de l'employé si son numéro est au format ХХХ.ХХХ.ХХХХ
décision
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Tableau des départements. Obtenez le premier mot du nom du département pour ceux qui ont plus d'un mot dans le nom
décision
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Tableau des employés. Obtenez les noms des employés sans la première et la dernière lettre du nom
décision
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Tableau des employés. Obtenez une liste de tous les employés dont la dernière lettre du nom est égale à 'm' et la longueur du nom est supérieure à 5
décision
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Tableau double. Obtenez la date de vendredi prochain
décision
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Tableau des employés. Obtenez une liste de tous les employés qui travaillent dans l'entreprise depuis plus de 17 ans
décision
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Tableau des employés. Obtenez une 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
décision
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Tableau des employés. Obtenir une liste de tous les employés dont la valeur job_id après le signe '_' a au moins 3 caractères, mais cette valeur après le '_' n'est pas égale à 'CLERK'
décision
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Tableau des employés. Obtenez une liste de tous les employés en remplaçant tous les '.' dans la valeur PHONE_NUMBER sur '-'
décision
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Utilisation des fonctions de conversion et des expressions conditionnelles
Tableau des employés. Obtenez une liste de tous les employés qui sont venus travailler le premier jour du mois (n'importe lequel)
décision
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Tableau des employés. Obtenez une liste de tous les employés qui sont venus travailler en 2008
décision
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Table DOUBLE. Afficher la date de demain au format : Demain est le deuxième jour de janvier
décision
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Tableau des employés. Obtenez une liste de tous les employés et leur date de début au format : 21 juin 2007
décision
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Tableau des employés. Obtenez une liste d'employés dont les salaires ont augmenté de 20 %. Afficher le salaire avec le signe dollar
décision
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Tableau des employés. Obtenez une liste de tous les employés qui sont venus travailler en février 2007.
décision
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 DOUBLE. Exporter la date actuelle, + seconde, + minute, + heure, + jour, + mois, + année
décision
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;
Tableau des employés. Obtenez une liste de tous les employés avec leur plein salaire (salaire + commission_pct(%)) au format : 24,000.00 XNUMX $
décision
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Tableau des employés. Obtenir une liste de tous les employés et des informations sur la disponibilité des bonus salariaux (Oui/Non)
décision
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Tableau des employés. Obtenez le niveau de salaire de chaque employé : moins de 5000 est considéré comme un niveau bas, supérieur ou égal à 5000 et inférieur à 10000 est considéré comme un niveau normal, supérieur ou égal à 10000 est considéré comme un niveau élevé
décision
SELECT first_name,
salary,
CASE
WHEN salary < 5000 THEN 'Low'
WHEN salary >= 5000 AND salary < 10000 THEN 'Normal'
ELSE 'High'
END salary_level
FROM employees;
Tableau des pays. Pour chaque pays, indiquez la région dans laquelle il se trouve : 1-Europe, 2-Amérique, 3-Asie, 4-Afrique (sans jointure)
décision
SELECT country_name country,
DECODE (region_id,
1, 'Europe',
2, 'America',
3, 'Asia',
4, 'Africa',
'Unknown')
region
FROM countries;
SELECT country_name
country,
CASE region_id
WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
WHEN 4 THEN 'Africa'
ELSE 'Unknown'
END
region
FROM countries;
Rapporter des données agrégées à l'aide des fonctions de groupe
Tableau des employés. Obtenez un rapport par department_id avec le salaire minimum et maximum, les dates d'arrivée anticipée et tardive et le nombre d'employés. Trier par nombre d'employés (desc)
décision
SELECT department_id,
MIN (salary) min_salary,
MAX (salary) max_salary,
MIN (hire_date) min_hire_date,
MAX (hire_date) max_hire_Date,
COUNT (*) count
FROM employees
GROUP BY department_id
order by count(*) desc;
Tableau des employés. Combien d'employés dont le nom commence par la même lettre ? Trier par quantité. Afficher uniquement ceux dont le nombre est supérieur à 1
décision
SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)
FROM employees
GROUP BY SUBSTR (first_name, 1, 1)
HAVING COUNT (*) > 1
ORDER BY 2 DESC;
Tableau des employés. Combien d'employés travaillent dans le même service et reçoivent le même salaire ?
décision
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Tableau des employés. Obtenez un rapport sur le nombre d'employés embauchés chaque jour de la semaine. Trier par quantité
décision
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Tableau des employés. Obtenez un rapport sur le nombre d'employés embauchés par année. Trier par quantité
décision
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Tableau des employés. Obtenir le nombre de départements qui ont des employés
décision
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Tableau des employés. Obtenir la liste des department_id avec plus de 30 employés
décision
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Tableau des employés. Obtenez une liste de department_ids et le salaire moyen arrondi des employés de chaque département.
décision
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Tableau des pays. Obtenir une liste de region_id somme de toutes les lettres de tous les country_names dans lesquels plus de 60
décision
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Tableau des employés. Obtenir une liste de department_id dans lesquels travaillent les employés de plusieurs (>1) job_id
décision
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Tableau des employés. Obtenez une liste de manager_id dont le nombre de subordonnés est supérieur à 5 et la somme de tous les salaires de ses subordonnés est supérieure à 50000
décision
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Tableau des employés. Obtenir une liste de manager_id dont le salaire moyen de tous ses subordonnés est compris entre 6000 et 9000 et qui ne reçoivent pas de bonus (commission_pct est vide)
décision
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;
Tableau des employés. Obtenez le salaire maximum de tous les employés job_id qui se termine par le mot 'CLERK'
décision
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';
Tableau des employés. Obtenir le salaire maximum parmi tous les salaires moyens du département
décision
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Tableau des employés. Obtenez le nombre d'employés avec le même nombre de lettres dans leur nom. Dans le même temps, affichez uniquement ceux dont le nom est supérieur à 5 et le nombre d'employés portant le même nom est supérieur à 20. Trier par longueur de nom
décision
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 de plusieurs tables à l'aide de jointures
Tableau Employés, Départements, Emplacements, Pays, Régions. Obtenir une liste des régions et le nombre d'employés dans chaque région
décision
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;
Tableau Employés, Départements, Emplacements, Pays, Régions. Obtenez des informations détaillées sur chaque employé :
Prénom, Nom, Département, Fonction, Rue, Pays, Région
décision
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);
Tableau des employés. Afficher tous les managers qui ont plus de 6 employés
décision
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;
Tableau des employés. Afficher tous les employés qui ne relèvent de personne
décision
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 Employés, Job_history. La table Employee stocke tous les employés. La table Job_history stocke les employés qui ont quitté l'entreprise. Obtenez un rapport sur tous les employés et leur statut dans l'entreprise (Employé ou quitté l'entreprise avec la date de départ)
Exemple:
prénom | statut
jennifer | A quitté l'entreprise au 31 décembre 2006
Clara | Travaille actuellement
décision
SELECT first_name,
NVL2 (
end_date,
TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'),
'Currently Working')
status
FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);
Tableau Employés, Départements, Emplacements, Pays, Régions. Obtenir une liste des employés qui vivent en Europe (region_name)
décision
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';
Tableau Employés, Départements. Afficher tous les départements de plus de 30 employés
décision
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Tableau Employés, Départements. Afficher tous les employés qui ne sont dans aucun département
décision
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;
Tableau Employés, Départements. Afficher tous les départements sans employés
décision
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Tableau des employés. Afficher tous les employés qui n'ont pas de subordonnés
décision
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Tableau Employés, Emplois, Départements. Afficher les employés au format : prénom, titre du poste, nom du service.
Exemple:
prénom | titre du poste | Nom du département
Donald | expédition | Commis à l'expédition
décision
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);
Tableau des employés. Obtenir une liste d'employés dont les managers ont obtenu un emploi en 2005, mais en même temps, ces travailleurs eux-mêmes ont obtenu un emploi avant 2005
décision
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');
Tableau des employés. Obtenez une liste des employés dont les managers ont obtenu un emploi au mois de janvier de n'importe quelle année et la longueur du titre de l'emploi de ces employés est supérieure à 15 caractères
décision
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
Tableau des employés. Obtenez une liste des employés avec le nom le plus long.
décision
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Tableau des employés. Obtenez une liste des employés dont le salaire est supérieur au salaire moyen de tous les employés.
décision
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Tableau Employés, Départements, Emplacements. Obtenez la ville dans laquelle les employés gagnent le moins au total.
décision
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);
Tableau des employés. Obtenez une liste des employés dont le responsable perçoit un salaire supérieur à 15000 XNUMX.
décision
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Tableau Employés, Départements. Afficher tous les départements sans employés
décision
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Tableau des employés. Afficher tous les employés qui ne sont pas des gestionnaires
décision
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Tableau des employés. Afficher tous les managers qui ont plus de 6 employés
décision
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Tableau Employés, Départements. Afficher les employés qui travaillent dans le service informatique
décision
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tableau Employés, Emplois, Départements. Afficher les employés au format : prénom, titre du poste, nom du service.
Exemple:
prénom | titre du poste | Nom du département
Donald | expédition | Commis à l'expédition
décision
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;
Tableau des employés. Obtenir une liste d'employés dont les managers ont obtenu un emploi en 2005, mais en même temps, ces travailleurs eux-mêmes ont obtenu un emploi avant 2005
décision
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');
Tableau des employés. Obtenez une liste des employés dont les managers ont obtenu un emploi au mois de janvier de n'importe quelle année et la longueur du titre de l'emploi de ces employés est supérieure à 15 caractères
décision
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 le moment.
J'espère que les tâches étaient intéressantes et passionnantes.
Je compléterai cette liste autant que possible.
Je serai également heureux de tous les commentaires et suggestions.
PS : Si quelqu'un propose une tâche intéressante sur SELECT, écrivez dans les commentaires, je l'ajouterai à la liste.
Merci.
Source: habr.com