SQL queries help 1. Display the Product ID of all products with its associated P
ID: 3920242 • Letter: S
Question
SQL queries help
1. Display the Product ID of all products with its associated Product Model ID and Model Name (by using JOIN)
2. Display the Product Model Name together with a count of products associated with a given model.
3. Find the Product Model names with more that 10 associated products
AdventureWorksLT Schema SalesLT Schema Best Print Results if 8.5x11 paper, Landscape Fit to 1 sheet Dbo Schema PK ProductID U1 Name U2 ProductNumber FK1 U1 Name U2 rowguid ModifiedDate PK ErrorloglD Database Version VersionDate ModifiedDate ErrorNumber CustomerAddress PK,FK2 CustomerlD PK,FK1 AddressID FK2 ProductCategorylD SellStartDate SellEndDate U1 Name ErrorLine U2 rowguid AddressType rowguid ModifiedDate ModifiedDate U1 leName U3 rowguid PK SalesOrderlD PK,FK2 ProductModellD RevisionNumber PK Culture DueDate 1 AddressLine1 11 City 11 StateProvince 1 PostalCode PK CustomerlD U2 SalesOrderNumber Title U1 rowguid FK1,11 CustomerlD ShipMethod ModifiedDate PK LastName PK,FK1 SalesOrderlD SubTotal TaxAmt Freight +TotalDue U1 rowguid Modified Date 11 EmailAddress FK2,11ProductID Key l Index U Unique Index X XML Index +Computed Colum PK Pimary Key FK Foreign Key PasswordHashh Password Salt U1 rowguid LineTotal rowguid ModifiedDate ModifiedDate U1 rowguid U1 FK2 ShipToAddressID FK3 BillToAddressID ModifiedDateExplanation / Answer
1. SELECT p.ProductID, pm.ProductModelID, pm.Name FROM Product p JOIN ProductModel pm ON p.ProductModelID = pm.ProductModelID;
2. SELECT pm.Name, count(*) FROM Product p JOIN ProductModel pm ON p.ProductModelID = pm.ProductModelID GROUP BY p.ProductModelID;
3. SELECT pm.Name FROM ProductModel pm, (SELECT productModelId, count(*) AS COUNT FROM Product GROUP BY ProductModelID) AS temp WHERE temp.ProductModelID = pm.ProductModelID AND temp.COUNT > 10;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.