Computer Science 4611
Database Management Systems

Programming Assignment 1
SQL Queries (75 points)
Due Thursday, February 15, 2001


In this assignment, you will work on ORACLE to perform SQL queries. The assignment has two parts. In the first part you are to set up a database (this should be a new database of your choosing, but try to do something other than a student/classes database). In the second part you will be accessing a large database and designing queries to retrieve data from that database.

You will be working on ORACLE in this assignment. Oracle is one of the most widely used DBMS programs in the world. It runs on virtually every kind of computer, from PCs and Macintoshes, to minicomputers and giant mainframes. Oracle uses a relational data model. Oracle provides a form of SQL as its query language. Oracle provides an interactive interface for generating queries, SQL*PLUS that will perform many useful functions for you.

Getting Started

Oracle can be accessed from either Unix or DOS (PC). Click here to find documentation on how to access ORACLE and how to work in SQLPLUS. Note that for this assignment (and this assignment only) you will need to log in to Bulldog or one of the other mainframes in order to work. For all later assignments you should plan on using the workstations in HH314 (either directly or remotely).

Building Your Own Database

In the first part you are to setup a small database with the following requirements:

Querying An Existing Database

In the second part you are to query an existing database. The database that you will query is for a book publishing company. You are to write queries for the database and then spool your query along with the results to a file. You should submit this file containing your results.

Design Of The Book Database

The bookbiz database keeps track of the activities of three subsidiary publishing companies. Since the fiscal arrangements of the companies are not independent, the parent publisher has chosen to maintain a single database:

The relations have the following schemas:

keys are shown in italics.

The tables were created using the following SQL statements:

create table authors 
        (au_id char(11) not null, 
        au_lname varchar(40) not null, 
        au_fname varchar(20) not null, 
        phone char(12) null, 
        address varchar(40) null, 
        city varchar(20) null, 
        state char(2) null, 
        zip char(5) null); 

create table publishers 
        (pub_id char(4) not null, 
        pub_name varchar(40) null, 
        address varchar(40) null, 
        city varchar(20) null, 
        state char(2) null); 

create table roysched 
        (title_id char(6) not null, 
        lorange int null, 
        hirange int null, 
        royalty float null); 
create table titleauthors 
        (au_id char(11) not null, 
        title_id char(6) not null, 
        au_ord int null, 
        royaltyshare float null);

create table titles 
        (title_id char(6) not null, 
        title varchar(80) not null, 
        type char(12) null, 
        pub_id char(4) null,
        price float null, 
        advance float null, 
        ytd_sales int null, 
        contract int not null, 
        notes varchar(200) null, 
        pubdate date null); 

create table editors 
        (ed_id char(11) not null, 
        ed_lname varchar(40) not null, 
        ed_fname varchar(20) not null, 
        ed_pos varchar(12) null, 
        phone char(12) null, 
        address varchar(40) null, 
        city varchar(20) null, 
        state char(2) null, 
        zip char(5) null); 
create table titleditors 
        (ed_id char(11) not null, 
        title_id char(6) not null, 
        ed_ord int null); 

create table sales 
        (sonum int not null, 
        stor_id char(4) not null, 
        ponum varchar(20) not null, 
        sdate date null); 
create table salesdetails 
        (sonum int not null, 
        qty_ordered int not null, 
        qty_shipped int null, 
        title_id char(6) not null, 
        date_shipped date null);

These tables have already been created. You can access any of these tables by specifying anagaraj.tablename. Here "tablename" will be the name of the table you want to work on.

To see all of the tables in your tablespace run the following query:

        SQL>Select * from tab;

To select all of the rows in a table such as "editors" you can use the query:

        SQL> Select * from anagaraj.editors;

You can copy the data from each of these tables to tables you create in your own tablespace. For example, to copy the data from the authors table to a table named authors1 in your own tablespace execute the following query:

        SQL>create table authors1 as select * from anagaraj.authors;

To view all the information about a table you use the query:

        SQL> desc tablename;

The Queries

You should write queries for the following situations:

  1. Write a query to retrieve information about authors who live in Oakland or Wisconsin.
  2. Write a query to find titles of all books with a price between and including $10 and $20.
  3. Write a query to find which book title has sold more than 50 copies but less than 75 copies from any store.
  4. Find the full names of the authors whose first names are John.
  5. List all the publishers associated with the authors in the authors table.
  6. List the editors for all the titles in the titles table.
  7. Find all science and business books that have advances over 5000$.
  8. List the names of all the titles whose authors live in California, Indiana or Maryland.
  9. Find the names of the books with the lowest and the highest prices.
  10. Write a query to find the names of authors who live in the same city as "Livia Karsen".
  11. Find the names (last and first) of the authors who do not make less than 50% of the royalties on at least one book.
  12. Find the names of all books with prices ranging from the minimum price to minimum price + $50.
  13. Find the types of books in which the maximum advance is at least $50 more than the average advance for that type.

What To Turn In

Turn in a script of a session on ORACLE with your queries and the answers. To do this, spool your queries along with the output to a file and then hand in a copy of the file. See the ORACLE guide for more information.