Write the SQL to create the 4 tables Customers Sales Products Fields:PID,name,pr

Question

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');

Explanation / Answer

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');

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.