.::: Sample Query T-SQL Select Statements from AdventureWorks2019 :::.

SELECT ROW_NUMBER() OVER (ORDER BY T.Name) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName

use AdventureWorks2019
go

--- sample 1
SELECT ROW_NUMBER() OVER (ORDER BY [TotalDue]) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
CustomerID, SalesOrderID, [SubTotal], [TotalDue]
FROM Sales.SalesOrderHeader

--- sample 2
SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
* FROM Sales.SalesOrderHeader
WHERE [OrderDate] BETWEEN '1/1/2000' AND '1/31/2023'
 
--- sample 3
SELECT ROW_NUMBER() OVER (ORDER BY PurchaseOrderID) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
* FROM [Purchasing].[PurchaseOrderHeader]
WHERE [OrderDate] BETWEEN '1/1/2008' AND '12/31/2023' AND [VendorID] = 1492

--- sample 4
SELECT ROW_NUMBER() OVER (ORDER BY [SalesOrderID]) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,  
[SalesOrderID]
,[CustomerID]
, [TerritoryID]
, STR([SubTotal]) AS [Sub Total], [TaxAmt]
, + '$' + CONVERT(varchar(12), [TotalDue], 1) AS [Total $ Due]
FROM Sales.SalesOrderHeader
WHERE [OrderDate] BETWEEN '1/1/2003' AND '1/31/2023'

--- sample 5
SELECT ROW_NUMBER() OVER (ORDER BY T.Name) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
T.[Name], [SalesOrderID],[CustomerID],
S.[TerritoryID],[SubTotal],[TaxAmt],[TotalDue]
FROM Sales.SalesOrderHeader AS S
INNER JOIN [Sales].[SalesTerritory] AS T
ON T.TerritoryID =S.TerritoryID
WHERE [OrderDate] BETWEEN '1/1/2003' AND '1/31/2023'

--- sample 6
SELECT ROW_NUMBER() OVER (ORDER BY T.[Name]) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
T.[Name], [CustomerID], [SalesOrderID], [SubTotal],[TaxAmt],[TotalDue]
FROM Sales.SalesOrderHeader AS S
INNER JOIN [Sales].[SalesTerritory] AS T ON T.TerritoryID =S.TerritoryID
WHERE [OrderDate] BETWEEN '1/1/2003' AND '1/31/2023'
ORDER BY T.Name, CustomerID

--- sample 7
SELECT DISTINCT([TerritoryID])
FROM Sales.SalesOrderHeader
ORDER BY TerritoryID

--- sample 8
SELECT ROW_NUMBER() OVER (ORDER BY t.Name) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
t.Name, SUM(s.SubTotal) AS [Sub Total],
STR(Sum([TaxAmt])) AS [Total Taxes],
STR(Sum([TotalDue])) AS [Total Sales]
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesTerritory as t ON
s.TerritoryID = t.TerritoryID
GROUP BY t.Name
ORDER BY t.Name

--- sample 9
SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
[SalesOrderID], COUNT([SalesOrderDetailID]) AS [# Line Items], STR(SUM([UnitPrice] * [OrderQty])) AS [Sales Order Total]
FROM [Sales].[SalesOrderDetail]
GROUP BY SalesOrderID

-- sample 10 need to lab
SELECT
ROW_NUMBER() OVER (ORDER BY p.Name) AS No,
@@SERVERNAME as ServerName, @@servicename as ServiceName,p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
ORDER BY p.Name DESC

 

 

https://faculty.business.wsu.edu/featherman/web-programming/sampletsql/