20 EXERCICES SQL POUR PRATIQUE

Structure et schéma de la table :

Créez une table avec une colonne de clé primaire

CRÉER TABLE départements

( department_id INTEGER PRIMARY KEY

, department_name VARCHAR(30)

, location_id ENTIER

) ;

Créer une table avec une clé étrangère

CRÉER TABLE employés

( employee_id INTEGER

, first_name VARCHAR(20)

, last_name VARCHAR(25)

, email VARCHAR(25)

, numéro_de_téléphone VARCHAR(20)

, hire_date DATE

, job_id VARCHAR(10)

, salaire INTEGER

, commission_pct ENTIER

, identifiant_du_manager INTEGER
 , department_id INTEGER

, contrainte pk_emp clé primaire (employee_id)

, contrainte fk_deptno clé étrangère (department_id) référence

departments(department_id)

) ;

Insérer des enregistrements dans des tables

## Insérer dans la table des départements

INSÉRER DANS les départements VALUES ( 20,'Marketing', 180);

INSÉRER DANS les départements VALEURS (30, 'Achats', 1700);

INSÉRER DANS les départements VALEURS (40, 'Ressources Humaines', 2400);

INSERT INTO departments VALUES ( 50, 'Shipping', 1500);

INSÉRER DANS les départements VALEURS ( 60 , 'TI', 1400);

INSÉRER DANS les départements Valeurs ( 70, 'Relations Publiques', 2700);

INSÉRER DANS les départements VALEURS ( 80 , 'Ventes', 2500 );

INSÉRER DANS les départements VALUES ( 90 , 'Exécutif', 1700);

INSÉRER DANS départements VALEURS ( 100 , 'Finance', 1700);

INSÉRER DANS départements VALEURS ( 110 , 'Comptabilité', 1700);

INSÉRER DANS les départements VALEURS ( 120 , 'Trésorerie' , 1700);

INSÉRER DANS les départements VALEURS ( 130 , 'Impôt sur les sociétés' , 1700 );

INSÉRER DANS les départements VALEURS ( 140, 'Contrôle et Crédit' , 1700);

INSÉRER DANS les départements VALEURS ( 150 , 'Services aux actionnaires', 1700);

INSÉRER DANS départements VALEURS ( 160 , 'Avantages', 1700);

 INSÉRER DANS départements VALEURS ( 170 , 'Paie' , 1700);

## Insérer dans la table des employés

INSÉRER DANS les employés VALUES (100, 'Steven', 'Roi', 'SKING', '515.123.4567',
'1987-06-17' , 'AD_PRES', 24000 , NULL, NULL, 20);

INSÉRER DANS les employés VALEURS (101, 'Neena', 'Kochhar', 'NKOCHHAR',

'515.123.4568' , '1989-11-21' , 'AD_VP' , 17000 , NULL , 100 , 20);

INSÉRER DANS les employés VALEURS (102, 'Lex', 'De Haan', 'LDEHAAN',
'515.123.4569' , '1993-09-12' , 'AD_VP' , 17000 , NULL , 100 , 30);

INSÉRER DANS les employés VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD')

'590.423.4567' , '1990-09-30', 'IT_PROG' , 9000 , NULL , 102 , 60);

INSÉRER DANS les employés VALEURS (104 , 'Bruce' , 'Ernst' , 'BERNST' ,

'590.423.4568' , '1991-05-21', 'IT_PROG' , 6000 , NULL , 103 , 60);

INSÉRER DANS employés VALUES (105 , 'David' , 'Austin' , 'DAUSTIN' ,

'590.423.4569' , '1997-06-25', 'IT_PROG' , 4800 , NULL , 103 , 60);

INSÉRER DANS employés VALEURS (106, 'Valli', 'Pataballa', 'VPATABAL'

'590.423.4560' , '1998-02-05', 'IT_PROG' , 4800 , NULL , 103 , 40);

