need help answering questih Demonstrate that you did the following (use a SELECT

Question

need help answering questih

Demonstrate that you did the following (use a SELECT statement to show the records in the table) (Screen shot) Insert 3 rows in the Category table 5. a. i. Appetizer ii Entree ii. Dessert b. (Screen shotís)) Insert 3 Product records for each category in the product table i. Bananas Foster, Dessert category. $6.99, Hot, Y ii. Molten Lava Cake, Dessert category, $5.99, Warm, N ii. Key Lime Pie, Dessert category, $4.50, Cold, N iv. Ribeye Steak, Entree category. $16.99, Hot,Y v. Chef Salad, Entree category, $8.99, Cold, Y vi. Fettuccini Alfredo, Entree category, $12.99, Warm, N vii. Spinach/Artichoke Dip, Appetizer category, $4.99, Warm, N vii. Buffalo Wings, Appetizer category, $7.99, Hot, Y ix. Shrimp Cocktail, Appetizer category, $8.50, Cold, Y C. {Screen shot) Use one statement to decrease the price of all products that are served warm by 25% d. (Screen shot.] Write a SELECT statement to display the first letter only of the category description, the e. f. e. MINimum price for that category, the MAXimum price for the category, and the AVeraGe price for the category (Screen shot.J Write a SELECT statement to ROUND all of the prices to the nearest dollar amount. (Screen shot.) Use one statement to delete all products that are served Cold (Screen shot. J Write a SELECT statement that will display all the category descriptions and any corresponding product description in UPPER case. Be sure to display a category description even if there are no corresponding products.

Explanation / Answer

-- Assumptions

-- Working in MS SQL SERVER

-- As not database schema provide, I Cretaed a database named TestDB with 2 tables Category & Product

-- Find table create scripts and all other queries in bold

-- Change table name or column name before running according to your schema.

Create Table Category (CategoryId int NOT NULL Primary Key,

Category varchar(32) NOT NULL,

[Description] varchar(2048) NULL);

Create Table Product (ProductId int NOT NULL Primary Key,

Name varchar(64) NOT NULL,

CategoryId int NOT NULL,

Price float NOT NULL,

[ServeType] varchar (32) NULL,

[Description] varchar(2048) NULL);

Select * From Category

Select * From Product

Insert Into category(CategoryId, Category, [Description]) values (1,'Appetizer','Appetiezer Description')

Insert Into category(CategoryId, Category, [Description]) values (2,'Entree','Entree Description')

Insert Into category(CategoryId, Category, [Description]) values (3,'Dessert','Dessert Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (1, 'Bananas Foster', 1, 6.99, 'Hot', 'Bananas Foster Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (2, 'Molten Lava Cake', 1, 5.99, 'Warm', 'Molten Lava Cake Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (3, 'Key Lime Pie', 1, 4.50, 'Cold', 'Key Lime Pie Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (4, 'Ribeye Streak', 2, 16.99, 'Hot', 'Ribeye Streak Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (5, 'Chef Salad', 2, 8.99, 'Cold', 'Chef Salad Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (6, 'Fettuccini Alfredo', 2, 12.99, 'Warm', 'Fettuccini Alfredo Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (7, 'Spinach/Artichoke Dip', 3, 4.99, 'Warm', 'Spinach/Artichoke Dip Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (8, 'Buffalo Wing', 3, 7.99, 'Hold', 'Buffalo Wing Description')

Insert Into product (productId, Name, CategoryId,Price, ServeType, [Description])

values (9, 'Shrimp Cocktail', 3, 8.50, 'Cold', 'Shrimp Cocktail Description')

Update product Set price = (price * 3)/4 Where ServeType = 'Warm';

Select c.categoryID, c.Category, SUBSTRING(c.[Description], 0, CHARINDEX(' ', c.[Description])) 'Desc',

Min(p.price) 'MinPrice', Max(p.price) 'MaxPrice', AVG(p.price) 'AvgPrice'

From category c Inner join product p

on c.categoryID = p.categoryID

Group By c.categoryID, c.Category, c.[Description]

Select Name, ROUND(Price,0,0) 'Price', ServeType, [Description] From Product

Delete From Product Where ServeType = 'Cold'

Select c.category, UPPER(c.[Description]) 'Category Desc', p.Name 'Product Name', UPPER(p.[Description]) 'Product Desc'

From Category c Left Outer Join Product p

on c.categoryID = p.categoryID

Drop Table category;

Drop Table product;

Drop Database TestDB;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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