CSCI A114 / INFO I111
Lab Notes Seven

First semester 2001-2002

Tutorial Project Seven: Creating a Report Using Design View. HWK3.

In lab today you will be turning in Lab 6 (Macros, Switchboards).

You should also turn in Homework Three.

Some help with Homework Three is provided below.

You also need to start on Lab 7 (Reports Using Design View), that you will turn in after the Practical.

Lab 7 is not due until Fri Sep 28, but is included in the list of topics for the Practical.

The Practical Exam is on the first 7 tutorials in the lab manual.

And now the crudest help with Homework Three follows.

Assume the following database organisation. 

Books: (*ISBN, Title, Topic, Author, Price)
Students: (*SID, SName, SAddress)
Transactions: (*ISBN, *SID, *Date Out, Date In, Rating)
Then you need to make sure some data is in the tables, to account for steps 1-5 in the assignment.

Then produce the five queries you're being asked for.

Query 1: All books currently out and overdue. 

SELECT Transactions.[Date Out], Students.SName, Transactions.ISBN, Books.Title, Date()-[Date Out] AS [Age in Days]
FROM Students INNER JOIN (Books INNER JOIN Transactions ON Books.ISBN = Transactions.ISBN) ON Students.SID = Transactions.SID
WHERE (((Transactions.[Date Out])<Date()-14));
Query 2: All books sorted by average ranking. 
SELECT Books.ISBN, Books.Title, Avg(Transactions.Rating) AS AvgOfRating
FROM Books LEFT JOIN Transactions ON Books.ISBN = Transactions.ISBN
GROUP BY Books.ISBN, Books.Title
ORDER BY Avg(Transactions.Rating) DESC;
Query 3: Patrons sorted by interest in our books. 
SELECT Students.SName, Count(Transactions.ISBN) AS CountOfISBN
FROM Students INNER JOIN Transactions ON Students.SID = Transactions.SID
GROUP BY Students.SName
ORDER BY Count(Transactions.ISBN) DESC;
Query 4: List books which are: a) overdue AND b) good (rating >= 3.3) 
SELECT Query2.AvgOfRating, Books.Title, Query1.[Age in Days]
FROM (Query2 INNER JOIN Books ON Query2.ISBN = Books.ISBN) INNER JOIN Query1 ON Books.ISBN = Query1.ISBN
WHERE (((Query2.AvgOfRating)>=3.3) AND ((Query1.[Age in Days])>14));
Query 5: Books referring to a topic, sorted by ranking. 
SELECT Query2.AvgOfRating, Query2.Title
FROM Query2 INNER JOIN Books ON Query2.ISBN = Books.ISBN
WHERE (((Books.Topic) Like "Busi*"))
ORDER BY Query2.AvgOfRating DESC;
Please keep in mind one thing: that this is just an example.

That means it can help you greatly, if you look at it in the right way.

You're not supposed to do it exactly this way.

You're responsible for your own design, your own data, and queries.

But the lecture notes and this set of lab notes is trying to help you put together one solution.

Use this as a reference.

Turn in what you have.

Turn in the homework by the end of the lab.

Let us know if you have any questions.

Last updated: Sep 21, 2001 by Adrian German for A114/I111