Create SQL queries to answer the following questions based on the given tables:
ID: 662606 • Letter: C
Question
Create SQL queries to answer the following questions based on the given tables:
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 id and the name of each customer that did not place an order on September 12th, 2007. (Be careful in performing this query.)
Select REP_ID,REP_LName+' '+REPFName from REP rep inner join CUSTOMER c on rep.rep_id = c.rep_id where c.cust_id not in (select cust_id from invoice where invoice_date='09/12/2007')
2. Display the invoice number, the invoice date, the product id, the product description, and the product type for each line in each order.
select iv.INVOICE_NUM,iv.INVOICE_DATE,p.PROD_ID,p.PROD_DESC,p.PROD_TYPE from LINE l inner join invoice iv on l.invoice_num on iv.invoice_num inner join product p on l.prod_id= p.prod_id
3. Display the same data as in question 7, but order the display by product type. Within each type, order the display by invoice number.
select iv.INVOICE_NUM,iv.INVOICE_DATE,p.PROD_ID,p.PROD_DESC,p.PROD_TYPE from LINE l inner join invoice iv on l.invoice_num on iv.invoice_num inner join product p on l.prod_id= p.prod_id order by p.PROD_TYPE ,iv.INVOICE_NUM
4. Display the sales representative