View source for 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:

# 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.
# Create a form to help you enter new data into the Students table.
# Query1: Create a query that lists all information from the Students table, and the BorrowedBooks table
# Query2: From the students and BorrowedBooks tables, list the fields: Student name, Bookname, loan duration, and department. 
# Query2b: Modify query2 to list information for the students who are in the "CS" department. 
# Query2c: Modify query2 to list the information of the students who are in "CS" department and their grade in SENG301 is "B" or "A".
# Query3: Create an aggregate query (Group by) to count the number of students coming from every department.
# Query3b: Modify query 3 to count the number of students coming from the "IT" department.
# 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.