Born to Thrive

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. You need to display all those cities that have only one department. Which query gives the correct output?
A.SELECT location_id, city
FROM locations l
WHERE 1 = (SELECT COUNT(*)
FROM departments
WHERE location_id = l.location_id);
B. SELECT location_id, city
FROM locations WHERE EXISTS (SELECT COUNT(*)
FROM departments
GROUP BY location_id HAVING COUNT(*) = 1);
C. SELECT location_id, city
FROM locations WHERE
1 = (SELECT COUNT(*) FROM departments
GROUP BY location_id);
D. SELECT l.location_id, city
FROM locations l JOIN departments d ON (l.location_id = d.location_id)
WHERE EXISTS (SELECT COUNT(*)
FROM departments d
WHERE l.location_id =d.location_id);

Exhibit:

LOCATIONS table:

DEPARTMENTS table:

Theory:

Since the question contains SELECT statements with a correlated and non-correlated subquery and a function EXISTS, so the information needed to answer this question can be found as follows: SELECT, SUBQUERIES, EXISTS and COUNT..

A SUBQUERY or INNER QUERY is a SELECT statement that exists within another SQL statement (QUTER QUERY). A subquery can be used within SELECT, INSERT, UPDATE and DELETE statements. A subquery is non-correlated when it can be executed successfully on its own, whereas a correlated subquery uses values from the outer query. Any valid SELECT statement can qualify as a subquery include those that retrieve data from one or more tables or views, those that use complex expressions or aggregate functions, and those that include the WHERE and GROUP BY clauses, HAVING or ORDER BY clauses, or any other other features available to SELECT statements. EXISTS or IN operators tests for existence of rows in a subquery, creating a semi-join condition. A semi-join between two tables returns rows from the first table where one or more matches are found in the second table. The difference between a semi-join and a conventional join is that rows in the first table will be returned at most once. A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Note that EXISTS will not scan all rows in the subquery, only one row is required to determine whether the outcome is TRUE or FALSE. However, NOT EXISTS must scan all rows, which may cause performance problems. COUNT function counts occurrences of data, not NULL values. However, when combined with the asterisk - COUNT(*) - it counts all rows including duplicate records and those with all NULL values in the results. COUNT function never returns NULL value as it returns a value of 0 if it encounters no values.

Answer:

A - Correct
B - Wrong - Since Count function always returns a value, so EXISTS function will always return TRUE value even if a city has more or has no departments.
C - Wrong - A subquery is not correlated to the outer query, hence the query will display all cities if there is one that has with one department.
D - Wrong - Even though a subquery is correlated, the COUNT function will always return a value, which taken by EXISTS will yield TRUE condition, hence the query will return all cities.