Geekly articles weekly

SQL Des puzzles divertissants

Bonjour, Habr!

Depuis plus de 3 ans maintenant, j'enseigne le SQL dans divers centres de formation, et l'une de mes observations est que les étudiants maîtrisent et comprennent mieux le SQL s'ils définissent une tâche, et pas seulement parler des possibilités et des fondements théoriques.

Dans cet article, je partagerai avec vous ma liste de tâches que je donne aux étudiants comme devoirs et sur lesquelles nous menons différents types de brainstormings, ce qui conduit à une compréhension profonde et claire de SQL.



SQL (ˈɛsˈkjuˈɛl; langage de requête structuré en anglais) 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é. En savoir plus

Vous pouvez lire sur SQL à partir de différentes sources .
Cet article n'est pas destiné à vous apprendre SQL à partir de zéro.

Alors allons-y.

Nous utiliserons le schéma RH bien connu d'Oracle avec ses tables ( plus ):


Je note que nous ne considérerons que les tâches SELECT. Il n'y a aucun problème avec DML et DDL.

Les tâches


Restriction et tri des données

Table des employés. Obtenez une liste avec des informations sur tous les employés
Solution
SELECT * FROM employees


Table des employés. Obtenez une liste de tous les employés nommés «David»
Solution
 SELECT * FROM employees WHERE first_name = 'David';


Table des employés. Obtenez une liste de tous les employés avec job_id égal à 'IT_PROG'
Solution
 SELECT * FROM employees WHERE job_id = 'IT_PROG'


Table des employés. Obtenez une liste de tous les employés du 50e département (département_id) avec salaire (salaire), plus de 4000
Solution
 SELECT * FROM employees WHERE department_id = 50 AND salary > 4000;


Table des employés. Obtenez une liste de tous les employés du 20e et du 30e département (department_id)
Solution
 SELECT * FROM employees WHERE department_id = 20 OR department_id = 30;


Table des employés. Obtenez une liste de tous les employés dont la dernière lettre du nom est «a»
Solution
 SELECT * FROM employees WHERE first_name LIKE '%a';


