requête avancée

Créer les 2 tables ci dessous :

DROP TABLE IF EXISTS EMPLOYE,DEPARTEMENT;

CREATE TABLE  DEPARTEMENT (
    idDept INT,
    nom VARCHAR(255),
    lieu VARCHAR(255),
    PRIMARY KEY(idDept)
);

INSERT INTO DEPARTEMENT (idDept,nom,lieu)  VALUES (10,"recherche","Besançon");
INSERT INTO DEPARTEMENT (idDept,nom,lieu)  VALUES (20,"vente","Montbéliard");
INSERT INTO DEPARTEMENT (idDept,nom,lieu)  VALUES (30,"direction","Belfort");
INSERT INTO DEPARTEMENT (idDept,nom,lieu)  VALUES (40,"fabrication","Sochaux");

CREATE TABLE  EMPLOYE (
    idEmploye INT,
    nom VARCHAR(255),
    fonction VARCHAR(255),
    idResponsable INT,
    date_embauche date,
    salaire NUMERIC(8,2),
    prime NUMERIC(8,2),
    departement_id INT,
    PRIMARY KEY(idEmploye)
);
INSERT INTO EMPLOYE(nom,idEmploye,fonction,idResponsable,date_embauche,salaire,prime,departement_id) VALUES 
('MARTIN',16712,'directeur',25717,'2000-05-23',8000,NULL,30);
INSERT INTO EMPLOYE(nom,idEmploye,fonction,idResponsable,date_embauche,salaire,prime,departement_id) VALUES 
('DUPONT',17574,'administratif',16712,'2005-05-03',1800,NULL,30),
('DUPOND',26691,'commercial',27047,'1998-04-04',5000,500,20),
('LAMBERT',25012,'administratif',27047,'2001-03-14',NULL,2400,20),
('JOUBERT',25717,'president',NULL,'1992-08-10',10000,NULL,30),
('LEBRETON',16034,'commercial',27047,'2001-06-01',3000,0,20),
('MARTIN',17147,'commercial',27047,'2005-05-03',4000,500,20),
('PAQUEL',27546,'commercial',27047,'1993-09-03',4400,1000,20),
('LEFEBVRE',25935,'commercial',27047,'1994-01-11',4700,400,20),
('GARDARIN',15155,'ingenieur',24533,'1995-03-22',4800,NULL,10),
('SIMON',26834,'ingenieur',24533,'1998-10-04',4000,NULL,10),
('DELOBEL',16278,'ingenieur',24533,'2004-11-16',4200,NULL,10),
('ADIBA',25067,'ingenieur',24533,'1997-10-05',6000,NULL,10),
('CODD',24533,'directeur',25717,'1985-11-12',11000,NULL,10),
('LAMERE',27047,'directeur',25717,'1999-09-07',9000,NULL,20),
('BALIN',17232,'administratif',24533,'1997-10-03',2700,NULL,10),
('BARA',24831,'administratif',16712,'1998-11-10',3000,NULL,30),
('toto','00001','livreur de pizzas',NULL,NULL,NULL,NULL,NULL);

jointure


WITH department_names AS (
    SELECT idEmploye, nom AS employee_name, departement_id
    FROM EMPLOYE
),
employee_departments AS (
    SELECT dn.employee_name, d.nom
    FROM department_names dn
    JOIN DEPARTEMENT d ON dn.departement_id = d.idDept
)
SELECT * FROM employee_departments;
WITH RECURSIVE employee_hierarchy AS (
    SELECT idEmploye, nom, idResponsable, 0 AS level
    FROM EMPLOYE
    WHERE idResponsable IS NULL -- Sélectionner le PDG (manager_id NULL)

    UNION ALL

    SELECT e.idEmploye, e.nom, e.idResponsable, eh.level + 1
    FROM EMPLOYE AS e
    JOIN employee_hierarchy AS eh ON e.idResponsable = eh.idEmploye
)
SELECT idEmploye, nom, idResponsable, level
FROM employee_hierarchy;

fonction de partition ou fenêtrage

Affichage des employés et de la somme des salaires


SELECT SUM(salaire)
FROM EMPLOYE;

SELECT *, SUM(salaire) OVER()
FROM EMPLOYE;

Affichage des employés et de la somme des salaires par fonctions ou départements

SELECT *
     , SUM(salaire) OVER(PARTITION BY fonction)
FROM EMPLOYE;


SELECT *
     , SUM(salaire) OVER(PARTITION BY departement_id)
FROM EMPLOYE;

Affichage avec ROW_NUMBER (retourne le numéro dans la partition (fenêtre))


SELECT idEmploye, nom, idResponsable, salaire, prime, departement_id -- , fonction, date_embauche
     , SUM(salaire) OVER(PARTITION BY departement_id)
     , ROW_NUMBER() OVER (PARTITION BY departement_id )
FROM EMPLOYE;


SELECT idEmploye, nom, idResponsable, salaire, prime, departement_id  , fonction -- , date_embauche
     , SUM(salaire) OVER(PARTITION BY departement_id)  AS calcul_sum_part_dep
     , ROW_NUMBER() OVER (PARTITION BY departement_id ORDER BY nom DESC ) AS position_sum_part_dep
FROM EMPLOYE;

SELECT idEmploye, nom, idResponsable, salaire, prime, departement_id  , fonction -- , date_embauche
     , SUM(salaire) OVER(PARTITION BY fonction)  AS calcul_sum_part_dep
     , ROW_NUMBER() OVER (PARTITION BY fonction ORDER BY salaire DESC ) AS position_sum_part_dep
     , RANK() OVER (PARTITION BY fonction ORDER BY salaire DESC ) AS rang_sum_part_dep
FROM EMPLOYE;


 SELECT
        idEmploye,
        nom,
        fonction,
        idResponsable,
        date_embauche,
        salaire,
        prime,
        departement_id,
        ROW_NUMBER() OVER (PARTITION BY departement_id ORDER BY salaire DESC) AS salary_rank_number,
        RANK() OVER (PARTITION BY departement_id ORDER BY salaire DESC) AS salary_rank
    FROM
        EMPLOYE;

WITH RankedEmployees AS (
    SELECT
        idEmploye,
        nom,
        fonction,
        idResponsable,
        date_embauche,
        salaire,
        prime,
        departement_id,
        ROW_NUMBER() OVER (PARTITION BY departement_id ORDER BY salaire DESC) AS salary_rank
    FROM
        EMPLOYE
)
SELECT
    idEmploye,
    nom,
    fonction,
    idResponsable,
    date_embauche,
    salaire,
    prime,
    departement_id
FROM
    RankedEmployees
WHERE
    salary_rank <= 2;