It wont let me insert my Orders values or Part order values

It won\'t let me insert my Orders values or Part order values. For Orders it keeps saying Customer.cID does not exist but as you can see I have the foreign key set for the orders table.

CREATE TABLE `jasocook_366f16`.`Employee` (
`eID` INT NOT NULL,
`fName` VARCHAR(45) NULL,
`lName` VARCHAR(45) NULL,
`zip` INT NULL,
PRIMARY KEY (`eID`));

CREATE TABLE `jasocook_366f16`.`Telephone` (
`tID` INT NOT NULL,
`eID` INT NULL,
`tNumber` INT NULL,
PRIMARY KEY (`tID`),
foreign key (eID) references Employee(eID));

CREATE TABLE `jasocook_366f16`.`Customer` (
`cID` INT NOT NULL,
`fName` VARCHAR(45) NULL,
`lName` VARCHAR(45) NULL,
`street` VARCHAR(50) NULL,
`city` VARCHAR(100) NULL,
`zip` INT NULL,
PRIMARY KEY (`cID`));

CREATE TABLE `jasocook_366f16`.`Part` (
`pID` INT NOT NULL,
`pName` VARCHAR(1000) NULL,
`price` INT NULL,
`quantity` INT NULL,
PRIMARY KEY (`pID`));

CREATE TABLE `jasocook_366f16`.`Orders` (
`oID` INT NOT NULL,
`rDate` DATE NULL,
`sDate` DATE NULL,
`cID` INT NULL,
`eID` INT NULL,
PRIMARY KEY (`oID`),
foreign key (cID) references Customer(cID),
foreign key (eID) references Employee(eID));

CREATE TABLE `jasocook_366f16`.`PartOrder` (
`pOID` INT NOT NULL,
`pID` INT NULL,
`oID` INT NULL,
`quantity` INT NULL,
PRIMARY KEY (`pOID`),
foreign key (pID) references Part(pID),
foreign key (oID) references Orders(oID));

insert into Employee values(1,\'Jason\',\'Cook\', 58104);
insert into Employee values(2,\'Alison\',\'Olson\',58104);

insert into Telephone values(1,1,1234567891);
insert into Telephone values(2,2,234567891);

insert into Customer values(1,\'Pam\',\'Wiseman\', \'123 West Palm St.\',\'Chicago\',23456);
insert into Customer values(2,\'John\',\'Wayne\', \'123 Bang Bang St.\',\'Austin\',55555);

insert into Part values(1,\'Part1\', 120, 2);
insert into Part values(2,\'Part2\', 500, 2);

insert into Orders values(1,\'09/28/2016\',\'09/13/2016\',Customer.cID(1),eID(1));
insert into Orders values(2,\'10/09/2016\',\'09/30/2016\',cID(2),eID(2));

insert into PartOrder values(1,Part.pID(1), Orders.oID(1),1);
insert into PartOrder values(2,Part.pID(2),Orders.oID(2),1);

Create and execute a query for order information joined with customer information.

Create and execute a query that lists employee information together with the number of orders they have processed.

Create and execute a query that deletes all PartOrder records for Orders for which the shipping date is in the past.

Add a column called processed to the PartOrder table.

Create a view of Part information together with the total quantity of such parts for which Orders were received in 2014 (use a different year, if there is no corresponding record in your database)

HELP!

Solution

You can try inserting values directly. As you already have desired foreign relationship, system will automatically cross check with the foreign tables. Also please Note that, we don\'t have to tell the column to be matched in the values part. There Customer.cID will be matched against the entries in the customer table which is unknown. If you want to specify the column name, try to insert like,

insert into Orders(oID,rDate,sDate,cID,eID) values(1,\'09/28/2016\',\'09/13/2016\',1,1);

Or

insert into Orders values(1,\'09/28/2016\',\'09/13/2016\',1,1);
insert into Orders values(2,\'10/09/2016\',\'09/30/2016\',2,2);

insert into PartOrder values(1,1,1,1);
insert into PartOrder values(2,2,2,1);

1.

SELECT Orders.oID, Customer.fname, Order.rDate
FROM Orders
INNER JOIN Customer
ON Orders.cID=Customer.cID;

Note: You can use as many fields you want, to be selected from orders and customer table. Here we used oID from orders, Customer\'s first name and rDate of Orders.

We used INNER JOIN because it Returns all rows when there is at least one match in BOTH tables. You can also try other JOINS.

2.

SELECT      e.fname, e.lname, e.zip,

OrdersCount = COUNT(o.oid)

FROM        Orders   o

Right JOIN Employee e ON o.eid = e.eid

GROUP BY    e.fname, e.lname, e.zip

NOTE: we used Right Join as there may be some entries of employees, who havn\'t order anything yet. So 0 order count should come in that place.

3.

NOTE: IN keyword used where more than one entries can come from the select.

4.

5.

CREATE VIEW [Parts Total_quantity] AS

SELECT p.pid, p.quantity

FROM part p JOIN PartOrder po ON p.pid = po.pid

WHERE po.oid IN (SELECT oid FROM orders WHERE YEAR(rDate) = 2016 ) ;

This is for reference purpose. You can append more columns to be a part of view