Question

Problems

Please write queries based on the following requirements using labdata.sql or premieresetupmysql.sql. For each question, you are required to submit 1) SQL query code; 2) a screen shot of your query result. You should copy and paste your SQL query code to the word document instead of taking a screenshot of your code. Missing either part for each question will result in 0 for this question.

1. List unique item classes stored in my database.
2. List the warehouse number and the number of different parts stored in each warehouse, only include those warehouse with more than 2 different parts.
3. List the warehouse number and the maximum number of units on hand for parts stored in each of the warehouse. Rename the new column Max_UOH.
4. List the part number, total dollar amount for each part stored, and the warehouse number for the part. Rename the calculated column TOTAL_AMOUNT. TOTAL_AMOUNT=Units_ON_HAND*UNIT_PRICE
5. List the class and total number of units on hand for each class. Rank your results in descending order on the total number of units on hand.
6. List stored information for all the orders placed between August 3^rd and August 6^th of 2013. (not including August 3^rd 2013 and August 6^th 2013).

Code needed: labdata

create table sales_rep (
slsrep_number number(5) constraint pk_sales_rep primary key,
srlast       varchar2(8),
srfirst       varchar2(7),
street       varchar2(13),
city       varchar2(7),
state       varchar2(2),
zip_code   number(5),
total_commission   number(7,2),
commission_rate       number(3,2));

insert into sales_rep values(3, 'Jones', 'Mary', '123 Main', 'Grant', 'MI', 42919, 2150, .05);
insert into sales_rep values(4, 'Morton', 'Tom', '300 College', 'Flint', 'MI', 49227, 2075, .06);
insert into sales_rep values(6, 'Smith', 'William', '102 Raymond', 'Ada', 'MI', 49441, 4912.5, .07);
insert into sales_rep values(12, 'Diaz', 'Miguel', '419 Harper', 'Lansing', 'MI', 49224, 2150, .05);

create table customer
   (c_number number(3) not null,
   clast varchar2(8),
   cfirst varchar2(7),
   street varchar2(13),
   city varchar2(7),
   state varchar2(2),
   zip_code number(5),
   balance number(7,2),
   credit_limit number(4),
   slsrep_number number(3),
constraint customer_pk primary key (c_number),
constraint fk1_customer foreign key (slsrep_number) references sales_rep(slsrep_number));

insert into customer values
(124, 'Adams', 'Sally', '418 Oak', 'Lansing', 'MI', 49224, 818.75, 1000, 3);

insert into customer values
(256, 'Samuels', 'Ann', '215 Pete', 'Grant', 'MI', 49219, 21.5, 1500, 6);

insert into customer values
(311, 'Charles', 'Don', '48 College', 'Ira', 'MI', 49034, 825.75, 1000, 12);

insert into customer values
(315, 'Daniels', 'Tom', '914 Cherry', 'Kent', 'MI', 48391, 770.75, 750, 6);

insert into customer values
(405, 'Williams', 'Al', '519 Watson', 'Grant', 'MI', 49219, 402.75, 1500, 12);

insert into customer values
(412, 'Adams', 'Sally', '16 Elm', 'Lansing', 'MI', 49224, 1817.75, 2000, 3);

insert into customer (c_number, clast, cfirst, street, city, state, zip_code, balance, credit_limit) values
(522, 'Nelson', 'Mary', '108 Pine', 'Ada', 'MI', 49441, 98.75, 1500);

insert into customer values
(567, 'Dinh', 'Tran', '808 Ridge', 'Harper', 'MI', 48421, 402.40, 750, 6);

insert into customer values
(587, 'Galvez', 'Mara', '512 Pine', 'Ada', 'MI', 49441, 114.60, 1000, 6);

insert into customer values
(622, 'Martin', 'Dan', '419 Chip', 'Grant', 'MI', 49219, 1045.75, 1000, 3);

create table part (
part_number varchar2(5) constraint pk_part primary key,
part_description varchar2(12),
units_on_hand number,
item_class char(2),
warehouse_number number,
unit_price number(7,2));

insert into part
values ('AX12', 'Iron', 104, 'HW', 3, 24.95);

insert into part
values ('AZ52', 'Dartboard', 20, 'SG', 2, 12.95);

insert into part
values ('BA74', 'Basketball', 40, 'SG', 1, 29.95);

insert into part
values ('BH22', 'Cornpopper', 95, 'HW', 3, 24.95);

insert into part
values ('BT04', 'Gas Grill', 11, 'AP', 2, 149.99);

insert into part
values ('BZ66', 'Washer', 52, 'AP', 3, 399.99);

insert into part
values ('CA14', 'Griddle', 78, 'HW', 3, 39.99);

