Skip to main content

Exercice Optimization

CREATE TABLE employees (

   emp_no      INT             NOT NULL,
   birth_date  DATE            NOT NULL,
   first_name  VARCHAR(14)     NOT NULL,
   last_name   VARCHAR(16)     NOT NULL,
   gender      ENUM ('M','F')  NOT NULL,    
   hire_date   DATE            NOT NULL,
);

CREATE TABLE departments (

   dept_no     CHAR(4)         NOT NULL,
   dept_name   VARCHAR(40)     NOT NULL
);

CREATE TABLE dept_manager (

  dept_no      CHAR(4)         NOT NULL,
  emp_no       INT             NOT NULL,
  from_date    DATE            NOT NULL,
  to_date      DATE            NOT NULL
);

CREATE TABLE dept_emp (

   emp_no      INT             NOT NULL,
   dept_no     CHAR(4)         NOT NULL,
   from_date   DATE            NOT NULL,
   to_date     DATE            NOT NULL
);

CREATE TABLE titles (

   emp_no      INT             NOT NULL,
   title       VARCHAR(50)     NOT NULL,
   from_date   DATE            NOT NULL,
   to_date     DATE); 
CREATE TABLE salaries (

   emp_no      INT             NOT NULL,
   salary      INT             NOT NULL,
   from_date   DATE            NOT NULL,
   to_date     DATE            NOT NULL
);
  • Requete pour avoir les employées feminin:
 select * from employees where last_name='titi' OR gender='F'
  • Requete RH pour faire de la BI
SELECT T1.emp_no, T1.gender,SUM(T1.salary), T1.hire_date, T1.birth_date from 
(SELECT employees.emp_no, employees.gender, salaries.salary, employees.hire_date, employees.birth_date 
 FROM employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no) AS T1 GROUP BY T1.salary
  • Requete les employées Feminines ainsi que les noms des département
SELECT departments.dept_name, employees.first_name, employees.last_name 
FROM employees INNER JOIN dept_emp ON dept_emp.emp_no = employees.emp_no 
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no 
WHERE employees.gender = 'F'
  • Requete pour avoir les salaire des senior staff
SELECT employees.first_name, employees.last_name, titles.title, salaries.salary 
FROM employees INNER JOIN titles ON titles.emp_no = employees.emp_no 
INNER JOIN salaries ON salaries.emp_no = employees.emp_no WHERE titles.title LIKE '%staff%'
Back to top