Question

Create or edit the SQL statements required to accomplish the following tasks. All columns should have a column name, feel free to create appropriate aliases Use screen shots to ca evidence that each task is completed; include the SQL records before and after statements are executed, as appropriate. Include all work, even if you get errors. Paste your screen shots into a single PowerPoint file pture statements output from the statements, and views of the object explorer when necessary. Use SELECT statements to displey create a database and the following tab es Be sure to u se te a propna e lar guage Screen shot # 1 shot. display the SQL code and show the Object Explorer AFTER the database and tables are created . n a scrip three statements un ita sn le c ck of he r ec te bu on n he screen a. Create a database named LastName. Test4 using appropriate DDL statements, where LastName is your last name b. Create the tables and appropniate constraints based on the following ER diagram Use acpropriate data tyees and sizes (see Chanter B). The atabase is for a famity-owned bicycle store i. The AvailableOnline column should only accept the letters Y. or N (for Yes or No). ii. The Cost column should only have values greater than zero. L The SalePrice column should only have values greater than zero. iv. All columns in both tables are required v. The CID and PID columns should have the IDENTITY property Category Product CID PIO CID (fk) Ceet Screen shota) Add records to the Caregory teble and the Prosucta tabie. One screenshot should show the SQL code and the output of a SELECT statement showing the records in the taole (two screenshots. one for each table) Insert 3 rows in the Category table Bicycles 2. Accesoories 2. Inserd the iolowing records into the Product table 726 PM ^萨ヨ瀛8/192018

need help

Answer 1

a. CREATE DATABASE lastNameTest4;

NOTE: Please replace lastName with your last name.

b.

CREATE TABLE Category (

CID int NOT NULL,

Description VARCHAR(50) NOT NULL,

PRIMARY KEY(CID)

);

CREATE TABLE Product (

PID int NOT NULL,

Description VARCHAR(50) NOT NULL,

CID int NOT NULL,

Cost int NOT NULL,

SalesPrice int NOT NULL,

AvailableOnline char(1) NOT NULL,

PRIMARY KEY(PID),

CHECK (Cost > 0),

CHECK (SalesPrice > 0),

FOREIGN KEY (CID) REFERENCES Category(CID)

);

c.

INSERT INTO Category VALUES (1, 'Bicycles');

INSERT INTO Category VALUES (2, 'Components');

INSERT INTO Category VALUES (3, 'Accessories');

INSERT INTO Product VALUES (1, 'Cosco Bicyle', 1, 1299, 1099, 'Y');

INSERT INTO Category VALUES (2, 'Gears', 3, 299, 249, 'N');

INSERT INTO Category VALUES (3, 'Pedals', 2, 199, 179, 'Y');

SELECT * FROM Category;

SELECT * FROM Product;

NOTE: Since you have not provided the values to be inserted into Product table, I have gone ahead and inserted random values.