Please help with the select statement part 1 Give me all of the suppliers who se
ID: 3685578 • Letter: P
Question
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 PKStudent 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,
CONSTRAINT PKStudent PRIMARY KEY(MotorId));
CREATE TABLE Employee(
EmpId CHAR(11) NOT NULL,
EmpType VARCHAR(20) NOT NULL,
EmpSalary DECIMAL(10,2),
CONSTRAINT PKStudent PRIMARY KEY(EmpId));
CREATE TABLE Order(
OrderId CHAR(11) NOT NULL,
OrderQuantity INTEGER NOT NULL,
OrderDate VARCHAR(50) NOT NULL,
CONSTRAINT PKStudent PRIMARY KEY(OrderId));
CREATE TABLE Supplier(
SuppId CHAR(11) NOT NULL,
SuppName VARCHAR(50) NOT NULL,
SuppPhoneNumber VARCHAR(30) NOT NULL,
CONSTRAINT PKStudent PRIMARY KEY(SuppId));
CREATE TABLE Shipping(
CompId CHAR(11) NOT NULL,
CompRate DECIMAL(10,2),
CompShipMode VARCHAR(50) NOT NULL,
CONSTRAINT PKStudent PRIMARY KEY(CompId));
CREATE TABLE Parts(
PartId CHAR(11) NOT NULL,
PartName VARCHAR(50) NOT NULL,
PartCost DECIMAL(3,2),
CONSTRAINT PKStudent PRIMARY KEY(PartId),
CONSTRAINT FKMotorId FOREIGN KEY(MotorId)
REFERENCES Motorcycle (MotorId)
ON DELETE SET NULL
ON UPDATE CASCADE);
Explanation / Answer
i) SELECT SuppId,SuppName
FROM customer
WHERE MotorModel = x and part = x1;
ii) select all EmpId from customer where cusid=x
iii) SELECT COUNT(part) FROM customer
iv)
v) select max (date) from customer where part =x and model = y
vi) select all supid, supname from customer groupby parts =w,z
vii) select all supid from customer where model =y;