Question

Assignment Instructions (MY WORK IN MYSQLBENCH)- Follow instructions in class to download and build a database in your DBMS. Write and demonstrate queries (in-class) that do the following:

1. Download the appropriate attached SQL file for your DBMS. Load the file into your database design tool and run it to build the Pine Valley Furniture Company database. This is the database that is used in the textbook examples for both Chapters 5 and 6.
2. Write a query to show the price reduced by 10% for all products that are tables or entertainment centers with a standard price greater than or equal to $200. Show the discounted price under the heading "Sale Price". Order the results by increasing price.
3. Write a query to show the average price for each finish available in desk products where the average price is greater than $300. Show the finish under the heading "Desk Finish" and the average price under the heading "Average Price". Order the results by decreasing average price.

After I have reviewed your assignment in class, submit (to this assignment on Blackboard) a screenshot of your database design tool window for each query - show both the query and the query results.

The results of the first query should look like this:

ProductDescription	Sale Price
Coffee Table	180
Entertainment Center	585
Dining Table	720

The results of the second query should look like this:

Desk Finish	Average Price
White Ash	750
Natural Ash	375
Cherry	325

DROP DATABASE IF EXISTS cis_3050_PVFC;
CREATE DATABASE cis_3050_PVFC;
USE cis_3050_PVFC;

CREATE TABLE DoesBusinessIn_T
(CustomerID DECIMAL(11,0) NOT NULL,
TerritoryID DECIMAL(11,0) NOT NULL,
CONSTRAINT DoesBusinessIn_PK PRIMARY KEY (CustomerID, TerritoryID),
CONSTRAINT DoesBusinessIn_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID),
CONSTRAINT DoesBusinessIn_FK2 FOREIGN KEY (TerritoryID) REFERENCES Territory_T(TerritoryID));

CREATE TABLE Employee_T
(EmployeeID VARCHAR(10) NOT NULL,
EmployeeName VARCHAR(25),
EmployeeAddress VARCHAR(30),
EmployeeCity VARCHAR(20),
EmployeeState CHAR(2),
EmployeeZipCode VARCHAR(10),
EmployeeDateHired DATE,
EmployeeSupervisor VARCHAR(10),
CONSTRAINT Employee_PK PRIMARY KEY (EmployeeID));

CREATE TABLE Skill_T
(SkillID VARCHAR(12) NOT NULL,
   SkillDescription VARCHAR(30),
CONSTRAINT Skill_PK PRIMARY KEY (SkillID));

CREATE TABLE EmployeeSkills_T
(EmployeeID VARCHAR(10) NOT NULL,
SkillID VARCHAR(12) NOT NULL,
CONSTRAINT EmployeeSkills_PK PRIMARY KEY (EmployeeID, SkillID),
CONSTRAINT EmployeeSkills_FK1 FOREIGN KEY (EmployeeID) REFERENCES Employee_T(EmployeeID),
CONSTRAINT EmployeeSkills_FK2 FOREIGN KEY (SkillID) REFERENCES Skill_T(SkillID));

CREATE TABLE Order_T
(OrderID DECIMAL(11,0) NOT NULL,
   CustomerID DECIMAL(11,0),
OrderDate TIMESTAMP DEFAULT NOW(),
CONSTRAINT Order_PK PRIMARY KEY (OrderID),
CONSTRAINT Order_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID));

CREATE TABLE WorkCenter_T
(WorkCenterID VARCHAR(12) NOT NULL,
WorkCenterLocation VARCHAR(30),
CONSTRAINT WorkCenter_PK PRIMARY KEY (WorkCenterID));

CREATE TABLE ProductLine_T
(ProductLineID DECIMAL(11,0) NOT NULL,
ProductLineName VARCHAR(50),
CONSTRAINT ProductLine_PK PRIMARY KEY (ProductLineID));

CREATE TABLE Product_T
(ProductID DECIMAL(11,0) NOT NULL,
ProductLineID DECIMAL(11,0),
ProductDescription VARCHAR(50),
ProductFinish VARCHAR(20),
ProductStandardPrice DECIMAL(6,2),
CONSTRAINT Product_PK PRIMARY KEY (ProductID),
CONSTRAINT Product_FK1 FOREIGN KEY (ProductLineID) REFERENCES ProductLine_T(ProductLineID));

CREATE TABLE ProducedIn_T
   (ProductID    DECIMAL(11,0) NOT NULL,
WorkCenterID VARCHAR(12) NOT NULL,
CONSTRAINT ProducedIn_PK PRIMARY KEY (ProductID, WorkCenterID),
CONSTRAINT ProducedIn_FK1 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID),
CONSTRAINT ProducedIn_FK2 FOREIGN KEY (WorkCenterID) REFERENCES WorkCenter_T(WorkCenterID));

CREATE TABLE OrderLine_T
   (OrderID DECIMAL(11,0) NOT NULL,
ProductID DECIMAL(11,0) NOT NULL,
OrderedQuantity DECIMAL(11,0),
CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID, ProductID),
CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID),
CONSTRAINT OrderLine_FK2 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID));

CREATE TABLE RawMaterial_T
(MaterialID VARCHAR(12) NOT NULL,
MaterialName VARCHAR(30),
MaterialStandardCost DECIMAL(6,2),
UnitOfMeasure VARCHAR(10),
CONSTRAINT RawMaterial_PK PRIMARY KEY (MaterialID));

