Create SQL queries to answer the following questions for the tables below: REP T
ID: 662608 • Letter: C
Question
Create SQL queries to answer the following questions for the tables below:
REP Table
REP_ID
REP_LNAME
REP_FNAME
REP_STREET
REP_CITY
REP_STATE
REP_ZIP
REP_COMM
REP_RATE
223
Roma
Theresa
735 First
Leonard
GA
25742
$21,756.50
0.06
237
Miller
Fred
643 High
Sheldon
GA
25753
$38,612.00
0.08
268
Rodriguez
Miguel
2737 Tyler
Springfield
GA
25758
$19,774.00
0.06
CUSTOMER Table
CUST_ID
CUST_NAME
CUST_STREET
CUST_CITY
CUST_STATE
CUST_ZIP
CUST_BALANCE
CUST_LIMIT
REP_ID
1159
Charles Appliance and Sport
3948 Brown
Leonard
GA
25742
$5,560.00
$7,500.00
223
1193
Streaming Direct
4938 Maple
Grove
GA
25721
$210.40
$10,000.00
237
1367
Hollister
REP_ID
REP_LNAME
REP_FNAME
REP_STREET
REP_CITY
REP_STATE
REP_ZIP
REP_COMM
REP_RATE
223
Roma
Theresa
735 First
Leonard
GA
25742
$21,756.50
0.06
237
Miller
Fred
643 High
Sheldon
GA
25753
$38,612.00
0.08
268
Rodriguez
Miguel
2737 Tyler
Springfield
GA
25758
$19,774.00
0.06
Explanation / Answer
1. Display the invoice number and the invoice date for each invoice that was placed by Charles Appliance and Sport and whose invoice does not contain an Electric Range. Use a set operation to perform this query.
To answer the first question we require the data of 4 tables:
1.Customer table (CUST_ID)
2.Invoice Table (INVOICE_ID)
3.Line Table (PROD_ID)
4.Product Table (PROD_DESC)
select * from CUSTOMER where CUST_ID = 1159
UNION
select INVOICE_NO, INVOICE_DATE from INVOICE where INVOICE _NO = 42419 and INVOICE_NO = 42449
UNION
select PROD_ID from LINE where PROD_ID=BT105 an PROD_ID=DS104
EXCEPT
select PROD_ID from LINE where PROD_DESC="Electric Range"
2. Display the product id, the product description, the product price, and the product type for each product whose product price is greater than the price of every part in product type SG. Be sure to correctly choose either the ALL or the ANY operator in your query.
select PROD_ID, PROD_DESC,PROD_PRICE,PROD_TYPE from PRODUCT where PROD_PRICE > ALL($283.95, $1390.00)
3. Display the same attributes as in the previous question. However, use the other of the two operators: ALL or ANY. This version of the SQL statement provides the answer to a question. What is that question? Add your answer as a comment to your list file.
select PROD_ID, PROD_DESC,PROD_PRICE,PROD_TYPE from PRODUCT where PROD_PRICE > ANY($283.95, $1390.00)
.4. Display the id, the description, the quantity, the invoice number, and the number of units ordered for each product. Make sure to include all products in your output. The order number and the number of ordered units must remain blank for any product that is not contained in an invoice. Order your display by product number.
select PROD_ID,PROD_DESC,PROD_QUANTITY from PRODUCT
UNION
select INVOICE_NUM,LINE_NUM_ORDERED from INVOICE