ThanhVP3_ASQL_Assignment 201.sql

CREATE DATABASE [ASQL ASSIGNMENT 201]
GO 
USE  [ASQL ASSIGNMENT 201]
GO

--Q1 1. UPDATE THE DATABASE SCHEMA ABOVE TO REFLECT THE BUSINESS LOGICS, DESIGN
CREATE SCHEMA WORK;
GO
--Q2 2. WRITE :
 --A. CREATE THE TABLES (WITH THE MOST APPROPRIATE FIELD/COLUMN CONSTRAINTS & TYPES) 
 --AND ADD ATLEAST 5 RECORDS INTO EACH CREATED TABLE.

CREATE TABLE [WORK].PROJECTS(
 PROJECTID INT PRIMARY KEY,
 PROJECTNAME VARCHAR(50) NOT NULL,
 PROJECTSTARTDATE DATETIME  NOT NULL,
 PROJECTDESCRIPTION VARCHAR(255) NOT NULL,
 PROJECTDETAIL VARCHAR(255) NOT NULL,
 PROJECTCOMPLETEON DATETIME  NOT NULL,
)

CREATE TABLE [WORK].EMPLOYEE(
EMPLOYEEID INT PRIMARY KEY,
EMPLOYEELASTNAME VARCHAR(50) NOT NULL,
EMPLOYEEFIRSTNAME VARCHAR(50) NOT NULL,
EMPLOYEEHIREDATE DATE NOT NULL,
EMPLOYEESTATUS VARCHAR(255) NOT NULL,
SUPERVISORID INT NOT NULL,
SOCIALSECURITYNUMBER INT NOT NULL UNIQUE,
CONSTRAINT FK_SUPERVISOR FOREIGN KEY (SUPERVISORID) REFERENCES  [WORK].EMPLOYEE(EMPLOYEEID) 
on Update Cascade on Delete set null
)

CREATE TABLE [WORK].PROJECT_MODULES(
MODULEID INT ,
PROJECTID INT NOT NULL,
EMPLOYEEID INT NOT NULL,
PROJECTMODULESDATE DATETIME NOT NULL,
PROJECTMODULESCOMPLEDON DATETIME NOT NULL,
PROJECTMODULESDESCRIPTION VARCHAR(255)
CONSTRAINT PK_PROJECT_MODULES PRIMARY KEY (MODULEID),
CONSTRAINT FK_PROJECT FOREIGN KEY (PROJECTID) REFERENCES [WORK].PROJECTS(PROJECTID),
CONSTRAINT FK_EMPLOYEE FOREIGN KEY (EMPLOYEEID) REFERENCES [WORK].EMPLOYEE(EMPLOYEEID)
)

CREATE TABLE [WORK].WORK_DONE(
WORKDONEID INT PRIMARY KEY,
MODULEID INT NOT NULL,
EMPLOYEEID INT NOT NULL,
WORKDONEDATE DATETIME NOT NULL,
WORKDONEDESCRIPTION VARCHAR(255),
WORKDONESTATUS VARCHAR(255),
CONSTRAINT FK_PROJECT_MODULE FOREIGN KEY (MODULEID) REFERENCES [WORK].PROJECT_MODULES(MODULEID),
CONSTRAINT FK_EMPLOYEE2 FOREIGN KEY (EMPLOYEEID) REFERENCES [WORK].EMPLOYEE(EMPLOYEEID)
)
GO

INSERT INTO [WORK].PROJECTS 
VALUES (1,'PROJECT A','2022-11-11','NONE','NONE','2023-11-11'),
(2,'PROJECT B','2022-11-12','NONE','NONE','2023-11-12'),
(3,'PROJECT C','2022-11-14','NONE','NONE','2023-11-13'),
(4,'PROJECT D','2022-11-15','NONE','NONE','2023-11-14'),
(5,'PROJECT E','2022-11-16','NONE','NONE','2023-11-15')
GO

INSERT INTO [WORK].EMPLOYEE
VALUES (1,'THANH','VU','2022-09-05','NONE',1,1234),
 (2,'LINH','VO','2022-09-05','NONE',1,1235),
 (3,'DUC','LE','2022-09-05','NONE',1,1236),
 (4,'MINH','PHAM','2022-09-05','NONE',1,1237),
 (5,'LAN','DAO','2022-09-05','NONE',1,1238),
 (6,'MINH','ANH','2022-09-05','NONE',2,1239)
 GO

 INSERT INTO [WORK].PROJECT_MODULES
 VALUES (1,2,2,'2022-12-12','2022-12-24','NONE'),
 (2,2,2,'2022-12-12','2022-12-14','NONE'),
 (3,3,1,'2022-12-12','2022-12-14','NONE'),
 (4,3,1,'2022-12-12','2022-12-15','NONE'),
 (5,4,4,'2022-12-12','2022-12-22','NONE'),
 (6,1,5,'2022-12-12','2022-12-26','NONE'),
 (7,2,4,'2022-12-12','2022-12-24','NONE'),
 (8,2,5,'2022-12-12','2022-12-24','NONE')
 GO
 
 INSERT INTO  [WORK].WORK_DONE
 VALUES (1,2,2,'2022-12-22','NONE','COMPLETED'),
 (2,2,2,'2022-12-13','NONE','COMPLETED'),
 (3,2,2,'2022-12-13','NONE','COMPLETED'),
 (4,3,1,'2022-12-13','I AM SICK SO I CAN NOT COMPLETED THIS MODULE','NOT COMPLETED'),
 (5,4,1,'2022-12-14','RESIGN','NOT COMPLETED'),
 (6,2,3,'2022-12-12','2022-12-24','COMPLETED'),
 (7,2,3,'2022-12-22','NONE','COMPLETED'),
 (8,4,4,'2022-12-22','NONE','COMPLETED')
 GO

 --B: SELECT THE MODULES THAT EMPLOYEE HAVE ID = 3 HAS BEEN WORKING ON.
  INSERT INTO [WORK].PROJECT_MODULES
 VALUES (9,3,3,'2022-12-12','2022-12-24','NONE')

