Introduction to AdventureWorks Database 2022: A Guide for Installation and Data Exploration
INTRODUCTION
In the data realm, AdventureWorks stands as a well-known and widely used sample database created by Microsoft. AdventureWorks is designed to showcase the capabilities of Microsoft SQL Server. This database is an essential resource for data practitioners to utilize and improve their SQL skills. In this blog post, we will embark on an adventurous journey of exploringthe latest version of AdventureWorks database – AdventureWorks2022, from its installation to navigating its intricacies in SQL Server Management Studio (SSMS). Let’s explore the world of AdventureWorks2022!
UNDERSTANDING ADVENTUREWORKS
AdventureWorks serves as an illustrative database, providing a realistic and comprehensive example of an enterprise data model. It covers various business aspects, including sales, production, and human resources, making it an ideal playground for learning, exploration, and experimentation. AdventureWorks comes in different versions, aligning with different releases of SQL Server. Whether you are using SQL Server 2012, 2014, 2016, or beyond, there is likely an AdventureWorks version suited to your needs. To have a better understanding on the schemas, tables and columns of the AdventureWorks database, a data dictionary can be downloaded following this link. The database diagram showing the visual representation of the tables, relationships, and connections within the database can be seen here.
SETTING UP ADVENTUREWORKS 2022 DATABASE
Now that we understand what the AdventureWorks Database is, let’s set up and install the AdventureWorks 2022 Database on our local machine. There are two ways to install the Database:
Using Scripts
Restoring the backup file (*.bak)
For the blog, we will be using the latter option, restoring a backup file. The prerequisites to this are to have installed the SQL Server Management Studio (SSMS) software and to download the backup file. You can follow this guide for a hassle-free installation. To download the backup file, follow this link. Upon following the backup file link attached, scroll down till you get to the download section. You will see three columns with a variety version of AdventureWorks database. Ensure to download the OLTP backup file, AdventureWorks2022.bak.
The AdventureWorks database comes in various versions to serve different purposes and scenarios. There are three different versions of backup file,
OLTP (Online Transaction Processing): This focuses on transactional processing for day-to-day operations. It is well-suited for activities like order processing and inventory management. It features normalized data structure for efficient handling of individual transactions.
Data Warehouse: It is geared towards analytical processing for business intelligence and reporting. It is ideal for complex queries and aggregations over large datasets. It utilizes denormalized or star-schema design to optimize query performance.
Lightweight Versions: This is a simplified version for educational purposes or scenarios with resource constraints. It may include a subset of tables, reduced data, or simplified relationships. It is designed for ease of use, quick setup, and learning SQL concepts.
The next step is to restore the downloaded AdventureWorks.bak backup file to your SQL server instance. Firstly, we need to move the backup file to your SQL Server backup location. Although this may vary depending on your installation location, instance name and also the installed version of SQL Server on your local machine. For SQL Server 2019, the default instance typically follows a path like: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup. For SQL Server 2022, the default instance path should resemble: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup.
Upon the completion of moving the backup file to the designated backup location, the next step is to launch the SQL Server Management Studio (SSMS) and establish a connection with the SQL Server instance where you plan to install the sample database. Right click on the “Databases” option on the object explorer pane, then select the “Restore Database” option to launch the Restore Database wizard.
The Restore Database window pops up on the screen, select the “Device” option under the source region and then select the ellipses represented by three consecutive dots (...) to add the backup file. Select the “Add” option to select the backup file (AdventureWorks2022.bak). This takes you directly to the Locate Backup File Wizard, select the backup file. If this is done correctly, the Restore Database window should look like the picture below:
N.B.: In the Locate Backup File Wizard, if the backup file doesn’t automatically appear then you must have moved the downloaded backup file to the wrong folder.
Moving on, you can select “OK”, to load the database successfully. You can refresh your SSMS in the object explorer pane and then expand your Databases.
DEFINITION OF TERMS
Table: A table in SQL is a structured representation of data organized in rows and columns. It is the basic unit for storing data in a relational database. Each table consists of one or more columns, each with a specific data type, and rows that represent individual records. Tables are used to store, retrieve, and manipulate data, and they form the core structure of a relational database.
Schema: In SQL, a schema is a logical container or namespace that holds database objects, including tables, views, procedures, and more. It provides a way to group related objects together within a database. Schemas are useful for organizing and managing database objects, avoiding naming conflicts, and controlling access to objects by different users or roles.
Database: A database in SQL is a structured collection of data that is organized and stored for easy retrieval and manipulation. It is a container that holds one or more related tables along with other objects, such as views, stored procedures, and indexes. Databases provide a way to organize and manage large amounts of data efficiently.
EXPLORATORY DATA ANALYSIS
Are you as curious as I am to know more about the data in the AdventureWorks2022 Database?! Join me on this adventure to explore the AdventureWorks2022 Database!
Firstly, let’s see all the tables available in the database using the code below:
“USE AdventureWorks2022
SELECT *
FROM AdventureWorks2022.INFORMATION_SCHEMA.TABLES ”
This query retrieves information about the tables present in the database. There are 91 rows indicating 91 tables available in the database
Before we move on, let’s have a brief understanding of the INFORMATION_SCHEMA used above. The INFORMATION_SCHEMA is a standard schema that provides a set of views containing metadata about the database objects. This schema is part of the SQL standard and offers a consistent way to access information about the structure and organization of a database.
We can also explore the how many schemas available in the database and the number of tables available in each schema using the code below:
“SELECT DISTINCT TABLE_SCHEMA, COUNT(TABLE_NAME) AS table_count
FROM AdventureWorks2022.INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA”
Now that we understand the structure of the database, let’s explore the Sales.SalesOrderDetail table. Firstly, let’s explore the structure of the table. How many columns and rows do we have in the Sales.SalesOrderDetail table. We can find out the column using the code below:
“SELECT COUNT(*) AS Number_of_col
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘SalesOrderDetail’;”
Our result should be 11, which indicates that there are 11 columns in the Sales.SalesOrderDetail table. To determine the number of rows in the Sales.SalesOrderDetail table, the code below is used:
“SELECT COUNT(*) AS row_count
FROM Sales.SalesOrderDetail”
There are 121,317 rows in the Sales.SalesOrderDetail table.
Let’s proceed, how many orders did we receive? To get this, use this code
“USE AdventureWorks2022
SELECT COUNT(DISTINCT SalesOrderID) AS Order_Count
FROM Sales.SalesOrderDetail”
How many products did we sell? To get this, use this code;
“USE AdventureWorks2022
SELECT COUNT(DISTINCT ProductID) AS Product_Count
FROM Sales.SalesOrderDetail”
What is the total quantity of orders received by products? To get this, use this code;
“USE AdventureWorks2022
SELECT ProductID, SUM(OrderQty) AS Order_Qty
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY Order_Qty DESC;”
What is the average quantity ordered and average amount spent by each customer? To get this, use this code;
“SELECT
H.CustomerID,
AVG(D.OrderQty) Avg_quantity,
ROUND(AVG(D.LineTotal),2) AS Avg_Amount
FROM Sales.SalesOrderHeader AS H
JOIN Sales.SalesOrderDetail AS D ON H.SalesOrderID = D.SalesOrderID
GROUP BY CustomerID
ORDER BY Avg_Amount DESC;”
What month do we get the most orders? To get this, use this code;
“SELECT FORMAT(OrderDate,’MMMM’) as Order_Month, COUNT(D.SalesOrderID) Order_Count
FROM Sales.SalesOrderHeader AS H
JOIN Sales.SalesOrderDetail AS D
ON H.SalesOrderID = D.SalesOrderID
GROUP BY FORMAT(OrderDate,’MMMM’)
ORDER BY Order_Count DESC”
CONCLUSION
In conclusion, this blog post has guided you through the installation and exploration of AdventureWorks Database 2022. From a hassle-free setup to navigating key tables, we've unveiled its versatility for honing SQL skills and gaining valuable insights. AdventureWorks 2022 serves not just as data but as a dynamic tool, making it an ideal resource for both beginners and experienced professionals. So, install AdventureWorks 2022, dive into the world of relational databases, and let your data exploration journey commence!
