Q.1 Create a table Employees.
Solution :-
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Department VARCHAR(30),
Salary INT,
Age INT
);
Q.2 Create a table Projects.
Solution :-
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(50),
EmployeeID INT,
Budget INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
Q.3 Insert into table Employees.
Solution :-
INSERT INTO Employees VALUES (1, ‘John Smith’, ‘IT’, 60000, 28);
INSERT INTO Employees VALUES (2, ‘Sarah Johnson’, ‘HR’, 55000, 35);
INSERT INTO Employees VALUES (3, ‘Michael Brown’, ‘IT’, 72000, 30);
INSERT INTO Employees VALUES (4, ‘Emily Davis’, ‘Finance’, 65000, 40);
INSERT INTO Employees VALUES (5, ‘David Wilson’, ‘IT’, 50000, 25);
Q.4 Insert into table Projects.
Solution :-
INSERT INTO Projects VALUES (101, ‘Cloud Migration’, 1, 200000);
INSERT INTO Projects VALUES (102, ‘Recruitment’, 2, 150000);
INSERT INTO Projects VALUES (103, ‘Security Upgrade’, 3, 180000);
INSERT INTO Projects VALUES (104, ‘Financial Audit’, 4, 220000);
INSERT INTO Projects VALUES (105, ‘App Development’, 1, 170000);
1. Find the name of the employee who is working on the project with the highest budget.
Solution:
SELECT EmployeeName
FROM Employees
WHERE EmployeeID = (SELECT EmployeeID
FROM Projects
WHERE Budget = (SELECT MAX(Budget) FROM Projects));
2.Find all employees whose salary is greater than the average salary of all employees.
Solution
Display the project names assigned to employees in the IT department.
Solution:SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
3.Display the project names assigned to employees in the IT department.
Solution:
SELECT ProjectName
FROM Projects
WHERE EmployeeID IN (SELECT EmployeeID
FROM Employees
WHERE Department = ‘IT’);
4.Find the employee who is working on the ‘Financial Audit’ project.
Solution:
SELECT EmployeeName
FROM Employees
WHERE EmployeeID = (SELECT EmployeeID
FROM Projects
WHERE ProjectName = ‘Financial Audit’);
5. List the names of employees who are not assigned to any project.
Solution:
SELECT EmployeeName
FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM Projects);
6. Find the total budget allocated to projects handled by employees aged over 30.
Solution:
SELECT SUM(Budget) AS TotalBudget
FROM Projects
WHERE EmployeeID IN (SELECT EmployeeID
FROM Employees
WHERE Age > 30);
7. Find the department with the highest average salary.
Solution:
SELECT Department
FROM Employees
GROUP BY Department
HAVING AVG(Salary) = (SELECT MAX(AvgSalary)
FROM (SELECT AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department) AS Temp);
8. List the names of employees who are working on more than one project.
Solution:
SELECT EmployeeName
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID
FROM Projects
GROUP BY EmployeeID
HAVING COUNT(ProjectID) > 1);
9. Find the employee name and their project name where the budget exceeds 150000.
Solution:
SELECT EmployeeName, ProjectName
FROM Employees
JOIN Projects
ON Employees.EmployeeID = Projects.EmployeeID
WHERE Budget > 150000;
10.Find the names of employees whose salary is below the average salary of their department.
Solution:
SELECT EmployeeName
FROM Employees E1
WHERE Salary < (SELECT AVG(Salary)
FROM Employees E2
WHERE E1.Department = E2.Department);
