The Pubs database, created by Microsoft provides a set of fictional pieces of information about publishers, authors, titles and sales of books.
Although considered obsolete as far as database learning tools are concerned, we will be using some aspects of the PUBS database as a Transact-SQL learning tool throughout this website.
The PUBS database includes a fictional set of information about:
Publishers,
Authors,
Titles and the sales of their associated books.
As we proceed through the tutorials, the Pubs database will help us with the ability to ask publishing-related questions, such as which employees work in which publishing groups, or which author has written a specific book.
The first step in working with the Pubs Sample Database is to download it. You can find it on GitHub from the following GitHub repository.
or download it from LearningDub.com to create and load the Pubs sample database for SQL Server.
Once downloaded, run the downloaded instpubs.sql script file to recreate the database on an instance of SQL Server using SQL Server Management Studio.
1 Open SQL Server Management Studio (SSMS).
2 Connect to the target SQL Server.
3 Open the script in a new query window.
4 Run the script (Click Execute).
Let’s take a minute to look at a diagram of the Pubs database and its 11 tables. We will use the diagram below as a reference as we write code and queries throughout this website.
Each category has a unique identifier, name, description, and picture. The picture is stored as a byte array in JPEG format.
Each product has a unique identifier, name, unit price, number of units in stock, and other columns.
Each product is associated with a category by storing the category’s unique identifier.
The relationship between Categories and Products is one-to-many, meaning each category can have zero, one, or more products.
Each product is supplied by a supplier company, indicated by storing the supplier’s unique identifier.
A quantity and unit price of a product is stored for each detail of an order.
Each order is made by a customer, taken by an employee, and shipped by a shipping company.
Each employee has a name, address, contact details, birth and hire dates, a reference to their manager (except for the boss whose ReportsTo field is null), and a photo stored as a byte array in JPEG format. The table has a one-to-many relationship to itself because one employee can manage many other employees.
Tip:
To open the Relationship Diagram SQL Server showing the six tables and the relationships between them, select Database Tools > Relationships.
Note
