Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Using the pubs2 and pubs3 sample databases

What is in 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. "