Table des employés. Obtenez une liste de tous les employés du 50e et du 80e département (department_id) qui ont un bonus (la valeur dans la colonne commission_pct n'est pas vide)
Solution
 SELECT * FROM employees WHERE (department_id = 50 OR department_id = 80) AND commission_pct IS NOT NULL;


Table des employés. Obtenez une liste de tous les employés dont le nom contient au moins 2 lettres «n»
Solution
 SELECT * FROM employees WHERE first_name LIKE '%n%n%';


Table des employés. Obtenez une liste de tous les employés dont le nom dépasse 4 lettres
Solution
 SELECT * FROM employees WHERE first_name LIKE '%_____%';


Table des employés. Obtenez une liste de tous les employés dont le salaire est compris entre 8000 et 9000 (inclus)
Solution
 SELECT * FROM employees WHERE salary BETWEEN 8000 AND 9000;


Table des employés. Obtenez une liste de tous les employés dont le nom contient le caractère '%'
Solution
 SELECT * FROM employees WHERE first_name LIKE '%\%%' ESCAPE '\';


Table des employés. Obtenez une liste de tous les gestionnaires d'ID
Solution
 SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL;


Table des employés. Obtenez une liste des employés avec leurs postes au format: Donald (sh_clerk)
Solution
 SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;



Utilisation des fonctions à une ligne pour personnaliser la sortie

Table des employés. Obtenez une liste de tous les employés dont le nom comporte plus de 10 lettres
Solution
 SELECT * FROM employees WHERE LENGTH (first_name) > 10;


Table des employés. Obtenez une liste de tous les employés qui ont la lettre «b» à leur nom (insensible à la casse)
Solution
 SELECT * FROM employees WHERE INSTR (LOWER (first_name), 'b') > 0;


Table des employés. Obtenez une liste de tous les employés dont le nom contient au moins 2 lettres «a»
Solution
 SELECT * FROM employees WHERE INSTR (LOWER (first_name),'a',1,2) > 0;


Table des employés. Obtenez une liste de tous les employés dont le salaire est un multiple de 1000
Solution
 SELECT * FROM employees WHERE MOD (salary, 1000) = 0;


Table 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 XXXX.XXX.XXXX
Solution
 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
Solution
 SELECT department_name, SUBSTR (department_name, 1, INSTR (department_name, ' ')-1) first_word FROM departments WHERE INSTR (department_name, ' ') > 0;


Table des employés. Obtenez les noms des employés sans les première et dernière lettres du nom
Solution
 SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name FROM employees;


Table des employés. Obtenez une liste de tous les employés dont la dernière lettre du nom est «m» et un nom long supérieur à 5
Solution
 SELECT * FROM employees WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;


Table double. Obtenez la date du prochain vendredi
Solution
 SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;


Table des employés. Obtenez une liste de tous les employés qui travaillent dans l'entreprise depuis plus de 17 ans
Solution
 SELECT * FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;


Table 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
Solution
 SELECT * FROM employees WHERE MOD (SUBSTR (phone_number, -1), 2) != 0 AND INSTR (phone_number,'.',1,3) = 0 AND INSTR (phone_number,'.',1,2) > 0;


Table des employés. Obtenez une liste de tous les employés qui ont au moins 3 caractères dans la valeur job_id après le signe «_», mais cette valeur après «_» n'est pas égale à «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 des employés. Obtenez une liste de tous les employés en remplaçant tous "." Dans la valeur PHONE_NUMBER. sur '-'
Solution
 SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number FROM employees;



Utilisation des fonctions de conversion et des expressions conditionnelles

Table des employés. Obtenez une liste de tous les employés qui sont venus travailler le premier jour du mois (n'importe lequel)
Solution
 SELECT * FROM employees WHERE TO_CHAR (hire_date, 'DD') = '01';


Table des employés. Obtenez une liste de tous les employés qui sont venus travailler en 2008
Solution
 SELECT * FROM employees WHERE TO_CHAR (hire_date, 'YYYY') = '2008';


Table DUAL. Afficher la date de demain au format: Demain est le deuxième jour de janvier
Solution
 SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info FROM DUAL;


Table des employés. Obtenez une liste de tous les employés et la date à laquelle chacun est venu travailler au format: 21 juin 2007
Solution
 SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date FROM employees;


Table des employés. Obtenez une liste des employés dont les salaires ont augmenté de 20%. Afficher le salaire avec le signe dollar
Solution
 SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary FROM employees;


Table des employés. Obtenez une liste de tous les employés qui viennent 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. Supprimer 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 des employés. Obtenez une liste de tous les employés avec un salaire complet (salaire + 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 des employés. Obtenez une liste de tous les employés et des informations sur la disponibilité des primes salariales (Oui / Non)
Solution
 SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus FROM employees;


Table 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 moins de 10000 est considéré comme un niveau normal, supérieur ou égal à 10000 est considéré comme un niveau élevé
Solution
 SELECT first_name, salary, CASE WHEN salary < 5000 THEN 'Low' WHEN salary >= 5000 AND salary < 10000 THEN 'Normal' WHEN salary >= 10000 THEN 'High' ELSE 'Unknown' 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 Join)
Solution
 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;



Rapport des données agrégées à l'aide des fonctions de groupe

Table des employés. Obtenez un rapport sur department_id avec un salaire minimum et maximum, avec une date d'arrivée précoce et tardive au travail et avec le nombre d'employés. Trier par nombre d'employés (décroissant)
Solution
  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;


Table des employés. Combien d'employés dont le nom commence par la même lettre? Trier par quantité. Afficher uniquement lorsque la quantité est supérieure à 1
Solution
 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;


Table des employés. Combien d'employés qui travaillent dans le même département et reçoivent le même salaire?
Solution
 SELECT department_id, salary, COUNT (*) FROM employees GROUP BY department_id, salary HAVING COUNT (*) > 1;


Table des employés. Obtenez un rapport sur le nombre d'employés embauchés chaque jour de la semaine. Trier par quantité
Solution
 SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_Date, 'Day') ORDER BY 2 DESC;


Table des employés. Obtenez un rapport sur le nombre d'employés embauchés par année. Trier par quantité
Solution
 SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_date, 'YYYY');


Table des employés. Obtenez le nombre de départements dans lesquels il y a des employés
Solution
 SELECT COUNT (COUNT (*)) department_count FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;


Table des employés. Obtenez une liste de department_id avec plus de 30 employés
Solution
  SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (*) > 30;


Table des employés. Obtenez une liste de department_id et un salaire moyen arrondi pour les employés de chaque département.
Solution
  SELECT department_id, ROUND (AVG (salary)) avg_salary FROM employees GROUP BY department_id;


Tableau des pays. Obtenez une liste de region_id la somme de toutes les lettres de tous country_name dans lesquels plus de 60
Solution
  SELECT region_id FROM countries GROUP BY region_id HAVING SUM (LENGTH (country_name)) > 60;


Table des employés. Obtenez une liste de department_id dans laquelle les employés de plusieurs (> 1) job_id travaillent
Solution
  SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (DISTINCT job_id) > 1;


