SQL /* Q1. This is Exercise #2 of end of Chapter 4 (p.156) Write a SELECT statement that returns four columns:

SQL

/* Q1. This is Exercise #2 of end of Chapter 4 (p.156)

Write a SELECT statement that returns four columns:

VendorNameFrom the Vendors table

InvoiceNumberFrom the Invoices table

InvoiceDateFrom the Invoices table

BalanceDerived from the Invoices table, 

i.e., InvoiceTotal minus the sum of PaymentTotal and CreditTotal

The result set should have one row for each invoice with a non-zero balance. Sort 

the result set by VendorName in ascending order.

Hint: correct query results 11 rows

*/

/* Q2. This is Exercise #5 of end of Chapter 4 (p.157)

Write a SELECT statement that returns five columns from three tables, all using 

column aliases:

VendorVendorName column

DateInvoiceDate column

NumberInvoiceNumber column

#InvoiceSequence column

LineItemInvoiceLineItemAmount column

Assign the following correlation names to the tables:

vVendors table 

iInvoices table 

liInvoiceLineItems table

Sort the final result set by Vendor, Date, Number, and #.

Hint: correct query results 118 rows

*/

/* Q3. This is Exercise #6 of end of Chapter 4 (p.157)

Write a SELECT statement that returns three columns: 

VendorIDFrom the Vendors table 

VendorNameFrom the Vendors table

NameA concatenation of VendorContactFName and VendorContactLName, 

with a space in between 

The result set should have one row for each vendor whose contact has the same first name as 

another vendor's contact. Sort the final result set by Name. 

Hint 1: Use a self-join

Hint 2: correct query results 6 rows

*/

/* Q4. This is Exercise #7 of end of Chapter 4 (p.157)

Write a SELECT statement that returns two columns from the GLAccounts table: AccountNo and 

AccountDescription. The result set should have one row for each account number that has never

been used in InvoiceLineItems. Sort the final result set by AccountNo.

Hint 1: Use an outer join to the InvoiceLineItems table

Hint 2: correct query results 54 rows

*/

/* Q5. This is Exercise #8 of end of Chapter 4 (p.157)

Use the UNION operator to generate a result set consisting of two columns from the Vendors table: 

VendorName and VendorState. If the vendor is in California, the VendorState value should be "CA"; 

otherwise, the VendorState value should be "Outside CA". Sort the final result set by VendorName.

*/