SELECT E.EMPLOYEEID,CONCAT(E.EMPLOYEEFIRSTNAME,' ',E.EMPLOYEELASTNAME)AS [FULL NAME],P.MODULEID,P.PROJECTID 
FROM [WORK].EMPLOYEE E JOIN [WORK].PROJECT_MODULES P
ON E.EMPLOYEEID = P.EMPLOYEEID
WHERE E.EMPLOYEEID = 3

--C. SELECT UNCOMPLETED PROJECTS WHICH HAVE RESIGNED MEMBERS.
SELECT P.PROJECTID,CONCAT(E.EMPLOYEEFIRSTNAME,' ',E.EMPLOYEELASTNAME)AS [FULL NAME]
FROM [WORK].EMPLOYEE  E
INNER JOIN [WORK].PROJECT_MODULES P ON E.EMPLOYEEID = P.EMPLOYEEID 
INNER JOIN [WORK].WORK_DONE W ON P.MODULEID = W.MODULEID
WHERE W.WORKDONEDESCRIPTION = 'RESIGN'


--D. SELECT THE WORKS INFORMATION THAT EMPLOYEE HAVE ID = 3 HAS BEEN INVOLVING THOUGH THOSE WERE NOT ASSIGNED TO HIM/HER.
 INSERT INTO [WORK].PROJECT_MODULES
 VALUES (10,3,4,'2022-12-12','2022-12-24','NONE')

 INSERT INTO  [WORK].WORK_DONE
 VALUES (10,10,3,'2022-12-22','NONE','COMPLETED')

 SELECT W.EMPLOYEEID,W.MODULEID,W.WORKDONESTATUS FROM [WORK].EMPLOYEE E JOIN [WORK].PROJECT_MODULES P
 ON E.EMPLOYEEID = P.EMPLOYEEID  JOIN [WORK].WORK_DONE W
 ON P.MODULEID = W.MODULEID
 WHERE P.EMPLOYEEID !=3 AND W.EMPLOYEEID = 3

--E. REMOVE THE PROJECT INFORMATION AFTER THREE MONTHS OF PROJECT COMPLETION.
INSERT INTO [WORK].PROJECTS 
VALUES (6,'PROJECT F','2022-11-11','NONE','NONE','2022-06-11')
GO

SELECT *  FROM [WORK].PROJECTS
WHERE DATEDIFF(MONTH,[WORK].PROJECTS.PROJECTCOMPLETEON,GETDATE()) = 3;

DELETE  FROM [WORK].PROJECTS
WHERE DATEDIFF(MONTH,[WORK].PROJECTS.PROJECTCOMPLETEON,GETDATE()) = 3;


--F. SELECT UNCOMPLETED MODULES, EMPLOYEE NAME AND EMPLOYEE’S REPORTS ABOUT THOSE MODULES.

SELECT * FROM [WORK].EMPLOYEE E 
INNER JOIN [WORK].PROJECT_MODULES PM
ON E.EMPLOYEEID = PM.EMPLOYEEID 
INNER JOIN [WORK].WORK_DONE W
ON PM.MODULEID = W.MODULEID
WHERE W.WORKDONESTATUS = 'NOT COMPLETED'

--G. SELECT EMPLOYEEID, SUPERVISOR’S FIRSTNAME, SUPERVISOR’S LASTNAME AND ALL 
--PROJECT NAME THAT EMPLOYEE AND THEIR’S SUPERVISOR WORK TOGETHER WITH EMPLOYEEID =2.

SELECT E.EMPLOYEEID,S.EMPLOYEEFIRSTNAME AS SUPERVISORFIRSTNAME,
S.EMPLOYEELASTNAME AS SUPERVISORLASTNAME,P.PROJECTNAME
FROM [WORK].EMPLOYEE E JOIN [WORK].EMPLOYEE S
ON E.SUPERVISORID = S.EMPLOYEEID
INNER JOIN [WORK].PROJECT_MODULES PM
ON E.EMPLOYEEID = PM.EMPLOYEEID 
INNER JOIN [WORK].PROJECTS P
ON PM.PROJECTID = P.PROJECTID
WHERE EXISTS (SELECT 1 FROM  [WORK].PROJECT_MODULES  WHERE  [WORK].PROJECT_MODULES.EMPLOYEEID = E.EMPLOYEEID) AND
EXISTS (SELECT 1 FROM [WORK].PROJECT_MODULES  WHERE [WORK].PROJECT_MODULES.EMPLOYEEID = S.EMPLOYEEID)
AND E.EMPLOYEEID = 2

--ORIGNAL

SELECT E.EMPLOYEEID, SP.EMPLOYEEFIRSTNAME, SP.EMPLOYEELASTNAME, P.PROJECTNAME FROM [WORK].EMPLOYEE  E
JOIN [WORK].EMPLOYEE  SP ON E.SUPERVISORID = SP.EMPLOYEEID
JOIN [WORK].PROJECT_MODULES PM ON PM.EMPLOYEEID = E.EMPLOYEEID
JOIN [WORK].PROJECTS P ON P.PROJECTID = PM.PROJECTID
WHERE EXISTS (SELECT 1 FROM [WORK].PROJECT_MODULES PRM WHERE PRM.EMPLOYEEID = E.EMPLOYEEID) AND
EXISTS (SELECT 1 FROM [WORK].PROJECT_MODULES PRM WHERE PRM.EMPLOYEEID = SP.EMPLOYEEID)
AND E.EMPLOYEEID = 2