For all the questions below write SQL query. Submit your queries in one pdf docu

Question

For all the questions below write SQL query. Submit your queries in one pdf document. You only need to submit the query statement, no need to include resultset.

The number at the end of the query statement indicates the number of rows returned by the query.

We are using AdventureWorkd2012 database for this assignment.

1.Show all the stores which have sales person with id 275 or 282 or 288 or 277 or 290(Sales.Store 307)

2. Show all the bike stores worked by sales person id 282 or 275.(eg. a bike store will have the word bike in the name)(Sales.Store 40)

3. For all products which are listed for less than $20 create a list which shows the name product number in one column. (Production.product 215). For Example if the product is id=922 the result will show Road TIre Tube - TT-R982

4. Show all products which start selling within 2 days of june 1, 2002.( Production.Product 211)

5. Show all products that we start selling in June and may.( Production.Product 211)

6. Show the maximum list price, average list price, maximum standard cost and minimum standard cost. Make sure all columns have a headings.(Production.Product 1

Explanation / Answer

1.

Select * from Store inner join Customer on Store.BusinessEntityID = Customer.StoreID inner join SalesTerritory on Customer.CustomerID = SalesTerritory.rowquid inner join SalesPerson on SalesTerritory.TerritoryID = SalesPerson.TerritoryID where SalesPerson.BusinessEntityID IN (275,282,288,277,290);

2.

Select Store.Name from Store inner join Customer on Store.BusinessEntityID = Customer.StoreID inner join SalesTerritory on Customer.CustomerID = SalesTerritory.rowquid inner join SalesPerson on SalesTerritory.TerritoryID = SalesPerson.TerritoryID where SalesPerson.BusinessEntityID = 282 or SalesPerson.BusinessEntityID = 275 and Store.Name like '%bike%';

3.

Select Product.Name from Product inner join ProductListPriceHistory on Product.ProductID = ProductListPriceHistory.ProductID where ProductListPriceHistory.ListPrice < 20;

4.

Select * from Products where SellStartDate between '2002/06/01 ' and '2002/06/03' ;

5.

Select * from Products where SellStartDate > (Select DATEPART(mm,SellStartDate) from Products where DATEPART(mm,SellStartDate) = 5) and (Select DATEPART(mm,SellStartDate) from Products where DATEPART(mm,SellStartDate) = 6);

6.

Select max(ListPrice) as MaximumListPrice,avg(ListPrice) as AverageListPrice ,max(StandardCost) as MaximumStandardCost ,min(StandardCost) as MinimumStandardCost from Products;

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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