sql
Fri Sep 16 2022 05:52:44 GMT+0000 (Coordinated Universal Time)
Saved by @Lokesh8126 #c++
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;
Comments