Use below tables for Q11 and Q12:
Create and insert tables:
Internal Use Only
CREATE TABLE EmployeeInfo (
EmpID INT PRIMARY KEY,
EmpFname VARCHAR(255),
EmpLname VARCHAR(255),
Department VARCHAR(255),
Project VARCHAR(255),
Address VARCHAR(255),
DOB DATE,
Gender CHAR(1)
);
CREATE TABLE EmployeePosition (
EmpID INT,
EmpPosition VARCHAR(255),
DateOfJoining DATE,
Salary INT
);
INSERT INTO EmployeeInfo (EmpID, EmpFname, EmpLname,
Department, Project, Address, DOB, Gender) VALUES
(1, 'Sanjay', 'Mehra', 'HR', 'P1', 'Hyderabad (HYD)', '1976-
12-01', 'M'),
(2, 'Ananya', 'Mishra', 'Admin', 'P2', 'Delhi (DEL)', '1968-05-
02', 'F'),
(3, 'Rohan', 'Diwan', 'Account', 'P3', 'Mumbai (BOM)', '1980-
01-01', 'M'),
(4, 'Sonia', 'Kulkarni', 'HR', 'P1', 'Hyderabad (HYD)', '1992-
05-02', 'F'),
(5, 'Ankit', 'Kapoor', 'Admin', 'P2', 'Delhi (DEL)', '1994-07-
03', 'M');
INSERT INTO EmployeePosition (EmpID, EmpPosition,
DateOfJoining, Salary) VALUES
(1, 'Manager', '2024-05-01', 500000),
(2, 'Executive', '2024-05-02', 75000),
(3, 'Manager', '2024-05-01', 90000),
(2, 'Lead', '2024-05-02', 85000),
Internal Use Only
(1, 'Executive', '2024-05-01', 300000);
1. Write a query to fetch all employees who also hold the
managerial position. Query result should have EmpFname,
EmpLname, EmpPosition as columns.
SELECT [Link], [Link], [Link]
FROM EmployeeInfo E INNER JOIN EmployeePosition P ON
[Link] = [Link] AND [Link] IN ('Manager');
2. Write a query to retrieve the list of employees working in
the same department. Query result should have EmpID,
EmpFname, Department as columns.
Select DISTINCT [Link], [Link], [Link]
FROM EmployeeInfo E, EmployeeInfo E1
WHERE [Link] = [Link] AND [Link] !=
[Link];
Internal Use Only