WGU C170 Code to create and populate the Premiere Products Database

https://www.stuvia.com/
https://www.stuvia.com
https://www.stuvia.com/




Code to create and populate the Premiere Products Database
Create a database called Premiere Products and then create the following tables:

CREATE TABLE Rep
(

rep_num INT(5) NOT NULL,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(20) NOT NULL,
street VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL,
state CHAR(2) NOT NULL,
zip VARCHAR(5) NOT NULL,
commission FLOAT NOT NULL,
rate FLOAT NOT NULL,
PRIMARY KEY (rep_num)

);

CREATE TABLE Customer
(
customer_num INT(5) NOT NULL,
customer_name VARCHAR(30) NOT NULL,
street VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL,
state CHAR(2) NOT NULL,
zip VARCHAR(5) NOT NULL,
balance FLOAT NOT NULL,
credit_limit FLOAT NOT NULL,
rep_num INT(5) NOT NULL,
PRIMARY KEY (customer_num),
FOREIGN KEY (rep_num) REFERENCES Rep (rep_num)

);

CREATE TABLE Orders
(
order_num INT(10) NOT NULL,
order_date DATE NOT NULL,
customer_num INT (5) NOT NULL,
PRIMARY KEY (order_num),
FOREIGN KEY (customer_num) REFERENCES Customer (customer_num)

Nitro Software, Inc.
100
This study source was downloaded by 100000818837220 from PortableonDocument
CourseHero.com Lane
02-05-2022 15:21:27 GMT -06:00

Wonderland
https://www.coursehero.com/file/100084992/Code-to-create-and-populate-the-Premiere-Products-Databasedocx/

,https://www.stuvia.com/
https://www.stuvia.com
https://www.stuvia.com/




);

CREATE TABLE Part
(
part_num VARCHAR(10) NOT NULL,
description VARCHAR(20) NOT NULL,
on_hand INT (5) NOT NULL,
class VARCHAR(3) NOT NULL,
warehouse VARCHAR(3) NOT NULL,
price FLOAT NOT NULL,
PRIMARY KEY (part_num)

);

CREATE TABLE Order_line
(
order_num INT(10) NOT NULL,
part_num VARCHAR(10) NOT NULL,
num_ordered INT(3) NOT NULL,
quoted_price FLOAT NOT NULL,
PRIMARY KEY (order_num, part_num)

);

Enter this sample data into the tables:

Rep

INSERT into Rep VALUES(20,"Kaiser", "Valerie", "624 Randall", "Grove", "FL", "33321", 20542.50,
.05);

INSERT into Rep VALUES(35,"Hall", "Richard", "532 Jackson", "Sheldon", "FL", "33553", 39216, .
07);

INSERT into Rep VALUES(65,"Perez", "Juan", "1626 Taylor", "Fillmore", "FL", "33336", 23487.05, .
05);

Customer

INSERT into Customer VALUES(148,"Al's Appliance and Sport", "2837 Greenway", "Fillmore",
"FL", "33336", 6550.00, 7500.00, 20);

INSERT into Customer VALUES(282,"Brookings Direct", "3827 Devon", "Grove", "FL", "33321",
431.50, 10000.00, 35);

Nitro Software, Inc.
100
This study source was downloaded by 100000818837220 from PortableonDocument
CourseHero.com Lane
02-05-2022 15:21:27 GMT -06:00

Wonderland
https://www.coursehero.com/file/100084992/Code-to-create-and-populate-the-Premiere-Products-Databasedocx/