Bienvenue

Dans cette section, vous trouverez des ressources sur les cours que j'enseigne à savoir de la documentation, du code, des vidéos etc. Dès que je trouve quelque chose d'intéressant à partager, je le mettrai à disposition ici pour mes étudiants.

---

Devoirs

Examen blanc

		/*************************************************************************************
		## Examen blanc
		## ************
		**************************************************************************************

		USE adventureworks;

		-- Question 1 : Création de requête
		-- ********************************
		-- # Votre code ici
		SELECT
			per.Title,
			per.LastName,
			per.FirstName,
			emp.JobTitle,
			emp.Gender
		FROM 
			person per, 
			employee emp
		WHERE
			per.BusinessEntityID = emp.BusinessEntityID AND
			emp.JobTitle LIKE '%engineer%' AND 
			emp.Gender = 'F';
			

		/*******************************************************************/

		-- Question 2 : Création de vue
		-- ****************************
		-- # Votre code ici
		DROP VIEW IF EXISTS vAdresseCompleteEmployes;
		GO

		CREATE VIEW vAdresseCompleteEmployes 
		AS 
		SELECT
			per.BusinessEntityID,
			per.Title,
			per.LastName,
			per.FirstName,
			adr.AddressLine1,
			adr.City,
			adr.PostalCode,
			aty.Name TypeAdresse,
			sta.Name ProvinceRegion,
			ema.EmailAddress Email,
			pho.PhoneNumber,
			pty.Name TypeTel	
		FROM
			person per,
			businessentityaddress bad,
			address adr,
			addresstype aty,
			stateprovince sta,
			emailaddress ema,
			personphone pho,
			phonenumbertype pty
		WHERE 
			per.BusinessEntityID = bad.BusinessEntityID AND 
			adr.AddressID = bad.AddressID AND 
			aty.AddressTypeID = bad.AddressTypeID AND 
			adr.StateProvinceID = sta.StateProvinceID AND 
			per.BusinessEntityID = ema.BusinessEntityID AND 
			per.BusinessEntityID = pho.BusinessEntityID AND 
			pho.PhoneNumberTypeID = pty.PhoneNumberTypeID;

		GO

		--# Vérification
		--# Votre code ici
		SELECT * FROM vAdresseCompleteEmployes;


		-- Question 3 : Création de table
		-- ******************************
		--# Votre code ici
		-- A-
		DROP TABLE IF EXISTS tblEmployeInfo;

		SELECT *
		INTO tblEmployeInfo
		FROM
			(
				SELECT 
					per.BusinessEntityID,
					per.Title,
					per.LastName,
					per.FirstName,
					emp.NationalIDNumber,
					emp.JobTitle,
					emp.HireDate,
					emp.Gender,
					emp.MaritalStatus,
					emp.VacationHours,
					emp.SickLeaveHours	
				FROM
					person per,
					employee emp
				WHERE emp.BusinessEntityID = per.BusinessEntityID
			) temp;

		/*
		B- Vérifier la création de cette nouvelle table dans le nouvelle base de données.
		-- Votre code ici --*/
		SELECT * FROM tblEmployeInfo;


		--C- Modifier la table tblEmployeInfo pour rajouter une clé primaire sur la colonne :
		--	- Le numéro d'entité (BusinessEntityID)
		-- Votre code ici

		ALTER TABLE tblEmployeInfo
			ADD PRIMARY KEY(BusinessEntityID);


		-- D - Modifier la table tblEmployeInfo pour rajouter les colonnes suivantes :
		-- Votre code ici

		ALTER TABLE tblEmployeInfo
			DROP COLUMN IF EXISTS GenreComplet;

		ALTER TABLE tblEmployeInfo
			DROP COLUMN IF EXISTS StatutMaritalComplet;

		ALTER TABLE tblEmployeInfo
			DROP COLUMN IF EXISTS JoursMaladie;

		ALTER TABLE tblEmployeInfo
			DROP COLUMN IF EXISTS JoursVacances;

		ALTER TABLE tblEmployeInfo
			ADD GenreComplet VARCHAR(20),
				StatutMaritalComplet VARCHAR(20),
				JoursMaladie DECIMAL(4,2),
				JoursVacances DECIMAL(4,2);

		-- # Vérification
		-- # Votre code ici
		EXEC sp_help tblEmployeInfo; 

		/* --------------------------------------------------------------------------------------------------------------
		E- Mise à jour de données
		*/
		-- # Votre code ici

		UPDATE tblEmployeInfo
			SET genreComplet = 
									CASE
										WHEN Gender = 'M' THEN 'Male'
										WHEN Gender = 'F' THEN 'Female'
									ELSE 'N/A'
									END,
				StatutMaritalComplet = 
											CASE
												WHEN MaritalStatus = 'S' THEN 'Single'
												WHEN MaritalStatus = 'M' THEN 'Married'
											ELSE
												'N/A'
											END,
				JoursMaladie = round(SickLeaveHours / 7.5 ,2),
				joursVacances = round(VacationHours / 7.5 , 2);
				

		-- # Vérification
		-- # Votre code ici		
		SELECT * FROM tblEmployeInfo;
											


		-- Question 4 : Création de tables avec spécifications
		-- ***************************************************
		-- # Votre code ici

		DROP TABLE IF EXISTS tblProprietaires;
		DROP TABLE IF EXISTS tblVoitures;
		DROP TABLE IF EXISTS tblMarques;

		-- tblMarques
		CREATE TABLE tblMarques (
			noMarque INT PRIMARY KEY IDENTITY(1,1),
			NomMarque NVARCHAR(20) NOT NULL,
			PaysMarque NVARCHAR(20)
		);

		-- tblVoitures
		CREATE TABLE tblVoitures (
			noSerie INT PRIMARY KEY IDENTITY(1,1),
			ModeleVoiture NVARCHAR(20) NOT NULL,
			CouleurVoiture NVARCHAR(20),
			Motorisation NVARCHAR(10),
			TypeVoiture NVARCHAR(15),
			NoMarque INT,
			FOREIGN KEY (NoMarque) REFERENCES tblMarques(NoMarque)
		);

		-- tblPropriétaires
		CREATE TABLE tblProprietaires (
			noProprietaire INT PRIMARY KEY IDENTITY(1,1),
			NomProprietaire VARCHAR(20) NOT NULL,
			PrenomProprietaire VARCHAR(20),
			NoSerie INT,
			FOREIGN KEY (NoSerie) REFERENCES tblVoitures(NoSerie)
		);

		-- # Question 12 : /6pts
		-- Insertion de valeurs dans les tables
		--# Votre code ici
		INSERT INTO tblMarques(NomMArque, PaysMarque)
		VALUES 
			('BMW', 'Allemagne'),
			('Mercedes-Benz', 'Allemagne'),
			('Lexus', 'Japon');
			

		INSERT INTO tblVoitures(ModeleVoiture,CouleurVoiture, Motorisation, TypeVoiture, NoMarque)
		VALUES 
			('750 iL', 'Gris', 'V8', 'Berline',1),
			('Maybach', 'Bleu-blanc', 'V12', 'Limousine',2),
			('S500', 'Vert','V8','Berline',2);
			
			
		INSERT INTO tblProprietaires(NomProprietaire, PrenomProprietaire, NoSerie)
		VALUES 
			('Doe', 'John', 1),
			('Doe', 'Jane', 3),
			('Blow', 'Joe',1);
		/* ************************* Fin de l'examen *****************************************/		
	  

---