I need the anwsers to these 5 queries *make sure to look at the hints!* /* Q1 .
ID: 3881198 • Letter: I
Question
I need the anwsers to these 5 queries
*make sure to look at the hints!*
/* Q1. This is Exercise #2 of end of Chapter 4 (p.156)
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.
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:
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 #.
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:
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 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.
Explanation / Answer
/* Q1. This is Exercise #2 of end of Chapter 4 (p.156)
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.
Hint: correct query results 11 rows
Answer:
SELECT V.VendorName, I.InvoiceNumber, I.InvoiceDate,I.InvoiceTotal-(I.PaymentTotal+I.CreditTotal) AS Balance
FROM Vendors V, Invoices I
WHERE V.VendorID=I.VendorID
ORDER BY VendorName ASCENDING
Note : For joining Vendors and Invoices we have specified V for Vendor and I for Invoice. In the where clause we need to add the condition to join these 2 tables. Plese replace the condition accordingly if vendor and invoice have any other common attribute instead of VendorID.
*/
/* 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:
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 #.
Hint: correct query results 118 rows
Answer :
SELECT v.Vendor AS VendorName,i.Date AS InvoiceDate,i.Number AS InvoiceNumber,li.# AS InvoiceSequence,li.LineItem AS InvoiceLineItemAmount
FROM Vendors v, Invoices i, InvoiceLineItems li
WHERE v.vendorID=i.vendorID
AND i.InvoiceNumber=li.InvoiceNumber
ORDER BY v.Vendor, i.Date, i.Number, li.#
Note : Here we are joining 3 tables. We nned to provide proper joining condition else the result will be wrong. Considering Vendor ID is the common attribute between Vendor and invoice table and invoice number is the common attribute between invoice and invoice line item table. If there is any common attribute among all these 3 table we can use that as well.
*/
/* Q3. This is Exercise #6 of end of Chapter 4 (p.157)
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 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
Answer :
SELECT v.VendorID, v.VendorName, v.VendorContactFName||" "||v.VendorContactLName AS Name
FROM Vendor v, Vendor w
WHERE v.VendorContactFName=w.VendorContactFName
ORDER BY Name
Note : In the joining condition we have compaired the first name of the vendor with a self join.
*/
/* 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
Answer :
SELECT G.AccountNo, G.AccountDescription
FROM GLAccounts G LEFT OUTER JOIN InvoiceLineItems I ON (AccountNo)
ORDER BY G.AccountNo
Note : The above query uses a left outer join which will include matching and not matching record from GLAcounts table. Use the common attribute accordingly for the joining condition.
OR
SELECT G.AccountNo, G.AccountDescription
FROM GLAccounts G, InvoiceLineItems I
WHERE G.AccountNo!=I.AccountNo
ORDER BY G.AccountNo ASC
*/
/* 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.
Answer :
SELECT VendorName,
CASE VendorState
WHEN 'CA' THEN 'CA'
ELSE 'OUTSIDE CA'
END AS VendorState
FROM Vendors
ORDER BY VendorName ASC
Note : Here we have use case statement to compare the VendorState value and change the out put accordingly. If it matched with CA then it will return 'CA' else 'OUTSIDE CA'.