Exercise 1: Write the following queries in SQL: a) Retrieve the names of all emp

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)
select Fname,Lname from EMPLOYEE
where Dno = 5

b)
select a.Super_ssn
FROM EMPLOYEE a
INNER JOIN DEPARTMENT b
ON a.Dno = b.Dno
where b.Dname = 'Finance'

c)
select p.Pname,d.Dname,e.Lname
FROM PROJECT p, DEPARTMENT d, EMPLOYEE e
WHERE p.Dnum=d.Dnumber and d.Dnumber=e.Dno
and p.Plocation='SHARJAH'

d)
select e.Fname, e.Lname
FROM WORKS_ON w, PROJECT p, EMPLOYEE e
WHERE w.Essn=e.Ssn and w.Pno=p.Pnumber
and p.Dnum=e.Dno and w.Essn='453453453'

e)
select e.Ssn
FROM DEPARTMENT d, PROJECT p, EMPLOYEE e
where d.Dnumber=4
and p.Plocation='HOUSTON' and p.Dnum=D.Dnumber
and e.Dno=D.Dnumber

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.