insert into part
values ('CB03', 'Bike', 44, 'SG', 1, 299.99);

insert into part
values ('CX11', 'Blender', 112, 'HW', 3, 22.95);

insert into part
values ('CZ81', 'Treadmill', 68, 'SG', 2, 349.95);

create table orders (
order_number number(5) constraint pk_orders primary key,
order_date date,
c_number number(3) constraint fk1_orders references customer(c_number));

insert into orders values (12489, '02-AUG-2013', 124);

insert into orders values (12491, '02-AUG-2013', 311);

insert into orders values (12494, '04-AUG-2013', 315);

insert into orders values (12495, '04-AUG-2013', 256);

insert into orders values (12498, '05-AUG-2013', 522);

insert into orders values (12500, '05-AUG-2013', 124);

insert into orders values (12504, '05-AUG-2013', 522);
create table order_line (
order_number number(5),
part_number varchar2(5),
number_ordered number,
quoted_price number(6,2),
constraint pk_order_line primary key (order_number, part_number),
constraint fk1_order_line foreign key (order_number) references orders(order_number),
constraint fk2_order_line foreign key (part_number) references part(part_number));

insert into order_line values (12489, 'AX12', 11, 21.95);
insert into order_line values (12491, 'BT04', 1, 149.99);
insert into order_line values (12491, 'BZ66', 1, 399.99);
insert into order_line values (12494, 'CB03', 4, 279.99);
insert into order_line values (12495, 'CX11', 2, 22.95);
insert into order_line values (12498, 'AZ52', 2, 12.95);
insert into order_line values (12498, 'BA74', 4, 24.95);
insert into order_line values (12500, 'BT04', 3, 149.99);
insert into order_line values (12504, 'CZ81', 2, 325.99);

.)premieresetup

create table sales_rep (
slsrep_number int(5) NOT NULL,
srlast varchar(8),
srfirst   varchar(7),
street   varchar(13),
city varchar(7),
state   varchar(2),
zip_code int(5),
total_commission decimal(7,2),
commission_rate   decimal(3,2),
constraint pk_sales_rep primary key (slsrep_number));

insert into sales_rep values(3, 'Jones', 'Mary', '123 Main', 'Grant', 'MI', 42919, 2150, .05);
insert into sales_rep values(4, 'Morton', 'Tom', '300 College', 'Flint', 'MI', 49227, 2075, .06);
insert into sales_rep values(6, 'Smith', 'William', '102 Raymond', 'Ada', 'MI', 49441, 4912.5, .07);
insert into sales_rep values(12, 'Diaz', 'Miguel', '419 Harper', 'Lansing', 'MI', 49224, 2150, .05);

create table customer
   (c_number int(3) not null,
   clast varchar(8),
   cfirst varchar(7),
   street varchar(13),
   city varchar(7),
   state varchar(2),
   zip_code int(5),
   balance decimal(7,2),
   credit_limit int(4),
   slsrep_number int(3),
constraint customer_pk primary key (c_number),
constraint fk1_customer foreign key (slsrep_number) references sales_rep(slsrep_number));

insert into customer values
(124, 'Adams', 'Sally', '418 Oak', 'Lansing', 'MI', 49224, 818.75, 1000, 3);

insert into customer values
(256, 'Samuels', 'Ann', '215 Pete', 'Grant', 'MI', 49219, 21.5, 1500, 6);

insert into customer values
(311, 'Charles', 'Don', '48 College', 'Ira', 'MI', 49034, 825.75, 1000, 12);

insert into customer values
(315, 'Daniels', 'Tom', '914 Cherry', 'Kent', 'MI', 48391, 770.75, 750, 6);

insert into customer values
(405, 'Williams', 'Al', '519 Watson', 'Grant', 'MI', 49219, 402.75, 1500, 12);

insert into customer values
(412, 'Adams', 'Sally', '16 Elm', 'Lansing', 'MI', 49224, 1817.75, 2000, 3);

insert into customer (c_number, clast, cfirst, street, city, state, zip_code, balance, credit_limit) values
(522, 'Nelson', 'Mary', '108 Pine', 'Ada', 'MI', 49441, 98.75, 1500);

insert into customer values
(567, 'Dinh', 'Tran', '808 Ridge', 'Harper', 'MI', 48421, 402.40, 750, 6);

insert into customer values
(587, 'Galvez', 'Mara', '512 Pine', 'Ada', 'MI', 49441, 114.60, 1000, 6);

insert into customer values
(622, 'Martin', 'Dan', '419 Chip', 'Grant', 'MI', 49219, 1045.75, 1000, 3);

