Share via

SQL Server 2016: Querying (Part 1/2)

  • Article

Objectives

This segment is intended for beginner or for those who are taking a review course of their querying SQL Server 2016 capability. This series of the lesson will allow you to familiarize the new sample database for SQL server 2016 which is the wide world importers. The database backup is located at this link. If you haven't installed SQL Server 2016 just yet you may acquire an evaluation version from this site

Another objective of this series of lectures is to build an inventory card for wide world importer.

Videos

A video version of this training is available from this link

Before You Begin

Install SQL server 2016. Download the Evaluation version from this link

Download Wide World Importers database from this link.

Queries

Below are the sample queries used for the part 1 of this series.

Lesson 1.  Query all column and Rows using * Keyword

select * from [Warehouse].[StockItems]

Lesson 2.  Query Using Selected Columns in the Select List

* select [StockItemName],[UnitPrice], [QuantityPerOuter] from [Warehouse].[StockItems]*

Lesson 3. PERFORM ARITHMETIC OPERATIONS IN THE COLUMN

select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems]

Lesson 4.  Sorting Data on a Calculated Column

select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems] order by 2

Lesson 5. Sorting on a Calculated Column in Descending Order

* select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox from [Warehouse].[StockItems] order by 2 desc*

Lesson 6. Limiting the result set using TOP Keyword

* select top 10 * from [Warehouse].[StockItemHoldings]*

Lesson 7.  Querying Data From Different Tables Using JOIN

select [StockItemName],[UnitPrice]*[QuantityPerOuter] as CostperOuterBox , sh.QuantityOnHand from [Warehouse].[StockItems] s join [Warehouse].[StockItemHoldings] shon s.StockItemID=sh.StockItemID order by 2

Lesson 8. Calculating data from different table using join and sorting the result

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

Lesson 9. Aggregating data with sum function

- 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

What's Next?

To proceed to Part 2 of this series, click here.