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. 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.

Fix this query to run in Oracle. This query is returning a message that says....No data found:

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 );

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.

Fix this query to run in Oracle. This query is returning a message that says....No data found:

select PNAME, PNUMBER , count(*)

from PROJECT , WORKS_ON

where PNUMBER = PNO

group by PNAME, PNUMBER

having count(*)>2;

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

Fix this query to run in Oracle. This query is returning a message that says....No data found:

select FNAME , LNAME

from EMPLOYEE

where BDATE like '____1950' ;

Explanation / Answer

A. select e.FNAME,e.LNAME,s.FNAME,s.LNAME from employee e,employee s where s.SUPERSSN=e.SSN;

The above query will the retrieve the employee name and his supervisor name.

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 );

C. select PNAME, PNUMBER , count(*) from PROJECT , WORKS_ON where PNUMBER = PNO group by PNAME, PNUMBER having count(*)>2;

The above 2 Queries are the perfectly valid queries and there is no error's in the queries. I think you are getting 'no data found' because there is no data like that you are searching for. Try to insert the data as in the tables as per your requirement and then use this queries you will get result.

D. select FNAME , LNAME from EMPLOYEE where BDATE between '01-01-1950' and '12-31-1959';

To retrieve the employees born in 1950's we need to take all the years from 1950 - 1959 should be considered otherwise if you you want to retrieve only those persons who born on 1950 try below query;
select FNAME , LNAME from EMPLOYEE where BDATE like '%1980';

If you get no data found then there might be no persons in your data base with birthyear as 1980's. So try to insert the data in your database and use this queries.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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