create table part (
part_number varchar(5),
part_description varchar(12),
units_on_hand int,
item_class char(2),
warehouse_number int,
unit_price decimal(7,2),
constraint pk_part primary key(part_number));

insert into part
values ('AX12', 'Iron', 104, 'HW', 3, 24.95);

insert into part
values ('AZ52', 'Dartboard', 20, 'SG', 2, 12.95);

insert into part
values ('BA74', 'Basketball', 40, 'SG', 1, 29.95);

insert into part
values ('BH22', 'Cornpopper', 95, 'HW', 3, 24.95);

insert into part
values ('BT04', 'Gas Grill', 11, 'AP', 2, 149.99);

insert into part
values ('BZ66', 'Washer', 52, 'AP', 3, 399.99);

insert into part
values ('CA14', 'Griddle', 78, 'HW', 3, 39.99);

insert into part
values ('CB03', 'Bike', 44, 'SG', 1, 299.99);

insert into part
values ('CX11', 'Blender', 112, 'HW', 3, 22.95);

insert into part
values ('CZ81', 'Treadmill', 68, 'SG', 2, 349.95);

create table orders (
order_number int(5),
order_date date ,
c_number int(3),
constraint pk_orders primary key(order_number),
constraint fk1_orders foreign key ( c_number) references customer(c_number));

insert into orders values (12489, str_to_date('02-AUG-2013', '%d-%M-%Y'), 124);

insert into orders values (12491, str_to_date('02-AUG-2013', '%d-%M-%Y'), 311);

insert into orders values (12494, str_to_date('04-AUG-2013', '%d-%M-%Y'), 315);

insert into orders values (12495,str_to_date('04-AUG-2013', '%d-%M-%Y'), 256);

insert into orders values (12498, str_to_date('05-AUG-2013', '%d-%M-%Y'), 522);

insert into orders values (12500, str_to_date('05-AUG-2013', '%d-%M-%Y'), 124);

insert into orders values (12504, str_to_date('05-AUG-2013', '%d-%M-%Y'), 522);
create table order_line (
order_number int(5),
part_number varchar(5),
number_ordered int,
quoted_price decimal(6,2),
constraint pk_order_line primary key (order_number, part_number),
constraint fk1_order_line foreign key (order_number) references orders(order_number),
constraint fk2_order_line foreign key (part_number) references part(part_number));

insert into order_line values (12489, 'AX12', 11, 21.95);
insert into order_line values (12491, 'BT04', 1, 149.99);
insert into order_line values (12491, 'BZ66', 1, 399.99);
insert into order_line values (12494, 'CB03', 4, 279.99);
insert into order_line values (12495, 'CX11', 2, 22.95);
insert into order_line values (12498, 'AZ52', 2, 12.95);
insert into order_line values (12498, 'BA74', 4, 24.95);
insert into order_line values (12500, 'BT04', 3, 149.99);
insert into order_line values (12504, 'CZ81', 2, 325.99);

Answer 1

1. The distinct keyword will be used to fetched the unique names of item class:

Query:

Select distinct(item_class) from part;

Output:

2. The group by clause will be used to group the data by warehouse_number and a havingclause will be used to return the only records which has cout(part_number) more than 2. An aggregate function count(part_number) will returnthe count of parts per warehouse.

Query:

select warehouse_number, count(part_number) as total_parts from part

group by warehouse_number having count(part_number) >=2;

Output:

3. The group by clause will be used to group the data by warehouse_number and an aggregate function MAX(units_on_hand) will be used to return the maximum number among the numbers of part held by particular warehouse.

Query:

select warehouse_number, max(units_on_hand) as Max_UOH from part

group by warehouse_number;

Output:

4. The data will be fetched from part table. TOTAL_AMOUNT is calculated as units_on_hand * unit_price

Query:

Select PART_NUMBER, (units_on_hand* unit_price) as TOTAL_AMOUNT from part;

OUTPUT:

5. The data will be fetched from part table with a SUM(units_on_hand) aggregate function on units_on_hand to calculate the sun based on a group by clause on item_class which will calculate data per item_class.

Query:

Select item_class, sum(units_on_hand) as total_number_of_units from part group by item_Class order by total_number_of_units desc;

Output:

6. The orders table will be used to fetch the data and a where condition is applied on order_date with AND operator to add date more than 03-Aug-2013 and less than 06-Aug-2013. Please note the between is not used as it returns the conditioned values also, but the result excluding the 3^rd and 6rd august is needed.

Query:

select * from orders_vk where order_date > ('03-AUG-2013') and order_date < ('06-AUG-2013')

Output:

**Kindly comment, if you need any input. Thanks!!!