Question

Answer the following questions using mysql. Please show screenshots to make sure the queries work.

1. Use a sub-query and the IN operator to find the number and name of each customer that placed an order on October 23, 2007.
2. Repeat step 2, but this time use the EXISTS operator in your answer.
3. Using a sub-query, find the number and name of each customer that did not place an order on October 23, 2007.
4. Using a sub-query, find the rep number, last name, and first name of each sales rep that represents at least one customer with a credit limit of $10,000. List each sales rep only once in the results.
5. Use a sub-query to list the order number and order date for each order that was placed by Johnson’s Department Store and that contains an order line for a Gas Range. How many rows did your query return?
6. Rewrite step 5 above to list the order number and order date for each order that was placed by Johnson’s Department Store but that does not contain an order line for a Gas Range.
7. Use a sub-query to list the part number, part description, unit price, and item class for each part that has a unit price greater than the unit price of every part in item class AP. Use either the ALL or ANY operator in your query. HINT: Make sure you select the correct operator.
8. If you used ALL in step 7, repeat the exercise using ANY. If you used ANY, repeat the exercise using ALL, and then run the new command. What question does this command answer?

CREATE DATABASE PREMIERE;

USE PREMIERE;

CREATE TABLE REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME CHAR(15),
FIRST_NAME CHAR(15),
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2) );
CREATE TABLE CUSTOMER
(CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CUSTOMER_NAME CHAR(35) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
BALANCE DECIMAL(8,2),
CREDIT_LIMIT DECIMAL(8,2),
REP_NUM CHAR(2) );
CREATE TABLE ORDERS
(ORDER_NUM CHAR(5) PRIMARY KEY,
ORDER_DATE DATE,
CUSTOMER_NUM CHAR(3) );
CREATE TABLE PART
(PART_NUM CHAR(4) PRIMARY KEY,
DESCRIPTION CHAR(15),
ON_HAND DECIMAL(4,0),
CLASS CHAR(2),
WAREHOUSE CHAR(1),
PRICE DECIMAL(6,2) );
CREATE TABLE ORDER_LINE
(ORDER_NUM CHAR(5),
PART_NUM CHAR(4),
NUM_ORDERED DECIMAL(3,0),
QUOTED_PRICE DECIMAL(6,2),
PRIMARY KEY (ORDER_NUM, PART_NUM) );
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);
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');
INSERT INTO ORDERS
VALUES
('21608','2007-10-20','148');
INSERT INTO ORDERS
VALUES
('21610','2007-10-20','356');
INSERT INTO ORDERS
VALUES
('21613','2007-10-21','408');
INSERT INTO ORDERS
VALUES
('21614','2007-10-21','282');
INSERT INTO ORDERS
VALUES
('21617','2007-10-23','608');
INSERT INTO ORDERS
VALUES
('21619','2007-10-23','148');
INSERT INTO ORDERS
VALUES
('21623','2007-10-23','608');
INSERT INTO PART
VALUES
('AT94','Iron',50,'HW','3',24.95);
INSERT INTO PART
VALUES
('BV06','Home Gym',45,'SG','2',794.95);
INSERT INTO PART
VALUES
('CD52','Microwave Oven',32,'AP','1',165.00);
INSERT INTO PART
VALUES
('DL71','Cordless Drill',21,'HW','3',129.95);
INSERT INTO PART
VALUES
('DR93','Gas Range',8,'AP','2',495.00);
INSERT INTO PART
VALUES
('DW11','Washer',12,'AP','3',399.99);
INSERT INTO PART
VALUES
('FD21','Stand Mixer',22,'HW','3',159.95);
INSERT INTO PART
VALUES
('KL62','Dryer',12,'AP','1',349.95);
INSERT INTO PART
VALUES
('KT03','Dishwasher',8,'AP','3',595.00);
INSERT INTO PART
VALUES
('KV29','Treadmill',9,'SG','2',1390.00);
INSERT INTO ORDER_LINE
VALUES
('21608','AT94',11,21.95);
INSERT INTO ORDER_LINE
VALUES
('21610','DR93',1,495.00);
INSERT INTO ORDER_LINE
VALUES
('21610','DW11',1,399.99);
INSERT INTO ORDER_LINE
VALUES
('21613','KL62',4,329.95);
INSERT INTO ORDER_LINE
VALUES
('21614','KT03',2,595.00);
INSERT INTO ORDER_LINE
VALUES
('21617','BV06',2,794.95);
INSERT INTO ORDER_LINE
VALUES
('21617','CD52',4,150.00);
INSERT INTO ORDER_LINE
VALUES
('21619','DR93',1,495.00);
INSERT INTO ORDER_LINE
VALUES
('21623','KV29',2,1290.00);

