1. Find the names of all employees who work for SBI.
select Emp_name
from Employee , Works , Company where
Employee.Emp_no = Works.EMP_NO and
Works.comp_no = Company.comp_no and
Company.comp_name = ‘SBI’;
2. Find the names and cities or residence of all employees who work for SBI
select Emp_name , Employee.city
from Employee , Works , Company where
Employee.Emp_no = Works.EMP_NO and
Works.comp_no = Company.comp_no and
Company.comp_name = ‘SBI’;
3. Find the names, street, address and cities of residence of all employees who work for SBI and earn more than 10,000.
select Emp_name , Street , Employee.city
from Employee , Works , Company where
Employee.Emp_no = Works.Emp_no and
Works.comp_no = Company.comp_no and
Company.comp_name = ‘SBI’ and
salary>10000;
4. Find all employees in the database who live in the same cities as the companies for which they work.
select Emp_name
from Employee , Works , Company where
Employee.Emp_no = Works.Emp_no and
Works.comp_no = Company.comp_no and
Employee.city = Company.city;
5. Find all employees in the database who live in the same cities and on the same streets as do there
managers.
6. Find all employees in the database who do not work for SBI.
select Emp_name
from Employee , Works , Company where
Employee.Emp_no = Works.Emp_no and
company.comp_no = Works.comp_no and
comp_name!=’SBI’;
7. Find all employees in the database who earn more than every employee of SBI.
select Emp_name
from Employee , Works where
Employee.Emp_no = Works.Emp_no and
(salary > all (select salary from Works , Company where Company.comp_no = Works.Comp_no and Comp_name = ‘SBI’));
8. Find all employees who earn more than the average salary of all employees of their company.
SELECT Emp_name
FROM Employee
WHERE (
SELECT Salary
FROM Works
WHERE Works.Emp_no = Employee.Emp_no
) > (
SELECT AVG(Salary)
FROM Works
WHERE Works.Comp_no = (
SELECT Comp_no
FROM Works
WHERE Works.Emp_no = Employee.Emp_no
)
);
9. Find the company that has the most employees.
SELECT Comp_name
FROM (
SELECT Company.Comp_name,
DENSE_RANK() OVER (ORDER BY COUNT(Works.Emp_no) DESC) AS EmployeeRank
FROM Company
JOIN Works ON Company.Comp_no = Works.Comp_no
GROUP BY Company.Comp_name
)
WHERE EmployeeRank = 1;
10. Find the company that has the smallest payroll.
SELECT Comp_name
FROM (
SELECT Company.Comp_name,
DENSE_RANK() OVER (ORDER BY SUM(Works.Salary) ASC) AS PayrollRank
FROM Company
JOIN Works ON Company.Comp_no = Works.Comp_no
GROUP BY Company.Comp_name
)
WHERE PayrollRank = 1;
11. Find those companies whose employees earn a higher salary, on average, than the average salary at SBI.
SELECT DISTINCT C.Comp_name
FROM Company C
JOIN Works W ON C.Comp_no = W.Comp_no
WHERE (
SELECT AVG(W2.Salary)
FROM Works W2
WHERE W2.Comp_no = (
SELECT Comp_no
FROM Company C2
WHERE C2.Comp_name = ‘SBI’
)
) < (
SELECT AVG(Salary)
FROM Works W3
WHERE W3.Comp_no = C.Comp_no
);
12. Modify the database so that John now lives in Kolkata.
13. Give all employees of SBI a 10 % raise.
UPDATE Works
SET Salary = Salary * 1.10
WHERE Comp_no = (
SELECT Comp_no
FROM Company
WHERE Comp_name = ‘SBI’
);
select* from Works where comp_no=’C1′;
14. Give all managers of SBI a 10 % raise.
UPDATE Works
SET Salary = Salary * 1.10
WHERE Emp_no IN (
SELECT M.Mgr_no
FROM Managers M
JOIN Employee E ON M.Mgr_no = E.Emp_no
JOIN Works W ON E.Emp_no = W.Emp_no
JOIN Company C ON W.Comp_no = C.Comp_no
WHERE C.Comp_name = ‘SBI’
);
select* from Works where comp_no=’C1′;
15. Delete all tuples in works relation for employees of SBI.
delete from Works where comp_no in (select comp_no from Company where comp_name = ‘SBI’);
select* from Works;
Please follow and like us:
