Question

Provide a list of employees and their phone numbers. Put the employee names together so that the list is last_name, first_name (with the two names together in a single field and a comma and space between the names). List the license_no and the dba_name of the establishments and the number of inspections done for each one. Call the number of inspections column No_inspections. Include inspections done during 2015. Only include establishments that have had over 5 inspections.List employee Name (first and last) and phone number for employees who have done inspections in SKOKIE (Illinois). Hint: Use IN. Do not use joins. Use Subqueries in the WHERE clause HINT: Try it with 2 levels of subqueries or it can also be done with a join in a single subquery.

Answer 1

Here is a solution using two levels of subqueries in the WHERE clause:

SELECT e.last_name || ', ' || e.first_name AS "Name", e.phone_number

FROM employees e

WHERE e.employee_id IN (

   SELECT i.employee_id

   FROM inspections i

   WHERE i.location_id IN (

       SELECT l.location_id

       FROM locations l

       WHERE l.city = 'SKOKIE' AND l.state = 'IL'

   )

)

To list the license_no, dba_name, and No_inspections for establishments with over 5 inspections:

SELECT l.license_no, l.dba_name, COUNT(i.inspection_id) AS "No_inspections"

FROM locations l

JOIN inspections i ON i.location_id = l.location_id

WHERE i.inspection_date >= '2015-01-01' AND i.inspection_date < '2016-01-01'

GROUP BY l.license_no, l.dba_name

HAVING COUNT(i.inspection_id) > 5

A subquery is used to return data that will be used in the outer SELECT, INSERT, UPDATE, or DELETE statement, or to set a value in the SET clause of an UPDATE statement. Subqueries can be used in various parts of a SQL statement, such as the WHERE clause, the FROM clause, or the SELECT clause.

Learn more about subquery, here https://brainly.com/question/14079843

#SPJ4