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

Question

Hello,

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. For each employee, retrieve the employee's first and last name and the first and last name of the his or her immediate supervisor.

B. Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

C. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on that project.

D. Find all employees who were born during the 1950s.

Explanation / Answer

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)

A.

select E1.FNAME, E1.LNAME , E2.FNAME , E2.LNAME

from EMPLOYEE E1 , EMPLOYEE E2

where E1.SSN = E2.SUPERSSN ;

Query returns result satisfying the where clause.

B.

select E.FNAME, E.LNAME

from EMPLOYEE E

where E.SSN in (select ESSN from DEPENDENT where E.FNAME = DEPENDENT_NAME and E.SEX = SEX );

First the inner query will be executed then the outer query will display names of employees where e.ssn is in the result provided by inner query.

C.

select PNAME, PNUMBER , count(*)

from PROJECT , WORKS_ON

where PNUMBER = PNO

group by PNAME, PNUMBER

having count(*)>2;

We always take all elements in group by that are present in select clause.

having returns those rows where count >2

D.

select FNAME , LNAME

from EMPLOYEE

where BDATE like '____1950' ;

like operator does the matching of date with given pattern. First 4 position are blank followed by 1950 i.e. all bdate where last 4position is 1950 are selected.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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