CS222 Lab 11 Relational Database and MySQL Practice

Instructor: Trish cornez


Part I: Building a Relational Datbase



Exercise : Consider a university with students living in dormitories.


Sketch out a complete database, with all necessary tables, to keep track of the college students with the following information:


Assume the following:
  1. Each student is assigned to one dorm room and has at most one roommate.
    The names of students are not unique.

  2. The college has many dorms.
    The dorm room contains a code for the room and the number of the particular room assigned to the student.
    For example, LAR221 means Larson Hall, room 221. Dorm names are Unique.

  3. The Dorm Address is the address of the dorm building. Each building has its own unique address.
    For example, Larson Hall has the address 123 Main Street, Hilltown, NM, 92373.

  4. Status tells the student's current status at the college:


  5. Meal Plan tells how many meals per week the student has choosen.
    Each mean plan has a single Meal Charge associated with it.

  6. The Room Charge is different for different dorms, but all students in the same dorm pay the same amount.


Part II: MySQL Practice




Exercise 1

Consider the following MySQL table: list
ID FirstName LastName AptHave AptWant
1 Nick Chu Redlands
2 Nick Chu Highland
3 Nick Chu Claremont
4 Aya Brown Redlands
5 Max Green Pasadena
6 Sam Sconz Irvine
7 Zar Baca Upland
8 Aya Brown Claremont
9 Max Green Claremont

  1. List all the records showing who has an apartment in Redlands or who wants an apartment in Redlands.
  2. List all the records showing where Nick owns apartments and where he wants to have one.
  3. How many records are stored in the "list" table?
  4. Count all the records showing people who currently have apartments.
  5. List all the first and last names of people in the table. DO NOT show duplicates.
  6. Count the number of people in the table. Do NOT count duplicate rows.
  7. List all the records showing records with firstnames beginning with an N.
  8. List all the records sorted in alphabetical order by lastname.





Exercise 2

Consider the following MySQL table: GRADES
ID FirstName CS Physics Math
1 Joen 68 56 76
2 Jim 96 89 92
3 Beck 65 67 57
4 Jann 69 59 82

  1. List all the students who scored over 90 on the CS test.
  2. List all thestudents who scored more than 85 in all subjects.
  3. display the sum of all grades for all students.
  4. Display the average grade for each subject.
  5. What is the minimum grade in Math?.
  6. What is the maximum gade in CS?
  7. Who got the highest grade in Physics?