ManylH

--3

SELECT 
    EMPLOYEE_NUMBER,
    RPAD(FIRST_NAME + ' ' + LAST_NAME, 25) AS NOM_PRENOM,
    DATEDIFF(YEAR, BIRTH_DATE, GETDATE()) AS AGE,
    DATEDIFF(YEAR, HIRE_DATE, GETDATE()) AS ANCIENNETE
FROM EMPLOYEES
WHERE TITLE = 'Mr.'
  AND (SALARY + ISNULL(COMMISSION, 0)) >= 8000

ManylH

--Employés affectés à plus d’un projet (avec le nombre de projets)
SELECT e.Nom, COUNT(ep.Projet_Num_P) AS Nombre_Projets
FROM Employe e
JOIN Employe_Projet ep ON e.Num_E = ep.Employe_Num_E
GROUP BY e.Nom
HAVING COUNT(ep.Projet_Num_P) > 1;

--Projets gérés par chaque département
SELECT d.Label, d.Nom_du_manager, p.Titre
FROM Departement d

ManylH

--Insertion des départements
INSERT INTO dbo.Departement (Num_S, Label, Nom_du_manager) VALUES
(1, 'IT', 'Alice Johnson'),
(2, 'HR', 'Bob Smith'),
(3, 'Marketing', 'Clara Bennett');

--Insertion des employés
INSERT INTO dbo.Employe (Num_E, Nom, Position, Salaire, Department_Num_S) VALUES
(101, 'John Doe', 'Développeur', 60000.00, 1),
(102, 'Jane Smith', 'Analyste', 55000.00, 2),

ManylH

IF OBJECT_ID('dbo.Employe_Projet', 'U') IS NOT NULL DROP TABLE dbo.Employe_Projet;
IF OBJECT_ID('dbo.Projet', 'U') IS NOT NULL DROP TABLE dbo.Projet;
IF OBJECT_ID('dbo.Employe', 'U') IS NOT NULL DROP TABLE dbo.Employe;
IF OBJECT_ID('dbo.Departement', 'U') IS NOT NULL DROP TABLE dbo.Departement;

CREATE TABLE dbo.Departement (
    Num_S INT NOT NULL PRIMARY KEY,
    Label VARCHAR(255) NOT NULL,
    Nom_du_manager VARCHAR(255) NOT NULL
);