Write the SQL to create the 4 tables Customers Sales Product

Write the SQL to create the 4 tables

Customers

Sales

Products

Fields:PID,name,price

Line Items

Fields:PID,SID,quantity

Once the table are created create INSERT INTO commands to add 10 records to each table.

Remember to end each SQL command with a semicolon and submit all of your SQL here.

Here is what I have:

CREATE TABLE customers(
cid varchar(15),
fname varchar(15),
PRIMARY KEY (cid));

INSERT INTO customers
VALUES(\'1\',\'abe\'),
(\'2\',\'bob\'),
(\'3\',\'cat\'),
(\'4\',\'dale\'),
(\'5\',\'earl\'),
(\'6\',\'fred\'),
(\'7\',\'greg\'),
(\'8\',\'hank\'),
(\'9\',\'iris\'),
(\'10\',\'jack\');

CREATE TABLE sales(
sid varchar(15),
cid varchar(15),
PRIMARY KEY (sid));

INSERT INTO courses
VALUES(\'0001\',\'1\'),
(\'0002\',\'2\'),
(\'0003\',\'3\'),
(\'0004\',\'4\'),
(\'0005\',\'5\'),
(\'0006\',\'6\'),
(\'0007\',\'7\'),
(\'0008\',\'8\'),
(\'0009\',\'9\'),
(\'0010\',\'10\');

CREATE TABLE products(
pid varchar(15),
pname varchar(15),
price varchar(15),
PRIMARY KEY (pid));

INSERT INTO students
VALUES(\'1\',\'Dog food\',\'9.99\'),
(\'2\',\'Cat Food\',\'9.99\'),
(\'3\',\'Candy\',\'1.99\'),
(\'4\',\'Milk\',\'0.99\'),
(\'5\',\'Tissues\',\'2.89\'),
(\'6\',\'Hooks\',\'1.29\'),
(\'7\',\'Pop\',\'1.49\'),
(\'8\',\'Rubber Bands\',\'0.89\'),
(\'9\',\'Beans\',\'3.49\'),
(\'10\',\'Toilet Paper\', \'4.99\');

CREATE TABLE line items(
pid varchar(15),
sid varchar(15),
quantity varchar(15),
PRIMARY KEY (cid,sid));

INSERT INTO line item
VALUES(\'1\',\'0001\',\'1\'),
(\'2\',\'0002\',\'2\'),
(\'3\',\'0003\',\'2\'),
(\'4\',\'0004\',\'2\'),
(\'5\',\'0005\',\'5\'),
(\'6\',\'0006\',\'7\'),
(\'7\',\'0007\',\'3\'),
(\'8\',\'0008\',\'3\'),
(\'9\',\'0009\',\'2\'),
(\'10\',\'00010\',\'1\');

Solution

The above statements are true.

CREATE TABLE customers(
cid varchar(15),
fname varchar(15),
PRIMARY KEY (cid));
INSERT INTO customers
VALUES(\'1\',\'abe\'),
(\'2\',\'bob\'),
(\'3\',\'cat\'),
(\'4\',\'dale\'),
(\'5\',\'earl\'),
(\'6\',\'fred\'),
(\'7\',\'greg\'),
(\'8\',\'hank\'),
(\'9\',\'iris\'),
(\'10\',\'jack\');

or we can give cid as number then written as below
CREATE TABLE customers(
cid int(15),
fname varchar(15),
PRIMARY KEY (cid));
INSERT INTO customers
VALUES(1,\'abe\'),
(2,\'bob\'),
(3,\'cat\'),
(4,\'dale\'),
(5,\'earl\'),
(6,\'fred\'),
(7,\'greg\'),
(8,\'hank\'),
(9,\'iris\'),
(10,\'jack\');

The second one can be written as below

CREATE TABLE sales(
sid varchar(15),
cid int(15),
PRIMARY KEY (sid) FOREIGN KEY (cid ) REFERENCES Customers(cid) );
INSERT INTO courses
VALUES(\'0001\',\'1\'),
(\'0002\',2),
(\'0003\',3),
(\'0004\',4),
(\'0005\',5),
(\'0006\',6),
(\'0007\',7),
(\'0008\',8),
(\'0009\',9),
(\'0010\',10);

CREATE TABLE products(
pid varchar(15),
pname varchar(15),
price varchar(15),
FOREIGN KEY (pid ) REFERENCES sales(pid) );
INSERT INTO students
VALUES(\'1\',\'Dog food\',\'9.99\'),
(\'2\',\'Cat Food\',\'9.99\'),
(\'3\',\'Candy\',\'1.99\'),
(\'4\',\'Milk\',\'0.99\'),
(\'5\',\'Tissues\',\'2.89\'),
(\'6\',\'Hooks\',\'1.29\'),
(\'7\',\'Pop\',\'1.49\'),
(\'8\',\'Rubber Bands\',\'0.89\'),
(\'9\',\'Beans\',\'3.49\'),
(\'10\',\'Toilet Paper\', \'4.99\');