Homework due Tuesday October 7, 2010
- Go to http://bit.dains.org and log in as “bitstudent” with password “student”.
- Select the “foster2” database in the left hand column.
- For each of the following questions copy and paste the SQL query that solves the problem into an email. Make sure to label the query with the number of the problem it solves.
- When you’ve solved all the problems send your solutions to me at email@example.com.
Note: There is a bug in SQL Buddy (the software that we use to run queries at bit.dains.org) so that if two columns have the same name it will only show one of them. For example, if you tried to select the name column from the dog and city tables in the same query only one would show up because they have the same name. This is an SQL Buddy bug. To work around it you’ll need to make sure to use columns aliases (the “AS” clause).
- Create a query to get an address list for all foster parents including first and last name, address, city, state, and zip code. Sort the list by last name then first name.
- Repeat the above query but rename the columns so they look nice. For example, instead of fname the column should be “First Name”. Instead of lname it should be “Last Name”, etc.
- Get a list of foster parents and their work phone numbers. For now this list will only include those foster parents that have a work phone number. In the next class we’ll learn how to get a list of all foster parents even if they don’t have a work phone.
- Get a list of unique dog names sorted by name. In other words this list should contain every dog name but if there are multiple dogs named “Fido” the name “Fido” should only appear on the list once.
- Generate a list with columns containing first name, last name, home phone and work phone for all foster parents. There should be only 1 row per foster parent. For now this table will only contain foster parents that have both a work and a home phone. In the next class we’ll learn how to get a list containing all foster parents.