IT358 Unit 5 INNER JOIN OUTER JOIN subquery

IT358 Unit 5 INNER JOIN OUTER JOIN subquery

Outcomes addressed in this activity:
Create a query with an INNER JOIN
Create a query with an OUTER JOIN
Create a query that contains a subquery

Project requirements:
In this project, you will write joins and subqueries:
1. Complete the Hands-on Assignments #1–5 for Chapter 9.
2. Complete the Hands-on Assignments #1–5 for Chapter 12.

Oracle 11G SQL Chapter 9 Hands-On assignment Solution
Generate and test two SQL queries for each of the following tasks: (a) the SQL statement needed to perform the stated task using the traditional approach, and (b) the SQL statement needed to perform the stated task using the JOIN keyword.
1. Create a list that displays the title of each book and the name and phone number of the person at the publisher’s office whom you would need to contact to reorder each book.
2. Determine which orders have not yet shipped and the name of the customer who placed each order. Sort the results by the date on which the order was placed.
3. List the customer number and names of all individuals who have purchased books in the fitness category.
4. Determine which books Jake Lucas has purchased. Perform the search using the customer name, not the customer number.
5. Determine the profit of each book sold to Jake Lucas. Sort the results by the date of the order. If more than one book was ordered, sort the results by the profit amount in descending order. Perform the search using the customer name, not the customer number.

Oracle 11G SQL Chapter 12 Hands-On assignment Solution
Use a subquery to accomplish each of the tasks. First, execute the query you will use as the subquery to verify the results.
1. Determine which books have a retail price that is less than the average retail price of all books sold by JustLee Books.
2. Determine which books cost less than the average cost of other books in the same category.
3. Determine which orders were shipped to the same state as order 1014.
4. Determine which orders had a higher total amount due than order 1008.
5. Determine which author or authors wrote the book(s) most frequently purchased by customers of JustLee Books.