Getting Started

In these exercises we will be using a database of that contains information about Nobel Prizes: nobel_prizes.sqlite. We’ve put up instructions on how to use SQL Manager for Firefox to interact with this database.

The exercises

1. Fetch the earliest year recorded in the database.
   (Click for our answer)
   SELECT MIN(Year) FROM Nobel_Prizes;
2. Fetch the number of records in the database.
   (Click for our answer)
   SELECT COUNT(*) FROM Nobel_Prizes;
3. Fetch the area, year, and number of awards given out for each area in each year.
   (Click for our answer)
   SELECT Area, Year, COUNT(*) FROM Nobel_Prizes GROUP BY Area, Year;
4. Fetch the area, and number of awards given out in each area, sorted in ascending order by area.
   (Click for our answer)

   SELECT Area, COUNT(*)
   FROM Nobel_Prizes
   GROUP BY Area
   ORDER BY Area ASC;

5. Fetch the area, and number of awards given out in each area, sorted in descending order by number of awards.
   (Click for our answer)

   SELECT Area, COUNT(*)
   FROM Nobel_Prizes
   GROUP BY Area
   ORDER BY Count(*) DESC;

6. Fetch the area, and number of awards given out in each area, except for in chemistry.
   (Click for our answer)

   SELECT Area, COUNT(*)
   FROM Nobel_Prizes
   WHERE Area != "chemistry"
   GROUP BY Area;