Question

2.

Change the name of customer 842 to All Season Sport. Copy and paste your construct

here. Then, display the customer table. Insert your query and results here.

======================================================

Change the description of part BV06 to “Fitness Gym”. Insert your 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', '624 Randall', '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', '3827 Devon', 'Grove', 'FL', '33321', 431.50, 10000.00, '35');

INSERT INTO customer values ('356', 'Ferguson''s', '382 Wildwood', 'Northfield', 'FL', '33146', 5785.00, 7500.00,
'65');

INSERT INTO customer values ('408', 'The Everything Shop', '1828 Raven', 'Crystal', 'FL', '33503', 5285.25, 5000.00, '35');

INSERT INTO customer values ('462', 'Bargains Galore', '3829 Central', '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 Department Store', '372 Oxford', 'Sheldon', 'FL', '33553', 2106.00, 10000.00, '65');

INSERT INTO customer values ('687', 'Lee''s Sport and Appliance', '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);

Answer 1

update customer set customer_name='All Season Sport' where customer_num=842;

select * from customer;

+--------------+----------------------------+---------------+------------+-------+-------+----------+--------------+---------+

| customer_num | customer_name | street | city | state | zip | balance | credit_limit | rep_num |

+--------------+----------------------------+---------------+------------+-------+-------+----------+--------------+---------+

| 148 | Al's Appliance and Sport | 2837 Greenway | Fillmore | FL | 33336 | 6550.00 | 7500.00 | 20 |

| 282 | Brookings Direct | 3827 Devon | Grove | FL | 33321 | 431.50 | 10000.00 | 35 |

| 356 | Ferguson's | 382 Wildwood | Northfield | FL | 33146 | 5785.00 | 7500.00 | 65 |

| 408 | The Everything Shop | 1828 Raven | Crystal | FL | 33503 | 5285.25 | 5000.00 | 35 |

| 462 | Bargains Galore | 3829 Central | Grove | FL | 33321 | 3412.00 | 10000.00 | 65 |

| 524 | Kline's | 838 Ridgeland | Fillmore | FL | 33336 | 12762.00 | 15000.00 | 20 |

| 608 | Johnson's Department Store | 372 Oxford | Sheldon | FL | 33553 | 2106.00 | 10000.00 | 65 |

| 687 | Lee's Sport and Appliance | 282 Evergreen | Altonville | FL | 32543 | 2851.00 | 5000.00 | 35 |

| 725 | Deerfield's Four Seasons | 282 Columbia | Sheldon | FL | 33553 | 248.00 | 7500.00 | 35 |

| 842 | All Season Sport | 28 Lakeview | Grove | FL | 33321 | 8221.00 | 7500.00 | 20 |

+--------------+----------------------------+---------------+------------+-------+-------+----------+--------------+---------+

10 rows in set (0.01 sec)

select * from part;

+----------+----------------+---------+-------+-----------+---------+

| part_num | description | on_hand | class | warehouse | price |

+----------+----------------+---------+-------+-----------+---------+

| AT94 | Iron | 50 | HW | 3 | 24.95 |

| BV06 | Home Gym | 45 | SG | 2 | 794.95 |

| CD52 | Microwave Oven | 32 | AP | 1 | 165.00 |

| DL71 | Cordless Drill | 21 | HW | 3 | 129.95 |

| DR93 | Gas Range | 8 | AP | 2 | 495.00 |

| DW11 | Washer | 12 | AP | 3 | 399.99 |

| FD21 | Stand Mixer | 22 | HW | 3 | 159.95 |

| KL62 | Dryer | 12 | AP | 1 | 349.95 |

| KT03 | Dishwasher | 8 | AP | 3 | 595.00 |

| KV29 | Treadmill | 9 | SG | 2 | 1390.00 |

+----------+----------------+---------+-------+-----------+---------+

10 rows in set (0.00 sec)

mysql> update part set description = 'Fitness Gym' where part_num = 'BV06';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from part;

+----------+----------------+---------+-------+-----------+---------+

| part_num | description | on_hand | class | warehouse | price |

+----------+----------------+---------+-------+-----------+---------+

| AT94 | Iron | 50 | HW | 3 | 24.95 |

| BV06 | Fitness Gym | 45 | SG | 2 | 794.95 |

| CD52 | Microwave Oven | 32 | AP | 1 | 165.00 |

| DL71 | Cordless Drill | 21 | HW | 3 | 129.95 |

| DR93 | Gas Range | 8 | AP | 2 | 495.00 |

| DW11 | Washer | 12 | AP | 3 | 399.99 |

| FD21 | Stand Mixer | 22 | HW | 3 | 159.95 |

| KL62 | Dryer | 12 | AP | 1 | 349.95 |

| KT03 | Dishwasher | 8 | AP | 3 | 595.00 |

| KV29 | Treadmill | 9 | SG | 2 | 1390.00 |

+----------+----------------+---------+-------+-----------+---------+

10 rows in set (0.00 sec)