Chapter 12 Oracle

IF finding the avg price for GROUP of books, such as categories, use....

GROUP BY function so find avg profit of each section, and use HAVING clause to further reduce info

Main/outer query

first query that appears in teh SELECT command

<ALL

less than the lowest value returned by the subquery

When writing a query

list what information you need and steps you would need to get that information

Correalted subquery

references a column in the outer query, and executes the subquery once for eevry row in the outer query

Subquery part

retrieves values that the main query's search condition must match.

Multiple-column subquery

returns to the outer query more than one column of results

Multiple-row subquery

returns to the outer query more than one row of results

Single- row subquery

returns to the outer query one row of results that consists of one column can only return ONE result operators include =, >, <, >=, <=, <>

Nested Queries used to

select results based on the result of a query

IN operator WHERE retail IN(SELECT MAX(retail) FROM books GROUP BY category))

the records the outer query processes must match one of the values the subquery returns "OR" statement

>ALL

More than the highest value return by the subquery

Nested Subqueries

-Maximum of 255 subqueries if nested in the WHERE clause -No limit if nested in the FROM clause -Innermost subquery is resolved first, then the next level, etc.

4 rules of subqueries

1. A subquery must be complete query in itself - it requires SELECT and FROM clauses 2. A subquery, except one in the FROM clause, can't have an ORDER BY clause (on the outer query's last clause). 3. Must be enclosed in parentheses to separate it from the outer/main query 4. Place on right side of comparison operator

Query4: List all book categories returning a higher average profit than the 'Literature' category.

1. calculate the average profit for all 'Literature' books. 2. calculate the average profit for each category. 3. compare the average profit for each category with the average profit for the 'Literature' category. SELECT category, AVG(retail-cost) "Average Profit" FROM books GROUP BY category HAVING AVG(retail-cost) > (SELECT avg(retail-cost) FROM books WHERE category = 'LITERATURE');

=ANY

Equal to any value returned by the subquery (same as IN)

Uncorrelated subquery

Executes the subquery first and passes the value to the outer query.

Queries consist of two parts:

Main/outer query & subquery

Multiple-Row Subqueries require

Require use of IN, ANY, ALL, or EXISTS operators

Query10: List all books that have a higher-than-average selling price compared with other books in the same category

SELECT b.title, b.retial, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category AND b.retail > a.cataverage;

Example single-row subquery Query1: List all computer books with a retail price higher than the book "Database Implementation"

SELECT category, title, cost FROM books WHERE cost > (SELECT cost FROM books WHERE title = 'DATABASE IMPLEMENTATION') AND category = 'COMPUTER

Query12: List all customers (customer#) who referred customer 1005 has referred any other customers to JustLee Books.

SELECT customer# FROM customers WHERE NVL(referred, 0) = (SELECT NVL(referred,0) FROM customers WHERE customer# = 1005);

Query13: List the name of the customer who has ordered the most books (4 books in this case) on one order (not including multiple quantities of the same book). Innermost is resolved first (A), then the second level (B), then the outer query (C)

SELECT customer#, lastname, firstname FROM customers JOIN orders USING (customer#) WHERE order# IN (SELECT order# FROM orderitems GROUP BY order# HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM orderitems GROUP BY order#));

Query9: Check whether any customer's recently placed order has a total amount due greater than the total amount due for every order placed recently by customers in Florida.

SELECT order#, SUM(quantity*paideach) FROM orderitems HAVING SUM(quantity*paideach) >ALL (SELECT SUM( quantity*paideach) FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) WHERE state = 'FL' GROUP BY order#) GROUP BY order#;

Query8: List all books with a retail price less than the most expensive book in the Cooking category.

SELECT tite, retail FROM books WHERE retails <ANY(SELECT retail FROM books WHERE category = 'COOKING');

Query2: List title of the most expensive book sold by JustLee Books

SELECT title, retail FROM books WHERE retail = (SELECT MAX(retail) FROM books);

Query5: Compare the price of each book in inventory against average price of all books in inventory.

SELECT title, retail, (SELECT TO_CHAR(AVG(retail), '999.99') FROM books) "Overall Average" FROM books;

List book titles, retail value and category that match the highest retail value for any book category (the right solution)

SELECT title, retail, category FROM books WHERE retail IN (SELECT category, MAX(retail) FROM books GROUP BY category) GROUP BY category;

Query7: List book titles, retail value and category that match the highest retail value for any book category 1. Determine the (retail) price of the most expensive book in each category 2. The maximum retail price in each category is to the WHERE clause of the outer query (more than one) 3. The outer query compares each book's price to the prices from #2 4. If a book's rail price matches one of the prices returned, the book's title, retail price, and category are displayed in the output

SELECT title, retail, category FROM books WHERE retail IN(SELECT MAX(retail) FROM books GROUP BY category) ORDER BY category;

Query6: List the difference between each book price and the average.

SELECT title, retail, retail - (SELECT TO_CHAR(AVG(retail), '999.99') FROM Books "DIFF from AVG" FROM books;

Query3: List title of all books published by publisher of 'Big Bear and Little Dove' that generate more than the average profit

Tasks: two unknown values: 1) the pubid of 'Big Bear and Little Dove', 2) the average profit of the all books. SELECT isbn, title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title= 'BIG BEAR AND LITTLE DOVE') AND retail - cost > (SELECT AVG(retail-cost) FROM books);

NVL Function

The NVL function is to address problems caused when performing arithmetic operations with fields that might contain NULL values.

Subquery

a nested query -- one complete query inside another query.

Multiple-Column Subqueries return

a temporary table can return more than one column to the outer query, column list on the left side of operator must be in parenthese, use the IN operator for WHERE and HAVING clauses.