INSÉRER DANS les employés VALABLES (107 , 'Diana' , 'Lorentz' , 'DLORENTZ' ,

'590.423.5567' , '1999-02-09', 'IT_PROG' , 4200 , NULL , 103 , 40);

INSÉRER DANS les employés VALEURS (108 , 'Nancy' , 'Greenberg' , 'NGREENBE' ,

'515.124.4569' , '1994-08-17', 'FI_MGR' , 12000 , NULL , 101 , 100);

INSÉRER DANS les employés VALUES (109 , 'Daniel' , 'Faviet' , 'DFAVIET' ,

'515.124.4169' , '1994-08-12', 'FI_ACCOUNT' , 9000 , NULL , 108 , 170);

INSÉRER DANS les employés VALEURS (110 , 'John' , 'Chen' , 'JCHEN' ,

'515.124.4269' , '1997-04-09', 'FI_ACCOUNT' , 8200 , NULL , 108 , 170);

INSÉRER DANS les employés VALEURS (111 , 'Ismael' , 'Sciarra' , 'ISCIARRA' ,

'515.124.4369' , '1997-02-01', 'FI_ACCOUNT' , 7700 , NULL , 108 , 160);

INSÉRER DANS les employés VALEURS (112 , 'Jose Manuel' , 'Urman' , 'JMURMAN' ,
'515.124.4469' , '1998-06-03', 'FI_ACCOUNT' , 7800 , NULL , 108 , 150);
INSÉRER DANS les employés VALEURS (113 , 'Luis' , 'Popp' , 'LPOPP' ,
'515.124.4567' , '1999-12-07', 'FI_ACCOUNT' , 6900 , NULL , 108 , 140);

INSÉRER DANS les employés VALUES (114 , 'Den' , 'Raphaely' , 'DRAPHEAL' ,

'515.127.4561' , '1994-11-08', 'PU_MAN' , 11000 , NULL , 100 , 30);

INSÉRER DANS les employés VALUES (115 , 'Alexander' , 'Khoo' , 'AKHOO' ,

'515.127.4562' , '1995-05-12', 'PU_CLERK' , 3100 , NULL , 114 , 80);

INSÉRER DANS les employés VALEURS (116 , 'Shelli' , 'Baida' , 'SBAIDA' ,

'515.127.4563' ,'1997-12-13', 'PU_CLERK' , 2900 , NULL , 114 , 70);

INSÉRER DANS les employés VALEURS (117, 'Sigal', 'Tobias', 'STOBIAS',

'515.127.4564' , '1997-09-10', 'PU_CLERK' , 2800 , NULL , 114 , 30);

INSÉRER DANS les employés VALEURS (118, 'Guy', 'Himuro', 'GHIMURO')

'515.127.4565' , '1998-01-02', 'PU_CLERK' , 2600 , NULL , 114 , 60);

INSÉRER DANS les employés VALEURS (119 , 'Karen' , 'Colmenares' , 'KCOLMENA' ,

'515.127.4566' , '1999-04-08', 'PU_CLERK' , 2500 , NULL , 114 , 130);

INSÉRER DANS les employés VALUES (120 , 'Matthew' , 'Weiss' , 'MWEISS' ,

'650.123.1234' ,'1996-07-18', 'ST_MAN' , 8000 , NULL , 100 , 50);

INSÉRER DANS les employés VALEURS (121 , 'Adam' , 'Fripp' , 'AFRIPP' ,

'650.123.2234' , '1997-08-09', 'ST_MAN' , 8200 , NULL , 100 , 50);

INSÉRER DANS les employés VALEURS (122 , 'Payam' , 'Kaufling' , 'PKAUFLIN' ,

'650.123.3234' ,'1995-05-01', 'ST_MAN' , 7900 , NULL , 100 , 40);

INSÉRER DANS les employés VALEURS (123 , 'Shanta' , 'Vollman' , 'SVOLLMAN' ,

'650.123.4234' , '1997-10-12', 'ST_MAN' , 6500 , NULL , 100 , 50);

