------Vitalie Bancu @vitallybankou

Listing 4.41 List the titles published on the first day of

the year 2000, 2001, or 2002. See Figure 4.41 for the

result.

*/

SELECT title_id, pubdate

  FROM titles

  WHERE pubdate IN

        (DATE '2000-01-01',

         DATE '2001-01-01',

         DATE '2002-01-01');

  ------Vitalie Bancu @vitallybankou         

NOT IN is equivalent to combining tests

for inequality with AND. This statement is

equivalent to Listing 4.39: */

SELECT au_fname, au_lname, state

FROM authors

WHERE state <> 'NY'

AND state <> 'NJ'

AND state <> 'CA';     

  ------Vitalie Bancu @vitallybankou

Listing 4.42 List the locations of all the publishers.

See Figure 4.42 for the result.

*/

SELECT pub_id, city, state, country

  FROM publishers;

  ------Vitalie Bancu @vitallybankou  

Listing 4.43 List the publishers located in California.

See Figure 4.43 for the result.

*/

SELECT pub_id, city, state, country

  FROM publishers

  WHERE state = 'CA';

  ------Vitalie Bancu @vitallybankou

Listing 4.44 List the publishers located outside

California (the wrong way—see Listing 4.45 for the

correct way). See Figure 4.44 for the result.

*/

SELECT pub_id, city, state, country

  FROM publishers

  WHERE state <> 'CA';

  ------Vitalie Bancu @vitallybankou

Listing 4.45 List the publishers located outside

California (the correct way). See Figure 4.45 for the

result.

*/  

SELECT pub_id, city, state, country

  FROM publishers

  WHERE state <> 'CA'

     OR state IS NULL;  

  ------Vitalie Bancu @vitallybankou

Listing 4.46 List the biographies whose (past or

future) publication dates are known. See Figure 4.46

for the result.

*/  

  SELECT title_id, type, pubdate

  FROM titles

  WHERE type = 'biography'

    AND pubdate IS NOT NULL;

SELECT pub_id, city, state, country

FROM publishers

WHERE country <> 'Canada';    

  ------Vitalie Bancu @vitallybankou

Listing 5.1 A constant expression in a SELECT clause.

No FROM clause is needed, because I’m not retrieving

data from a table. See Figure 5.1 for the result.

*/

  SELECT 2 + 3 --don't work in oaracle 11.

  FROM  DUAL;

    ------Vitalie Bancu @vitallybankou

  Listing 5.2 Here, I’ve retrieved a column and a

constant expression. See Figure 5.2 for the result.

  */

  SELECT au_id, 2 + 3

  FROM authors;

  ------Vitalie Bancu @vitallybankou  

Listing 5.3 List the book prices discounted by 10

percent. The derived columns would have DBMSspecific

default names if the AS clauses were

removed. See Figure 5.3 for the result.

*/ 

SELECT title_id,

       price,

       0.10 AS "Discount",

       price * (1 - 0.10) AS "New price"

  FROM titles;

  ------Vitalie Bancu @vitallybankou

Listing 5.4 The negation operator changes the sign of

a number. See Figure 5.4 for the result.

*/  

SELECT title_id,

       -advance AS "Advance"

  FROM royalties;

  ------Vitalie Bancu @vitallybankou

Listing 5.5 List the biographies by descending

revenue (= price x sales). See Figure 5.5 for the result.

*/

SELECT title_id,

       price * sales AS "Revenue"

  FROM titles

  WHERE type = 'biography'

  ORDER BY "Revenue" DESC;

  ------Vitalie Bancu @vitallybankou

Listing 5.6 This query’s first derived column

divides pages by the integer constant 10, and the

second derived column divides pages by the

floating-point constant 10.0. In the result, you’d

expect identical values to be in both derived

columns. See Figures 5.6a and 5.6b for the results.

*/  

SELECT title_id,

       pages,

       pages/10   AS "pages/10",

       pages/10.0 AS "pages/10.0"

  FROM titles;

  ------Vitalie Bancu @vitallybankou

Listing 5.7 The first and second columns show how

to use parentheses to override precedence rules.

The third and fourth columns show how to use

parentheses to override associativity rules. See

Figure 5.7 for the result.

*/  

SELECT 2 + 3 * 4   AS "2+3*4",

       (2 + 3) * 4 AS "(2+3)*4",

       6 / 2 * 3   AS "6/2*3",

       6 / (2 * 3) AS "6/(2*3)"

    FROM  DUAL;