Create SQL queries to answer the following questions for the displayed tables: R
ID: 662605 • Letter: C
Question
Create SQL queries to answer the following questions for the displayed 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)
Select invoice_num, invoice_date from invoice,cust_name from customer where INVOICE.Cuts_id=CUSTOMER.Cuts_id
2)
Select INVOICE_NUM,CUST_ID,INVOICE_DATE from invoice Cust_name from Custimer where Invoice.cust_id=Customer.Cust_id and Invoice_Date="09-12-2007"
3)
Select Invoice_Num,Invoice_date from Invoice PROD_ID,LINE_NUM_ORDERED from LINE Where Invoice.Invoice_Num=Line.Invoice_num.
4)
Select Cust_id,Cust_namefrom Customer where Invoice.Cust_id=cutomer.Customer.Cust_id And Invoice.Invoice_Date IN ("09-12-2007)
The IN operator allows you to specify multiple values in a WHERE clause.
SQL IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
5)
This SQL tutorial explains how to use the SQL EXISTS condition with syntax and examples.
Description
The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the SQL EXISTS condition is:
Parameters or Arguments
subquery
The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.
Select Cust_id,Cust_name from Customer where EXISTS( Seelct InvoiceDate from Invice where Invoice.Custid=Customer.Cust_id And Invoice.date="09-27-2007")