Question

Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compare with your solution to make sure I did it correctly. Thank you very much!

LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees who work for the Research' department. 2. For every project located in "Stafford", list the project number, the controlling department number, and the department manager's last name, address, and Bdate 3. List the names of all employees with two or more dependents 4. Retrieve the names of employees who have no dependents. 5. List the names of managers who have at least one dependent 6. (Bonus submit it on the day of final exam) Find the names of the employee who works on ALL projects controlled by department 4 Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Super ssn Dno DEPARTMENT Dname Dnumber Mgr.ssn Mgr.start date DEPT LOCATIONS PROJECT Pname PumberPlocation Dnum WORKS ON Hours DEPENDENT ame Sex Bdate Relationship Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Ssn Fname Minit Lname Sex Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 33445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 JZelaya 999887777 1968-01-19 3321 Castle, Spring. TX F 25000 98765432 4 JenniferS Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX 43000 8886655554 Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX | M | 38000 | 333445555 | 5 A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 3334455555 AhmadV Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M25000 987654321 4 AliciaJ James Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT LOCATIONS 333445555 987654321 888665555 1988-05-22 1995-01-01 1981-06-19 WORKS ON Hours Pname 123456789 123456789 666884444340.0 453453453 4534534532 20.0 333445555 333445555 333445555 333445555 999887777 DEPENDENT 333445555 F 1986-04-05 Daughter M 1983-10-25 Son 987987987 10 987987987 987654321 987654321 888665555 987654321 Abn 123456789 123456789 123456789 Elizabeth M 1942-02-28 Spouse M 1988-01-04 Son F 1988-12-30 Daughter Alice

Answer 1

Please find the below Query:

Select Concat(Fname,',',Minit,',',Lname) AS FullName from Employee e,Works_on w,project p where e.Ssn=w.Essn and w.Pno=p.Pnumber where p.Dnum=4;

As the works_on table and Project table has project number so we can join them.Also Works_on table has Essn which is same as Ssn in Employee table.so we can join them using that.

Output Tuples are below:

FullName

---------------

Franklin,T,Wong
Alicia,J,Zelaya
Ahmed,V,Jabbar
Jennifer,S,Wallace

I have not run the query in any compiler. So there may be some compilation error.

Please let me know if you have any doubt;