Problem Statement: Use the tables in the next page to answer the following quest
ID: 3865773 • Letter: P
Question
Problem Statement: Use the tables in the next page to answer the following questions in SQL 1. Print the names of employees who work in a project located in Houston 2. Print the names of employees with no dependents 3. Print the names of employees who are managers. 4. Print the names of employees who have more than one wife. . Print the names of employees who work in all the projects. Deliverables A cover page which contains your ID, name, and the IDs and names of your partners. The SOL statement of each question A list of all the references that you used. .Explanation / Answer
1. Print the employees who work in a project located in houston
select Fname, Lname from
EMPLOYEE
where EMPLOYEE.ssn in (
select Essn
from WORKS_ON
where Pno in(
select Pnumber
from PROJECT
where Plocation = 'Houston'
)
)
2. Print the names of the employee with no dependents:
select Fname, Lname from
EMPLOYEE
where EMPLOYEE.ssn not in (
select distinct Essn from DEPENDENT
)
3. Print the names of the employees who are managers.
select Fname, Lname from
EMPLOYEE
where EMPLOYEE.ssn in (
select distinct Mgr_ssn from DEPARTMENT
)
4. Print the names of the employees who hve more than one wife:
select EMPLOYEE.Fname, EMPLOYEE.Lname from
EMPLOYEE, DEPENDENT
where EMPLOYEE.ssn = DEPENDENT.Essn
and EMPLOYEE.sex = 'M'
and DEPENDENT.sex = 'F'
and DEPENDENT.relationship = 'Spouse'
group by EMPLOYEE.Fname ,EMPLOYEE.Lname
having count(DEPENDENT.dependent_name) > 1
5. Print the names of employees who work in all the projects:
select EMPLOYEE.Fname, EMPLOYEE.Lname from
EMPLOYEE
where EMPLOYEE.ssn in (
select Ess
from WORKS_ON
group by Ess
having count(Pno) = (
select count(*)
from PROJECT
)
)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.