use ma_premiere_db
create table students (
studentID int,
Nom varchar(255),
Prenom varchar(255)
);

create table Pays (
Numero int NOT NULL primary key,
Nom varchar(30) not null
);

create table Localite (
NumLoc decimal(3,0) not null primary key,
codePostal decimal(4) not null,
Libelle varchar(50) not null,
Pays int not null,
foreign key (Pays) references Pays(Numero)
);

create table Personne (
NumCarteID decimal(20) not null primary key,
NomPrenom varchar(100) not null,
NbEnfants decimal(2),
Salaire decimal(6,2),
Localite decimal(3) not null,
foreign key (Localite) references Localite(NumLoc)
);

alter table Personne add Telephone varchar(20);

create table Utilisateur (
UserName varchar(20) not null primary key,
Nom varchar(30) not null,
Prenom varchar(50) not null,
Categorie varchar(20) not null check (
Categorie in ('Etudiant','Professeur','Administrateur')
),
DateCreation date not null,
constraint student_unique_nom_prenom
unique(Nom,Prenom)
);

create table Local (
Etage integer not null,
Numero decimal(2) not null,
NbPlaces decimal(3),
constraint NBLocal primary key (Etage, Numero)
);

create table Pc (
Matricule varchar(10) not null primary key,
Etage integer,
Numero decimal(2),
foreign key (Etage,Numero) references Local(Etage,Numero)
);

create table SessionTravail (
Login varchar(20) not null,
Pc varchar(10) not null,
Debut date not null,
Fin date not null,
foreign key (login) references Utilisateur(UserName),
foreign key (Pc) references Pc(Matricule)
);

insert into Utilisateur (UserName,Nom,Prenom,Categorie,DateCreation) value
('melchiorn','Melchior','Nicolas','Professeur', str_to_date('11/12/15','%d/%m/%Y'));

----------------
REQUETE BASE
-----------------
select First_name, Last_name, Department_id From EMPLOYEES order by Department_id, Last_name desc;
select Last_name, (Salary*1.1) FROM EMPLOYEES;
select * FROM EMPLOYEES WHERE Employee_id BETWEEN 100 and 200;
select * FROM EMPLOYEES WHERE Commission_pct is not null;
select * FROM EMPLOYEES WHERE last_name in ('King','Ernst','Greenberg','Chen');
select distinct Department_id, Job_id from EMPLOYEES;
select Last_name, DATE_FORMAT(Hire_date, 'embauché le %e %M %Y') from EMPLOYEES where Department_id in (20, 50);
select Last_name, First_name FROM EMPLOYEES WHERE Last_name Like 'D%' or Last_name like '%a%' or Last_name like '_e%';
select * FROM EMPLOYEES where Manager_id in (100,103,124) and Salary not between 2500 and 4000 order by Manager_id, Salary DESC;
SELECT Employee_id from EMPLOYEES WHERE Department_id is null;

-------------
jointure
--------------
select last_name, JOB_Title from EMPLOYEES JOIN JOBS ON EMPLOYEES.Job_id = JOBS.Job_id where Commission_pct is null;
select EMPLOYEES.Last_name, Department_name from EMPLOYEES join DEPARTMENTs;
select Department_name, Last_name from DEPARTMENTS JOIN EMPLOYEES ON DEPARTMENTs.Manager_id = Employee_id;
select Last_name, l.City from EMPLOYEES e JOIN DEPARTMENTS d on e.Department_id = d.Department_id join LOCATIONS l on d.Location_id = l.location_id;
select Last_name, l.City FROM EMPLOYEES e join DEPARTMENTS d ON e.Department_id = d.Department_id join LOCATIONS l ON d.Location_id = l.Location_id where City = 'Toronto' or City = 'Seattle';
select Last_name, d.Department_name, l.City, c.country_name, r.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;

-----------
requete grouper
-------------

