download adventureworks 2012 database include person table

LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh Replace the existing query with the following code: SELECT c.FirstName, c.LastName, oh.SalesOrderNumber

For example, suppose you want to retrieve a list of all customers and any orders they have placed, including customers who have registered but never placed an order. In cases where a row in the outer table has no corresponding rows in the related table, NULL values are returned for the related table fields. Run the modified query and note that it returns data from all three tables.Īn outer join is used to retrieve all rows from one table, and any corresponding rows from a related table.ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID Replace the query with the following code, which retrieves sales order data from the SalesLT.SalesOrderHeader, SalesLT.SalesOrderDetail, and SalesLT.Product tables: SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name AS ProductName, od.OrderQty, od.UnitPrice, od.LineTotal

The use of table aliases can greatly simplify a query, particularly when multiple joins must be used. Run the modified query and confirm that it returns the same results as before. ON p.ProductCategoryID = c.ProductCategoryID Modify the query to assign aliases to the tables in the JOIN clause, as shown here: SELECT p.Name AS ProductName, c.Name AS Category INNER joins are the default kind of join. The results should be the same as before. Modify the query as follows to remove the INNER keyword, and re-run it. Because the query uses an INNER join, any products that do not have corresponding categories, and any categories that contain no products are omitted from the results. Use the ⏵Run button to run the query, and after a few seconds, review the results, which include the ProductName from the products table and the corresponding Category from the product category table. In the query editor, enter the following code: SELECT AS ProductName, AS Category

In the new SQLQuery_… pane, use the Connect button to connect the query to the AdventureWorks saved connection.

Start Azure Data Studio, and create a new query (you can do this from the File menu or on the welcome page).

You can find the relevant product category record for a product based on its ProductCategoryID field which is a foreign-key in the product table that matches a primary key in the product category table. For example, suppose you need to retrieve data about a product and its category from the SalesLT.Product and SalesLT.ProductCategory tables. Use inner joinsĪn inner join is used to find related data in two tables. Note: If you’re familiar with the standard AdventureWorks sample database, you may notice that in this lab we are using a simplified version that makes it easier to focus on learning Transact-SQL syntax. For your reference, the following diagram shows the tables in the database (you may need to resize the pane to see them clearly). Always refer to the latest documentation for any version-specific details or updates.In this lab, you’ll use the Transact-SQL SELECT statement to query multiple tables in the adventureworks database.

Always use the latest version of SQL Server Management Studio and AdventureWorks databases for better compatibility.īy following these steps, you can set up AdventureWorks databases for different versions on your SQL Server instance.

Make sure to check the Microsoft documentation for any specific version-related instructions or updates.

Update your applications or tools to connect to the AdventureWorks database using the appropriate connection string for the SQL Server version you are using.

After the restoration is complete, you should see the AdventureWorks database listed under the 'Databases' node in SSMS.

Ensure that your user account has the necessary permissions to access and modify the AdventureWorks database.

Click 'OK' to start the restoration process.

Select 'Device' and then click on the ellipsis (.

Right-click on the 'Databases' node in Object Explorer.

Open SQL Server Management Studio (SSMS).

If not already installed, download and install SQL Server Management Studio (SSMS).

Install SQL Server Management Studio (SSMS): bak backup file for the desired version (e.g., AdventureWorks2019.bak, AdventureWorks2022.bak). Here are the instructions for setting up AdventureWorks databases for different versions: Download the AdventureWorks Database Backup The process for setting up any version of the AdventureWorks database (e.g., AdventureWorks2019, AdventureWorks2022) is generally similar. If you just need to practice a few SQL queries on the AdventureWorks database, you could just use dbfiddle website which already has the AdventureWorks database available for use.Ĭheck out the AdventureWorks 2022 database at dbfiddle!

Download adventureworks 2012 database include person table