Question

Using the Premier Products database answer the following questions

1. Using a union, display all customers that have rep number 65 or live in the town of

Sheldon. Display all customer information. Insert your snip of the query and resultset

together here:

2. Using a subquery, list the rep information for all customers who have the same credit

limit as customer number 725. To receive credit for this question, do not explicitly

query for rep number 35. Let MySQL do the work for you. Display the customer

number, customer name, credit limit, and rep number. Insert your snip of the query

and resultset together here.

3. Which customer(s) have ordered part number KT03? Display the customer number,

customer name, and part number. Insert your snip of the query and resultset together

here:

Premier Product database:

#--Project: Premier Products DB script file using create and insert

#-----CREATE AND OPEN THE DATABASE

#-- Creating Databases

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

If you have any doubts, please give me comment...

-- 1

SELECT *

FROM customer

WHERE rep_num = 65

UNION

SELECT *

FROM customer

WHERE city = 'Sheldon';

-- 2

SELECT customer_num, customer_name, credit_limit, rep_num

FROM customer

WHERE rep_num!=35 AND credit_limit = (

SELECT credit_limit

FROM customer

WHERE customer_num = 725

);

-- 3

SELECT customer_num, customer_name, part_num

FROM customer, orders, order_line

WHERE customer.customer_num = orders.customer_num AND orders.order_num = order_line.order_num AND part_num = 'KT03';