Lab 5 of 7: Oracle Single Row and Group Functions
Using the BOOK_CUSTOMER table and the NVL2 function, create a query
that will return a list containing the customer number, first name, last
name, and the characters ‘NOT REFERRED’ if the customer was not referred
by another customer. Give the derived column an alias of STATUS. Do
not list any customers that were referred by another customer.
NOTE: Be sure that all of the numbers in the fourth
column are positive numbers.
Using the PUBLISHER table, create a query that will return the entire
publisher name with the first letter of each word in upper case.
Using the BOOKS table, create a query using the correct functions to
return the category name, total number books per category with a column
alias of “Category Total”, and the total cost of all the books per
category with an alias of “Cost”. Format the cost column using the
correct function to show dollars and cents with a dollar sign.
Using the ORDERITEMS table, create a query using the correct
functions to return the item#, the total quantity of items ordered with
an alias of “Total”, the average quantity per order with an alias of
“Average” (since averages are often in decimals, only show two decimal
places), the minimum quantity ordered with an alias of “Minimum”, and
the maximum quantity ordered with an alias of “Maximum”. Your output
results set should have 5 columns and 4 rows of data.
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 (concatenated together with a space between the
names) 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.
Using the BOOK_CUSTOMER, BOOK_ORDER, ORDERITEMS, 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 ‘FITNESS’ category.
Using the correct tables, create a query using the traditional join
operation that will list the customer first and last name concatenated
together, book title, and order date (formatted as MM/DD/YYYY with an
alias of “Order Date”) for all the customers who have purchased books
published by 'PRINTING IS US'.
Using the BOOK_ORDER, ORDERITEMS, 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 you output in
descending order by book ISBN. There are three books that have never
been ordered which should show up at the top of your listing.
