Chapter 1 SQL

Jason C. H. Chen, Ph.D.

Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 [email protected]

Figure 1-5 JustLee Books’ table structures after normalization

#1 Which tables and fields would you access to determine which books have been purchased by a customer over the last year?

CUSTOMERS:



customer#, ORDERS:



order#, orderdate, customer#; ORDERITEMS:



order#, ISBN; BOOKS:



ISBN, title

#3. If management needed to determine which book category generated the most sales for last month, which tables and fields would be consulted to derive this information?

ORDERS:



orderdate, order#;

ORDERITEMS:



order#, ISBN, quantity;

BOOKS:



ISBN, retail, category

#5. If a customer inquired about a book written in 1999 by an author named Thompson, which access path (tables and fields) would you need to follow to find the list of books meeting the customer’s request?

AUTHOR:



Lname, AuthorID;

BOOKAUTHOR:



AuthorID, ISBN;

BOOKS:



ISBN, Pubdate.

10.

Data for an information technology conference needs to be collected. The conference has a variety of sessions scheduled over a two day period. Each attendee must register for the sessions he/she plans to attend. Some speakers are only presenting one

10.

Data for an information technology conference needs to be collected. The conference has a variety of sessions scheduled over a two day period. Each attendee must register for the sessions he/she plans to attend. Some speakers are only presenting one session while others are handling multiple sessions. Each session has only one speaker

.

Speakers Sessions Attendees

Speakers Sessions

Registration

Attendees

#2. How would you determine which orders have not yet been shipped to the customer?

Identify all orders that do not have an entry for the date shipped.

#4. How would you determine how much profit was generated from orders placed this month?

Determine the amount of profit generated by each book (Retail-Cost), multiply the profit for each book by the quantity sold last month, then total the amount of profit generated by each book.