Expert reply only. Use the relational schema to provide SQL queries. (For Oracle

Question

Expert reply only. Use the relational schema to provide SQL queries. (For Oracle)

RELATIONAL SCHEMA:

EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, #SUPERSSN, #DNO)

DEPARTMENT (DNAME, DNUMBER, #MGRSSN, MGRSTARTDATE)

DEPT_LOCATIONS (#DNUMBER, DLOCATION)

PROJECT (PNAME, PNUMBER, PLOCATION, #DNUM)

WORKS_ON (#ESSN, #PNO, HOURS)s

DEPENDENT (#ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP)

Provide sql query/statements: (To be run in Oracle)

A. Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project.

(Please provide an sql query for number A. above.

Explanation / Answer

the query would be this.

(SELECT DISTINCT PNUMBER

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’)

UNION

(SELECT DISTINCT PNUMBER

FROM PROJECT, WORKS_ON, EMPLOYEE

WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME=‘Smith’);

here in the first part of the query before UNION we are joining the tables project and department on the basis of department number which is denoted by dnum and dnumber in both the tables. this is because we need employee who works on the project which is controlled by that department. so every department has a manager so we are joining employee and department tables now on the basis of ssn and mgrssn. every employee will have his ssn so by comparing it to mgrssn we can get if that employee of the department is the manager of that department or not.

now we used DISTINCT to select distinct project numbers on which this employee has worked because one employee could work on more than one projects.

now this query helps us find the list of project numbers on which employee whose last name is smith works as either manager or employee in a department.

now we are using UNION to combine the result set of both the query.

after the UNION in the second part of the query we are joining the tables project and works_on on the basis of project number and tables works_on and employee on the basis of ssn.

now this query will help us find the list of project numbers on which the employee whose last name is smith works on.

therefore by combining the result set of both the query we will get a list of project numbers for projects that involve an employee whose last name is 'Smith',either as a worker or as a manager of the department that controls the project.

NOTE:UNION returns the combined result set without any duplicate rows so don't worry about that.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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