DROP TABLE IF EXISTS EmployeeDetails;
DROP TABLE IF EXISTS EmployeeSalary;
CREATE TABLE EmployeeDetails (
EmpId INT,
FullName VARCHAR(255),
ManagerId INT,
DateOfJoining DATE,
City VARCHAR(255)
);
CREATE TABLE EmployeeSalary (
EmpId INT,
Project VARCHAR(255),
Salary INT,
Variables INT
);
-- EmployeeDetails table data
INSERT INTO EmployeeDetails (EmpId, FullName, ManagerId, DateOfJoining, City)
VALUES
(1, 'John Smith', NULL, '2020-03-15', 'New York'),
(4, 'Jane Doe', 1, '2019-07-10', 'Los Angeles'),
(6, 'Michael Johnson', 1, '2021-01-20', 'Chicago'),
(8, 'Emily Williams', 2, '2018-11-05', 'Houston'),
(9, 'David Brown', 3, '2022-02-18', 'Miami'),
(10, 'Sarah Wilson', 2, '2023-04-30', 'New York'),
(20, 'Robert Lee', 3, '2017-09-12', 'Seattle'),
(100, 'Olivia Martinez', NULL, '2022-08-03', 'Boston'),
(120, 'Daniel Taylor', 7, '2020-06-25', 'New York'),
(2, 'Sophia Anderson', 8, '2019-04-08', 'New York');
-- EmployeeSalary table data
INSERT INTO EmployeeSalary (EmpId, Project, Salary, Variables)
VALUES
(1, 'Project A', 60000, 5000),
(2, 'Project B', 75000, 6000),
(3, 'Project A', 80000, 7000),
(4, 'Project C', 55000, 4000),
(5, 'Project B', 65000, 5500),
(6, 'Project C', 70000, 6000),
(7, 'Project A', 72000, 5500),
(8, 'Project D', 68000, 5000),
(9, 'Project B', 60000, 4500),
(10, 'Project C', 62000, 4800);
-- Write a query to display both the EmpId and ManagerId together.
-- Expected results:
-- EmpId ManagerId
-- 1 NULL
-- 4 1
-- 6 1
-- 8 2
-- 9 3
-- 10 2
-- 20 3
-- 100 NULL
-- 120 7
-- 2 8
-- select EmpId,ManagerId from EmployeeDetails
-- Write a query to display all employee details from the EmployeeDetails table, only for
those employees who joined in the year 2020.
-- Expected results:
-- EmpId FullName ManagerId DateOfJoining City
-- 1 John Smith NULL 2020-03-15 New York
-- 120 Daniel Taylor 7 2020-06-25 New York
-- select * from EmployeeDetails
-- where extract(year from DateOfJoining) = 2020
-- Write a query to display all those employees who work on a project other than 'Project A'.
-- Expected results:
-- EmpId Project Salary Variables
-- 2 Project B 75000 6000
-- 4 Project C 55000 4000
-- 5 Project B 65000 5500
-- 6 Project C 70000 6000
-- 8 Project D 68000 5000
-- 9 Project B 60000 4500
-- 10 Project C 62000 4800
-- select [Link],[Link],[Link],[Link]
-- from EmployeeSalary e
-- where [Link] != 'Project A'
-- Write a query to display all employee records from the EmployeeDetails table, only for
those employees who also have a salary record in the EmployeeSalary table.
-- Expected results:
-- EmpId FullName ManagerId DateOfJoining City
-- 1 John Smith NULL 2020-03-15 New York
-- 2 Sophia Anderson 8 2019-04-08 New York
-- 4 Jane Doe 1 2019-07-10 Los Angeles
-- 6 Michael Johnson 1 2021-01-20 Chicago
-- 8 Emily Williams 2 2018-11-05 Houston
-- 9 David Brown 3 2022-02-18 Miami
-- 10 Sarah Wilson 2 2023-04-30 New York
-- select e.* from EmployeeDetails e
-- right join EmployeeSalary p
-- on [Link] = [Link]
-- where [Link] = [Link]
-- Write a query to fetch employee names and salary records. Display the employee details
even if a salary record is not present for the employee.
-- Expected results:
-- FullName Project Salary Variables
-- John Smith Project A 60000 5000
-- Sophia Anderson Project B 75000 6000
-- Jane Doe Project C 55000 4000
-- Michael Johnson Project C 70000 6000
-- Emily Williams Project D 68000 5000
-- David Brown Project B 60000 4500
-- Sarah Wilson Project C 62000 4800
-- Robert Lee NULL NULL NULL
-- Olivia Martinez NULL NULL NULL
-- Daniel Taylor NULL NULL NULL
select [Link],[Link],[Link],[Link]
from EmployeeDetails e
join
EmployeeSalary p
on [Link] = [Link]
where [Link] = [Link] and
