Share
Explore BrainMass

Outer and Inner Joins in Databases

Use the attached WEEK2_ACCESS2000.mdb database

I just need the SQL code, which can be cut and pasted into a word document.

1. Perform an inner join between the BOOKS table and the ORDER_LINES table. Show the titles from the BOOK table and quantity column from the ORDER_LINES table
-------------------------------------------------------------------------------------------------------
2. Perform an inner join between tables CUSTOMERS and ORDERS (on the customer_numb column), joining on the customer_numb column. Find the customer first and last name where the order_date is between 11/1/1999 and 1/1/2000.

Order by the order_date.

NOTE: Don't forget to surround the dates with # signs. For example ...WHERE orders.order_date Between #11/1/1999# And #1/1/2000#

HINT: Basically, fill in the blanks below to answer the question.

SELECT ____, _____ (if you'd like, you can select more than just the customer first and last names)
FROM ________ INNER JOIN ___________ ON ____. ____ = ____. _____

WHERE _______ between ______ and _______

ORDER BY _____;

-------------------------------------------------------------------------------------------------------
3. Perform an inner join on tables BOOKS and SOURCES. Find the source cities for publisher_name "Random House" using the LIKE command to restrict record selection. I'm purposely not telling you which field to do the JOIN on, but its obviously got to be the same field in each table (there's only one field that meets that criteria. You can click on TOOLS and then RELATIONSHIPS in Access to get a visual look at how the two tables are related (the line connecting the tables shows how the primary and foreign key relate between the two tables & the columns that the line connects are obviously the columns you'd want to do your JOIN on)

------------------------------------------------------------------------------------------------------

4. Perform a Left Outer Join using tables BOOKS and ORDER_LINES. Show all the ISBNs in the BOOKS table and any associated ISBNs in the ORDER_LINES table.

------------------------------------------------------------------------------------------------------

5. Perform a Left Outer Join using tables BOOKS and ORDER_LINES. Show the titles from the BOOKS table that don't have ISBNs that appear in the ORDER_LINES table.
------------------------------------------------------------------------------------------------------
6. Perform a UNION of two tables, customers and sources, selecting customer_state and source_state into one column.

Attachments

$2.19