Project Assignment 1

CSC319 - Database Technology

Due: 10/15/2001

You are a new hire at a local consulting firm and have been assigned to a development team that is about to start creating application programs for a database system. The database system is being developed for a local bookstore chain. Microsoft SQL Server will be the database management system.

You attend the first project meeting and discover that the database administrators have already created the database and have also populated the tables with sample data provided by the client. The development manager has asked you to work with a programmer who will be developing the application interface and has asked you to develop SQL code for the queries that will be needed.

The database is named pubs and contains eight tables (publisher, authors, titles, titleauthor, sales, stores, discounts, roysched). Details of the tables (i.e. column name, domain, primary key etc.) are available.

To complete the assignment, do the following:

1. Install Microsoft SQL Server as explained in Chapter 13 (365 -366) of your text.

   Note: Your database will be created and the tables populated during installation. Do not attempt to create the pubs database. Pages 367 - 381 contain sections on creating a database, creating tables, and entering data. You should read these sections for important background information but you will not need to create your database.

2. Use the SQL Server Enterprise Manager to get to the pubs database.

   Note: Page 366 provides details of how to launch Enterprise Manager. Figure 13-1 illustrates Enterprise Manager.

3. Review the table details provided in the attachment. After getting familiar with the database, use SQL Query Analyzer to code and test SQL queries for the following:

   Note: Page 369 mentions SQL Query Analyzer and Figure 13-4 illustrates its use.

   1. List the name, address, and city of all stores in California only.
   2. List the name (i.e. as a single string: last name, ", ", first name), and city of residence of all authors that do not live in a given city.
      Note: Your query should work for cities that are not in the database. You may use Gary to illustrate your answer.
   3. List the price of the most expensive book only.
   4. List the type, title, and price of all books that range in price from $10.00 to $19.99 inclusive. The list should be in ascending order of type and descending order of price.
   5. List the type and average price for each type of book.
   6. List all authors and publishers that are based in the same city.
      Hint: Requires a basic join.
   7. List the publishers of Psychology books.
      Hint: Requires a basic join.
   8. List the name, address, city, state, and zip of all stores that have sold more than a given quantity of a given type of book.
      Note: Extra credit. Requires a basic join. Use quantity 30, and type Psychology to illustrate your answer.

   Submit a typed report with the SQL query and the corresponding output for the pubs database.