8. Write a SQL query to retrieve the names of all employees working for Stark In

Question

8. Write a SQL query to retrieve the names of all employees working for Stark Industries.

9. Without using a subquery, write a SQL query to retrieve the names of all employees who are not employed.

The following data samples show a few rows from each table. For the questions that follow, assume that each table contains many more rows than are shown here. employee ID employeeFirstName employee LastName employee HomeCity employeeHomeState Smith Boston MA 1 John 2 Jane Boston MA NY Johnson 3 Sue New York Johnson New York 4 Tim Employee employerCity employer State employerID employer Name CA 1 Stark Industries Los Angeles 2 Initech Newark NJ CA Los Angeles 3 Acme Corporation NY 4 Wayne Enterprises New York City Employer employee Employerl employeelD employerlD date Hired 11 4 2005-08-05 22 2007-04-02 33 1 2006-05-09 4 1 2005- 11-25 EmployeeEmployer

Explanation / Answer

Question 8:

SELECT employeeFirstName, employeeLastName FROM Employee
WHERE employeeID IN
(SELECT employeeID FROM EmployeeEmployer
WHERE employerID IN
(SELECT employerID FROM Employer
WHERE employerName LIKE 'Stark Industries'));

Question 9:

SELECT employeeFirstName, employeeLastName FROM
Employee NATURAL LEFT OUTER JOIN EmployeeEmployer
WHERE employerID IS NULL;

NATURAL LEFT OUTER JOIN RESULTS IN ROWS WHERE Employee.employeeID = EmployeeEmployer.employeeID along with all other rows from left side table with the columns of right side table padded with NULL.

Thus, in the result, there will be rows where employeeID that are not there in EmployeeEmployer table has the employerID as NULL.

Related Questions

Navigate

• Browse (All)
• Browse 8
• Subjects

---

---

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