Hands-on Assignment To these assignments, refer to the tables in the JustLee Boo

Question

Hands-on Assignment To these assignments, refer to the tables in the JustLee Books database. Generate and test two SQL queries for each of the following tasks: a) the SQL statement needed to perform the stated task with the traditional approach, and b) the needed to perform the stated task with the JOIN keyword. Apply table aliases in all queries 1. create a list that displays the title of each book and the name and phone number of the contact at the publishers office for reordering each book. 2. 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. 3. Produce a list of all customers who live in the state of Florida and have ordered books about computers. 4. 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. 5. Determine the profit of each book sold to Jake Lucas, using the actual price the customer paid the book's regular retail price). Sort the results by order date. If more than one book was ordered, sort results by profit amount order. Perform the search using the customer name, not the customer number. 6. Which books were written by an author with the last name Adams? Perform the search using the author name. 7. What gift will a customer who orders the book shortest Poems receive? Use the actual book retail value to determine the gift.

Explanation / Answer

1 )

a) Traditional way

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

select B.TITLE as BOOK_TITLE, P.NAME AS PUBLISHER_NAME, P.PHONE as PUBLISHER_NAME from BOOKS B, PUBLISHER P where B.PUBID=P.PUBID;

b) Using Join

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

select B.TITLE as BOOK_TITLE, P.NAME AS PUBLISHER_NAME, P.PHONE as PUBLISHER_NAME from BOOKS B join PUBLISHER P using(PUBID);

2) In this case we need to join 3 Tables and print values from more than 1 tableso there is no traditional way other than JOINS.

select OI.ORDER# as ORDER_ID, C.FIRSTNAME as FIRSTNAME, C.LASTNAME as LASTNAME from ORDERITEMS OI inner join ORDER O on OI.ORDER#!=O.ORDER# inner join CUSTOMERS C on O.CUSTOMER#=C.CUSTOMER# order by O.ORDERDATE;

3)

a) This problem can be solved using subqueries

Select C.CUSTOMER# as CUSTOMER_ID, C.FIRSTNAME as FIRSTNAME,C.LASTNAME as LASTNAME from CUSTOMER C where C.STATE='FLORIDA' and C.CUSTOMER# in
(select O.CUSTOMER# from ORDERS O where O.ORDER# in (select OI.ORDER# from ORDERITEMS OI where OI.ISBN in (select B.ISBN from BOOKS B where B.CATEGORY='COMPUTER';);););

b) Using Joins

Select C.CUSTOMER# as CUSTOMER_ID, C.FIRSTNAME as FIRSTNAME,C.LASTNAME as LASTNAME from CUSTOMER C inner join ORDER O on C.CUSTOMER#=O.CUSTOMER# where C.STATE='FLORIDA' inner join ORDERITEMS OI on O.ORDER#=OI.ORDER# inner join BOOKS B on OI.ISBN=B.ISBN where B.CATEGORY='COMPUTER';

4)

a ) This problem can be solved using subqueries

Select B.TITLE as BOOK_TITLE from BOOKS B where ISBN IN
(Select OI.ISBN from ORDERITEMS O where OI.ORDER# in(Select O.ORDER# from ORDERS O where CUSTOMER# in(select C.CUSTOMER# from CUSTOMERS C where C.FIRSTNAME='JAKE'and C.LASTNAME='LUCAS';);););

b) Using Join

select B.TITLE as BOOK_TITLE from BOOKS B inner join ORDERITEMS OI on B.ISBN=OI.ISBN inner join ORDER O on OI.ORDER#=O.ORDER# inner join CUSTOMERS C on O.CUSTOMER#=C.CUSTOMER# where C.FIRSTNAME='JAKE'and C.LASTNAME='LUCAS';

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.