Up: Selecting Data
Next: Exercises

Exercises

July 7th, 2011 Leave a comment Go to comments

Getting Started

In these exercises we will be using a database of that contains information about Nobel Prizes. Download the exercise database nobel_prizes.sqlite, and open it in SQL Manager for Firefox. (Click for detailed instructions.)

  1. Download the database we’ll be using in this exercise:
    nobel_prizes.sqlite
  2. Install the SQL Manager plugin for Firefox.
    1. Start firefox.
    2. From the Tools menu, select Add-ons.
    3. From the Get Add-ons tab, search for “SQL Manager”.
    4. Click the “Add to Firefox…” for SQL Manager, and follow the instructions (you will need to restart firefox).
  3. Open the database in SQL Manager.
    1. Start firefox.
    2. From the Tools menu, select SQL Manager.
    3. From the Database menu, select Connect Database.
    4. Select the nobel_prizes.database you just downloaded.

Alternatively, you can use the command line client that is packaged with SQLite. (Instructions). For the rest of these exercises we will assume you are using SQL Manager.

  1. Download SQLite here. You’ll probably want to get a compiled binary. If you’re using Cygwin you’ll want to use the Cygwin installer to install the sqlite3 package.
  2. From the command line, type sqlite3 nobel_prizes.database to open the Nobel prizes database. You can now type in SQL statements which will be run against the database

This database only contains one table: Nobel_Prizes. You can view the data in this table by expanding the Tables node on the side bar, selecting Nobel_Prizes,

and then going to the browse and search tab.

The table has a field for the year of the prize was awarded, the area, the name of the person the prize was awarded to, and a short description of the award.

Note: The rowid column is not part of the structure of the table, but it is instead just a field that is appended on to every query result by SQLite. You can ignore it (in our screencasts, we simply hid it from view).

The exercises

  1. Fetch all of the records from the Nobel_Prizes table. (Click for our answer)

    SELECT * FROM Nobel_Prizes;

    Notice also how we used the * to select all of the columns

  2. Fetch all of the records from the Nobel_Prizes table, but only show return the award winner’s name and year the prize was awarded. (Click for our answer)
    SELECT Year, Name FROM Nobel_Prizes;
  3. Fetch a list of all of the different areas the Nobel Prizes have been awarded in. Don’t return any duplicates! (Click for our answer)
    SELECT DISTINCT Area FROM Nobel_Prizes;
  4. The UPPER(str) function takes a string str and returns a new, upper-case version of str. For instance, SELECT UPPER("Hello"); returns the string “HELLO”.

    Using UPPER(), write a query that fetches all of the Nobel Prize winner’s names, both as it is in the database, and in upper-case.
    (Click for our answer)

    SELECT Name, UPPER(Name) FROM Nobel_Prizes;