MSBA 230 UOP SQL Exercises with Explicit Join

Unless otherwise stated, use the explicit join syntax.Include snippets of your SQL code as well as your result sets.
1. Write a SELECT statement that returns all columns from the Vendors table inner-joined with
the Invoices table.
2. Write a SELECT statement that returns four columns:
VendorName
From the Vendors table
InvoiceNumber
From the Invoices table
InvoiceDate
From the Invoices table
Balance
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.
3. Write a SELECT statement that returns three columns:
VendorName
From the Vendors table
DefaultAccountNo
From the Vendors table
AccountDescription From the GLAccounts table
The result set should have one row for each vendor, with the account number and account
description for that vendor’s default account number. Sort the result set by AccountDescription,
then by VendorName
4. Generate the same result set described in exercise 2, but use the implicit join syntax.
5. 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 #.
6. 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: Use a self-join.
7. 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. Sort the final result set by AccountNo.
Hint: Use an outer join to the InvoiceLineItems table.
8. 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.
MSBA 230
Database Management Systems with SQL
and R
Shyla C. Solis, M.S.
(209) 986-2918
Email: ssolis1@pacific.edu
Wk 4: Join & Data Collection Techniques
Recall..
●
You learned how to create results from single tables. Even tried a complex
query using inner join.
Today
●
You will learn how to create result sets that contain data from two or more
tables. To do that you can either use a join or a union.
Define Join
●
●
A Join let’s you combine columns from two or more tables into a single result
set.
Let’s review how to use the most common type of join, an inner join.
How to code an inner join: Look at explicit syntax
Note: The INNER keyword is optional
and is seldom used.
How to code an inner join: Look at implicit syntax
Note: It is
recommended to use
explicit syntax versus
implicit
How to use correlation names or table aliases
●
●
●
You can assign temporary names to the tables called correlation names or
table aliases.
AS phrase
You must use that name in place of the original table name
When and how to use correlation names
Working with tables from different databases
Working with tables from different databases (Cont’d)
Compound join conditions
Using self-join
A self-join is a join where a table is joined with itself.
• When you code a self-join, you must use correlation
names for the tables, and you must qualify each column
name with the correlation name.
• Self-joins frequently include the DISTINCT keyword to
eliminate duplicate rows.
Inner joins that join more than two tables
Working with outer joins
Working with outer joins
Working with outer joins
• An outer join retrieves all rows that satisfy the join condition, plus unmatched rows in one or both
tables.
• When a row with unmatched columns is retrieved, any columns from the other table that are included
in the result set are given null value
• The OUTER keyword is optional and typically omitted.
Working with outer joins
Working with outer joins
For you
●
●
●
●
Outer joins that join more than two tables (pg 144-145)
Combining inner and outer joins (pg 146-147)
How to use cross joins (pg 148-149)
Working with unions (pg 150-151)
○
●
Unions that combine data from the same table (pg 152-153)
How to use except and intersect operators (pg 154-155)
HW posted on Canvas!
Due before next lecture!
If you do not have
the book, use
google!