New create table Please help with the select statement part 1 Give me all of the

Question

New create table

Please help with the select statement part 1

Give me all of the suppliers who sell parts for brand x

Give me all employees who work with customer x

How many of part x do we currently have in inventory?

How many employees who work with customer x make over $47K/year?

Have any customers recently ordered part x for make/model y?

Who are the suppliers who supply parts for makes w & z?

List all parts we currently have in inventory for make/model x

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

Answers:)

1. select SuppId from Parts where Brand = 'x';
2. select EmpId from Employee where CusId = 'x';
3. select COUNT(Brand) from Parts where Brand = 'x';
4. select COUNT(EmpId from Employee where EmpSalary >= 47000;
5.
6.
7. select PartID, PartName from Parts where Brand = 'x';

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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