SHOW DATABASES; USE ssd_assignment_2; CREATE TABLE PERSON ( EmpID int NOT NULL PRIMARY KEY, NamePrefix varchar(10), FirstName varchar(255), MiddleInitial varchar(10), LastName varchar(255), Gender varchar(10), Email varchar(255), FatherName varchar(255), MotherName varchar(255), MotherMaidenName varchar(255), DOB varchar(12), TimeOfBirth varchar(12), WeightInKgs int, DateOfJoining varchar(12),TimeOfBirth Salary int, LastHike varchar(10), PlaceName varchar(255), Country varchar(255), City varchar(255), State varchar(255), Region varchar(255) ); -- SELECTING PERSON TABLE select * from PERSON; -- 1.A----------------------------------- -- CREATING hike2022 table CREATE TABLE hike2022 ( HikePK int AUTO_INCREMENT PRIMARY KEY, EmpIDFK INT , FirstName varchar(255), LastName varchar(255), Gender varchar(255), WeightInKg INT, LastHike varchar(10), LastSalary INT, NewHike varchar(10), NewSalary INT , FOREIGN KEY ( EmpIDFK) REFERENCES PERSON ( EmpID) ); -- drop hike2022 table drop table hike2022; -- INSERTING FROM TABLE PERSON INSERT INTO hike2022 ( EmpIDFK, FirstName, LastName, Gender, WeightInKg, LastHike, LastSalary ) select EmpID, FirstName, LastName, Gender, WeightInKgs, LastHike, Salary from PERSON WHERE WeightInKgs < 50; -- SELECTING FROM hike2022 table SELECT * FROM hike2022; -- PROCEDURE FOR UPDATE_HIKE --------- drop PROCEDURE UPDATE_HIKE; DELIMITER // CREATE PROCEDURE UPDATE_HIKE() BEGIN UPDATE hike2022 SET NewHike = CONCAT(cast(CAST(substring(LastHike, 1, length(LastHike)-1) AS unsigned) + 2 as char) , "%" ) where HikePK != 0 ; UPDATE hike2022 SET Newsalary = LastSalary + (LastSalary * CAST(substring(NewHike, 1, length(NewHike)-1) AS unsigned)) / 100 where HikePK != 0 ; END // DELIMITER ; -- PROCEDURE ENDED -- EXECUTE AND PRINT HIKE TABLE call `UPDATE_HIKE`(); select * from hike2022; -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= drop table PersonJoining; -- 1.B.--------------------------------------------------------------------- CREATE TABLE PersonJoining ( PJoinPK int AUTO_INCREMENT PRIMARY KEY, EmpIDFK INT , FirstName varchar(255), LastName varchar(255), DateofBirth varchar(12), Age INT , DateofJoining varchar(255), DayofJoining varchar(255), MonthofJoining varchar(255), YearofJoining varchar(255), WorkExpinDays varchar(255) , FOREIGN KEY ( EmpIDFK) REFERENCES PERSON ( EmpID) ); -- INSERTING FROM TABLE PERSON TO PERSON JOINING INSERT INTO PersonJoining ( EmpIDFK, FirstName, LastName, DateofBirth, DateofJoining ) select EmpID, FirstName, LastName, DOB, DateofJoining from PERSON ; -- SELECT FROM PERSONJIONING SELECT * FROM PersonJoining ; -- PROCEDURE FOR UPDATE_DATES --------- drop PROCEDURE UPDATE_DATES; DELIMITER // CREATE PROCEDURE UPDATE_DATES() BEGIN UPDATE PersonJoining SET AGE =(DATEDIFF(CURRENT_DATE, STR_TO_DATE(DateofBirth, '%m/%d/%Y'))/365.25) where PJoinPK != 0 ; UPDATE PersonJoining SET DayofJoining = substring(DateofJoining,length(DateofJoining)-6, 2) where PJoinPK != 0 ; UPDATE PersonJoining SET MonthofJoining = monthname(STR_TO_DATE(DateofJoining, '%m/%d/%Y')) where PJoinPK != 0 ; UPDATE PersonJoining SET YearofJoining = substring(DateofJoining,length(DateofJoining)-3, length(DateofJoining) ) where PJoinPK != 0 ; UPDATE PersonJoining SET WorkExpinDays = (DATEDIFF(CURRENT_DATE, STR_TO_DATE(DateofJoining, '%m/%d/%Y'))) where PJoinPK != 0 ; END // DELIMITER ; -- PROCEDURE ENDED -- EXECUTE AND PRINT HIKE TABLE call `UPDATE_DATES`(); select * from PersonJoining; -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= DESC PERSON; drop TABLE PersonTransfer ; -- 1.c ------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>. CREATE TABLE PersonTransfer ( PTPK int AUTO_INCREMENT PRIMARY KEY, EmpIDFK INT, FirstName varchar(255), LastName varchar(255), Gender varchar(10), DateofJoining varchar(12), CurrentRegion varchar(255), NewRegion varchar(255), WorkExpinYear int , FOREIGN KEY ( EmpIDFK) REFERENCES PERSON ( EmpID) ); -- INSERTING FROM TABLE PERSON TO PERSON JOINING INSERT INTO PersonTransfer ( EmpIDFK, FirstName, LastName, Gender, DateofJoining, CurrentRegion ) select EmpID, FirstName, LastName, Gender, DateofJoining, Region from PERSON ; -- SELECT FROM PERSONJIONING SELECT * FROM PersonTransfer ; -- Populate NewRegion column to “DC” using your stored procedure -- for employees with Gender “F” whose Work Experience is more than 10 years. Populate -- the NewRegion column to “Capitol” using the same stored procedure for employees with -- Gender “M” whose Work Experience is more than 20 years. Re-running the storedprocedure should delete the data from existing PersonTransfer table and should -- repopulate the PersonTransfer tabl -- PROCEDURE FOR UPDATE_DATES --------- drop PROCEDURE UPDATE_Transfer_city; DELIMITER // CREATE PROCEDURE UPDATE_Transfer_city() BEGIN UPDATE PersonTransfer SET WorkExpinYear = (DATEDIFF(CURRENT_DATE, STR_TO_DATE(DateofJoining, '%m/%d/%Y'))/365.25) where PTPK != 0 ; UPDATE PersonTransfer SET NewRegion = "DC" where PTPK != 0 AND Gender ="F" AND WorkExpinYear >10 ; UPDATE PersonTransfer SET NewRegion = "Capitol" where PTPK != 0 AND Gender ="M" AND WorkExpinYear > 20 ; END // DELIMITER ; -- PROCEDURE ENDED -- EXECUTE AND PRINT HIKE TABLE call `UPDATE_Transfer_city`(); select * from PersonTransfer; -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= -- ============================================================================================================================= Q3---> SELECT REGION ,TIME_A FROM PERSON -- dummy DROP PROCEDURE IF EXISTS Person_T; DELIMITER // CREATE PROCEDURE Person_T() BEGIN -- Drop Table IF exists PersonTransfer; CREATE TABLE PersonTransfer1( PT int PRIMARY KEY AUTO_INCREMENT, emp_ID int, FOREIGN KEY (emp_ID) REFERENCES PERSON(EmpID), FirstName varchar(200), LastName varchar(200), Gender varchar(10), DateofJoining date, CurrentRegion varchar(200), NewRegion varchar (200) ); INSERT INTO PersonTransfer1(emp_ID, FirstName,LastName,Gender, DateofJoining, CurrentRegion) SELECT EmpID, FirstName,LastName,Gender,STR_TO_DATE(DateofJoining, "%m/%d/%Y"),Region FROM PERSON; UPDATE PersonTransfer1 set NewRegion="DC" WHERE PT!=0 AND Gender="F" AND (DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),DateofJoining)), '%Y') + 0)>10; END // DELIMITER ; CALL Person_T(); select * from PersonTransfer1 ; UPDATE PERSON SET TimeOfBirth = STR_TO_DATE( TimeofBirth , ' %h:%i:%s %p' ) where EmpID != 0; SELECT Region, COUNT(case when time_format(TimeOfBirth,"%T") between '00:00:00' and '08:00:00' then 1 else 0 end) as daystart, COUNT(case when time_format(TimeOfBirth,"%T") between '08:01:00' and '15:00:00' then 1 else 0 end) as midday, COUNT(case when time_format(TimeOfBirth,"%T") between '15:01:00' and '23:59:00' then 1 else 0 end) as lastday FROM PERSON -- where TimeofBirth between 00: GROUP BY Region;