New tables Please help with the select statement part 2 List all employees in in
ID: 3690041 • Letter: N
Question
New tables
Please help with the select statement part 2
List all employees in increasing salary order
List all customers to whom we have sold part x from supplier y
How much does it cost to ship part x?
Show me the details of order number xyz
Give me the shipping / tracking info for order xyz
here is the create tables:
CREATE TABLE Customer(
CusId CHAR(11) NOT NULL,
CusFirstName VARCHAR(50) NOT NULL,
CusLastName VARCHAR(50) NOT NULL,
CusCity VARCHAR(50) NOT NULL,
CusState CHAR(2) NOT NULL,
CusZip CHAR(10) NOT NULL,
CusPhoneNumber VARCHAR(30) NOT NULL,
CONSTRAINT PKCustomer PRIMARY KEY(CusId));
CREATE TABLE Motorcycle(
MotorId CHAR(11) NOT NULL,
MotorMake VARCHAR(50) NOT NULL,
MotorModel VARCHAR(20) NOT NULL,
MotorStatus VARCHAR(10) NOT NULL,
MotorYear INTEGER NOT NULL,
OrderId CHAR(11) NOT NULL,
SuppId CHAR(11) NOT NULL,
CONSTRAINT PKMotorcycle PRIMARY KEY(MotorId),
CONSTRAINT FKOrderId FOREIGN KEY(OrderId)
REFERENCES Orders (OrderId)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT FKSuppId FOREIGN KEY(SuppId)
REFERENCES Supplier (SuppId)
ON DELETE SET NULL
ON UPDATE CASCADE);
CREATE TABLE Employee(
EmpId CHAR(11) NOT NULL,
EmpType VARCHAR(20) NOT NULL,
EmpSalary DECIMAL(10,2),
CONSTRAINT PKEmployee PRIMARY KEY(EmpId));
CREATE TABLE Orders(
OrderId CHAR(11) NOT NULL,
OrderDate VARCHAR(50) NOT NULL,
CusId CHAR(11) NOT NULL,
EmpId CHAR(11) NOT NULL,
CompId CHAR(11) NOT NULL,
CONSTRAINT PKOrders PRIMARY KEY(OrderId),
CONSTRAINT FKCusId FOREIGN KEY(CusId)
REFERENCES Customer (CusId)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT FKEmpId FOREIGN KEY(EmpId)
REFERENCES Employee (EmpId)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT FKShippId FOREIGN KEY(CompId)
REFERENCES Shipping (CompId)
ON DELETE SET NULL
ON UPDATE CASCADE);
CREATE TABLE Supplier(
SuppId CHAR(11) NOT NULL,
SuppName VARCHAR(50) NOT NULL,
SuppPhoneNumber VARCHAR(30) NOT NULL,
CONSTRAINT PKSupplier PRIMARY KEY(SuppId));
CREATE TABLE Shipping(
CompId CHAR(11) NOT NULL,
CompRate DECIMAL(10,2),
CompShipMode VARCHAR(50) NOT NULL,
CONSTRAINT PKShipping PRIMARY KEY(CompId));
CREATE TABLE OrderPart(
OrderId CHAR(11) NOT NULL,
PartId CHAR(11) NOT NULL,
OrderQuantity INTEGER NOT NULL,
CONSTRAINT PKOrderPart PRIMARY KEY(OrderId, PartId),
CONSTRAINT FKPartId FOREIGN KEY(PartId)
REFERENCES Parts (PartId)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT FKOrderId FOREIGN KEY(OrderId)
REFERENCES Orders (OrderId)
ON DELETE SET NULL
ON UPDATE CASCADE);
CREATE TABLE Parts(
PartId CHAR(11) NOT NULL,
PartName VARCHAR(50) NOT NULL,
PartCost DECIMAL(10,2),
Brand VARCHAR(20),
SuppId CHAR(11) NOT NULL,
CONSTRAINT PKParts PRIMARY KEY(PartId),
CONSTRAINT FKMotorId FOREIGN KEY(Brand)
REFERENCES Motorcycle (MotorMake)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT FKSuppId FOREIGN KEY(SuppId)
REFERENCES Supplier (SuppId)
ON DELETE SET NULL
ON UPDATE CASCADE);
Explanation / Answer
1.
SELECT EmpId
FROM Employee
ORDER BY EmpSalary
2.
SELECT Customer.CusFirstName
FROM Customer
INNER JOIN Orders ON (Orders.CusId = Customer.CusId)
INNER JOIN OrderPart ON (OrderPart.OrderId = Orders.OrderId)
INNER JOIN Parts ON (Parts.PartId = OrderPart.PartId)
INNER JOIN Supplier ON (Supplier.SuppId = Parts.SuppId)
WHERE Parts.PartName = 'x' AND Supplier.SuppName = 'y'
3.
SELECT Shipping.CompRate
FROM Parts
INNER JOIN OrderPart ON (OrderPart.PartId = Parts.PartId)
INNER JOIN Orders ON (Orders.OrderId = OrderPart.OrderId)
INNER JOIN Shipping ON (Shipping.CompId = Orders.CompId)
WHERE Parts.PartName = 'x'
4.
What all details do you want. Please specify, there are many columns