Exercise 1: Write the following queries in SQL: a) Retrieve the names of all emp
ID: 3767553 • Letter: E
Question
Exercise 1: Write the following queries in SQL:
a) Retrieve the names of all employees who work in department number 5.
b) Retrieve the supervisor SSNs of all employees who work for the ‘Finance’ department.
c) For every project located in ‘Sharjah’, retrieve the project name, the controlling department name and the department manager’s last name.
d) Retrieve the names of employees who work on all the projects in which the employee with ssn ‘453453453’ is working on.
e) Make a list of the SSN of all employees who are working in department number 4 or employees who are working on projects that are located in Houston.
Explanation / Answer
a) Retrieve the names of all employees who work in department number 5.
Select ename
from emp, dept
where emp.deptno = dept.deptno and dno = 5;
b) Retrieve the supervisor SSNs of all employees who work for the ‘Finance’ department.
Select SSN
from emp,dept
where emp.deptno = dept.deptno
and dname = "Finance";
c) For every project located in ‘Sharjah’, retrieve the project name, the controlling department name and the department manager’s last name.
Select project_name,dept_name, mgr_last_name
from project,dept
where project_loc = 'Sharjah'
and dept.p_no = project.p_no;
d) Retrieve the names of employees who work on all the projects in which the employee with ssn ‘453453453’ is working on.
SELECT EMPLOYEE.FNAME, EMPLOYEE.LNAME
FROM EMPLOYEE
WHERE EXISTS
(SELECT *
FROM WORKS_ON
WHERE WORKS_ON.ESSN = EMPLOYEE.SSN
AND WORKS_ON.PNO =453453453)
e) Make a list of the SSN of all employees who are working in department number 4 or employees who are working on projects that are located in Houston.
Select SSN
From emp
where dno = 4;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.