Advanced SQL  «Prev 

PUBS database Explained

What you will need to learn Advanced SQL

The database we will be using is the PUBS database. The PUBS database includes a fictional set of information about
  1. publishers,
  2. authors,
  3. titles and
  4. the sales of their associated books.
The database is often used as a model database that you can experiment with, as is the case with this course.
When you install many of the different database packages on the market, this database is often included as a learning tool. We are also using it in this course, both in the online Web-based query tool, and in the Access database that is available.
If you are using Microsoft Access, you will be able to download the PUBS database directly for Access. If you are using any other database, you will need the scripts provided to populate the database with the required data. Both the Access database and the scripts are available from the course Help area.

The PUBs Database: An Overview of Microsoft's Sample Database

The PUBs database, also known as the Northwind and PUBs sample database, is a sample relational database provided by Microsoft Corporation. Originally introduced with Microsoft SQL Server, it has been used as a learning and demonstration tool for database administrators, developers, and other data professionals. The PUBs database showcases various database concepts, SQL techniques, and features available in Microsoft SQL Server, enabling users to explore and understand these aspects in a hands-on manner.
Key Features and Components of the PUBs Database
  1. Sample Data and Schema: The PUBs database includes a sample dataset that models a fictitious publishing company. It consists of several tables, such as authors, titles, sales, and publishers, which store data related to books, authors, sales transactions, and publishing houses. The database schema is designed to demonstrate primary and foreign key relationships, normalization, and indexing, providing users with a realistic example of a well-structured relational database.
  2. Stored Procedures and Views: In addition to the tables, the PUBs database also includes sample stored procedures and views. These elements serve as examples of how to implement business logic within the database layer, as well as how to create reusable, pre-defined queries that can be executed efficiently. Users can examine these examples to understand best practices for creating and using stored procedures and views in a Microsoft SQL Server environment.
  3. Compatibility and Availability: The PUBs database has been adapted for different versions of Microsoft SQL Server, ensuring compatibility across various releases. Users can download the appropriate version of the PUBs database for their specific SQL Server installation from the Microsoft website or GitHub repositories. Additionally, the PUBs database has been adapted for other database management systems, such as MySQL and PostgreSQL, to facilitate learning and exploration beyond the Microsoft SQL Server ecosystem.
  4. Educational Value The primary purpose of the PUBs database is to serve as an educational tool for learning SQL and database concepts. Users can practice writing SQL queries, designing and implementing stored procedures, and optimizing database performance using the sample data and schema provided. The PUBs database also serves as a valuable resource for tutorials, workshops, and training programs related to SQL and database management.

The PUBs database from Microsoft Corporation is a sample relational database that serves as a valuable learning and demonstration tool for data professionals. With its realistic dataset, schema, and additional database components, the PUBs database provides an excellent platform for users to practice SQL techniques and explore various features available in Microsoft SQL Server and other database management systems.

The two main tools for administering the database system and running SQL queries are
  1. Enterprise Manager and
  2. Query Analyzer.
You can access both by clicking -> Start -> Programs -> Microsoft SQL Server.
Enterprise Manager provides an easy-to-use user interface for managing the database system. In order to run SQL queries, you need to use Query Analyzer. Load Query Analyzer by clicking Start -> Programs -> Microsoft SQL Server

Six databases are already installed; four of them are related to the database system in some way and best left alone unless you are sure of what you are doing. However, the Pubs and Northwind databases are both example databases that you can play around with and modify. Click the plus sign next to the Northwind database to reveal more details. Click the plus sign next to User Tables to see the tables that the database contains. Click next to a particular table to see the columns in the table. The second half of the Object Browser, called Common Objects, shows the various functions that the database system supports and acts as quite a handy reference guide.