BIT 275

Homework due Tuesday October 19, 2010

General Instructions:

  1. 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.
  2. 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:

Casey Cunningham

Malcolm Delaney

  1. 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:
  1. 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
  2. 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.
  3. 10 extra points: Figure out how to perform the same query as #5 above in MySQL.