2.
Change the name of customer 842 to All Season Sport. Copy andpaste your construct
here. Then, display the customer table. Insert your query andresults here.
======================================================
Change the description of part BV06 to “Fitness Gymâ€. Insertyour query and results
here.
======================================================
Database to use:
CREATE DATABASE premier_products;
#-- Creating Tables
USE premier_products;
#-- Rep Table
CREATE TABLE rep
(
rep_num char(2) primary key,
last_name varchar(15),
first_name varchar(15),
street varchar(15),
city varchar(15),
state char(2),
zip char(5),
commission decimal(7,2),
rate decimal(3,2)
);
#-- Customer table
CREATE TABLE customer
(
customer_num char(3) primary key,
customer_name varchar(30),
street varchar(15),
city varchar(15),
state char(15),
zip char(5),
balance decimal(7,2),
credit_limit decimal(7,2),
rep_num char(2)
);
#-- Orders table
CREATE TABLE orders
(
order_num char(5) primary key,
order_date timestamp,
customer_num char(3)
);
#-- Order_line table
CREATE TABLE order_line
(
order_num char(5),
part_num char(4),
num_ordered int(5),
quoted_price decimal(7,2),
PRIMARY KEY (order_num, part_num)
);
#-- Part table
CREATE TABLE part
(
part_num char(4) primary key,
description varchar(20),
on_hand int(4),
class char(2),
warehouse char(2),
price decimal (7,2)
);
#-- Populate rep table
INSERT INTO rep values ('20', 'Kaiser', 'Valerie', '624Randall', 'Grove', 'FL', '33321', 20542.50, 0.05);
INSERT INTO rep values ('35', 'Hull', 'Richard', '532 Jackson','Sheldon', 'FL', '33553', 39216.00, 0.07);
INSERT INTO rep values ('65', 'Perez', 'Juan', '1626 Taylor','Fillmore', 'FL', '33336', 23487.00, 0.05);
#-- Populate customer table
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', '3827Devon', 'Grove', 'FL', '33321', 431.50, 10000.00, '35');
INSERT INTO customer values ('356', 'Ferguson''s', '382Wildwood', 'Northfield', 'FL', '33146', 5785.00, 7500.00,
'65');
INSERT INTO customer values ('408', 'The Everything Shop', '1828Raven', 'Crystal', 'FL', '33503', 5285.25, 5000.00, '35');
INSERT INTO customer values ('462', 'Bargains Galore', '3829Central', 'Grove', 'FL', '33321', 3412.00, 10000.00, '65');
INSERT INTO customer values ('524', 'Kline''s', '838 Ridgeland','Fillmore', 'FL', '33336', 12762.00, 15000.00, '20');
INSERT INTO customer values ('608', 'Johnson''s DepartmentStore', '372 Oxford', 'Sheldon', 'FL', '33553', 2106.00, 10000.00,'65');
INSERT INTO customer values ('687', 'Lee''s Sport andAppliance', '282 Evergreen', 'Altonville', 'FL', '32543', 2851.00,5000.00, '35');
INSERT INTO customer values ('725', 'Deerfield''s Four Seasons','282 Columbia', 'Sheldon', 'FL', '33553', 248.00, 7500.00,'35');
INSERT INTO customer values ('842', 'All Season', '28 Lakeview','Grove', 'FL', '33321', 8221.00, 7500.00, '20');
#-- Populate orders table
INSERT INTO orders value ('21608', '2015-10-20', '148');
INSERT INTO orders value ('21610', '2015-10-20', '356');
INSERT INTO orders value ('21613', '2015-10-21', '408');
INSERT INTO orders value ('21614', '2015-10-21', '282');
INSERT INTO orders value ('21617', '2015-10-23', '608');
INSERT INTO orders value ('21619', '2015-10-23', '148');
INSERT INTO orders value ('21623', '2015-10-23', '608');
#-- Populate order_line table
INSERT INTO order_line value ('21608', 'AT94', 11, 21.95);
INSERT INTO order_line value ('21610', 'DR93', 1, 495.00);
INSERT INTO order_line value ('21610', 'DW11', 1, 399.99);
INSERT INTO order_line value ('21613', 'KL62', 4, 329.95);
INSERT INTO order_line value ('21614', 'KT03', 2, 595.00);
INSERT INTO order_line value ('21617', 'BV06', 2, 794.95);
INSERT INTO order_line value ('21617', 'CD52', 4, 150.00);
INSERT INTO order_line value ('21619', 'DR93', 1, 495.00);
INSERT INTO order_line value ('21623', 'KV29', 2, 1290.00);
#--Populate part table
INSERT INTO part value ('AT94', 'Iron', 50, 'HW', '3',24.95);
INSERT INTO part value ('BV06', 'Home Gym', 45, 'SG', '2',794.95);
INSERT INTO part value ('CD52', 'Microwave Oven', 32, 'AP', '1',165.00);
INSERT INTO part value ('DL71', 'Cordless Drill', 21, 'HW', '3',129.95);
INSERT INTO part value ('DR93', 'Gas Range', 8, 'AP', '2',495.00);
INSERT INTO part value ('DW11', 'Washer', 12, 'AP', '3',399.99);
INSERT INTO part value ('FD21', 'Stand Mixer', 22, 'HW', '3',159.95);
INSERT INTO part value ('KL62', 'Dryer', 12, 'AP', '1',349.95);
INSERT INTO part value ('KT03', 'Dishwasher', 8, 'AP', '3',595.00);
INSERT INTO part value ('KV29', 'Treadmill', 9, 'SG', '2',1390.00);
