I need the SQL query from Microsoft SQL Server Management please. MySQL doesn\'t

Question

I need the SQL query from Microsoft SQL Server Management please. MySQL doesn't always correlate. I believe query statement progression should be done after each part. Hopefully this is all you need to answer the question. Thanks

The marketing department wants to know if the Road-650 bicycle has more than 10 styles currently available. Using the Production.Product table, create a statement using IF THEN logic to trigger on whether there are more or less than 10 of Road-650 bicycle units available. Submit the statement and the result of the query.

The marketing department has decided that it will only feature black colored Road-650 bicycles in the sales promotion. However, the marketing department may want to modify the sale at a later date. Use a DECLARE and SET statement to pre-set a variable color to "Black." Using the variable, run a query to show all the type of bicycles including all fields.

Building on the question, add the "Quantity" and "ListPrice." Filter the results to only items in "Finished Goods Storage."

Building on the question, the marketing department has decided that any products that currently have more than 100 units in stock will be discounted by 10%. All other products will have a 5% discount as part of the promotion. Create a CASE statement that modifies the "ListPrice" into a new field called "SalesPrice."

Explanation / Answer

Question 1

IF EXISTS (SELECT COUNT(*)
FROM Production.Product
WHERE ProductModel = "Road-650"
HAVING COUNT(*) >= 10) BEGIN
PRINT 'More than 10 styles of Road-650 is available'
END ELSE BEGIN
PRINT 'Less than 10 styles of Road-650 is available'
END

Question 2

declare @bicyclecol varchar(15)
set @bicyclecol ='Black'
select * from Production.ProductModel ab
join Production.product xy on xy.ProductModel=ab.ProductModel where ab.name='Road-650'and color=@bicyclecol

Question 3

declare @bicyclecol varchar(15)
set @bicyclecol ='Black'
select *, Quantity, ListPrice from Production.ProductModel ab
join Production.product xy on xy.ProductModel=ab.ProductModel
join Production.ProductInventory cd on cd.ProductID=xy.ProductID
where ab.bicname='Road-650'and color=@bicyclecol
and FinishedGoodsStorageStatus=1

Question 4

declare @bicyclecol varchar(15)
set @bicyclecol ='Black'
select *, Qty, case when Qty>100 then LP *.90 else LP *.95 end SP from Production.ProductModel ab join Production.product xy on xy.ProductModel=abProductModel
join Production.ProductInventory cd on cd.ProductID=xy.ProductID
where ab.name='Road-650'and color=@bicyclecol  
and FinishedGoodsStorageStatus=1

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects