BIT 275

Homework due September 30, 2010

General Instructions:

• Go to http://bit.dains.org and log in as “bitstudent” with password “student”.
• Select the “foster1” database in the left hand column.
• Then select the foster_parents table. You can use the “Browse” or “Structure” links at the top to see the columns and data in this table.
• Use the “Query” link at the top to try out queries that will solve the homework problems below.
• 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 odain@cascadia.edu.

Homework problems:

1. Get a list foster parent names, home phones and work phones
2. The same list as above but sort the list by last name then first name
3. Get a list of all foster parents who aren’t currently fostering a dog
4. Get a list of all foster parents who are currently fostering a dog
5. Get a list of all “adolescent dogs” - that is dogs whose age is between 1 and 3 inclusive (e.g. include dogs whose age is 1, 2, or 3).
6. Find all foster parents whose first and last names are the same. For example, if someone’s name is “Randolf Randolf” they should be in this list.
7. Get a list of all foster parents who are fostering a dog younger than 5 and live in Bothell. Sort this list by the dog’s age and break any ties by sorting by foster parents first and last name.
8. Find the “LIMIT BY” clause documentation in the MySQL documents (http://dev.mysql.com/doc/refman/5.1/en/index.html) and read it. Once you understand it create a query that returns the 3rd through 5th foster parent when the names are sorted by last name then first name. For example, if the list of all foster parents was:
   
   Roth Abbott
   Louis Alston
   Haley Andrews
   Octavius Baldwin
   Graham Bates
   Harriet Bates
   Eliana Battle
   Clayton Beach
   Claire Beard
   Karen Beck
   
   Then your query should return:
   
   Haley Andrews
   Octavius Baldwin
   Graham Bates
   
   as these individuals are the 3rd, 4th, and 5th people in the list.