SQL TUTORIALS

Answer Key

1. Query used:
   SELECT parts.part_num, parts.name, CASE
   WHEN inventory_id IS NULL THEN 'Not Available'
   ELSE 'Available'
   END AS status
FROM parts
LEFT JOIN inventory_parts ON parts.part_num = inventory_parts.part_num;
2. Query used:
   SELECT sets.set_num, sets.name, CASE
   WHEN themes.name IN('4 Juniors', 'Animals', 'Cars', 'Dino', 'Dino 2010', 'Dino Attack') THEN 'Kids'
   WHEN themes.name IN('Adventurers', 'Agents', 'Avengers', 'Ben 10', 'Coast Guard') THEN 'Young Teens'
   WHEN themes.name IN('Harry Potter', 'Indiana Jones', 'Lamborghini') THEN 'Older Teens'
   END AS category
FROM sets
LEFT JOIN inventory_sets ON sets.set_num = inventory_sets.set_num
JOIN themes ON sets.theme_id = themes.id;
3. Query used:
   SELECT *, CASE
   WHEN quantity > 20 THEN 'Popular'
   WHEN quantity BETWEEN 10 AND 20 THEN 'Average'
   WHEN quantity < 10 THEN 'Not Popular'
   END AS popularity
FROM inventory_sets
4. Query used:
   SELECT name, quantity,
   SUM(CASE WHEN quantity > 20 THEN quantity * 2
      WHEN quantity BETWEEN 10 AND 20 THEN quantity * 1.75
      ELSE quantity END) AS recommended_quantity
FROM inventory_sets
JOIN sets ON inventory_sets.set_num = sets.set_num
GROUP BY name, quantity;