SQL TUTORIALS

Answer Key

1. Query used:
   SELECT sets.name AS set, themes.name AS theme, SUM(quantity) AS num_sets
FROM inventory_sets
JOIN sets ON inventory_sets.set_num = sets.set_num
GROUP BY ROLLUP(themes.name, sets.name)
ORDER BY num_sets DESC;
2. Query used:
   SELECT parts.name AS part, colors.name AS color, SUM(quantity) AS num_parts
FROM inventory_parts
JOIN parts ON inventory_parts.part_num = parts.part_num
JOIN colors ON inventory_parts.color_id = colors.id
GROUP BY CUBE(colors.name, parts.name)
ORDER BY num_parts DESC, part, color;
3. Query used:
   SELECT parts.name AS part, colors.name AS color, SUM(quantity) AS num_parts
FROM inventory_parts
JOIN parts ON inventory_parts.part_num = parts.part_num
JOIN inventory_parts ON inventory_parts.color_id = colors.id
GROUP BY GROUPING SETS((part), (color), (part, color));