1.

--Write one SELECT statement in the given space beneath each problem

description

--to answer the query. Query 1 requires two SELECT statements.

--Each clause of SELECT, FROM, WHERE, and ORDER BY is expected to be coded

on its own

--line and each line is left justified.

--Only one SELECT statement is allowed to be the answer of each query. If

you include

Write a SELECT statement that returns four columns:

VendorName From the Vendors table

InvoiceNumber From the Invoices table

InvoiceDate From the Invoices table

Balance Derived 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.

2.

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

column aliases:

Vendor VendorName column

Date InvoiceDate column

Number InvoiceNumber column

# InvoiceSequence column

LineItem InvoiceLineItemAmount column

Assign the following correlation names to the tables:

v Vendors table

i Invoices table

li InvoiceLineItems table

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

3.

Write a SELECT statement that returns three columns:

VendorID From the Vendors table

VendorName From the Vendors table

Name A 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 asanother vendors contact. Sort the final result set by Name.

Hint 1: Use a self-join

4.

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

5.

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, theVendorState value should be

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