V Urgent : 4 SQL Query Modifications Required

1 
    Using
    	the schema diagram below, have to develop TSQL script for each question.
    
    Use
    	JOIN wherever applicable (do not use EXISTS!).
    	Do
    	NOT use variable or temporary table.
    
    <img src="/uploads/db8100/original/1X/8de7f3237885b2701e3dffe5268cfc1449ebf70a.png" width="573" height="154">

Note: CustomerName is also involved in Customer Table

1. List the top 10 customers for the current year by total sales.

Column List:		CustomerName, [Total Sales]
Sorting Order:	        [Total Sales] (desc)
	













2. List the customers with the total sales, who placed an order last year but not this year.

Column List:		CustomerName, [Total Sales] 
Sorting Order:	[Total Sales] (desc)  
		
	
3. List last year’s 10 best-selling products and 10 least-selling products
	with the total sales. 
	

Column List:		ProductName, ProductNumber, [Total Sales]
Sorting Order:	        [Total Sales] (desc)
	

4. Reduce Product’s ListPrice by 20% if it did not receive any order for the last two years and the reduced price would be still above StandardCost.

- Update ListPrice in Product.
2

looks like a test.
post what have you done so far.

3

4 Tables are below and are related as 1-->2-->3-->4

Customer Table
Customer ID
CustomerName
Street
City
State
CountryCode

Salesorderheader
SalesOrderID
CustomerID
OrderDate
DueDate
ShipDate
Status

Salesorderdetail
SalesOrderDetailID
SalesOrderID
ProductID
OrderQTY
UnitPrice
LineTotal

Product

ProductID
ProductName
ProductNumber
StandardCost
ListPrice

For Query 1:

selec t Top 10 c.customername, sum( sod.linetotal) totalsales from c.customer innerjoin s.salesorderdetail on c.customerid = soh.customerid
where datepart( 'year',soh.orderdate) = '2015'
group by c.customername order by totalsales desc

For Query 2:
select c.customername,sum(sod.linetotal) totalsales from c.customer innerjoin s.salesorderdetail on c.customerid = soh.customerid
where datepart('year',soh.orderdate ) = '2014'
groupby c.customername order by total sales desc

For Query3:
select * from
(
select ProductID,ProductName,SUM(OrderQty) TotalQty from salesorderdetail sod join productp
group by produuctname
orderby sum(orderquanity) desc, product name asc) A where rownumber < = 10;

For Query4:

Update Product Set listprice = ( Listprice + 20% ) where datepart ( 'year', orderdate ) between '2014' and '2015.

These are my answers kindly correct me

4

Do your queries work?