SQL exercise improve a query AdventureWorks-CodePudding

im looking to improve my SQL skills, and i find this exercise without a solution. The exercises go like this: The production department needs to analyze the products that have the word Bikes as part of their subcategory names and a list price between 3K and 4K. (K means 1000) The information required is as follows:

Product: ID, Name, Color, reorderpoint, standardcost, safetystocklevel, listprice, daystomanufacture Model Name and Description Subcategory Name Category Name Consider that the StandardCost field should show with only two decimals – Data type: decimal(10,2) Click here for AdventureWorks Schema

so far my solution go like this:

SELECT p.ProductID, p.Name, p.Color, p.reorderpoint, p.StandardCost, p.SafetyStockLevel, p.listprice, p.DaysToManufacture, pm.Name, pd.Description, psc.Name, pc.Name
FROM              Product p LEFT JOIN ProductModel pm
                                         ON p.ProductModelID = pm.ProductModelID
                            LEFT JOIN ProductModelProductDescriptionCulture pmpdc
                                         ON p.ProductModelID = pmpdc.ProductModelID
                            LEFT JOIN ProductDescription pd
                                         ON pd.ProductDescriptionID = pmpdc. ProductDescriptionID
                            LEFT JOIN ProductSubcategory psc
                                         ON psc.ProductSubCategory = p.ProductSubCategory
                            LEFT JOIN ProductCategory pc
                                         ON pc.ProductCategoryID = psc.ProductCategoryID

WHERE    UPPER(psc.Name) LIKE “BIKE%” AND p.listprice  BETWEEN  3000 AND 4000 ;

I do not know why but i think i may have some issue here, or maybe everything is wrong

CodePudding user response：

psc.ProductSubCategory = p.ProductSubCategory should be psc.ProductSubCategoryid = p.ProductSubCategoryid

and

“BIKE%” should be 'BIKE%'

and in adventureworks2012 which is the last version I have to hand there are 12 bikes returned using your query as fixed, but not in the price range you are looking for. I suggest you fix and run with the where clause excluding price while you investigate further

CodePudding user response：

Looking at the Adventure Works database the names in the Product Subcategory table as Mountain Bikes and Road Bikes, so your where clause should be '%bike%'