Question 6 of 8

Question 6 (10+10=20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Question 5's same four relations, with Artist's key renamed Tid (was Aid) and ArtObject's artist-reference column renamed to match — a pure relabelling, not a schema change. The query in (a) uses an unqualified FROM ArtObject yet references A.Cost/A.Acquired/A.Origin throughout, so the FROM clause is read as the evident FROM ArtObject A (every correlated reference to the alias "A" is present, which is how the intended alias is recovered).

Find. (a) a plain-English reading of the given query; (b) the SQL for artists with zero acquired art objects.

Approach. (a) is read from the inside out: the correlated subquery counts how many rows share a given Cost among post-1999 acquisitions, and the HAVING clause keeps only groups where that count exceeds 1. (b) is the same "never" pattern as Question 5(b), now needed directly in SQL rather than RA, using NOT EXISTS for correctness under NULLs.

1. (a) Reading the query. The outer query restricts to art objects acquired after 1999 and groups them by Cost. The correlated subquery, for each such group, counts how many post-1999 art objects (A2) share that SAME Cost value as the current row A — note this count always includes A itself, so it is always ≥1. The HAVING clause keeps only Cost groups where that count is > 1, i.e. Cost values shared by TWO OR MORE art objects acquired since 2000. In plain English: the query finds every purchase-cost value that recurs among two or more Art Objects acquired after 1999, and returns that cost together with the Origin of one of the matching objects. Equivalently, it flags art objects that share their exact purchase price with at least one other post-1999 acquisition.
Check — non-standard GROUP BY
Selecting A.Origin while grouping only by A.Cost is not valid under strict SQL (a non-aggregated, non-grouped column is ambiguous whenever a Cost group spans more than one Origin) — some engines (e.g. MySQL's default mode) permit it and silently return an arbitrary matching row's Origin per group, while standard-conforming engines (PostgreSQL, SQL Server) reject it outright. The reading above assumes the lenient behaviour, since the question is clearly asking what the query returns rather than whether it is portable.
2. (b) artists never linked to any acquired Art Object.

   SELECT Ar.Tname
   FROM   Artist Ar
   WHERE  NOT EXISTS (
            SELECT 1 FROM ArtObject AO WHERE AO.Tid = Ar.Tid
          );

   An equivalent Tid NOT IN (SELECT Tid FROM ArtObject) form reads more simply, but is unsafe if ArtObject.Tid can ever be NULL (a single NULL in the subquery's result set makes the whole NOT IN evaluate to UNKNOWN for every row, silently returning zero artists) — NOT EXISTS has no such trap, since it only tests row EXISTENCE and never compares against a NULL value directly.