------Vitalie Bancu @vitallybankou
Listing 4.17 This query
sorts by an expression. See
Figure 4.17 for the
result. I’ve created a column alias
for the expression
because it would be cumbersome
to repeat the expression
in the ORDER BY clause and
because it creates a
more meaningful column label in
the result.
*/
SELECT title_id,
price,
sales,
price * sales AS "Revenue"
FROM titles
ORDER BY
"Revenue" DESC;
------Vitalie Bancu @vitallybankou
Listing 4.18 List
the authors whose last name is not
Hull. See Figure 4.18
for the result.
*/
SELECT au_id,
au_fname, au_lname
FROM authors
WHERE au_lname
<> 'Hull';
------Vitalie Bancu @vitallybankou
Listing 4.19 List
the titles for which there is no signed
contract. See Figure
4.19 for the result.
*/
SELECT
title_name, contract
FROM titles
WHERE contract =
0;
------Vitalie Bancu @vitallybankou
Listing 4.20 List
the titles published in 2001 and later.
See Figure 4.20 for the
result.
*/
SELECT
title_name, pubdate
FROM titles
WHERE pubdate
>= DATE '2001-01-01';
------Vitalie Bancu @vitallybankou
Listing 4.21 List
the titles that generated more than
$1 million in revenue.
This search condition uses an
arithmetic expression.
See Figure 4.21 for the result.
*/
SELECT title_name,
price * sales AS "Revenue"
FROM titles
WHERE price *
sales > 1000000;
------Vitalie Bancu @vitallybankou
If you alias a
column in a SELECT clause
(see "Creating Column
Aliases with AS”
earlier in this
chapter), you can’t reference
it in the WHERE clause.
The following
query fails because the
WHERE clause is
evaluated before the
SELECT clause, so the
alias copies_sold
doesn’t yet exist when
the WHERE clause is
evaluated:
-- Wrong
SELECT sales AS
copies_sold
FROM titles
WHERE copies_sold >
100000;
Instead, use a subquery
(Chapter 8) in the
FROM clause, which is
evaluated before the
WHERE clause:
-- Correct
SELECT *
FROM (SELECT sales AS
copies_sold
FROM titles) ta
WHERE copies_sold >
100000;
This solution works not
only for columns
aliases but also for
aggregate functions,
scalar subqueries, and
windowing functions
referenced in WHERE
clauses. Note
that in the latter
query, the subquery is
aliased ta (a table
alias).
*/
SELECT sales AS
copies_sold
FROM titles;
------Vitalie Bancu @vitallybankou----------------------------------------------------------------*/
SELECT *
FROM (SELECT sales AS
copies_sold
FROM titles) ta
WHERE copies_sold >
100000;
------Vitalie Bancu @vitallybankou
Listing 4.22 List
the biographies that sell for less than
$20. See Figure 4.22 for
the result.
*/
SELECT title_name, type,
price
FROM titles
WHERE type =
'biography' AND price < 20;
------Vitalie Bancu @vitallybankou
Listing 4.23 List
the authors whose last names begin
with one of the letters
H through Z and who don’t live
in California. See
Figure 4.23 for the result.
*/
SELECT au_fname,
au_lname, state
FROM authors
WHERE au_lname
>= 'H'
AND
au_lname <= 'Zz'
AND state
<> 'CA';
------Vitalie Bancu @vitallybankou
Listing 4.24 List the
authors who live in New York
State, Colorado, or San
Francisco. See Figure 4.24 for
the result.
*/
SELECT au_fname,
au_lname, city, state
FROM authors
WHERE (state =
'NY')
OR
(state = 'CO')
OR
(city = 'San Francisco');
------Vitalie Bancu @vitallybankou
Listing 4.25 List the
publishers that are located in
California or are not
located in California. This example
is contrived to show the
effect of nulls in conditions;
see Figure 4.25 for the
result.
*/
SELECT pub_id,
pub_name, state, country
FROM publishers
WHERE (state =
'CA')
OR
(state <> 'CA');
SELECT title_id, type,
price
FROM titles
WHERE NOT type =
'biography'
AND NOT price < 20;
------Vitalie Bancu @vitallybankou
Listing 4.26 List the
authors who don’t live in
California. See Figure
4.26 for the result.
*/
SELECT au_fname,
au_lname, state
FROM authors
WHERE NOT (state
= 'CA');
------Vitalie Bancu @vitallybankou
Listing 4.27 List the
titles whose price is not less than
$20 and that have sold
more than 15,000 copies. See
