Courses/Computer Science/CPSC 203/CPSC 203 Template/Winter 2010 Lab Manual/Databases review

The following Access database has some basic data. Please download that data sheet (below), and do the following tasks:


 * 1) Create a relationship between the BorrowedBooks table and the Students table, where the ID field in the Students table becomes a foreign key in the BorrowedBooks table.
 * 2) Create a form to help you enter new data into the Students table.
 * 3) Query1: Create a query that lists all information from the Students table, and the BorrowedBooks table
 * 4) Query2: From the students and BorrowedBooks tables, list the fields: Student name, Bookname, loan duration, and department.
 * 5) Query2b: Modify query2 to list information for the students who are in the "CS" department.
 * 6) Query2c: Modify query2 to list the information of the students who are in "CS" department and their grade in SENG301 is "B" or "A".
 * 7) Query3: Create an aggregate query (Group by) to count the number of students coming from every department.
 * 8) Query3b: Modify query 3 to count the number of students coming from the "IT" department.
 * 9) Query4: Create a query to show all the fields of the Students table. Add a new field "Computer knowledge" that would take its value from an If statement based on the CPSC203 grade, as follows:If CPSC203 grade = "A" then ComputerKnowledge = "Computer Expert", otherwirse if CPSC203 grade = "D" then ComputerKnowledge = "Knows of Computers", otherwise ComputerKnowledge = "Intermediate knowledge"

[[Media: Access_review.mdb]]

Please note that this review covers some of Access module material.