INSÉRER DANS les employés VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS',

'650.123.5234' , '1999-11-12', 'ST_MAN' , 5800 , NULL , 100 , 80);

INSÉRER DANS les employés VALEURS (125, 'Julia', 'Nayer', 'JNAYER',

'650.124.1214' , '1997-07-02', 'ST_CLERK' , 3200 , NULL , 120 , 50);

INSÉRER DANS les employés VALEURS (126, 'Irene', 'Mikkilineni', 'IMIKKILI',

'650.124.1224' , '1998-11-12', 'ST_CLERK' , 2700 , NULL , 120 , 50);

INSÉRER DANS les employés VALEURS (127, 'James', 'Landry', 'JLANDRY',

'650.124.1334' , '1999-01-02' , 'ST_CLERK' , 2400 , NULL , 120 , 90);
INSÉRER DANS les employés VALUES (128, 'Steven', 'Markle', 'SMARKLE',
'650.124.1434' , '2000-03-04' , 'ST_CLERK' , 2200 , NULL , 120 , 50);

INSÉRER DANS les employés VALEURS (129, 'Laura', 'Bissot', 'LBISSOT',

'650.124.5234' ,'1997-09-10' , 'ST_CLERK' , 3300 , NULL , 121 , 50);

INSÉRER DANS les employés VALEURS (130, 'Mozhe', 'Atkinson', 'MATKINSO',

'650.124.6234' , '1997-10-12' , 'ST_CLERK' , 2800 , NULL , 121 , 110);

Donc, maintenant nous avons 2 tables et des données prêtes à exécuter notre SQL. Il est temps pour
quelques exercices.

Résoudre des exercices SQL

1. Sélectionnez le prénom, le nom, l'identifiant de poste et le salaire des employés dont

le prénom commence par la lettre S
sélectionner prénom

last_name,

job_id,

salaire

des employés

où upper(prénom) comme 'S%';

2. Write a query to select employee with the highest salary

sélectionnez l'identifiant_de_l'employé,

first_name,

last_name,

job_id,

salaire

des employés
 où salaire = (sélectionner max(salaire) des employés);

3. Sélectionner l'employé avec le deuxième salaire le plus élevé

sélectionner l'identifiant_employé

first_name,

last_name,

job_id,

salaire

des employés

où le salaire != (sélectionner max(salaire) de employés)

ordre par salaire desc

limite 1;

La requête ci-dessus sélectionne uniquement une personne avec le deuxième salaire le plus élevé. Mais
Que faire s'il y a plus d'une personne avec le même salaire ? Ou, que faire si nous voulons
sélectionner le 3ème ou le 4ème salaire le plus élevé ? Donc, essayons une approche générique.

4. Récupérer les employés avec le 2ème ou 3ème salaire le plus élevé

#changer l'entrée pour le 2ème, 3ème ou 4ème salaire le plus élevé

définir @input:=3;

sélectionner identifiant_employé,

first_name,

last_name,

job_id,

salaire
 des employés e

où @input = (sélectionner le COMPTE(DISTINCT Salaire)

des employés p

où [Link] <= [Link]);

5. Écrivez une requête pour sélectionner les employés et leur correspondant

les gestionnaires et leurs salaires

Maintenant, c'est un exemple classique de SELF JOIN dans les exercices SQL. De plus, je suis
utiliser la fonction CONCAT pour concaténer le prénom et le nom de famille de
chaque employé et manager.
sélectionnez concat(emp.first_name,' ',emp.last_name) employé,

[Link] emp_sal,

concat(mgr.first_name,' ',mgr.last_name) responsable,

[Link] mgr_sal

des employés emp

joindre les employés mgr sur emp.manager_id = mgr.employee_id;

Écrivez une requête pour afficher le nombre d'employés sous chaque manager
par ordre décroissant
sélectionner

sup.employee_id employee_id

concat([Link]énom,' ', [Link])nom_du_manager

COUNT (sub.employee_id) AS nombre_de_rapports

