_edited.jpg)
Pubs and Northwind Database: A Complete Guide to Download and Use Them in SQL Server
How to Download and Use Pubs and Northwind Database
If you are a database administrator, developer, or data professional, you might have heard of pubs and northwind database. These are sample relational databases provided by Microsoft that contain fictional information about publishers, authors, titles, sales, customers, orders, products, and suppliers. They are often used as examples and walkthroughs for learning and demonstrating various features and functionalities of SQL Server and other database tools.
download pubs and northwind database
In this article, you will learn what are pubs and northwind database, why they are useful, what are their benefits and alternatives, how to download and install them, and how to use them for your own purposes. By the end of this article, you will have a better understanding of how to work with these sample databases and how to apply your skills and knowledge to real-world scenarios.
What are Pubs and Northwind Database and Why are They Useful
Pubs and northwind database are two of the oldest sample databases for SQL Server. They were originally created for SQL Server 2000, but they can still be used with later versions. They contain a set of tables, views, stored procedures, functions, triggers, indexes, constraints, and relationships that represent a typical business scenario.
Pubs database provides information about publishers, authors, titles, and sales of books. It has 19 tables, such as authors, publishers, titles, sales, discounts, etc. It also has some views, such as titleview, salesview, etc. It has some stored procedures, such as byroyalty, titleauthor, etc. It has some functions, such as getpubinfo. It has some triggers, such as deltitletrigger. It has some indexes, such as titleind. It has some constraints, such as au_id_pk.
Northwind database provides information about customers, orders, products, and suppliers. It has 13 tables, such as customers, orders, order details, products, suppliers, etc. It also has some views, such as current product list, quarterly orders, etc. It has some stored procedures, such as custordersdetail, custorderhist, etc. It has some functions, such as discount, extended price, etc. It has some triggers, such as updproducttrigger, delproducttrigger, etc. It has some indexes, such as productnameind, customeridind, etc. It has some constraints, such as orderid_pk, productid_pk, etc.
Pubs and northwind database are useful because they provide realistic data that can be used for testing and developing various database applications. They can also be used for learning and demonstrating different aspects of SQL Server and other database tools. For example,
You can use pubs database to learn how to create tables, views, stored procedures, functions, triggers, indexes, and constraints.
You can use northwind database to learn how to perform queries, joins, subqueries, aggregations, and transactions.
You can use both databases to learn how to use SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), or Visual Studio for managing, designing, and debugging databases.
You can use both databases to learn how to use LINQ to SQL, ADO.NET, or Entity Framework for accessing data from .NET applications.
You can use both databases to learn how to use Reporting Services , Analysis Services, or Integration Services for creating reports, dashboards, or data pipelines.
Pubs and northwind database are not the only sample databases available for SQL Server. There are also other databases, such as AdventureWorks, WideWorldImporters, or Contoso, that provide more complex and diverse data models and scenarios. However, pubs and northwind database are still popular and widely used because they are simple, easy to understand, and familiar to many database professionals.
How to Download and Install Pubs and Northwind Database
To use pubs and northwind database, you need to download and install them on your SQL Server instance. Here are the steps to do so:
Download the sample databases for SQL Server from this link. You will get a zip file named SQL2000SampleDb.zip that contains two files: instpubs.sql and instnwnd.sql. These are the scripts that will create and load the pubs and northwind database respectively.
Extract the zip file to a folder of your choice. For example, C:\SQL2000SampleDb.
Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
In SSMS, open a new query window and run the following command to set the database compatibility level to 80 (SQL Server 2000):
EXEC sp_dbcmptlevel 'master', 80;
This is necessary because the scripts use some syntax and features that are deprecated in later versions of SQL Server.
In SSMS, open the instpubs.sql file from the folder where you extracted the zip file. This script will create a database named pubs and populate it with data. Before running the script, you may want to change the path of the data and log files to match your system configuration. For example, you can change the line:
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
to:
FILENAME = 'C:\SQL2000SampleDb\pubs.mdf'
After making any necessary changes, run the script by pressing F5 or clicking Execute. You should see a message saying "Command(s) completed successfully."
In SSMS, open the instnwnd.sql file from the folder where you extracted the zip file. This script will create a database named northwind and populate it with data. Before running the script, you may want to change the path of the data and log files to match your system configuration. For example, you can change the line:
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.mdf'
to:
FILENAME = 'C:\SQL2000SampleDb\northwnd.mdf'
After making any necessary changes, run the script by pressing F5 or clicking Execute. You should see a message saying "Command(s) completed successfully."
In SSMS, expand the Databases node in Object Explorer. You should see two new databases: pubs and northwind. Right-click on each database and select Properties. In the Database Properties dialog box, click on Options in the left pane. In the right pane, change the Compatibility level to match your SQL Server version. For example, if you are using SQL Server 2019, change it to SQL Server 2019 (150). Click OK to save the changes.
In SSMS, right-click on each database and select Tasks > Detach. In the Detach Database dialog box, check the Drop Connections checkbox and click OK. This will detach the databases from SQL Server.
In SSMS, right-click on the Databases node in Object Explorer and select Attach. In the Attach Databases dialog box, click Add. In the Locate Database Files dialog box, browse to the folder where you extracted the zip file and select pubs.mdf. Click OK. Repeat this step for northwnd.mdf. Click OK again to attach the databases to SQL Server.
Congratulations! You have successfully downloaded and installed pubs and northwind database on your SQL Server instance.
How to Use Pubs and Northwind Database
Now that you have pubs and northwind database on your SQL Server instance, you can start using them for various purposes. Here are some examples of how to use them:
How to Connect to Pubs and Northwind Database Using Different Tools
You can connect to pubs and northwind database using different tools depending on your needs and preferences. For example:
You can use SSMS for managing, designing, and debugging databases. You can use the Object Explorer to browse and modify the objects in the databases, such as tables, views, stored procedures, etc. You can use the Query Editor to write and execute SQL statements against the databases. You can use the Database Diagrams to create and visualize the relationships among the tables. You can use the Solution Explorer to organize and deploy your database projects.
You can use SQL Server Data Tools (SSDT) or Visual Studio for developing and deploying database applications. You can use the SQL Server Object Explorer to connect and interact with the databases. You can use the SQL Server Database Project to create and manage your database schema and code. You can use the Data Tools Operations to perform various tasks, such as compare schemas, data, or publish databases. You can use the Data Sources and Data Sets to define and access data from your .NET applications.
You can use SQL Server Reporting Services (SSRS) for creating and delivering reports based on the data in the databases. You can use the Report Builder or Report Designer to design and format your reports. You can use the Report Server or Report Manager to publish and manage your reports. You can use the Report Viewer or Web Portal to view and interact with your reports.
You can use SQL Server Analysis Services (SSAS) for creating and analyzing multidimensional or tabular data models based on the data in the databases. You can use the Analysis Services Project to define and deploy your data models. You can use the Cube Designer or Tabular Model Designer to design and modify your data models. You can use the Browser or Excel to explore and query your data models.
You can use SQL Server Integration Services (SSIS) for creating and executing data pipelines that extract, transform, and load data from or to various sources, including the databases. You can use the Integration Services Project to create and manage your data pipelines. You can use the SSIS Designer to design and configure your data pipelines. You can use the SSIS Catalog or SSIS Server to deploy and execute your data pipelines.
These are just some of the tools that you can use to connect to pubs and northwind database. There are also other tools, such as PowerShell, Azure Data Studio, or third-party applications that you can use depending on your needs and preferences.
How to Query and Manipulate Data Using SQL
One of the most common ways to use pubs and northwind database is to query and manipulate data using SQL. SQL is a standard language for interacting with relational databases. It allows you to perform various operations, such as select, insert, update, delete, join, group, sort, filter, aggregate, etc.
Here are some examples of how to query and manipulate data using SQL:
To select all columns from a table, you can use the SELECT * FROM statement. For example:
SELECT * FROM pubs.dbo.authors;
This will return all rows and columns from the authors table in pubs database.
To select specific columns from a table, you can specify them after the SELECT keyword. For example:
SELECT companyname, contactname FROM northwind.dbo.customers;
This will return only two columns: companyname and contactname from the customers table in northwind database.
To filter rows from a table based on a condition, you can use the WHERE clause. For example:
SELECT * FROM pubs.dbo.titles WHERE type = 'business';
This will return only rows where the type column is equal to 'business' from the titles table in pubs database.
To join multiple tables based on a common column, you can use the JOIN clause. For example:
SELECT o.orderid, o.orderdate, c.companyname, p.productname, od.quantity, od.unitprice FROM northwind.dbo.orders o JOIN northwind.dbo.customers c ON o.customerid = c.customerid JOIN northwind.dbo.[order details] od ON o.orderid = od.orderid JOIN northwind.dbo.products p ON od.productid = p.productid;
This will return columns from four tables: orders, customers, order details, and products from northwind database. The tables are joined on the matching columns: customerid, orderid, and productid.
To group rows by a column and calculate an aggregate value, you can use the GROUP BY and an aggregate function. For example:
SELECT type, AVG(price) AS average_price FROM pubs.dbo.titles GROUP BY type;
This will return the average price for each type of book from the titles table in pubs database.
To sort rows by a column in ascending or descending order, you can use the ORDER BY clause. For example:
SELECT companyname, country FROM northwind.dbo.suppliers ORDER BY country ASC, companyname DESC;
This will return the company name and country of each supplier from the suppliers table in northwind database. The rows are sorted by country in ascending order and by company name in descending order.
These are just some of the examples of how to query and manipulate data using SQL. There are many more SQL statements and clauses that you can use to perform various operations on the data in pubs and northwind database. You can learn more about SQL syntax and usage from this link.
How to Use Pubs and Northwind Database for Learning and Demonstration Purposes
Another way to use pubs and northwind database is to use them for learning and demonstration purposes. You can use them to practice and improve your database skills and knowledge. You can also use them to showcase your database projects and solutions to others. For example:
You can use pubs and northwind database to learn how to use different features and functionalities of SQL Server and other database tools. You can follow the tutorials and walkthroughs provided by Microsoft or other sources that use these databases as examples. You can also explore the databases on your own and try different scenarios and tasks.
You can use pubs and northwind database to demonstrate your database skills and knowledge to others. You can create your own portfolio or resume that showcases your database projects and solutions based on these databases. You can also present your database projects and solutions to your peers, instructors, employers, or clients using these databases as examples.
Pubs and northwind database are not only useful for learning and demonstration purposes, but also for testing and developing purposes. You can use them as a starting point or a reference for creating your own databases or applications based on real-world scenarios.
Conclusion
In this article, you have learned what are pubs and northwind database, why they are useful, what are their benefits and alternatives, how to download and install them, and how to use them for various purposes. You have also seen some examples of how to connect to pubs and northwind database using different tools, how to query and manipulate data using SQL, and how to use pubs and northwind database for learning and demonstration purposes.
Pubs and northwind database are two of the oldest and most popular sample databases for SQL Server. They provide realistic data that can be used for testing, developing, learning, and demonstrating various database applications. They are simple, easy to understand, and familiar to many database professionals. They are also free and easy to download and install.
However, pubs and northwind database are not the only sample databases available for SQL Server. There are also other databases, such as AdventureWorks, WideWorldImporters, or Contoso, that provide more complex and diverse data models and scenarios. You may want to explore these databases as well to expand your database skills and knowledge.
We hope that this article has helped you to learn more about pubs and northwind database and how to use them effectively. If you have any questions or feedback, please feel free to contact us. Thank you for reading!
FAQs
Here are some frequently asked questions and answers about pubs and northwind database:
What are some common errors and solutions when using pubs and northwind database?
Some of the common errors that you may encounter when using pubs and northwind database are:
The scripts fail to run or create the databases due to syntax errors or compatibility issues. To fix this, you may need to change the database compatibility level to 80 (SQL Server 2000) before running the scripts, or modify the scripts to use the correct syntax and features for your SQL Server version.
The databases fail to attach or detach due to permission errors or file access errors. To fix this, you may need to run SSMS as an administrator, or grant full control permission to the folder where the data and log files are located.
The databases fail to connect or open due to login errors or network errors. To fix this, you may need to check your connection string, firewall settings, or server configuration.
If you encounter any other errors or issues when using pubs and northwind database, you can search online for possible solutions or contact Microsoft support for assistance.
How can I backup and restore the databases?
You can backup and restore the databases using SSMS or other tools. To backup the databases using SSMS, you can right-click on each database and select Tasks > Backup. In the Backup Database dialog box, you can specify the backup type, destination, options, etc. To restore the databases using SSMS, you can right-click on each database and select Tasks > Restore > Database. In the Restore Database dialog box, you can specify the source, destination, options, etc.
How can I update or modify the data in the databases?
You can update or modify the data in the databases using SQL statements or other tools. To update or modify the data using SQL statements, you can use the UPDATE, INSERT, DELETE, MERGE, etc. statements. For example:
UPDATE pubs.dbo.authors SET phone = '415 555-1212' WHERE au_id = '172-32-1176';
This will update the phone number of an author in pubs database.
INSERT INTO northwind.dbo.categories (categoryname, description) VALUES ('Beverages', 'Soft drinks, coffees, teas, beers');
This will insert a new category into northwind database.
DELETE FROM pubs.dbo.discounts WHERE discounttype = 'B';
This will delete all rows with discount type B from pubs database.
MERGE northwind.dbo.products AS target USING (SELECT productid, productname, supplierid, categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued FROM northwind.dbo.[order details] WHERE orderid = 10248) AS source ON (target.productid = source.productid) WHEN MATCHED THEN UPDATE SET target.unitsinstock = target.unitsinstock - source.quantity WHEN NOT MATCHED THEN INSERT (productid, productname, supplierid, categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued) VALUES (source.productid, source.productname, source.supplierid, source.categoryid, source.quantityperunit, source.unitprice, source.unitsinstock, source.unitsonorder, source.reorderlevel, source.discontinued);
This will merge the data from order details table into products table in northwind database based on product id.
How can I export or import the data from or to other sources?
You can export or import the data from or to other sources using SSIS or other tools. To export or import the data using SSIS, you can create a data pipeline that uses a source and a destination component. For example, you can use a SQL Server source to read data from pubs or northwind database, and use a Flat File destination to write data to a CSV file. You can also use other components, such as Excel, XML, OLE DB, etc. to export or import data fro