Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Using the pubs2 and pubs3 sample databases

Using the pubs2 and pubs3 sample databases

The pubs2 sample database is used for most examples in this manual. Where noted, the pubs3 database is used instead. You can try any of the examples on your own workstation.

The query results you see on your screen may not look exactly as they do in this manual. That is because some of the examples here have been reformatted (for example, the columns have been realigned) for visual clarity or to take up less space on the page.

To change the sample database using create or data modification statements, you may need to get additional permissions from a System Administrator. If you do change the sample database, Sybase suggests that you return it to its original state for the sake of future users. Ask a System Administrator if you need help restoring the sample databases.

What is in the sample databases?

The sample database, pubs2, contains these tables: publishers, authors, titles, titleauthor, roysched, sales, salesdetail, stores, discounts, au_pix, and blurbs. The pubs3 sample database adds store_employees but does not include au_pix. pubs3 is an updated version of pubs2 and can be used for referential integrity examples. Its tables differ slightly from the tables defined in pubs2.

Here is a brief description of each table:

• publishers contains the identification numbers, names, cities, and states of three publishing companies.

• authors contains an identification number, first and last name, address information, and contract status for each author.

• titles contains the book ID, name, type, publisher ID, price, advance, royalty, year-to-date sales, comments, and publication date for each book.

• titleauthor links the titles and authors tables together. It contains each book's title ID, author ID, author order, and the royalty split among the authors of a book.

• roysched lists the unit sales ranges and the royalty connected with each range. The royalty is some percentage of the net receipts from sales.

• sales records the store ID, order number, and date of book sales. It acts as the master table for the detail rows in salesdetail.

• salesdetail records the bookstore sales of titles in the titles table.

• stores lists bookstores by store ID.

• store_employees lists employees for the stores described in the stores table.

• discounts lists three types of discounts for bookstores.

• au_pix contains pictures of the authors in binary form using the image datatype. au_pix is in pubs2 only.

• blurbs contains long book descriptions using the text datatype.

The pubs2 database is illustrated in Appendix A, "The pubs2 Database," while pubs3 is illustrated in Appendix B, "The pubs3 Database. "