You are to use SQL Developer to perform the requested operations based on tables. I need...

Transcribed Image Text:

You are to use SQL Developer to perform the requested operations based on tables. I need to see your SQL command followed immediately by the result. You are to use comments to record your name, the assignment number, and the question number for all questions. No exceptions will be accepted your list file is to show all the required details as stated. Number each question for example 1a, and 1b. Place these altogether, so all question 1 solution are together and all question 2 solutions are together etc. If the questions are not numbered for this one I will not mark it and a grade of 0 will be issued. There are too many solutions and I will not attempt to figure which is which. Do not wait till the last minute to start this one, this one will raise the stress level and can cause baldness and gray hair not to mention sleepless nights. You need to use the traditional Oracle method and the JOIN... ON for each query. Watch for the OUTER JOIN queries. Download the update script for this assignment for the Just Lee database. A script has been provided for you on Blackboard called JLDB_Build_9.sql. You need to run this script for this question. So use up to the four different methods, JOIN... ON, JOIN... USING, NATURAL JOIN and the traditional join method. Do them together so both methods follow each other for each question. I have indicated how many queries for each question. Question 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. (4 methods) Question 2 Determine which orders have not yet been shipped and the name of the customer who placed the order. You are to sort the results by the date on which the order was placed. (4 methods) Question 3 Produce a list of all customers who live in the state of Florida (FL) and have ordered a book about computers. Category is COMPUTERS. Display the customer's name, the book title, the order number and the category. (4 methods) Question 4 Determine which books Jake Lucas has purchased. Perform the search using the customer's last and first names and not he customer number or an individual part of the customer's name. If he has purchased multiple copies of the same book, unduplicate the results. (4 methods) Question 5 Determine the profit of each book sold to Jake Lucas, using the actual price the customer paid (not the book's regular retail price). 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 by using the customer's full name and not the customer number or part of his name. (4 methods) Question 6 Which book was written by an author with the last name of Adams? Perform the search using the author's last name. (4 methods) Question 7 What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift. (Use 2 methods JOIN... ON and Traditional) Question 8 Identify the author(s) of the books ordered by Becca Nelson. Perform the search using the customer's full name. (4 methods) Question 9 Display a list of all books in the BOOKS table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides. (Outer join needed here, two methods only for the Outer Join) Question 10 An EMPLOYEES table was added to the Just Lee database to track employee information. Display a list of each employee's name, job title, and manger's name. Use column aliases to clearly identify employee and manger name values. Include all employees in the list and sort by manager name. If you are missing the EMPLOYEES table you did not download the new script. (4 methods) CUSTOMERS Customer# Lastname Firstname Email Address City State Zip Referred Region ORDERS Order# Customer# Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost ORDERITEMS Order# Item# ISBN Quantity Paideach BOOKS ISBN Title Pubdate PublD Cost Retail Discount Category BOOKAUTHOR AUTHOR Author D Lname Fname PROMOTION Gift Minretail Maxretail ISBN Author D PUBLISHER PublD Name Contact Phone You are to use SQL Developer to perform the requested operations based on tables. I need to see your SQL command followed immediately by the result. You are to use comments to record your name, the assignment number, and the question number for all questions. No exceptions will be accepted your list file is to show all the required details as stated. Number each question for example 1a, and 1b. Place these altogether, so all question 1 solution are together and all question 2 solutions are together etc. If the questions are not numbered for this one I will not mark it and a grade of 0 will be issued. There are too many solutions and I will not attempt to figure which is which. Do not wait till the last minute to start this one, this one will raise the stress level and can cause baldness and gray hair not to mention sleepless nights. You need to use the traditional Oracle method and the JOIN... ON for each query. Watch for the OUTER JOIN queries. Download the update script for this assignment for the Just Lee database. A script has been provided for you on Blackboard called JLDB_Build_9.sql. You need to run this script for this question. So use up to the four different methods, JOIN... ON, JOIN... USING, NATURAL JOIN and the traditional join method. Do them together so both methods follow each other for each question. I have indicated how many queries for each question. Question 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. (4 methods) Question 2 Determine which orders have not yet been shipped and the name of the customer who placed the order. You are to sort the results by the date on which the order was placed. (4 methods) Question 3 Produce a list of all customers who live in the state of Florida (FL) and have ordered a book about computers. Category is COMPUTERS. Display the customer's name, the book title, the order number and the category. (4 methods) Question 4 Determine which books Jake Lucas has purchased. Perform the search using the customer's last and first names and not he customer number or an individual part of the customer's name. If he has purchased multiple copies of the same book, unduplicate the results. (4 methods) Question 5 Determine the profit of each book sold to Jake Lucas, using the actual price the customer paid (not the book's regular retail price). 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 by using the customer's full name and not the customer number or part of his name. (4 methods) Question 6 Which book was written by an author with the last name of Adams? Perform the search using the author's last name. (4 methods) Question 7 What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift. (Use 2 methods JOIN... ON and Traditional) Question 8 Identify the author(s) of the books ordered by Becca Nelson. Perform the search using the customer's full name. (4 methods) Question 9 Display a list of all books in the BOOKS table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides. (Outer join needed here, two methods only for the Outer Join) Question 10 An EMPLOYEES table was added to the Just Lee database to track employee information. Display a list of each employee's name, job title, and manger's name. Use column aliases to clearly identify employee and manger name values. Include all employees in the list and sort by manager name. If you are missing the EMPLOYEES table you did not download the new script. (4 methods) CUSTOMERS Customer# Lastname Firstname Email Address City State Zip Referred Region ORDERS Order# Customer# Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost ORDERITEMS Order# Item# ISBN Quantity Paideach BOOKS ISBN Title Pubdate PublD Cost Retail Discount Category BOOKAUTHOR AUTHOR Author D Lname Fname PROMOTION Gift Minretail Maxretail ISBN Author D PUBLISHER PublD Name Contact Phone