Database Concepts

A common exam question gives a table and a SELECT query and asks which records the query returns. The approach:

1. Look at the FROM clause to find the table.
2. Look at the WHERE clause and identify the condition each record must satisfy.
3. Check each record in the table against the condition.
4. Keep only the records where every condition is true.
5. Look at the SELECT clause and write down only the columns asked for.

Walkthrough: a Dublin or Osaka query

Using the Customers table from section 6:

SELECT *
FROM Customers
WHERE City = 'Dublin' OR City = 'Osaka';

Step by step:

• Maya Hassan: City = 'Toronto' → does not match either → excluded.
• Liam O'Brien: City = 'Dublin' → matches → included.
• Hiroshi Tanaka: City = 'Osaka' → matches → included.
• Priya Gupta: City = 'Mumbai' → does not match either → excluded.

Result:

Walkthrough: an animal breeding query

Consider the tbl_zoo table:

A keeper wants the names of every species that is held in captivity and eats more than 10 kg of food a day:

SELECT Species
FROM tbl_zoo
WHERE InCaptivity = 'Yes'
  AND DailyFeedKg > 10;

Working through each record against both conditions:

• Giraffe: in captivity and 35 > 10 → both true → included.
• Cheetah: in captivity but 3 is not > 10 → excluded.
• Tortoise: in captivity but 2 is not > 10 → excluded.
• Anaconda: not in captivity → excluded.
• Macaw, Pelican, Komodo Dragon: in captivity but their feed amounts (1, 2, 4) are not > 10 → excluded.
• Bison: in captivity and 25 > 10 → included.
• Tarantula: not in captivity → excluded.
• Gorilla: in captivity and 20 > 10 → included.

Result: