I'm struggling to write the right Select statement in order to get the quantity of vehicles sold, monthly commission, and sales amount for each sales person. Salesman with less than 5 years of employment make 4% commission, over 5 get 7%.

 

I can't get any case statement that I've written to work.

 

 

 

 

CREATE TABLE Customer

(

CustomerID INT(10),

First_Name VARCHAR(50) NOT NULL,

Last_Name VARCHAR(50) NOT NULL,

Street_Address VARCHAR(50) NOT NULL,

Street_Address2 VARCHAR(50) NOT NULL,

City VARCHAR(50) NOT NULL,

State VARCHAR(50) NOT NULL,

Zip INT(10) NOT NULL,

Phone VARCHAR(15) NOT NULL,

Sales_PersonID INT(10) NOT NULL,

CONSTRAINT pk_customerid PRIMARY KEY (CustomerID)

);

 

ALTER TABLE Customer

ADD CONSTRAINT fk_sales_personid 

FOREIGN KEY (Sales_PersonID)

REFERENCES Sales_Person(Sales_PersonID);

 

CREATE TABLE Sales_Person

(

Sales_PersonID INT(10),

First_Name VARCHAR(50) NOT NULL,

Last_Name VARCHAR(50) NOT NULL,

Years_of_Employment INT(2) NOT NULL,

 

CONSTRAINT pk_sales_personid PRIMARY KEY (Sales_PersonID)

);

 

CREATE TABLE Orders

(

OrderID INT(10),

CustomerID INT(10),

VIN VARCHAR(10),

T_VIN VARCHAR(10),

Total DECIMAL(8,2),

Down_Payment DECIMAL(8,2),

Credit_Amount DECIMAL(8,2),

Taxes DECIMAL(8,2),

License_Fee DECIMAL(8,2),

Sale_Date DATE NOT NULL,

Option_Completion DATE NOT NULL,

 

CONSTRAINT pk_orderid PRIMARY KEY (OrderID)

);

 

ALTER TABLE Orders

ADD CONSTRAINT fk_customerid

FOREIGN KEY (CustomerID)

REFERENCES Customers(CustomerID);

 

ALTER TABLE Orders

ADD CONSTRAINT fk_vin

FOREIGN KEY (VIN)

REFERENCES Vehicle(VIN);

 

ALTER TABLE Orders

ADD CONSTRAINT fk_t_vin

FOREIGN KEY (T_VIN)

REFERENCES Trade_Vehicle(T_VIN);

 

CREATE TABLE Vehicle

(

VIN VARCHAR(10),

Vehicle_Make VARCHAR(50) NOT NULL,

Vehicle_Model VARCHAR(50) NOT NULL,

Vehicle_Year INT(4) NOT NULL,

Vehicle_Price DECIMAL(8,2) NOT NULL,

Vehicle_Location VARCHAR(50) NOT NULL,

 

CONSTRAINT pk_vin PRIMARY KEY (VIN)

);

 

CREATE TABLE Trade_Vehicle

(

T_VIN VARCHAR(10),

T_Make VARCHAR(50) NOT NULL,

T_Model VARCHAR(50) NOT NULL,

T_Year INT(4) NOT NULL,

T_Price DECIMAL(8,2) NOT NULL,

Vehicle_Location VARCHAR(50) NOT NULL,

 

CONSTRAINT pk_t_vin PRIMARY KEY (T_VIN)

);

 

CREATE TABLE Vehicle_Options

(

OptionID INT(10),

Option_Description VARCHAR(50) NOT NULL,

Option_Price DECIMAL(8,2) NOT NULL,

Option_OnHand INT(10) NOT NULL,

 

CONSTRAINT pk_optionid PRIMARY KEY (OptionID)

);

 

CREATE TABLE Option_Order

(

OrderID INT(10) NOT NULL,

OptionID INT(10) NOT NULL,

NumOrd INT(10) NOT NULL

);

 

ALTER TABLE Option_Order

ADD CONSTRAINT fk_orderid1

FOREIGN KEY (OrderID)

REFERENCES Orders(OrderID);

 

ALTER TABLE Option_Order

ADD CONSTRAINT fk_optionid1

FOREIGN KEY (OptionID)

REFERENCES Vehicle_Options(OptionID);

 

INSERT INTO Customers VALUES

(201, 'Addy', 'Alligator', '23 Apple Road', ' ', 'Dallas', 'TX', 98546, 5554580, 103),

(202, 'Bob', 'Bird', '546 Carrot Ave', 'Apt. 3', 'Oakland', 'CA', 25413, 5556540, 103),

(203, 'Cathy', 'Cat', '998 Lemon Blvd.', ' ', 'Pine', 'AL', 87439, 5551234, 102),

(204, 'Derrick', 'Dog', '56 Banana Drive', 'Apt. 6A', 'Chip', 'IL', 41589, 5556879, 104),

(205, 'Ellis', 'Elephant', '73 Melon Street', ' ', 'Austin', 'NV', 87429, 5558974, 101);

 

INSERT INTO Sales_Person VALUES

(101, 'Bill', 'Meyers', 2),

(102, 'Mike', 'Ipsy', 9),

(103, 'Ralph', 'Calos', 3),

(104, 'George', 'King', 15),

(105, 'Mary','Starling', 7),

(106, 'Kathren','Jones', 1);

 

 

INSERT INTO Vehicle VALUES

 

('sa56wh78', 'Honda', 'Macarooni', 2014, 23000.00, 'Online'),

('l0j7g6h7', 'Toyota', 'Hamburger', 1998, 11000.00, 'Lot'),

('45692923', 'Tesla', 'Fishcake', 2005, 55000.00, 'Lot'),

('9823vbhd', 'Honda', 'Meatloaf', 2010, 40000.00, 'Online'),

('985ewere', 'Accord', 'Pasta', 2009, 30500.00, 'Lot'),

('ccvcvc892', 'Mazda', 'Steak', 1999, 70000.00, 'Online'),

('sdnjdfd03', 'Ford', 'Pizza', 2015, 60000.00, 'Lot'),

('sdsd78879', 'Acura', 'Putu', 2001, 30000.00, 'Online');

 

INSERT INTO Trade_Vehicle VALUES

 

('345reerre', 'Honda', 'Hermit', 2007, 3000.00),

('5465656e6', 'Acura', 'Urchin', 2001, 7000.00),

('91djde345', 'Mazda', 'Meatball', 2017, 350.00),

('f45h67f89', 'Subarru', 'Pizza', 1996, 1200.00),

('gj56dfr34', 'Telsa', 'Lemon', 2017, 10.00)

('ka92nd67', 'Ford', 'Linguini', 2013, 6000.00),

('msty6725', 'Nissan', 'Omlette', 2002, 900.00);

 

 

INSERT INTO Vehicle_Options VALUES

(901, 'Queen Bed', 600.00, 500),

(902, 'Leather Furniture', 2000.00 ,100),

(903, 'GPS', 300.00, 800),

(904, 'Dish Washer', 250.00, 900),

(905, 'Swamp Cooler', 400.00, 875),

(906, 'Deluxe Stereo System', 1500.00, 725),

(907, 'Filtration Water System', 3000.00, 200),

(908, 'Overhead Cargo Holders', 500.00, 450),

(910, 'DVD Player', 70.00, 610),

(911, 'Bear Trap', 250.00, 345);

 

INSERT INTO Option_Order VALUES

(701, 901, 1),

(701, 902, 1),

(704, 906, 1),

(702, 907, 2),

(704, 907, 3),

(703, 908, 1),

(705, 903, 1),

(705, 911, 1);

 

INSERT INTO Orders VALUES

(701, 202, '45692923', '345reerre', 26000.00, 23.00, 6000.00, 678.00, 70.00, '2017-06-01', '2017-07-30'),

(702, 204, '9823vbhd', ' ', 37000.00, 3000.00, 0.00, 242.00, 30.00, '2017-06-15', '2017-07-15'),

(703, 205, '985ewere', '91djde345', 65000.00, 10000.00, 3500.00, 654.00, 50.00, '2017-06-23', '2017-07-21'),

(704, 201, 'sdnjdfd03', ' ', 48000.00, 0.00, 12000.00, 30.00, 65.00, '2017-07-15', '2017-08-01'),

(705, 203, 'sdsd78879', 'gj56dfr34', 57000.00, 20000.00, 0.00, 479.00, 60.00, '2017-07-22', '2017-08-16'),

(706, 206, 'dhd78s56', ' ', 33000.00, 1600.00, 0, 564.00, 75.00, '2017-06-30', '2017-07-15'),

(707, 207, 'pq720wnh', '83jd9k5s', 56000.00, 5000.00, 1700.00, 354.00, 55.00, '2017-08-06', '2017-09-17'),

(708, 208, 'nd67fe67', 'mi6hd9j7', 42000.00, 1000.00, 600.00, 124.00, 35.00, '2017-07-12', '2017-09-15');