sql 1) Write a query against the TSQL2012 database to retrieve; from the product

Question

sql

1) Write a query against the TSQL2012 database to retrieve;

from the production.products table, the [supplierId] and the count of products supplied by each supplier that are not discontinued. Only return suppliers that supply 3 or more products. Order the list by the count returned in descending order then by [supplierId] in ascending order.

2)

Create a query that returns the following columns of the order placed by the customer with [Sales].[Customer].[AccountNumber] of  AW00029716 on [ SalesOrderHeader].[OrderDate] March 31, 2014.

Sales.SalesOrderHeader.PurchaseOrderNumber,
Sales.SalesOrderHeader.SubTotal,  
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.TotalDue,
Person.Address.AddressLine1,
Person.Address.AddressLine2,
Person.Address.City,
Person.Address.PostalCode

3)

From the AdvetureWorks database's production.productCostHistory table;

Calculate the average [standardCost] of all active products with IDs 707 through to (and including) 799. Display this single value result set with 2 decimal places precision.

Explanation / Answer

Solution:

1)

SELECT supplierID, COUNT(productSupplied) FROM  production.products WHERE status= 'not discontinued' GROUP BY supplierID ORDER BY COUNT(productSupplied) DESC AND supplier ID ASC

2)

SELECT [Customer].[AccountNumber],  [ SalesOrderHeader].[OrderDate] FROM production.products WHERE

[Customer].[AccountNumber] = 'AW00029716' and [ SalesOrderHeader].[OrderDate]= 'March 31, 2014'

3)

SELECT AVG(stabdardCost) FROM production.productCostHistory WHERE prodictiD= 707 IN BETWEEN productID= 799

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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