BC Shortest Poems Worksheet
Description
Your boss is away at a conference this week. You are pretty confident with running queries and sorting to provide more complex reports, but you have only done this with one table so far. He mentioned that the projects would begin to become more complex and time-intensive. Before he headed out for his conference, he left you some detailed instructions. (ERD – Figure 1.5 is attached).
Image credits: Woman Biting Pencil While Working. Canva.
Work Project Details
This work project focuses on adding join conditions involving queries that combine data from more than one table. You are a little nervous because Michael will not be back until next week, and this project will use the WHERE clause and the ANSI syntax. Although you have not done this before, you want to prove that you can figure out how to complete this work on your own. Michael’s instructions direct you to refer to the tables in the JustLee Books database. You are to generate and test two SQL queries for each of the following tasks:
- SQL statement needed to perform the stated task with the traditional approach
- SQL statement needed to perform the stated task with the JOIN keyword. Apply table aliases in all queries.
- What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift.
- Create a list that displays the title of each book and the name and phone number of the contact at the publisher’s office for reordering each book.
- Determine which orders haven’t yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed.
- Produce a list of all customers who live in the state of Florida and have ordered books about computers.
- Determine which books customer Jake Lucas has purchased. Perform the search using the customer name, not the customer number. If he has purchased multiple copies of the same book, unduplicate the results.
- 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 order date. If more than one book was ordered, sort the results by profit amount in descending order. Perform the search using the customer name, not the customer number.
- Which books were written by an author with the last name Adams? Perform the search using the author’s name.
Have a similar assignment? "Place an order for your assignment and have exceptional work written by our team of experts, guaranteeing you A results."