STEP 1: Using the BOOKS and PUBLISHER tables, create a query using the tradition

Question

STEP 1:

Using the BOOKS and PUBLISHER tables, create a query using the traditional join method that will return a list containing the book title, publisher contact person, and publisher phone number for all publishers whose publisher name starts with an %u2018R%u2019.

STEP 2:

Using the DUAL table, create a query that will return the day of the week, hour, minutes, and seconds of the current date setting on a computer. The day should be in all upper case.

STEP 3:

Using the BOOK_CUSTOMER and BOOK_ORDER tables, create a query using the NATURAL JOIN method that will return a list containing the customer first name and last name and the order number for all orders that have been shipped. Give the customer name column an alias of "Customer Name" and order the output by the customer number in the BOOK_ORDER table in ascending order.

STEP 4:

Using the BOOK_ORDER table, create a query that will return the order number, order date, shipping address, city, state, and zip code for all orders going to Atlanta or Austin. Format the order date to display as Month DD, YYYY and give the column an alias of "Date Ordered". Be sure the month begins with a capital letter.

STEP 5:

Using the BOOK_ORDER table, create a query using the correct function to return the order number, the date ordered, the date shipped, and a column representing the number of months between the two dates for all columns where a date shipped exists. Format the number returned from the function to display only two decimals, and give the column an alias of "Months Between".

NOTE: Be sure that all of the numbers in the fourth column are positive numbers.

STEP 6:

Using the correct tables in your schema, create a query using either join operation that will list the title of each book and author name(s). Give the title column an alias of "Book Title". Sort the results by title and then by author last name.

STEP 7:

Using the BOOKS table, create a query that will return the book title, cost, and retail price for all books with a title starting with the letter %u2018H%u2019. Use the correct conversion function to format the cost and retail columns to show dollars and cents with a dollar sign (e.g., a price of $25 would display $25.00 in the result set).

STEP 8:

Using the BOOK_ORDER, ORDER_ITEMS, and BOOKS tables, create a query using an OUTER JOIN operation that will list the book title, order date, and order number for all books in the BOOKS table. Order your output in descending order by ORDER_ITEMS.BOOKID. There are three books that have never been ordered which should show up at the top of your listing.

STEP 9:

Using the correct tables, create a query using the traditional join operation that will list the customer first and last name, book title, and order date (formatted as MM/DD/YYYY with an alias of %u201COrder Date%u201D) for all the customers who have purchased books published by 'PRINTING IS US'.

STEP 10:

Using the BOOKS and ORDER_ITEMS table, write a query using the correct Relational Set Operator that will show all of the Book IDs in the BOOKS table that have not been ordered.

STEP 11:

Using the BOOK_CUSTOMER, BOOK_ORDER, ORDER_ITEMS, and BOOKS tables, create a query using traditional join conditions based on comparisons between primary and foreign keys that will list the customer number, first and last name, and book title. Limit your listing to only those books in the %u2018FITNESS%u2019 category.

STEP 12:

Using the BOOKS, ORDER_ITEMS, and BOOK_ORDER tables, create a query that will list the title, retail, quantity, and order date for all books ordered after April 30, 2009.

STEP 13:

Using the correct tables, create a query using either join operation you wish that will list the order id, order date, quantity ordered, and retail price for every book that has been ordered. Format the date as MM/DD/YYYY with an alias of %u201COrder Date%u201D and format the retail price column using the correct function to show dollars and cents with a dollar sign ( $ ) and a column alias of %u201CRetail%u201D.

This is the end of lab #5

Explanation / Answer

1. select b.title,p.contact,p.phone from publisher p,books b where p.pubid=b.pubid and p.name like 'R%'; 2. select firstname||' '||lastname as "Customer Name",order# XXXX BOOK_CUSTOMER natural join BOOK_ORDER order by customer#; 3. select bc.customer#,bc.firstname,bc.lastname,b.title from BOOK_CUSTOMER bc,BOOK_ORDER bo,ORDERITEMS o,BOOKS b where b.category='FITNESS' and b.isbn=o.isbn and o.order#=bo.order# XXX bo.customer#=bc.customer#; 4. select title,count(bookauthor.isbn) from books,bookauthor where bookauthor.isbn=books.isbn group by (bookauthor.isbn,title) having count(bookauthor.isbn)>1; 6. select bc.firstname||' '||bc.lastname as "Customer Name", bc.city from book_customer bc,book_order bo,orderitems o,books b where bc.customer#=bo.customer# and bo.order#=o.order# XXX o.isbn=b.isbn and b.retail=(select max(retail) from books); 7. select o.order#,to_char(bo.orderdate,'mm/dd/yyyy') as "Order Date",o.quantity,'$'||b.retail as "Retail" from orderitems o,book_order bo,books b where o.order#=bo.order# XXX o.isbn=b.isbn; 8. select bc.firstname||' '||bc.lastname,b.title,to_char(bo.orderdate,'mm/dd/yyyy') as "Order Date" from book_customer bc,book_order bo,books b,orderitems o,publisher p where bc.customer#=bo.customer# XXX bo.order#=o.order# XXX o.isbn=b.isbn and b.pubid=p.pubid and p.name='PRINTING IS US'; 9. select b.title,bo.orderdate,bo.order# XXXX books b,book_order bo,orderitems o where b.isbn=o.isbn(+) and o.order#=bo.order#(+) order by b.isbn; 11. update books set retail=retail*1.05 where pubid=(select pubid from publisher where name='PRINTING IS US'); 12. select firstname||' '||lastname as "Customer Name" from book_customer where firstname!='JORGE' and lastname!='PEREZ' and referred=(select referred from book_customer where firstname='JORGE' and lastname='PEREZ');

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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