Listing 5.30 Raise the price of history books by 10

percent and psychology books by 20 percent, and

leave the prices of other books unchanged. See

Figure 5.30 for the result.

*/

  SELECT

    title_id,

    type,

    price,

    CASE type

      WHEN 'history'

        THEN price * 1.10

      WHEN 'psychology'

        THEN price * 1.20

      ELSE price

    END

      AS "New price"

  FROM titles

  ORDER BY type ASC, title_id ASC;

  ------Vitalie Bancu @vitallybankou  

Listing 5.31 List the books categorized by different

sales ranges, sorted by ascending sales. See

Figure 5.31 for the result.

*/

SELECT

    title_id,

    CASE

      WHEN sales IS NULL

        THEN 'Unknown'

      WHEN sales <= 1000

        THEN 'Not more than 1,000'

      WHEN sales <= 10000 

        THEN 'Between 1,001 and 10,000'

      WHEN sales <= 100000

        THEN 'Between 10,001 and 100,000'

      WHEN sales <= 1000000

        THEN 'Between 100,001 and 1,000,000'

      ELSE 'Over 1,000,000'

    END

      AS "Sales category"

  FROM titles

  ORDER BY sales ASC;

    ------Vitalie Bancu @vitallybankou

  Listing 5.32 List the publishers’ locations. If the state

is null, print N/A. See Figure 5.32 for the result.

  */

  SELECT

    pub_id,

    city,

    COALESCE(state, 'N/A') AS "state",

    country

  FROM publishers;

    ------Vitalie Bancu @vitallybankou

  Listing 5.33 In the table titles, the column contract

contains zero if no book contract exists. This query

changes the value zero to null. Nonzero values aren’t

affected. See Figure 5.33 for the result.

  */

  SELECT

    title_id,

    contract,

    NULLIF(contract, 0) AS "Null contract"

  FROM titles;

  --Creating Aggregate Expressions

  SELECT type, SUM(sales)

FROM titles

GROUP BY type;

  ------Vitalie Bancu @vitallybankou

You can use more than one aggregate

expression in a SELECT clause:

*/

SELECT MIN(sales), MAX(sales)

FROM titles;

  ------Vitalie Bancu @vitallybankou  

You can use aggregate expressions in

subqueries. This statement finds the title

of the book with the highest sales:

*/

SELECT title_id, price --Legal

FROM titles

WHERE sales =

(SELECT MAX(sales) FROM titles);  

SELECT AVG(MAX(sales))

FROM titles

GROUP BY type; --Legal in Oracle

SELECT AVG(s.max_sales)

FROM (SELECT MAX(sales) AS max_sales

FROM titles

GROUP BY type) s;

  ------Vitalie Bancu @vitallybankou

Listing 6.1 Some MIN() queries. See Figure 6.1 for the

results.

*/

  SELECT MIN(price) AS "Min price"

  FROM titles;

SELECT MIN(pubdate) AS "Earliest pubdate"

  FROM titles;

SELECT MIN(pages) AS "Min history pages"

  FROM titles

  WHERE type = 'history';

  ------Vitalie Bancu @vitallybankou  

Listing 6.2 Some MAX() queries. See Figure 6.2 for the

results.

*/

SELECT MAX(au_lname) AS "Max last name"

  FROM authors;

SELECT

    MIN(price) AS "Min price",

    MAX(price) AS "Max price",

    MAX(price) - MIN(price) AS "Range"

  FROM titles;

SELECT MAX(price * sales)

         AS "Max history revenue"

  FROM titles

  WHERE type = 'history';