Consider the following relational schema - Write SQL statements for the followin

Question

Consider the following relational schema - Write SQL statements for the following queries employee fname CHARACTER VARYING(15) minit CHARACTER VARYING(1) Iname CHARACTER VARYING(15) dependent works on CHARACTER) essnCHARACTER(O) dependent name CHARACTER VARYING 15) essn CHARACTERO) pno NUMERIC hours NUMERIC bdate DATE CHARACTER(1) DATE CHARACTER VARYING(B) address CHARACTER VARYING(E0) bdate CHARACTER1 dept locations relationship salary NUMERIC super ssn CHARACTERO) dno dno NUMERIC dlocation CHARACTER VARYING(15) NUMERIC dname dno mgrssn mgrstartd ate DATE department CHARACTER VARYING(25) NUMERIC CHARACTER) project pname CHARACTER VARYING5) pno plocation CHARACTER VARYING 15) dno NUMERIC NUMERIC

Explanation / Answer

1. List the first names of employees who had worked more than two projects

SQL Query:

Explanation:

The inner SQL query will retrieve all the essn who are working more than 2 projects from works_on table.

The Outer SQL query will retrieve the names of the employee based on the result given by inner SQL query.

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

2. Retrieve the first name of employee who has the highest salary.

SQL Query:

Explanation:

The inner query will retieve the highest salary from the employee table.

The outer query will retrieve the fname of employee based on the result provided by inner SQL query.

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

3. Retrieve the first names of the employees whose working hours are less than 15 hours.

SQL Query:

Explanation:

The inner query will retrieve the essn of employees who works less than 15 hours.

The outer query retrieves the fname for the employees given by inner SQL query.

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

4. Retrieve the first name of employees who have more than two dependents.

SQL Query:

SELECT fname FROM employee WHERE essn in ( select essn from dependent GROUP BY essn HAVING COUNT(essn) > 2)

Explanation:

The inner SQL query will give the essn of employees with more than two dependents.

The outer query will retrieve the first name of employees with essn provided by inner SQL query.

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

5. Retrieve the first name of employees whose gender differs from their dependents.

SQL Query:

SELECT E.fname FROM employee AS E WHERE E.essn IN (SELECT essn FROM dependent WHERE E.sex<>sex);

Explanation:

The inner query will retrieve the essn of empoyees who has dependent with different gender.

The outer query will retrieve the fname of employees with essn provided by inner SQL query.

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

Please upvote if you are happy with the answer

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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