Homework due Tuesday October 12, 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).
- Get a list of all foster parents and their home phone numbers. This list should contain 3 columns: first name, last name and phone number. The list should contain everyone in the foster parents table even if they don’t have a work phone.
- The same as the last problem but add columns for the city and state they live in. Sort this list by city, then last name, then first name.
- Get a list of all foster parents and their home and work phones. This list should contain 4 columns: first name, last name, home phone, work phone. It should include everyone in the foster parents table even if they don’t have a work phone or a home phone.
- Get a list of all foster parents who are not currently fostering a dog. The list shoud contain just first and last names and be sorted by first name and then last name.