CREATE DATABASE CaseStudy2
USE CaseStudy2
CREATE TABLE LOCATION (
Location_ID INT PRIMARY KEY,
City VARCHAR(50));
INSERT INTO LOCATION (Location_ID, City) VALUES
(122, 'New York'),
(123, 'Dallas'),
(124, 'Chicago'),
(167, 'Boston');
CREATE TABLE DEPARTMENT (
Department_Id INT PRIMARY KEY,
Name VARCHAR(50),
Location_Id INT,
FOREIGN KEY (Location_Id) REFERENCES LOCATION(Location_ID)
);
INSERT INTO DEPARTMENT (Department_Id, Name, Location_Id)
VALUES (10, 'Accounting', 122),
(20, 'Sales', 124),
(30, 'Research', 123),
(40, 'Operations', 167);
CREATE TABLE JOB
(Job_ID INT PRIMARY KEY,
Designation VARCHAR(50));
-- CREATE TABLE JOB
-- (JOB_ID INT PRIMARY KEY,
-- DESIGNATION VARCHAR(20))
INSERT INTO JOB VALUES
(667, 'CLERK'),
(668,'STAFF'),
(669,'ANALYST'),
(670,'SALES_PERSON'),
(671,'MANAGER'),
(672, 'PRESIDENT')
CREATE TABLE EMPLOYEE
(EMPLOYEE_ID INT,
LAST_NAME VARCHAR(20),
FIRST_NAME VARCHAR(20),
MIDDLE_NAME CHAR(1),
JOB_ID INT FOREIGN KEY
REFERENCES JOB(JOB_ID),
MANAGER_ID INT,
HIRE_DATE DATE,
SALARY INT,
COMM INT,
DEPARTMENT_ID INT FOREIGN KEY
REFERENCES DEPARTMENT(DEPARTMENT_ID))
INSERT INTO EMPLOYEE VALUES
(7369,'SMITH','JOHN','Q',667,7902,'17-DEC-84',800,NULL,20),
(7499,'ALLEN','KEVIN','J',670,7698,'20-FEB-84',1600,300,30),
(7505,'DOYLE','JEAN','K',671,7839,'04-APR-85',2850,NULl,30),
(7506,'DENNIS','LYNN','S',671,7839,'15-MAY-85',2750,NULL,30),
(7507,'BAKER','LESLIE','D',671,7839,'10-JUN-85',2200,NULL,40),
(7521,'WARK','CYNTHIA','D',670,7698,'22-FEB-85',1250,500,30)
SELECT * FROM LOCATION
SELECT * FROM DEPARTMENT
SELECT * FROM JOB
SELECT * FROM EMPLOYEE
-- SIMPLE QUERIES:
-- 1. List all the employee details.
SELECT * FROM EMPLOYEE
-- 2. List all the department details.
SELECT * FROM DEPARTMENT
-- 3. List all job details.
SELECT * FROM JOB
-- 4. List all the locations.
SELECT * FROM LOCATION
-- 5. List out the First Name, Last Name, Salary, Commission for all Employees.
SELECT First_Name, Last_Name, Salary, Comm FROM EMPLOYEE
-- 6. List out the Employee ID, Last Name, Department ID for all employees and alias Employee ID as
"ID of the Employee", Last Name as "Name of the Employee", Department ID as "Dep_id".
SELECT EMPLOYEE_ID AS ID_of_the_Employee, LAST_NAME AS Name_of_the_Employee,
DEPARTMENT_ID AS Dep_id FROM EMPLOYEE
-- 7. List out the annual salary of the employees with their names only.
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS Employee_Name, SALARY*12 AS Annual_Salary
FROM EMPLOYEE
-- WHERE CONDITION:
-- 1. List the details about "Smith".
SELECT * FROM EMPLOYEE
WHERE FIRST_NAME = 'Smith' OR LAST_NAME = 'Smith'
-- 2. List out the employees who are working in department 20.
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID = 20
-- 3. List out the employees who are earning salaries between 3000 and4500.
SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 3000 AND 4500
-- 4. List out the employees who are working in department 10 or 20.
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID = 10 OR DEPARTMENT_ID = 20
-- 5. Find out the employees who are not working in department 10 or 30.
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID != 10 AND DEPARTMENT_ID != 30
-- 6. List out the employees whose name starts with 'S'.
SELECT * FROM EMPLOYEE
WHERE FIRST_NAME LIKE 'S%' OR LAST_NAME LIKE 'S%'
-- 7. List out the employees whose name starts with 'S' and ends with 'H'.
SELECT * FROM EMPLOYEE
WHERE FIRST_NAME LIKE 'S%H' OR LAST_NAME LIKE 'S%H'
-- 8. List out the employees whose name length is 4 and start with 'S'.
SELECT * FROM EMPLOYEE
WHERE LEN(FIRST_NAME) = 4 AND (FIRST_NAME LIKE 'S%' OR LAST_NAME LIKE 'S%')
-- 9. List out employees who are working in department 10 and draw salaries more than 3500.
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID = 10 AND SALARY > 3500
-- 10 List out the employees who are not receiving commission.
SELECT * FROM EMPLOYEE
WHERE COMM IS NULL
-- ORDER BY CLAUSE:
-- 1. List out the Employee ID and Last Name in ascending order based on the Employee ID.
SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEE
ORDER BY EMPLOYEE_ID
-- 2. List out the Employee ID and Name in descending order based on salary.
SELECT EMPLOYEE_ID, CONCAT(FIRST_NAME, ' ', LAST_NAME) AS Employee_Name, SALARY FROM
EMPLOYEE
ORDER BY SALARY DESC
-- 3. List out the employee details according to their Last Name in ascending-order.
SELECT * FROM EMPLOYEE
ORDER BY LAST_NAME DESC
-- 4. List out the employee details according to their Last Name in ascending order and then
Department ID in descending order.
SELECT * FROM EMPLOYEE
ORDER BY LAST_NAME
SELECT * FROM EMPLOYEE
ORDER BY DEPARTMENT_ID DESC
-- GROUP BY AND HAVING CLAUSE
-- 1. How many employees are in different departments in the organization?
SELECT DEPARTMENT_ID, COUNT(LAST_NAME) AS Ct FROM EMPLOYEE AS E
GROUP BY DEPARTMENT_ID
-- 2. List out the department wise maximum salary, minimum salary and average salary of the
employees.
SELECT DEPARTMENT_ID, MAX(SALARY) AS Maximum_Salary, MIN(SALARY) AS Minimum_Salary,
AVG(SALARY) AS Avgerage_Salary FROM EMPLOYEE
GROUP BY DEPARTMENT_ID
-- 3. List out the job wise maximum salary, minimum salary and average salary of the employees.
SELECT JOB_ID, MAX(SALARY) AS Maximum_Salary, MIN(SALARY) AS Minimum_Salary, AVG(SALARY)
AS Avgerage_Salary FROM EMPLOYEE
GROUP BY JOB_ID
-- 4. List out the number of employees who joined each month in ascendingorder.
SELECT HIRE_DATE, COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE
GROUP BY HIRE_DATE
ORDER BY MONTH(HIRE_DATE)
-- 5. List out the number of employees for each month and year in ascending order based on the
year and month.
SELECT YEAR(HIRE_DATE) AS Hire_Year, MONTH(HIRE_DATE) Hire_Month, COUNT(EMPLOYEE_ID)
AS Ct FROM EMPLOYEE
GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
ORDER BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
-- 6. List out the Department ID having at least four employees.
SELECT Department_ID, COUNT(Employee_ID) FROM Employee
GROUP BY Department_ID
HAVING COUNT(Employee_ID)>=4
-- 7. How many employees joined in the month of January?
SELECT MONTH(HIRE_DATE) Mon, COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE
GROUP BY MONTH(HIRE_DATE)
HAVING MONTH(HIRE_DATE) = 1
-- 8. How many employees joined in the month of January or September?
SELECT MONTH(HIRE_DATE) Mon, COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE
GROUP BY MONTH(HIRE_DATE)
HAVING MONTH(HIRE_DATE) = 1 OR MONTH(HIRE_DATE) = 9
-- 9. How many employees joined in 1985?
SELECT YEAR(HIRE_DATE) Yr , COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE
GROUP BY YEAR(HIRE_DATE)
HAVING YEAR(HIRE_DATE) = 1985
-- 10. How many employees joined each month in 1985?
SELECT YEAR(HIRE_DATE) Yr, MONTH(HIRE_DATE) Mon , COUNT(EMPLOYEE_ID) AS Ct FROM
EMPLOYEE
GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
HAVING YEAR(HIRE_DATE) = 1985
ORDER BY MONTH(HIRE_DATE)
-- 11. How many employees joined in March 1985?
SELECT YEAR(HIRE_DATE) Yr, MONTH(HIRE_DATE) Mon , COUNT(EMPLOYEE_ID) AS Ct FROM
EMPLOYEE
GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
HAVING YEAR(HIRE_DATE) = 1985 AND MONTH(HIRE_DATE) = 3
-- 12. Which is the Department ID having greater than or equal to 3 employees joining in April 1985?
SELECT DEPARTMENT_ID, YEAR(HIRE_DATE) AS Yr, MONTH(HIRE_DATE) Mon,
COUNT(EMPLOYEE_ID) Ct FROM EMPLOYEE
GROUP BY DEPARTMENT_ID, YEAR(HIRE_DATE), MONTH(HIRE_DATE)
HAVING COUNT(EMPLOYEE_ID)>= 3 AND MONTH(HIRE_DATE) = 4 AND YEAR(HIRE_DATE) = 1985
-- JOINS
-- 1. List out employees with their department names.
SELECT E.*, D.Name FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
-- 2. Display employees with their designations.
SELECT E.*, J.Designation FROM EMPLOYEE AS E
JOIN
JOB AS J
ON J.Job_ID = E.JOB_ID
-- 3. Display the employees with their department names and regional groups.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, D.Name, L.City FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
JOIN
LOCATION AS L
ON D.Location_Id = L.Location_ID
-- 4. How many employees are working in different departments? Display with department names.
SELECT E.DEPARTMENT_ID, D.Name, COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, D.Name
-- 5. How many employees are working in the sales department?
SELECT E.DEPARTMENT_ID, D.Name, COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, D.Name
HAVING D.Name LIKE 'Sales'
-- 6. Which is the department having greater than or equal to 5 employees? Display the department
names in ascending order.
SELECT E.DEPARTMENT_ID, D.Name, COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
GROUP BY E.DEPARTMENT_ID, D.Name
HAVING COUNT(EMPLOYEE_ID)>=5
-- 7. How many jobs are there in the organization? Display with designations.
SELECT J.DESIGNATION, COUNT(E.JOB_ID) FROM EMPLOYEE AS E
JOIN
JOB AS J
ON E.JOB_ID = J.Job_ID
GROUP BY j.Designation
-- 8. How many employees are working in "New York"?
SELECT COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
JOIN
LOCATION AS L
ON D.Location_Id = L.Location_ID
GROUP BY L.City
HAVING City = 'New York'
-- 9. Display the employee details with salary grades. Use conditional statement to create a grade
column.
SELECT *,
CASE
WHEN SALARY < 1000 THEN 'LOW'
WHEN SALARY BETWEEN 1000 AND 2000 THEN 'MEDIUM'
ELSE 'HIGH'
END
AS SALARY_CATEGORY
FROM EMPLOYEE
-- 10. List out the number of employees grade wise. Use conditional statement to create a grade
column.
SELECT E.SalGrade, COUNT(1) FROM
( SELECT Employee_ID,SalGrade=
CASE
WHEN Salary<2000 THEN 'C'
WHEN Salary BETWEEN 2000 AND 5000 THEN 'B'
ELSE 'A'
END
FROM Employee ) AS E
GROUP BY E.SalGrade
SELECT EMPLOYEE_ID, SALARY_CATEGORY =
CASE
WHEN SALARY < 1000 THEN 'LOW'
WHEN SALARY BETWEEN 1000 AND 2000 THEN 'MEDIUM'
ELSE 'HIGH'
END
AS SALARY_CATEGORY
FROM EMPLOYEE
-- 11. Display the employee salary grades and the number of employees between 2000 to 5000
range of salary.
SELECT E.SalGrade, COUNT(1) FROM
( SELECT Employee_ID,SalGrade=
CASE
WHEN Salary<2000 THEN 'C'
WHEN Salary BETWEEN 2000 AND 5000 THEN 'B'
ELSE 'A'
END
FROM Employee
WHERE Salary BETWEEN 2000 AND 5000) AS E
GROUP BY E.SalGrade
-- 12. Display all employees in sales or operation departments.
SELECT * FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
WHERE D.Name in ('Sales', 'Operations')
-- SET OPERATORS:
-- 1. List out the distinct jobs in sales and accounting departments.
SELECT DISTINCT (JOB_ID) FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
WHERE D.Name in ('Sales', 'Accounting')
-- 2. List out all the jobs in sales and accounting departments.
SELECT JOB_ID FROM EMPLOYEE AS E
JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
WHERE D.Name in ('Sales', 'Accounting')
-- 3. List out the common jobs in research and accounting departments in ascending order.
SELECT JOB_ID FROM EMPLOYEE AS E
INNER JOIN
DEPARTMENT AS D
ON E.DEPARTMENT_ID = D.Department_Id
WHERE D.Name in ('Research', 'Accounting')
ORDER BY JOB_ID ASC
-- SUB QUERIES:
-- 1. Display the employees list who got the maximum salary.
SELECT * FROM EMPLOYEE
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE)
-- 2. Display the employees who are working in the sales department.
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE NAME = 'Sales')
-- 3. Display the employees who are working as 'Clerk'.
SELECT * FROM EMPLOYEE
WHERE JOB_ID = (SELECT JOB_ID FROM JOB WHERE Designation = 'CLERK')
-- 4. Display the list of employees who are living in "New York".
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID =
(SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE Location_Id =
(SELECT Location_Id FROM LOCATION WHERE City = 'New York')
-- 5. Find out the number of employees working in the sales department.
SELECT COUNT(EMPLOYEE_ID) AS Ct FROM EMPLOYEE
WHERE DEPARTMENT_ID =
(SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE Name = 'Sales')
-- 6. Update the salaries of employees who are working as clerks on the basis of 10%.
CREATE FUNCTION Rev_Sal (@Salary FLOAT, @Increament FLOAT)
RETURNS INT
AS
BEGIN
RETURN @Salary*(1+@increament)
END;
SELECT *, dbo.Rev_Sal (Salary,0.10) AS Increamented_Salary FROM EMPLOYEE
WHERE JOB_ID =
(SELECT JOB_ID FROM JOB WHERE Designation = 'Clerk')
-- 7. Delete the employees who are working in the accounting department.
DELETE EMPLOYEE
WHERE DEPARTMENT_ID =
(SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE Name = 'Accounting')
-- 8. Display the second highest salary drawing employee details.
SELECT * FROM
(SELECT *, RANK()OVER(ORDER BY SALARY DESC) AS R FROM EMPLOYEE ) AS RT
WHERE R=2
-- 9. Display the nth highest salary drawing employee details.
CREATE FUNCTION NSAL(@NPOS INT)
RETURNS TABLE
AS RETURN ( SELECT * FROM (SELECT *, RANK()OVER(ORDER BY Salary DESC) AS
R FROM Employee ) AS RT
WHERE R=@NPOS)
SELECT * FROM dbo.NSAL(5)
-- 10. List out the employees who earn more than every employee in department 30.
SELECT * FROM Employee WHERE Salary =
(SELECT MAX(Salary) FROM Employee WHERE Department_ID=30 GROUP BY
Department_ID)
-- 11. List out the employees who earn more than the lowest salary in department. (Why am I getting
only one department Id)
SELECT * FROM Employee WHERE Salary >
(SELECT MIN(Salary) FROM Employee WHERE Department_ID=30 GROUP BY
Department_ID)
-- 12. Find out which department has no employees.
SELECT NAME FROM DEPARTMENT WHERE Department_Id NOT IN (SELECT Department_Id FROM
EMPLOYEE)
-- 13. Find out the employees who earn greater than the average salary for their department. (Why
am I getting only one department Id)
WITH AvgSal AS
(SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEE
GROUP BY DEPARTMENT_ID)
SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID FROM AvgSal AS AvS
JOIN
EMPLOYEE AS E
ON AvS.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.SALARY > AvS.AVG_SALARY
GROUP BY E.DEPARTMENT_ID, E.EMPLOYEE_ID