CREATE TABLE Salesperson_T
(SalespersonID DECIMAL(11,0) NOT NULL,
SalespersonName VARCHAR(25),
SalespersonPhone VARCHAR(50),
SalespersonFax VARCHAR(50),
TerritoryID DECIMAL(11,0),
CONSTRAINT Salesperson_PK PRIMARY KEY (SalesPersonID),
CONSTRAINT Salesperson_FK1 FOREIGN KEY (TerritoryID) REFERENCES Territory_T(TerritoryID));

CREATE TABLE Vendor_T
(VendorID DECIMAL(11,0) NOT NULL,
VendorName VARCHAR(25),
VendorAddress VARCHAR(30),
VendorCity VARCHAR(20),
VendorState CHAR(2),
VendorZipcode VARCHAR(50),
VendorFax VARCHAR(10),
VendorPhone VARCHAR(10),
VendorContact VARCHAR(50),
VendorTaxID VARCHAR(50),
CONSTRAINT Vendor_PK PRIMARY KEY (VendorID));

CREATE TABLE Supplies_T
(VendorID DECIMAL(11,0) NOT NULL,
MaterialID VARCHAR(12) NOT NULL,
SuppliesUnitPrice DECIMAL(6,2),
CONSTRAINT Supplies_PK PRIMARY KEY (VendorID, MaterialID),
CONSTRAINT Supplies_FK1 FOREIGN KEY (MaterialId) REFERENCES RawMaterial_T(MaterialID),
CONSTRAINT Supplies_FK2 FOREIGN KEY (VendorID) REFERENCES Vendor_T(VendorID));

CREATE TABLE Uses_T
(ProductID DECIMAL(11,0) NOT NULL,
MaterialID VARCHAR(12) NOT NULL,
GoesIntoQuantity INTEGER,
CONSTRAINT Uses_PK PRIMARY KEY (ProductID, MaterialID),
CONSTRAINT Uses_FK1 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID),
CONSTRAINT Uses_FK2 FOREIGN KEY (MaterialID) REFERENCES RawMaterial_T(MaterialID));

CREATE TABLE WorksIn_T
   (EmployeeID VARCHAR(10) NOT NULL,
WorkCenterID VARCHAR(12) NOT NULL,
CONSTRAINT WorksIn_PK PRIMARY KEY (EmployeeID, WorkCenterID),
CONSTRAINT WorksIn_FK1 FOREIGN KEY (EmployeeID) REFERENCES Employee_T(EmployeeID),
CONSTRAINT WorksIn_FK2 FOREIGN KEY (WorkCenterID) REFERENCES WorkCenter_T(WorkCenterID));

I
INSERT INTO EmployeeSkills_T (EmployeeID, SkillID)
VALUES ('123-44-345', 'BS12');
INSERT INTO EmployeeSkills_T (EmployeeID, SkillID)
VALUES ('123-44-345', 'RT1');
INSERT INTO EmployeeSkills_T (EmployeeID, SkillID)
VALUES ('454-56-768', 'BS12');

INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1001, '2018-10-21', 1);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1002, '2018-10-21', 8);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1003, '2018-10-22', 15);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1004, '2018-10-22', 5);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1005, '2018-10-24', 3);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1006, '2018-10-24', 2);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1007, '2018-10-27', 11);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1008, '2018-10-30', 12);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1009, '2018-11-05', 4);
INSERT INTO Order_T (OrderID, OrderDate, CustomerID)
VALUES (1010, '2018-11-05', 1);

INSERT INTO ProductLine_T (ProductLineID, ProductLineName)
VALUES (1, 'Cherry Tree');
INSERT INTO ProductLine_T (ProductLineID, ProductLineName)
VALUES (2, 'Scandinavia');
INSERT INTO ProductLine_T (ProductLineID, ProductLineName)
VALUES (3, 'Country Look');

INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (1, 'End Table', 'Cherry', 175, 1);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (2, 'Coffee Table', 'Natural Ash', 200, 2);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (3, 'Computer Desk', 'Natural Ash', 375, 2);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (4, 'Entertainment Center', 'Natural Maple', 650, 3);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (5, 'Writers Desk', 'Cherry', 325, 1);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (6, '8-Drawer Desk', 'White Ash', 750, 2);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (7, 'Dining Table', 'Natural Ash', 800, 2);
INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID)
VALUES (8, 'Computer Desk', 'Walnut', 250, 3);

INSERT INTO WorksIn_T (EmployeeID, WorkCenterID)
VALUES ('123-44-345', 'SM1');

Answer 1

1.

SELECT
ProductDescription,
cast ((ProductStandardPrice-((ProductStandardPrice*10) /100)) as decimal(10,0)) as [Sale Price]
FROM Product_T
WHERE ProductStandardPrice>=200
and (ProductDescription like '%Table%' or ProductDescription like '%Entertainment Center%')
ORDER BY 2 ASC

2.

SELECT ProductFinish as [Desk Finish],
AVG(ProductStandardPrice) AS [Average Price]
FROM Product_T
WHERE ProductDescription like '%Desk%'
GROUP BY ProductFinish
HAVING AVG(ProductStandardPrice)>=300
ORDER BY AVG(ProductStandardPrice) desc