select count(employee_id), sum(Salary) from EMPLOYEES;
select Department_id, count(employee_id), Salary from EMPLOYEES group by Department_id order by Salary;
select distinct Salary, job_id, count(employee_id) from EMPLOYEES group by Job_id;
select Department_id, avg(salary) from EMPLOYEES where date_format(1988/07/10, %Y %M %e) < Hire_date and Commission_pct;
select Last_name from Employees join JOBS on Employees.job_id = JOBS.job_id where commission_pct is null;
select Last_name, Locations.city from Employees join Departments on Employees.Department_id = Departments.Department_id join Locations on Departments.Location_id = Locations.Location_id where city = "Toronto" or city = "Seattle";
select count(employee_id) from employees;
select department_id, count(employee_id) from employees group by department_id having count(employee_id) > 2;
select manager_id, count(employee_id) from employees group by manager_id having count(employee_id) < 5;

-----------------
IMBRIQUER
------------------
select * from Employees where department_id in (select Department_id from employees where Last_name like '%u%' group by Department_id);

select Last_name, Hire_date from Employees where job_id in (select job_id from Employees where Last_name like 'Davies') and Last_name not like "Davies";

select Last_name, salary from Employees where manager_id in (select employee_id from Employees where Last_name like "King");

select Last_name, salary, manager_id from Employees where manager_id in (select manager_id from Employees where Last_name like "Abel") and salary > (select salary from Employees where Last_name like "Davies");

select * from Employees where salary > (select salary from Employees where job_id like "SA_MAN");

select * from Employees where department_id in (select department_id from Employees where Last_name = "Davies") and manager_id in (select manager_id from Employees where Last_name = "Davies");

select Last_name, Job_id, manager_id from Employees where job_id in (select job_id from Employees where Department_id in (20 , 50 )) and manager_id in (select manager_id from Employees where Department_id in (20, 50));

--------

excercise complémentaires
----------

Correction sql
Requêtes simple
Select * from locations
Where postal_code between 500 and 5500 ;

Insert into countries (country_name, country_id, region_id) value ("Belgique", 32, 1);

update departments set department_name = "RH" where department_id = 5;

delete from employees where first_name like "Albert" and last_name like "Camus";

Requêtes de sélection
select last_name, first_name from employees
where department_id = 5 or department_id = 8 or department_id = 12 or job_id =17;

select date_format(hire_date, "embauché le %d %M %Y"), last_name, first_name from employees
where department_id = 11;

select commission_pct from employees
where date_format("2012-01-01", "%Y-%m-%d") > hire_date;

Jointures
select region_name, country_name from countries
join regions on countries.region_id = regions.region_id
where country_id > 4;

select department_name, city from departments
join locations on departments.location_id = locations.location_id
where manager_id = 1 or manager_id = 2 or manager_id = 3;

select city from locations
join countries on locations.country_id = countries.country_id
join regions on countries.region_id = regions.region_id
where region_name like "Asie";

select last_name, first_name from employees
join departments on employees.department_id = departments.department_id
join locations on departments.location_id = locations.location_id
join countries on locations.country_id = countries.country_id
join regions on countries.region_id = regions.region_id
where region_name like "Asie" and department_name not like "RH";

Groupements
select count(employee_id)from employees
where first_name like "John";

select min(salary), max(salary), avg(salary) from employees
where department_id = 25;

select country_name, count(department_id)
from departments
join locations on departments.location_id = locations.location_id
join countries on locations.country_id = countries.country_id
group by countries.country_id;

Requêtes imbriquées
select date_format(hire_date, "%d %M %Y") from employees
where manager_id in
(select manager_id from employees
where first_name like "Albert" and last_name like "Camus");
(Suis pas trop sûr de celle la)
select country_name from countries
join regions on countries.region_id = regions.region_id
where region_name in
(select region_name from regions
where region_name like "A%" or region_name like "E%" or region_name like "Y%" or region_name like "O%" or region_name like "U%" or region_name like "Y%");