Waiting for answer This question has not been answered yet. You can hire a professional tutor to get the answer.
You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data...
- You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update.
- You will also develop at least four meaningful reports. One must include a join, one must use a subquery, and one must use an aggregate function. You need to describe the business requirement of the update and each report. Note that numeric PK values are not often meaningful to a person looking at the report.
##DROP all tables
DROP TABLE IF EXISTS Shipment;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS OrderItem;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Vendor;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS PaymentMethod;
DROP TABLE IF EXISTS Customer;
##CREATE all tables with constraints
CREATE TABLE Customer
(
CustomerID
INT NOT NULL AUTO_INCREMENT,
FirstName
VARCHAR(50) NOT NULL,
LastName
VARCHAR(50) NOT NULL,
EmailAddress
VARCHAR(50) NOT NULL,
Address
VARCHAR(200) NOT NULL,
City
VARCHAR(50) NOT NULL,
State
VARCHAR(50) NOT NULL,
Zip
VARCHAR(10) NOT NULL,
PhoneNumber
VARCHAR(10) NOT NULL,
CONSTRAINT Customer_PK
PRIMARY KEY (CustomerID)
);
CREATE TABLE PaymentMethod
(
PaymentMethodID INT NOT NULL AUTO_INCREMENT,
PaymentMethodName VARCHAR(50) NOT NULL,
CONSTRAINT PaymentMethod_PK
PRIMARY KEY (PaymentMethodID)
);
CREATE TABLE Orders
(
OrderNumber INT NOT NULL AUTO_INCREMENT,
PaymentMethodID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL,
CONSTRAINT Order_PK
PRIMARY KEY (OrderNumber ),
CONSTRAINT Order_FK_Customer
FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID),
CONSTRAINT Order_FK_PaymentMethod
FOREIGN KEY (PaymentMethodID) REFERENCES PaymentMethod (PaymentMethodID)
);
CREATE TABLE Vendor
(
VendorID INT NOT NULL AUTO_INCREMENT,
VendorName VARCHAR(50) NOT NULL,
Address
VARCHAR(200) NOT NULL,
City
VARCHAR(50) NOT NULL,
State
VARCHAR(50) NOT NULL,
Zip
VARCHAR(10) NOT NULL,
ContactPerson VARCHAR(50) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL,
CONSTRAINT Vendor_PK
PRIMARY KEY (VendorID)
);
CREATE TABLE Product
(
ProductID INT NOT NULL AUTO_INCREMENT,
ProductName VARCHAR(50) NOT NULL,
BrandName VARCHAR(200),
Price DECIMAL(10,2) NOT NULL,
VendorID INT NOT NULL,
CONSTRAINT Product_PK
PRIMARY KEY (ProductID),
CONSTRAINT Vendor_FK_Product
FOREIGN KEY (VendorID) REFERENCES Vendor(VendorID)
);
CREATE TABLE OrderItem
(
OrderItemID INT NOT NULL AUTO_INCREMENT,
OrderNumber INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
SellingPrice DECIMAL(10,2) NOT NULL,
CONSTRAINT OrderItem_PK
PRIMARY KEY (OrderItemID),
CONSTRAINT OrderItem_FK_OrderNumber
FOREIGN KEY (OrderNumber) REFERENCES Orders(OrderNumber),
CONSTRAINT OrderItem_FK_Product
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
CREATE TABLE Employee
(
EmployeeID INT NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Position VARCHAR(50),
HireDate DATETIME NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
CONSTRAINT Employee_PK
PRIMARY KEY (EmployeeID)
);
CREATE TABLE Shipment
(
ShipmentID INT NOT NULL AUTO_INCREMENT,
ShipmentDate DATETIME NOT NULL,
Address
VARCHAR(200) NOT NULL,
City
VARCHAR(50) NOT NULL,
State
VARCHAR(50) NOT NULL,
Zip
VARCHAR(10) NOT NULL,
OrderNumber
VARCHAR(10) NOT NULL,
CONSTRAINT Shipment_PK
PRIMARY KEY (ShipmentID),
CONSTRAINT Shipment_FK_Order
FOREIGN KEY (OrderNumber) REFERENCES Orders(OrderNumber)
);
## INSERT data into all tables
INSERT INTO ','100 East ','210 West 101st','New ','11 Sixth Ave','New ','100 Maiden Lane','Richmond ','109 Bleeker','Jamaica','NY',89011,230-415-5555);
INSERT INTO Employee(FirstName,LastName,HireDate,Salary) VALUES
('Sernand','Ganks','2014-01-01',25000),
('Bash','Crain','2014-02-01',15000),
('Simran','Rathore','2014-12-01',6000),
('James','Cameron','2014-12-01',6000),
('Marilyn','Toosi','2015-01-01',5000),
('Sara','Tenkradi','2015-05-01',4000);
INSERT INTO PaymentMethod (PaymentMethodName) VALUES
('PayPal'),
('Credit Card');
INSERT INTO
Vendor(`VendorName`,`Address`,`City`,`State`,`Zip`,`ContactPerson`,`PhoneNumber`,`EmailAddress`) VALUES
('Jordan', '200 Maker LN','Chicago', 'IL', 10070, 'John, Berry',
'(101) 21 '),
('Nike', '212 Downing Street', 'New York', 'NY', 10198,'Alex, Bonita',
'(101) 21 ');
INSERT INTO
Product(`ProductName`,`BrandName`,`Price`,`VendorID`) VALUES
('Sneaker','Air Jordon XI',220,1),
('Sneaker','Nike Penny 1',150,2),
('Sneaker','Air Jordan IV',190,1),
('Sneaker','Air Jordan XXXIII',175,1),
('Sneaker','Nike LeBron 16',185,2);
INSERT INTO
Orders(`PaymentMethodID`,`CustomerID`,`OrderDate`)VALUES
(1,1,'2018-01-01'),
(2,2,'2018-02-02'),
(3,3,'2018-03-03'),
(4,4,'2018-04-04'),
(5,5,'2018-05-05'),
(6,6,'2018-11-11'),
(7,7,'2018-12-02');
INSERT INTO OrderItem (`OrderNumber`,`ProductID`,`Quantity`,`SellingPrice`)VALUES
(1,1,1,220),
(2,2,1,190),
(1,1,2,500),
(2,2,1,150),
(2,4,1,300),
(2,7,2,250);
INSERT INTO Shipment (ShipmentDate,Address,City,State,Zip,OrderNumber)VALUES ('2018-01-01','450 East 87th','New York','NY',10078, 1);
INSERT INTO Shipment (ShipmentDate,Address,City,State,Zip,OrderNumber)VALUES ('2018-01-01','450 East 87th','New York','NY',10078, 1);
INSERT INTO Shipment (ShipmentDate,Address,City,State,Zip,OrderNumber)VALUES ('2018-01-03','610 West 101st','New York','NY',10098,2);
INSERT INTO Shipment (ShipmentDate,Address,City,State,Zip,OrderNumber)VALUES ('2018-02-01','150 Lane','Richmond Hill','NY',10927, 3);
INSERT INTO Shipment (ShipmentDate,Address,City,State,Zip,OrderNumber)VALUES ('2018-03-03','103 Main Street','New Jersey','NJ',34567,4);
INSERT INTO Shipment (ShipmentDate,Address,City,State,Zip,OrderNumber)VALUES ('2018-01-12','1010 Bleeker St','New York','NY',10010,5);
##SELECT data from all tables to show the data in each table
SELECT * FROM Customer;
SELECT * FROM Employee;
SELECT * FROM Orders;
SELECT * FROM OrderItem;
SELECT * FROM Product;
SELECT * FROM PaymentMethod;
SELECT * FROM Shipment;
SELECT * FROM Vendor;
