SQL Server 2016: Querying (Part 2/2)

Introduction

This is part 2 on the series of lectures on querying SQL Server 2016 database.

Read part 1 of this series of lecture if have not done so.

Objectives

In this lesson we will build more complex queries. This part of the lecture will aim to build the sales data for the inventory card which is one  of the objective of this course among others.

Before You Begin

 Install SQL server 2016. Download the Evaluation version from this link. Download wide word importers sample database from here.

Video 

A video edition of this training is available in this link.

Query

Below are the query that you need to learn. These queries will be used to build the sales data in the stock card that we are trying to achieve.

Lesson 11. Get Product Name and Product ID for Orders Data

SELECT
[StockItemID],[StockItemName]
FROM
[Warehouse].[StockItems]

Lesson 12. Use Join to get the inventory and the related orders.

SELECT
s.[StockItemID],[StockItemName],[OrderedOuters],[ReceivedOuters],[PurchaseOrderID],[LastReceiptDate]
FROM
[Warehouse].[StockItems] s
join
[Purchasing].[PurchaseOrderLines] pd
on
s.StockItemID=pd.stockitemid

Lesson 13. Use Join to get data from order details and Order Summary

      SELECT  
                 s.[StockItemID]      
                 ,[StockItemName]      
                 ,[OrderedOuters]      
                 ,[ReceivedOuters]      
                 ,[OrderDate]      
                 ,PO.[PurchaseOrderID]      
                 ,[LastReceiptDate]      
                 ,po.[SupplierID]      
                 FROM [Warehouse].[StockItems] s  
                 join [Purchasing].[PurchaseOrderLines] pd  
                 on s.StockItemID=pd.stockitemid  
                 JOIN [Purchasing].[PurchaseOrders] PO  
                 ON PD.PurchaseOrderID=PO.PurchaseOrderID