sql
/*
1. Count how many different Sales Stores exist. [5 marks]
2. Retrieve all information of Special offers regarding all discontinued products type. (consider: SpecialOffer) [5 marks]
3. Show Order Quantity, Unit Price, Shipping date, Status of those sales orders where quantity sold is greater than 4. (SalesOrderHeader, SalesOrderDetail)[10 marks]
4. Display Order Quantity, Unit Price, Name of product, Order date sorted by Unit price. [10 marks]
5. Show Order Quantity, Unit Price, Name of product, Order date, Ship date, Due Date for those sales, where item was shipped within 7 days. [10 marks]
6. Show those Sales order details where Unit price is greater than average Unit price. [10 marks]
7. Show total number of products ordered along with their names for those products which were sold more than 1000 items. [10 marks]
*/
--1
select count (distinct name) from Sales.Store
where name is not null
--2
select * from Sales.SpecialOffer
where type LIKE 'discontinued product'
--3
select orderQty, UnitPrice, ShipDate status
from sales.SalesOrderDetail sod join sales.SalesOrderHeader soh
on sod.SalesOrderID = soh.SalesOrderID
where OrderQty>4
--4
select orderQty, UnitPrice, name, OrderDate
from Sales.SalesOrderDetail sod join Production.Product pp
on sod.ProductID = pp.ProductID
join Sales.SalesOrderHeader soh
on sod.SalesOrderID= soh.SalesOrderID
order by UnitPrice
--5
select orderqty, UnitPrice, name, orderdate, shipdate, duedate, DATEDIFF(DAY, OrderDate, ShipDate)
from Sales.SalesOrderDetail sod join Production.Product pp
on sod.ProductID=pp.ProductID
join Sales.SalesOrderHeader soh
on soh.SalesOrderID= sod.SalesOrderID
where DATEDIFF(DAY, orderdate, shipdate)<=7
--6
select *
from Sales.SalesOrderDetail
where UnitPrice>(select avg(UnitPrice) from Sales.SalesOrderDetail)
--7
select sum(orderqty) as quantity, pp.name
from Sales.SalesOrderDetail sod join Production.Product pp
on pp.ProductID=sod.ProductID
group by pp.name
having sum(OrderQty)>1000
order by pp.name