SELECT title_name,
sales, price
FROM titles
WHERE NOT (price
< 20)
AND (sales
> 15000);
------Vitalie Bancu @vitallybankou
Listing 4.28 This query
won’t work if I want to list
history and biography
titles less than $20, because
AND has higher
precedence than OR. See Figure 4.28
for the result.
*/
SELECT title_id, type,
price
FROM titles
WHERE type =
'history'
OR
type = 'biography'
AND price
< 20;
------Vitalie Bancu @vitallybankou
Listing 4.29 To fix
Listing 4.28, I’ve added parentheses
to force OR to be
evaluated before AND. See Figure 4.29
for the result.
*/
SELECT title_id, type,
price
FROM titles
WHERE (type
= 'history'
OR
type = 'biography')
AND price
< 20;
------Vitalie Bancu @vitallybankou
Under the
default precedence rules, the
condition x AND NOT y OR
z is equivalent
to (x AND (NOT y)) OR z.
*/
SELECT type,
INSTR (type,'history')
AS "Hist?",
INSTR
(type,'biography')AS "Bio?",
price
--price < 20 AS
"<20?"
FROM titles;
------Vitalie Bancu @vitallybankou
Listing 4.30 List the
authors whose last names begin
with Kel. See Figure
4.30 for the result.
*/
SELECT au_fname,
au_lname
FROM authors
WHERE au_lname
LIKE 'Kel%';
------Vitalie Bancu @vitallybankou
Listing 4.31 List the
authors whose last names
have ll (el-el) as the
third and fourth characters.
See Figure 4.31 for the
result.
*/
SELECT au_fname,
au_lname
FROM authors
WHERE au_lname
LIKE '__ll%';
------Vitalie Bancu @vitallybankou
Listing 4.32 List
the authors who live in the San
Francisco Bay Area. (Zip
codes in that area begin
with 94.) See Figure
4.32 for the result.
*/
SELECT au_fname,
au_lname, city, state, zip
FROM authors
WHERE zip LIKE
'94___';
------Vitalie Bancu @vitallybankou
Listing 4.33 List
the authors who live outside the 212,
415, and 303 area codes.
This example shows three
alternative patterns for
excluding telephone numbers.
You should favor the
first alternative because singlecharacter
matches (_) are faster
than multiplecharacter
ones (%). See Figure
4.33 for the result.
*/
SELECT au_fname,
au_lname, phone
FROM authors
WHERE phone NOT
LIKE '212-___-____'
AND phone
NOT LIKE '415-___-%'
AND phone
NOT LIKE '303-%';
------Vitalie Bancu @vitallybankou
Listing 4.34 List the
titles that contain percent signs.
Only the % that follows
the escape character ! has its
literal meaning; the
other two percent signs still act
as wildcards. See Figure
4.34 for the result.
*/
SELECT title_name
FROM titles
WHERE title_name
LIKE '%!%%' ESCAPE '!';
------Vitalie Bancu @vitallybankou
Listing 4.35 List the
authors who live outside the zip
range 20000–89999. See
Figure 4.35 for the result.
*/
SELECT au_fname,
au_lname, zip
FROM authors
WHERE zip NOT
BETWEEN '20000' AND '89999';
------Vitalie Bancu @vitallybankou
Listing 4.36 List the
titles priced between $10 and
$19.95, inclusive. See
Figure 4.36 for the result.
*/
SELECT title_id,
price
FROM titles
WHERE price
BETWEEN 10 AND 19.95;
------Vitalie Bancu @vitallybankou
Listing 4.37 List the
titles published in 2000. See
Figure 4.37 for the
result.
*/
SELECT title_id, pubdate
FROM titles
WHERE pubdate
BETWEEN DATE '2000-01-01'
AND DATE '2000-12-31';
------Vitalie Bancu @vitallybankou
Listing 4.38 List the
titles priced between $10 and
$19.95, exclusive. See
Figure 4.38 for the result.
*/
SELECT title_id, price
FROM titles
WHERE (price >
10)
AND (price
< 19.95);
------Vitalie Bancu @vitallybankou
Listing 4.39 List the
authors who don’t live in New
York State, New Jersey,
or California. See Figure 4.39
for the result.
*/
SELECT au_fname,
au_lname, state
FROM authors
WHERE state NOT
IN ('NY', 'NJ', 'CA');
------Vitalie Bancu @vitallybankou
Listing 4.40 List
the titles for which advances of $0,
$1,000, or $5,000 were
paid. See Figure 4.40 for the
result.
*/
SELECT title_id, advance
FROM royalties
WHERE advance IN
(0.00, 1000.00, 5000.00);
