Exercices MySQL – Partie 1
Table employee
+-------------+------------+-----------+----------+--------------+-------------+
| Employee_id | First_name | Last_name | Salary | Joining_date | Departement |
+-------------+------------+-----------+----------+--------------+-------------+
| 1 | Bob | Kinto | 1000000 | 2019-01-20 | Finance |
| 2 | Jerry | Kansxo | 6000000 | 2019-01-15 | IT |
| 3 | Philip | Jose | 8900000 | 2019-02-05 | Banking |
| 4 | John | Abraham | 2000000 | 2019-02-25 | Insurance |
| 5 | Michael | Mathew | 2200000 | 2019-02-28 | Finance |
| 6 | Alex | chreketo | 4000000 | 2019-05-10 | IT |
| 7 | Yohan | Soso | 1230000 | 2019-06-20 | Banking |
+-------------+------------+-----------+----------+--------------+-------------+
Table reward (Prime)
+-----------------+-------------+--------+
| Employee_ref_id | date_reward | amount |
+-----------------+-------------+--------+
| 1 | 2019-05-11 | 1000 |
| 2 | 2019-02-15 | 5000 |
| 3 | 2019-04-22 | 2000 |
| 1 | 2019-06-20 | 8000 |
+-----------------+-------------+--------+
1. Récupérez tous les employés.
SELECT * FROM EMPLOYEE;
2. Récupérez les valeurs de la colonne « First_Name » et
« Last_Name ».
SELECT First_name, Last_name FROM EMPLOYEE;
3. Récupérez les valeurs de la colonne « First_Name » en utilisant
le nom d’alias « Employee Name »
SELECT First_name AS ¨ Employee_Name¨ FROM EMPLOYEE;
4. Récupérez les valeurs de la colonne « Last_Name » en
minuscule.
SELECT LOWER(Last_name) FROM EMPLOYEE;
5. Récupérez toutes les valeurs de la colonne « Last_Name » en
majuscule.
SELECT UPPER(Last_name) FROM EMPLOYEE ;
1
6. Sélectionnez les valeurs uniques dans la colonne
« DEPARTMENT ».
SELECT DISTINCT Departement FROM EMPLOYEE;
7. Sélectionnez les 4 premiers caractères de « FIRST_NAME ».
SELECT SUBSTRING(First_name,1,4) FROM EMPLOYEE ;
8. Récupérez la position de ‘h’ dans le nom ‘John’ dans la table
« employee ».
SELECT LOCATE(‘h’,First_name) FROM EMPLOYEE WHERE
First_name=’John’;
9. Récupère toutes les valeurs de la colonne « FIRST_NAME »
après avoir supprimé les espaces blancs à droite.
SELECT RTRIM(first_name) FROM EMPLOYEE ;
10. Récupère toutes les valeurs de la colonne « FIRST_NAME »
après avoir supprimé les espaces blancs à gauche.
SELECT LTRIM(first_name) FROM EMPLOYEE ;
/* Créer la table Employee */
CREATE TABLE Employee (
Employee_id int AUTO_INCREMENT PRIMARY KEY,
First_name VARCHAR(50),
Last_name VARCHAR(50),
Salary int,
Joining_date Date,
Departement VARCHAR(50)
);
/* Créer la table reward */
CREATE TABLE reward (
Employee_ref_id int,
date_reward Date,
amount int,
FOREIGN KEY (Employee_ref_id) REFERENCES Employee(Employee_id)
);
2
/* Insérer des lignes dans la table Employee */
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (1, 'Bob', 'Kinto', 1000000, "2019-01-20", "Finance");
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (2, 'Jerry', 'Kansxo', 6000000, "2019-01-15", "IT");
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (3, 'Philip', 'Jose', 8900000, "2019-02-05", "Banking");
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (4, 'John', 'Abraham', 2000000, "2019-02-25", "Insurance");
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (5, 'Michael', 'Mathew', 2200000, "2019-02-28", "Finance");
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (6, 'Alex', 'chreketo', 4000000, "2019-05-10", "IT");
INSERT INTO Employee (Employee_id, First_name, Last_name, Salary, Joining_date,
Departement) VALUES (7, 'Yohan', 'Soso', 1230000, "2019-06-20", "Banking");
/* Insérer des lignes dans la table reward */
INSERT INTO reward (Employee_ref_id, date_reward, amount) VALUES (1, '2019-05-11',
'1000');
INSERT INTO reward (Employee_ref_id, date_reward, amount) VALUES (2, '2019-02-15',
'5000');
INSERT INTO reward (Employee_ref_id, date_reward, amount) VALUES (3, '2019-04-22',
'2000');
INSERT INTO reward (Employee_ref_id, date_reward, amount) VALUES (1, '2019-06-20',
'8000');