Table 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 à 50 000
Solution
  SELECT manager_id FROM employees GROUP BY manager_id HAVING COUNT (*) > 5 AND SUM (salary) > 50000;


Table des employés. Obtenez une liste de manager_id pour lesquels le salaire moyen de tous ses subordonnés est compris entre 6000 et 9000 qui ne reçoivent pas de bonus (commission_pct est 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. Obtenez le salaire maximum de tous les employés job_id qui 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. Obtenez le salaire maximum parmi tous les salaires moyens du département
Solution
  SELECT MAX (AVG (salary)) FROM employees GROUP BY department_id;


Table des employés. Obtenez le nombre d'employés avec le même nombre de lettres dans le nom. Dans le même temps, affichez uniquement ceux dont la longueur de nom est supérieure à 5 et le nombre d'employés portant ce nom est supérieur à 20. Trier par longueur de 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 de plusieurs tables à l'aide de jointures

Tableau Employés, Départements, Emplacements, Pays, Régions. Obtenez une 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;


Tableau Employés, Départements, Emplacements, Pays, Régions. Obtenez des informations détaillées sur chaque employé:
Prénom, Nom, Département, Travail, 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. Afficher tous les managers ayant plus de 6 employés
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. Afficher tous les employés qui ne rendent compte à personne
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, Job_history. La table Employé stocke tous les employés. La table Job_history stocke les employés qui ont quitté l'entreprise. Obtenir un rapport sur tous les employés et leur statut dans l'entreprise (travaille ou a quitté l'entreprise avec la date de départ)
Un exemple:
prénom | statut
Jennifer | A quitté l'entreprise au 31 décembre 2006
Clara | Travaille actuellement
Solution
 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. Obtenez une liste des employés qui vivent en Europe (nom_région)
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. Afficher tous les départements de plus de 30 employés
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. Afficher tous les employés qui ne font partie d'aucun service
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. Afficher tous les départements sans un seul 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. Afficher tous les employés qui n'ont pas de subordonnés
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 Employés, Emplois, Départements. Afficher les employés au format: First_name, Job_title, Department_name.
Un exemple:
Prénom | Job_title | Nom_département
Donald | Expédition | Expédition de commis
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. Obtenez une liste des employés dont les gestionnaires ont obtenu un emploi en 2005, mais en même temps, ces employés eux-mêmes ont obtenu un emploi 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 une liste des employés dont les gestionnaires ont obtenu un emploi au cours du mois de janvier de n'importe quelle année et la durée du titre_emploi de ces employés 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 une liste des employés avec le nom le plus long.
Solution
 SELECT * FROM employees WHERE LENGTH (first_name) = (SELECT MAX (LENGTH (first_name)) FROM employees);


Table des employés. Obtenez une liste des employés dont le salaire est supérieur au salaire moyen de tous les employés.
Solution
 SELECT * FROM employees WHERE salary > (SELECT AVG (salary) FROM employees);


Tableau Employés, départements, emplacements. Obtenez une ville dans laquelle les employés gagnent moins au 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 une liste des employés dont le gestionnaire reçoit un salaire supérieur à 15 000.
Solution
 SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE salary > 15000)


Table des employés, Départements. Afficher tous les départements sans un seul 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. Afficher tous les employés qui ne sont pas des gestionnaires
Solution
 SELECT * FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL)


Table des employés. Afficher tous les managers ayant plus de 6 employés
Solution
 SELECT * FROM employees e WHERE (SELECT COUNT (*) FROM employees WHERE manager_id = e.employee_id) > 6;


Table des employés, Départements. Afficher les employés qui travaillent dans le service informatique
Solution
 SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');


Table Employés, Emplois, Départements. Afficher les employés au format: First_name, Job_title, Department_name.
Un exemple:
Prénom | Job_title | Nom_département
Donald | Expédition | Expédition de commis
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. Obtenez une liste des employés dont les gestionnaires ont obtenu un emploi en 2005, mais en même temps, ces employés eux-mêmes ont obtenu un emploi 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 une liste des employés dont les gestionnaires ont obtenu un emploi au cours du mois de janvier de n'importe quelle année et la durée du titre_emploi de ces employés 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 fascinantes.
Si possible, je compléterai cette liste de tâches.
Je serai également heureux de tout commentaire et suggestion.

PS: Si quelqu'un propose une tâche SELECT intéressante, écrivez dans les commentaires, ajoutez à la liste.

Je vous remercie

Source: https://habr.com/ru/post/fr461567/

More articles:


All Articles