Answer 1

CREATE TABLE REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME CHAR(15),
FIRST_NAME CHAR(15),
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2) );

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);

CREATE TABLE CUSTOMER
(CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CUSTOMER_NAME CHAR(35) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
BALANCE DECIMAL(8,2),
CREDIT_LIMIT DECIMAL(8,2),
REP_NUM CHAR(2) );

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');

CREATE TABLE ORDERS
(ORDER_NUM CHAR(5) PRIMARY KEY,
ORDER_DATE DATE,
CUSTOMER_NUM CHAR(3) );

INSERT INTO ORDERS VALUES('21608', TO_DATE('2007-10-20','YYYY-MM-DD'), '148');
INSERT INTO ORDERS VALUES('21610',TO_DATE('2007-10-20','YYYY-MM-DD'),'356');
INSERT INTO ORDERS VALUES('21613',TO_DATE('2007-10-21','YYYY-MM-DD'),'408');
INSERT INTO ORDERS VALUES('21614',TO_DATE('2007-10-21','YYYY-MM-DD'),'282');
INSERT INTO ORDERS VALUES('21617',TO_DATE('2007-10-23','YYYY-MM-DD'),'608');
INSERT INTO ORDERS VALUES('21619',TO_DATE('2007-10-23','YYYY-MM-DD'),'148');
INSERT INTO ORDERS VALUES('21623',TO_DATE('2007-10-23','YYYY-MM-DD'),'608');

CREATE TABLE PART
(PART_NUM CHAR(4) PRIMARY KEY,
DESCRIPTION CHAR(15),
ON_HAND DECIMAL(4,0),
CLASS CHAR(2),
WAREHOUSE CHAR(1),
PRICE DECIMAL(6,2) );

INSERT INTO PART VALUES('AT94','Iron',50,'HW','3',24.95);
INSERT INTO PART VALUES('BV06','Home Gym',45,'SG','2',794.95);
INSERT INTO PART VALUES('CD52','Microwave Oven',32,'AP','1',165.00);
INSERT INTO PART VALUES('DL71','Cordless Drill',21,'HW','3',129.95);
INSERT INTO PART VALUES('DR93','Gas Range',8,'AP','2',495.00);
INSERT INTO PART VALUES('DW11','Washer',12,'AP','3',399.99);
INSERT INTO PART VALUES('FD21','Stand Mixer',22,'HW','3',159.95);
INSERT INTO PART VALUES('KL62','Dryer',12,'AP','1',349.95);
INSERT INTO PART VALUES('KT03','Dishwasher',8,'AP','3',595.00);
INSERT INTO PART VALUES('KV29','Treadmill',9,'SG','2',1390.00);

CREATE TABLE ORDER_LINE
(ORDER_NUM CHAR(5),
PART_NUM CHAR(4),
NUM_ORDERED DECIMAL(3,0),
QUOTED_PRICE DECIMAL(6,2),
PRIMARY KEY (ORDER_NUM, PART_NUM) );

INSERT INTO ORDER_LINE VALUES('21608','AT94',11,21.95);
INSERT INTO ORDER_LINE VALUES('21610','DR93',1,495.00);
INSERT INTO ORDER_LINE VALUES('21610','DW11',1,399.99);
INSERT INTO ORDER_LINE VALUES('21613','KL62',4,329.95);
INSERT INTO ORDER_LINE VALUES('21614','KT03',2,595.00);
INSERT INTO ORDER_LINE VALUES('21617','BV06',2,794.95);
INSERT INTO ORDER_LINE VALUES('21617','CD52',4,150.00);
INSERT INTO ORDER_LINE VALUES('21619','DR93',1,495.00);
INSERT INTO ORDER_LINE VALUES('21623','KV29',2,1290.00);

SELECT * FROM REP;
SELECT * FROM CUSTOMER;
SELECT * FROM ORDERS;
SELECT * FROM PART;
SELECT * FROM ORDER_LINE;

