MySQL Forums
Forum List  »  Newbie

mySQL help with SELECT statement
Posted by: Niral Patel
Date: June 11, 2012 04:10PM

I need help with this three questions from my 10 question assignment...

8. List the sales rep number and name for every sales rep that represents at least one customer with a credit limit of $10,000. Do not allow duplicates of the sales rep. Put into numeric order by rep number. Insert your screen shot here.


9. List the sum of the balances of all customers for each sales rep. Order and group the results by sales rep number. Insert your screen shot here.


10. List by warehouse the count of the number of items that have an on_hand amount less than 20 items. Only display those warehouses that have more than 1 item that is less than 20 on hand. Insert your screen shot here.

script file: in notepad
#--Project: Premier Products DB script file using create and insert
#--by Debbie Reid
#-----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', '2008-10-20', '148');
INSERT INTO orders value ('21610', '2008-10-20', '356');
INSERT INTO orders value ('21613', '2008-10-21', '408');
INSERT INTO orders value ('21614', '2008-10-21', '282');
INSERT INTO orders value ('21617', '2008-10-23', '608');
INSERT INTO orders value ('21619', '2008-10-23', '148');
INSERT INTO orders value ('21623', '2008-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);

Options: ReplyQuote


Subject
Written By
Posted
mySQL help with SELECT statement
June 11, 2012 04:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.