SIMS 257 Extra Credit Assignment

Using the ORACLE Cookie database, create queries that produce the answers to the following questions. The results should contain only the answers requested -- plus evidence to support them -- and no more (see below). In addition one question asks you to create SQL for inserting new data into the database. The Cookie database consists of 7 tables in Oracle ray.bib, ray.call, ray.indx, ray.lib, ray.pub, ray.sub and ray.tkey. This is approximately the same database that was discussed in Lecture 8, except that the author name is included in the bib table, and there is a table tkey that is the keywords from titles for the bib entries.  The ER diagram for the Cookie database is:

Use the appropriate Oracle SQL commands to examine the attributes of each table (e.g., DESCRIBE ray.bib).
Then answer the following queries...

1. Who wrote "Microcosmographia Academica"?
   
2. How many pages long is Alfred Whitehead's "The Aims of Education and Other Essays"?
   
3. Which branches in Berkeley's public library system are open on Sunday?
   
4. What is the call number of Moffitt Library's copy of Abraham Flexner's book "Universities: American, English, German"?
   
5. What books on the subject of higher education are among the holdings of Berkeley libraries? (Note: this means in the City of Berkeley, not only Berkeley Public Library)
   
6. Print a list of the Mechanics Library holdings, in descending order by height.
   
7. What would it cost to replace every copy of each book that contains illustrations (including graphs, maps, portraits, etc.)?
   
8. Which library closes earliest on Friday night?
   
9. What is the total replacement cost of the Moffitt Library (assume that replacement books would be bought at the same price as the original books)?
10. Create SQL to add the following bibliographic information into the database (Note that each record will require multiple statements).
    
<> 
Accno: D121
Duggan, Mary Kay. CD-ROM in the Library. Boston, MA : G.K. Hall & Co.,
1990. $17.95
(Illustrated, Pagination: X roman numeral pages, 126 arabic numeral pages,
23 cm.)
Held by the main branch of the San Francisco Public Library (1 copy), and
by Stanford university (1 copy) both places use the call number "Z681.3O67C28"
Subjects: 'OPTICAL DISKS', 'LIBRARIES - AUTOMATION'
New Subject to be added for this book is 'CD-ROM'

Accno: J122
James, Geoffrey. The Tao of Programming. Santa Monica, CA: Infobooks, 1987.
$7.95
(Illustrated, XVII roman numberal pages, 151 arabic numeral pages, 18 cm.)
Held by the main branch of the San Francisco Public Library (1 copy), and
by Stanford university (1 copy) both places use the call number "QA76.6J354 1986"
Subjects: 'ELECTRONIC DIGITAL COMPUTERS'