Question

Using Microsoft SQL Server

2 2020-05-29 Describe how SQL queries work USE Adventure Works2012 example no 1 USE Adventure Works2012 GO SELECT AVG(TotalDue) as AVG_TotalDue FROM [Sales) (SalesOrder Header) example no 2 SELECT SalesOrderID Order Date. TotalDue st Name AS TerritoryName. SELECT AVG(TotalDue) FROM (Sales) (SalesOrder Header) ) AS AVG_TotalDue FROM [Sales) (SalesOrderHeader) soh inner join (Sales] [Sales Territory) st ON soh TerritoryID = st.TerritoryID WHERE st Country RegionCode="GB' and Order Date between '2004-06-01' and '2012-06-30 and TotalDue > SELECT AVG(TotalDue) AS AVG TotalDue FROM [Sales) (SalesOrder Header] example no 3 SELECT TOP 3 CustomerID, SUM(TotalDue) as TotalSales FROM [Sales] [SalesOrderHeader] soh GROUP BY CustomerID ORDER BY TotalSales DESC example no 4 SELECT SalesOrderID Order Date. TotalDue, CustomerID from [Sales) (SalesOrderHeader] soh WHERE CustomerID in SELECT TOP 3 CustomerID SUM(TotalDue as TotalSales FROM [Sales] [SalesOrderHeader] soh GROUP BY CustomerID ORDER BY TotalSales DESC) example no 5 SELECT SalesOrderID Order Date, soh. CustomerID FROM [Sales) (SalesOrder Header) soh INNER JOIN SELECT TOP 3 CustomerID, SUM(TotalDue) as TotalSales FROM [Sales) (SalesOrderHeader] soh GROUP BY CustomerID ORDER BY TotalSales DESC ) a on soh CustomerID a CustomerID

Answer 1

example no 1 USE Adventure Works2012 GO SELECT AVG(TotalDue as AVG_TotalDue FROM [Sales) [SalesOrderHeader)

It specifies the use of database,here the name AdventureWork2012 is used to specify the name of the database on which the following query have to be executed

in above query the average value of the column "TotalDue" is calculated from SalesOrderHeader table in the sales schema and shown with the AVG_TotalDue instead of AVG(TotalDue).

example no 2 SELECT SalesOrderID Order Date, TotalDue st Name AS TerritoryName. SELECT AVG(TotalDue) FROM [Sales). [SalesOrderHeader] ) AS AVG_TotalDue FROM [Sales] [SalesOrderHeadersoh inner join (Sales] [Sales Territory) st On soh TerritoryID = st. TerritoryID WHERE st Country RegionCode="GB' and Order Date between '2004-06-01' and 2012-06-30 and TotalDue SELECT AVG(TotalDue) AS AVG_TotalDue FROM [Sales). [SalesOrder Header]

The above query shows the SalesOrderID,OrderDate,TotalDue, st.Name as TerritoryName of all the terrritory where CountryRegion code is 'GB and totalDue in terriototy is more than or equal to average of the toalDue in all territory for period of order from 1 June 2004 to 30 June 2012

example no 3 SELECT TOP 3 CustomerID, SUM(TotalDue) as TotalSales FROM [Sales) (SalesOrderHeader) soh GROUP BY CustomerID ORDER BY TotalSales DESC

Here the query is to show customer ID and total sales of top 3 customers in descending order from SalesOrderHeader table

example no 4 SELECT SalesOrderID Order Date. TotalDue, CustomerID from [Sales] [SalesOrderHeader] soh WHERE Customer1D in SELECT TOP 3 CustomerID, SUM(TotalDue) as TotalSales FROM [Sales] [SalesOrderHeader) soh GROUP BY CustomerID ORDER BY TotalSales DESC)

The above query shows order details(SalesOrderID,OrderDate,TotalDue,CustomerID) of the top 3 customers in from SalesOrderHeader table

example no 5 SELECT SalesOrderID Order Date, soh. CustomerID FROM [Sales] [SalesOrder Header] soh INNER JOIN SELECT TOP 3 CustomerID SUM(TotalDue) as TotalSalesFROM [Sales] [SalesOrderHeader] soh GROUP BY CustomerID ORDER BY TotalSales DESC ) a on soh CustomerID = a CustomerID

The above query shows order details(SalesOrderID,OrderDate,CustomerID) of the top 3 customers order from SalesOrderHeader table.

This query is a equivalent to example 4 in result, the only difference is the example 4 uses where clause whereas in example 5 INNER join is used