Question

Write a SQL script to add another table to your database. Include these fields in

your Product table:

Field Name	Description	Data Type	Sample Value
ProductID	Product ID	integer	5
ProductName	Product Name	varchar(50)	candle
Description	Product Description	varchar(255)	Bee’s wax candle
picUrl	Filename of the product’s picture	varchar(50)	candle.gif
Price	Product Price	decimal	10.99

          ProductID should be the Primary Key. It is an auto-increment field.

          The Price field stores prices to 7 significant digits and to 2 decimal places.

          Add at least ten records to the Product table using INSERT.

Answer 1

Below is the create Product table script

As given The Price field stores prices to 7 significant digits and to 2 decimal places, so price column in create statement will be Price DECIMAL(7, 2),

CREATE TABLE IF NOT EXISTS Product (
    ProductID int NOT NULL AUTO_INCREMENT,
    ProductName varchar(50) NOT NULL,
    Description varchar(255) NOT NULL,
    picUrl varchar(50) NOT NULL,
    Price DECIMAL(7, 2),
    PRIMARY KEY (ProductID)
);

Below are the insert statement for inserting 10 Reccords. As primary key is auto increment we don't need to include in insert statement. Primary key will be generated automatically.

Also tried insert statement with price - 22222.1777. As Price column is 7 significant digits and to 2 decimal places hence price will be saved as 22222.18

INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product1','This is sample product 1', 'product1.gif', 22.19);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product2','This is sample product 2', 'product2.gif', 145.77);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product3','This is sample product 3', 'product3.gif', 999.99);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product4','This is sample product 4', 'product4.gif', 1245.41);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product5','This is sample product 5', 'product5.gif', 11455.65);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product6','This is sample product 6', 'product6.gif', 21455.33);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product7','This is sample product 7', 'product7.gif', 99.66);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product8','This is sample product 8', 'product8.gif', 1.68);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product9','This is sample product 9', 'product9.gif', 42.190);
INSERT INTO Product (ProductName,Description,picUrl,Price) VALUES ('Product10','This is sample product 10', 'product10.gif', 22222.1777);

Below is the screenshot of inserted data into Product table

Select * from Product

Note - Above queries are for MySQL. And tested against MySQL database.