a) Retrieve the names of all employees who work in department number 5. b) Retri
ID: 3771557 • Letter: A
Question
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.
please note that in part (c) assume the location name is Sharjah is available in Dept_location table and Project Table as Fictitious name and solve it like the other questions
Chapter 3The Relational Data Model and Relational Database Constraints Figure 3.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname MinitLname Bdate Address Sex Salary Super_ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 JZelaya 999887777 1968-01-19 3321 Castle, Spring, TXF 25000 987654321 4 F 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 A English453453453 1972-07-31 5631 Rice, Houston, TX F 25000 3334455555 AhmadV Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 Alicia JenniferSWallace 987654321 1941-06-20 291 Berry, Bellaire, TX James 888665555 1937-11-10450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT_LOCATIONS Dnumber Dlocation Houston Stafford Bellaire Sugarland Houston Dnumber Dname Research Administration Headquarters Mgr ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 5 5 5 WORKS ON PROJECT Hours Pname Pnumber Plocation Dnum Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 ProductX ProductY ProductZ Computerization Reorganization Newbenefits Bellaire Sugarland Houston 32.5 5 3 3 4. 20.0 Houston 3 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent name Sex Relationship 30.0 1986-04-05 Daughter Theodore M 1983-10-25 Son F 1958-05-03 Spouse M 1942-02-28 Spouse M 1988-01-04 Son F 1988-12-30 Daughter F 1967-05-05Spouse 35.0 oy Michael NULL ElizabethExplanation / Answer
1. Select Fname,Lname from EMPLOYEE where Dno=5;
2.Select Ssn from EMPLOYEE where Dno = (select Dnumber from DEPARTMENT where Dname='Finance');
3.
4. Select Fname, Lname from EMPLOYEE where SSN= (Select SSN from WORKS_ON where Pno All(Select Pno from Works_On where Essn='453453453') )
5.Select SSN from Employee where Dno=4 or Dno=(Select Dnumber from DEPT_LOCATION where Dlocation='Huston')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.