des employés sub

rejoindre les employés sup

 sur sub.manager_id = sup.employee_id

grouper par sup.employee_id, sup.first_name, sup.last_name

ordre par 3 desc;

7. Trouvez le nombre d'employés dans chaque département

sélectionner dept.nom_du_département,

compte(emp.employee_id) emp_count

de employés emp

joindre les départements dept sur emp.department_id = dept.department_id

grouper par dept.department_name

trier par 2 desc;

8. Obtenez le nombre d'employés embauchés par année

sélectionnez l'année(hire_date) année_embauche, compte(*) nombre_employés_embauchés

from employees

grouper par année(hire_date)

order by 2 desc;

9. Trouvez la fourchette de salaire des employés

sélectionnez min(salaire) min_sal,

max(salary) max_sal,

round(avg(salary)) avg_sal

des employés;

10. Écrivez une requête pour diviser les personnes en trois groupes en fonction de leur
salaires
 sélectionner concat(prénom, ' ', nom_de_famille) employé

salaire

cas

quand le salaire >=2000 et le salaire < 5000 alors "faible"

quand salaire >=5000 et salaire < 10000 alors "médian"

sinon

élevé

fin en tant que niveau_de_salaire

des employés

commande par 1;

11. Sélectionnez les employés dont le prénom contient « an »

sélectionner (prénom)

des employés

où lower(prénom) like '%an%';

12. Sélectionner le prénom de l'employé et le numéro de téléphone correspondant

numéro au format (_ _ _)-(_ _ _)-(_ _ _ _)
sélectionnez concat(prénom, ' ', nom) employé

remplacer(numéro_de_téléphone,'.','-') numéro_de_téléphone

des employés;

13. Trouvez les employés qui ont rejoint en août 1994.

sélectionner concat(prénom, ' ', nom_de_famille) employé

hire_date
 des employés

où année(hire_date) = '1994'

et mois(hire_date) = '08';

14. Écrivez une requête SQL pour afficher les employés qui gagnent plus que
le salaire moyen dans cette entreprise

sélectionner

concat([Link]énom, nom) nom,

emp.id_employé

département.nom_du_département département

dept.department_id,

[Link]

des départements dept

JOINDRE les employés emp sur dept.department_id = emp.department_id

où [Link] > (sélectionner avg(salaire) de employés)

trier par dept.department_id;

15. Trouvez le salaire maximum de chaque département.

sélectionner

dept.department_id,

département.nom_du_département département

max([Link])maximum_salary

des départements dept

REJOINDRE les employés emp sur dept.department_id = emp.department_id

 grouper par dept.department_name,

dept.department_id

trier par dept.department_id ;

16. Écrivez une requête SQL pour afficher les 5 employés les moins bien rémunérés

sélectionner

first_name, last_name,

employee_id,

salaire

des employés

trier par salaire

limite 5;

17. Trouvez les employés embauchés dans les années 80

sélectionner employee_id
concat(prénom, ' ', nom) employé,
hire_date
des employés
où l'année(hire_date) est entre 1980 et 1989;

18. Afficher le prénom de l'employé et le nom dans l'ordre inverse

sélectionner minuscule(prénom) nom

minuscule(inverser(prénom)) nom_en_inverse

des employés;

19. Trouvez les employés qui ont rejoint l'entreprise après le 15 du

mois
sélectionnez l'identifiant_de_l'employé
 concat(prénom, ' ', nom) employé,

hire_date

des employés

où jour(hire_date)> 15;

20. Afficher les managers et les employés subordonnés qui travaillent dans
différents départements
sélectionner

concat(mgr.first_name,' ',mgr.last_name) directeur,

concat([Link]énom,' ',[Link]) employé,

mgr.department_id mgr_dept,

emp.department_id emp_dept

de employés emp

joindre les employés mgr sur emp.manager_id = mgr.employee_id

où emp.department_id != mgr.department_id

commande par 1;

Source: [Link]