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

From wiki.ucalgary.ca
< Courses‎ | Computer Science‎ | CPSC 203‎ | CPSC 203 Template‎ | Winter 2010 Lab Manual
Revision as of 18:34, 8 March 2010 by Smakady (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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.