Querying SQL Server 2016 Part 1




In this episode, I talk about querying SQL Server 2016.

If you have got your SQL server yet, you may download the installer from this link.

You can download the wide world importers database from this link.

Below is the query that I used in this video:
–return everything
select * from [Warehouse].[StockItems]
–query selected column
select [StockItemName],[UnitPrice], [QuantityPerOuter] from [Warehouse].[StockItems]

— multiply 2 columns in one
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems]

–sort by the column multiplication result
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems]
order by 2

–sort by the column multiplication result desc
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox
from [Warehouse].[StockItems]
order by 2 desc

–select top 10 from the inventory
select top 10 * from [Warehouse].[StockItemHoldings]
— join the product information to stocks in the inventory
select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox , sh.QuantityOnHand
from [Warehouse].[StockItems] s
join
[Warehouse].[StockItemHoldings] sh
on s.StockItemID=sh.StockItemID
order by 2
— calculate for the costonhand
select [StockItemName],
[UnitPrice]*[QuantityPerOuter] as CostperOuterBox ,
sh.QuantityOnHand,
[UnitPrice]*[QuantityPerOuter]* sh.QuantityOnHand as costofinventoryonhand
from [Warehouse].[StockItems] s
join
[Warehouse].[StockItemHoldings] sh
on s.StockItemID=sh.StockItemID
order by 4 desc

— calculate the totalcost of inventory in the warehourehouse
select
sum([UnitPrice]*[QuantityPerOuter]* [QuantityOnHand]) as costofinventory
from
[Warehouse].[StockItems] s
left join
[Warehouse].[StockItemHoldings] sh
on
s.stockitemid=sh.stockitemid

 




Leave a Reply

Your email address will not be published. Required fields are marked *