-3

CREATE TABLE Customer_Master ( customer_ID INTEGER(30) NOT NULL AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, street_address VARCHAR(100) NOT NULL, apt INTEGER(9), city VARCHAR(30) NOT NULL, state VARCHAR(3) NOT NULL, zip VARCHAR(10) NOT NULL, home_phone INTEGER NOT NULL, mobile_phone INTEGER(10) NOT NULL, other_phone INTEGER(10) NOT NULL, PRIMARY KEY (customer_ID) );

CREATE TABLE Order_Master ( Donut_order_ID INTEGER(10) NOT NULL, Order_date DATETIME NOT NULL, Special_notes VARCHAR(255) NOT NULL, Customer_ID INTEGER(30) NOT NULL, PRIMARY KEY (Donut_order_ID), FOREIGN KEY (Customer_ID) REFERENCES Customer_Master(Customer_ID) );

CREATE TABLE Donut_Master ( Donut_ID VARCHAR(10) NOT NULL, Name VARCHAR(30) NOT NULL, Unit_price NUMERIC(10,2) NOT NULL, PRIMARY KEY (Donut_ID) );

CREATE TABLE Order_Details ( Donut_Order_ID INTEGER(10) NOT NULL, Donut_ID VARCHAR(10) NOT NULL, Quantity INTEGER(100) NOT NULL, FOREIGN KEY (Donut_Order_ID) REFERENCES Order_Master (Donut_Order_ID),
FOREIGN KEY (Donut_ID) REFERENCES Donut_Master (Donut_ID) );

CREATE VIEW Customer_Information AS SELECT CONCAT (first_name,'',last_name) AS Customer_Name, customer_ID, street_address, apt, city, state, zip, home_phone, mobile_phone, other_phone FROM Customer_Master;

CREATE INDEX Donut ON Donut_Master (Donut_ID);

INSERT INTO Customer_Master VALUES ('1','Bruce','Wayne','123_Gotham','12','Gotham_City', 'NY','12345','123456789','000000000','000000000');

INSERT INTO Order_Master VALUES ('1','2017-05-11','Please_include_plates_and_napkins','1');

INSERT INTO Donut_Master VALUES ('1','Plain','1.50'), ('2','Glazed','1.75'), ('3','Cinnamon','1.75'), ('4','Chocolate','1.75'), ('5','Sprinkle','1.75'), ('6','Gluten-Free','2.00'); INSERT INTO Order_Details VALUES ('1','1','1');

kb2425
  • 1
  • 2
  • Hi, and welcome to stackoverflow! You are receiving downvotes because your question does not quite fit the rules here, for example, you did not present a mvce (http://stackoverflow.com/help/mcve) of your problem but instead just pasted all your code. I'd recommend you take a look at http://stackoverflow.com/help/how-to-ask so your questions can also benefit others in the future. Good luck with your homework. – Florian Moser May 14 '17 at 18:30

1 Answers1

0

Well, the errors that you get should be clear enough to fix any mistakes. There are some typos, like an extra comma in the CREATE statement of table Donut_Master and the Customer_ID of Order_Details is varchar and in Customer_Master is int. I think your INSERT statement is incomplete too, see here: https://www.w3schools.com/sql/sql_insert.asp These are some great lessons actually that you can go through! Best of luck!

Stamatia Ch
  • 104
  • 1
  • 3
  • 17
  • Thank you for your help! – kb2425 May 14 '17 at 18:20
  • You're welcome. If this answer helped you, please accept it to help others too! – Stamatia Ch May 14 '17 at 18:22
  • I keep getting "Table 'db_9_9eecb7d.customer' doesn't exist"...I think it is a Fiddle issue at this point. I did looked everything over and corrected the eerrors you pointed out. I couldn't see anything wrong with my Insert statement though (Although I am not an expert :) ) If I understand correctly, I do not need to declare the columns as long as I have data for each one, in order (which I did)...let me know your thoughts... – kb2425 May 14 '17 at 19:43
  • I run your code on MySQL 5.7. The INSERT didn't worked for me until I added the column names. If you don't want to add them then I think you should also provide a number for the id field. – Stamatia Ch May 14 '17 at 20:50
  • Ok, I put in the column names....still receive the error.."Table 'db_9_9eecb7d.customer' doesn't exist". Banging head for answers – kb2425 May 14 '17 at 21:28
  • I think this is something to do with fiddle but I don't know how to help you in this case. Your statements are correct since I test them again on MySQL. It is very important to format your question and code so that you make it easier for other people to help you and you might get a solution :) – Stamatia Ch May 15 '17 at 06:12