SELECT CUSTOMER_NUM,CUSTOMER_NAME FROM CUSTOMER
WHERE CUSTOMER_NUM IN (SELECT CUSTOMER_NUM
FROM ORDERS
WHERE ORDER_DATE=TO_DATE('2007-10-23','YYYY-MM-DD'));

SELECT CUSTOMER_NUM,CUSTOMER_NAME FROM CUSTOMER
WHERE EXISTS (SELECT CUSTOMER_NUM
FROM ORDERS
WHERE ORDER_DATE=TO_DATE('2007-10-23','YYYY-MM-DD'));

SELECT CUSTOMER_NUM,CUSTOMER_NAME FROM CUSTOMER
WHERE CUSTOMER_NUM NOT IN (SELECT CUSTOMER_NUM
FROM ORDERS
WHERE ORDER_DATE=TO_DATE('2007-10-23','YYYY-MM-DD'));

SELECT REP_NUM,LAST_NAME,FIRST_NAME
FROM REP
WHERE REP_NUM IN(SELECT REP_NUM FROM CUSTOMER
WHERE CREDIT_LIMIT=10000);

SELECT ORDER_NUM, ORDER_DATE
FROM ORDERS
WHERE ORDER_NUM IN(SELECT O.ORDER_NUM
                   FROM ORDERS O,ORDER_LINE OL,CUSTOMER C,PART P
                   WHERE C.CUSTOMER_NAME='Johnson''s Department Store' AND
                   C.CUSTOMER_NUM=O.CUSTOMER_NUM AND
                   O.ORDER_NUM=OL.ORDER_NUM AND
                   OL.PART_NUM=P.PART_NUM AND
                   P.DESCRIPTION='Gas Range');
                  

SELECT ORDER_NUM, ORDER_DATE
FROM ORDERS
WHERE ORDER_NUM IN(SELECT O.ORDER_NUM
                   FROM ORDERS O,ORDER_LINE OL,CUSTOMER C,PART P
                   WHERE C.CUSTOMER_NAME='Johnson''s Department Store' AND
                   C.CUSTOMER_NUM=O.CUSTOMER_NUM AND
                   O.ORDER_NUM=OL.ORDER_NUM AND
                   OL.PART_NUM=P.PART_NUM AND
                   P.DESCRIPTION<>'Gas Range');
                  
                  
SELECT PART_NUM,DESCRIPTION,PRICE,CLASS
FROM PART
WHERE CLASS <> 'AP' AND PRICE > ANY (SELECT PRICE FROM PART WHERE CLASS='AP');

SELECT PART_NUM,DESCRIPTION,PRICE,CLASS
FROM PART
WHERE CLASS <> 'AP' AND PRICE > ALL (SELECT PRICE FROM PART WHERE CLASS='AP');

SQL> SELECTFROM PART PART DESCRIPTION ON HAND CL W PRICE AT94 Iron BUG6 Home Gum CD52 Microwave Oven DL71 Cordless Drill1 DR93 Gas Range DW11 Washer FD21 Stand Mixer KL62 Dryer KT03 Dishwasher KU29 Treadmill 50 HW3 45 SG2 32 AP1 21 HW 3 8 AP 2 12 AP3 22 HW 3 12 AP 1 8 AP 3 9 SG 2 24.95 794.95 165 129.95 495 399.99 159.95 349.95 595 1390 10 rows selected. SQL> SELECT FROM ORDER_LINE; ORDER PART NUM ORDERED QUOTED PRICE 21698 AT94 21610 DR93 21610 DW11 21613 KL62 21614 KT 03 21617 BU 06 21617 CD52 21619 DR93 21623 KU29 21.95 495 399.99 329.95 595 794.95 150 495 1290 1 2 2 2 rows selected

SQL> SQL> SQL> SELECT PART_NUM,DESCRIPTION, PRICE, CLASS 2 FROM PART 3 WHERE CLASS <> ·AP" AND PRICE ANY (SELECT PRICE FROM PART WHERE CLASS="AP"); PART DESCRIPTION PRICE CL KU29 Treadmi11 BUg6 Home Gum 1390 SG 794.95 SG SQL> SQL> SQL> SELECT PART_NUM,DESCRIPTION,PRICE, CLASS 2 FROM PART 3 WHERE CLASS く〉 ·AP" AND PRICE 〉 ALL (SELECT PRICE FROM PART WHERE CLASS, AP"); PART DESCRIPTION BU06 Home Gym PRICE CL 794.95 SG 1390 SG KU29 Treadmi11