I need this two queries written in WinRdbi I have provided the query #1 but in R

Question

I need this two queries written in WinRdbi I have provided the query #1 but in Relational Algebra. WinRDBI does not provide division and q-join operators to encourage the use of the fundamental relational algebra operators.

I need the queries written in Winrdbi

1) Retrieve the first and last names of all employees who work on every project.

2) Write the first and the last names of all employees who have exactly 2 dependents.

This is the query in relational algebra for #1

PROJ_EMPS(PNO,SSN) <-- ? PNO,ESSN (WORKS_ON)

ALL_PROJS(PNO) <-- ? PNUMBER (PROJECT)

EMPS_ALL_PROJS <-- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *)

RESULT <-- ? LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)

162 Chapter 5 The Relational Data Model and Relational Database Constraints Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Sex Salary Super_ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M30000 333445555 5 Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M40000 888665555 5 Alicia JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TXF 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 3334455555 Joyce A English 453453453 1972-07-315631 Rice, Houston, TX F 25000 333445555 5 AhmadVJabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 JamesE Borg 888665555 1937-11-10 450 Stone, Houston, TXM 55000 NULL DEPARTMENT DEPT LOCATIONS Dlocation Mgr ssn 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 1981-06-19 Houston Bellaire 5 WORKS ON PROJECT Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Pno Hours Pnumber Plocation Dnum 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 20 10.0 30 30.0 10.0 10 35.0 5.0 30 20.0 20 15.0 20 NULL Bellaire 5 Computerization10 Reorganization Newbenefits 2 20 30 Stafford 10 DEPENDENT Essn 333445555 333445555 Theodore 333445555 Joy 987654321 123456789 23456789 Alice 23456789 Elizabeth Dependent_name Sex Bdate F 1986-04-05 Daughter M | 1983-10-25 | Son F 1958-05-03 Spouse M 1942-02-28 Spouse M | 1988-01-04 | Son F 1988-12-30Daughter F 1967-05-05 Spouse 10

Explanation / Answer

Q1) Retrieve the first and last names of all employees who work on every project.

SELECT FNAME, LNAME, PNAME FROM EMPLOYEE JOIN WORKS_ON ON EMPLOYEE.SSN = WORKS_ON.ESSN JOIN PROJECT ON WORKS_ON.PNO = PROJECT.PNUMBER;

Q2) Write the first and the last names of all employees who have exactly 2 dependents.

SELECT FNAME, LNAME, COUNT(ESSN) FROM EMPLOYEE JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN GROUP BY (FNAME, LNAME) HAVING COUNT(ESSN) = 2;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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