Waiting for answer This question has not been answered yet. You can hire a professional tutor to get the answer.

QUESTION

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...

  1. 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.
  2. 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;

Show more
LEARN MORE EFFECTIVELY AND GET BETTER GRADES!
Ask a Question