Please help with the select statement part 2 List all employees in increasing sa
ID: 3685579 • Letter: P
Question
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 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
1)List all employees in increasing salary order :-
SELECT name, salary FROM employee ORDER BY salary;
2) List all customers to whom we have sold part x from supplier y :-
SELECT CusId , CusFirstName
3) How much does it cost to ship part x?
SELECT PartCost
FROM Part X , Shipping S
where S.CompID = X.PartId ;
4)Show me the details of order number xyz:-
SELECT *
From Order
Where OrderId = 'xyz' ;
5) Give me the shipping / tracking info for order xyz
SELECT OrderId ,OrderDate
From Order O , Shipping S
Where OrderId = 'xyz' and S.CompId = O.OrderId ;