Homework due Tuesday October 19, 2010
- Go to http://bit.dains.org and log in as “bitstudent” with password “student”.
- Select the “cascadia1” 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.
- Note: keep your SQL queries as simple as possible. Don’t join tables that don’t need to be part of the query. Don’t use an outer join when an inner join will work. Make the query as simple as possible while still solving the problem. Points will be deducted for overly complex queries.
- When you’ve solved all the problems send your solutions to me at firstname.lastname@example.org. Please just cut and paste your solutions into the email. Do not put your solutions in an attachment.
- Get a list of students who are enrolled in more than 4 classes. The list should contain 2 columns: the first and last name. Sort the list by first name, then last name.
- We want the same list as before, but we want only a single column in the result, the students first name concatenated with their last name. We want this list sorted by last name, then first name. For example, the first two rows of your query should be:
- We want to know how many classes are in each department. For examples, BIT-275 and BIT-220 are both in the BIT department while ENGL-101 and ENGL-220 are both in the ENGL department. The list should contain two columns. The first column should contain the name of the department (ENGL, BIT, etc.) and the second column should contain the number of classes in that department. Sort your list by the number of classes so that the department with the most classes comes first. A few hints:
- Use the MySQL manual to find a function or functions that will extract the department name from the class name.
- Its probably smart to fist just try to generate a list of departments
- If you can’t complete solve this problem show me what you were able to do. Could you get a list of departments? What did you try? Etc.
- Get a list of people born before 1970. Sort this list by birthdate so the oldest people are shown first. The list should contain just two columns: first name and last name
- Look at the manual for Postgresql and figure out how you could write a query that would list first name, last name, and age of everyone in the people table if that data were stored in a Postgresql database instead of a MySQL database. Note that Postresql has different data functions so this query won’t work in MySQL.
- 10 extra points: Figure out how to perform the same query as #5 above in MySQL.