CSE 385 Databases - Miami University - SQL Assignment One_ Comprehensive Q&A.

Assignment Day1 –SQL: Comprehensive practice Write queries for following scenarios Using AdventureWorks Database 1. Write a query that retrieves the columns ProductID, Name, Color and ListPrice f rom the Production.Product table, with no filter. select ProductID, Name, Color, ListPrice from Production.Product 2. Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table, the rows that are 0 for the column ListPrice select ProductID, Name, Color, ListPrice from Production.Product where ListPrice = 0 3. Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table, the rows that are rows that are NULL for the Color column. select ProductID, Name, Color, ListPrice from Production.Product where Color is Null 4. Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table, the rows that are not NULL for the Color column. select ProductID, Name, Color, ListPrice from Production.Product where Color is not NULL 5. Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table, the rows that are not NULL for the column Color, and the column ListPrice has a value greater than zero. select ProductID, Name, Color, ListPrice from Production.Product where Color is not NULL and ListPrice 0 6. Generate a report that concatenates the columns Name and Color from the Production.Product table by excluding the rows that are null for color. select 'Name: ' + Production.Product.Name + ' -- COLOR:' + Color AS 'Name AND Color' from Production.Product where color is not NULL 7. Write a query that generates the following result set from Production.Product: Name And Color -------------------------------------------------- NAME: LL Crankarm -- COLOR: Black NAME: ML Crankarm -- COLOR: Black NAME: HL Crankarm -- COLOR: Black NAME: Chainring Bolts -- COLOR: Silver NAME: Chainring Nut -- COLOR: Silver NAME: Chainring -- COLOR: Black ……… select 'Name: ' + Name + ' -- COLOR:' + Color AS 'Name AND Color' This study source was downloaded by from CourseH on :23:51 GMT -05:00