Need help writing queries. 1. Sales by Territory Enter a SQL SELECT command that

Question

Need help writing queries.

1.Sales by Territory Enter a SQL SELECT command that displays the following columns from the Sales.SalesTerritory table: TerritoryID, Name, CountryRegionCode, SalesYTD, and SalesLastYear. Include a clause that sorts the output in descending order by SalesYTD, so that the territory with the highest year-to-date sales will be displayed first.

2. Sales by Country Enter a SQL SELECT command that displays the following from the Sales.SalesTerritory table: CountryRegionCode, sum of SalesYTD, and sum of SalesLastYear. Use aggregate functions for the sums. Assign appropriate column headings, such as TotalSalesYTD and TotalSalesLastYear to the sums. Include a clause that will group results based on CountryRegionCode

3. Sales Growth by U.S. Territory Enter a SQL SELECT command that displays the following from the Sales.SalesTerritory table: TerritoryID, Name, SalesYTD, SalesLastYear, and a formula that calculates the percent change in sales. The formula for percent change should divide the difference between SalesYTD and SalesLastYear by SalesLastYear and multiply by 100. Assign an appropriate column heading such as PercentChange to this formula. Include a clause that will limit results to rows where the CountryRegionCode is 'US'.

4. Best-Selling Products Enter a SQL SELECT command that displays the following from the Sales.SalesOrderDetails table: ProductID and sum of LineTotal. Use an aggregate function for the sum. Assign an appropriate column heading such as TotalSales to the sum. Include a clause that will group results based on ProductID. Include a clause that will limit the displayed results to those having a sum of LineTotal greater than $2 million. Include a clause that will sort the results in descending order by the sum or LineTotal, so the product with the highest sales will be displayed first. The figure $2 million should be entered as 2000000.

5. Best-Selling Products With Product Names Using query from #4, modify the FROM clause of the query to join the Sales.SalesOrderDetail table to the Production.Product table, using the ProductID column in both tables for the join. Modify the SELECT clause of the query to display the Name column (from the Production.Product table) after the ProductID column in the query results. Modify the GROUP BY clause of the query to group by both ProductID and Name. (This is necessary because columns can only appear in the SELECT clause of a grouped query if they also appear in the GROUP BY column, or if they are used in an aggregate function.) Wherever the ProductID column appears in the query, ensure that it is fully qualified, that is, that it has the name of the appropriate table and a period in front of it (that is, either SalesOrderDetail.ProductID or Product.ProductID). This is necessary because there is a ProductID column in both tables and you must tell SQL which one you mean in each case.

Explanation / Answer

1. Select TerritoryID, Name, CountryRegionCode, SalesYTD, SalesLastYear from SalesTerritory order by SalesYTD Desc

2. Select CountryRegionCode, sum(SalesYTD), sum (SalesLastYear) from SalesTerritory Group by CountryRegionCode

3. Select TerritoryID, Name, SalesYTD, SalesLastYear, (SalesYTD-SalesLastYear/SalesLastYear*100) "PercentChange" from SalesTerritory where CountryRegionCode = 'US'

4. Select ProductID, sum(LineTotal) "TotalSales" from salesorderdetails group by productID having sum(LineTotal) > '2000000' order by sum (LineTotal) Desc

5. Select S.ProductID, Name from salesorderdetails S join production P on S.ProductID = P.ProductID group by S.ProductID, Name

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.