Conditional Queries
While there are times when one needs to see all of the rows in a table, by far the more common situation is that only some rows of a table are needed. In order to restrict the rows to be returned by a SELECT statement you can specify a conditional expression in a SELECT statement WHERE clause which specifies that only those rows for which the conditional expression is true are to be retrieved. The syntax for the SELECT statement containing the WHERE clause is as follows.
select_statement: SELECT expression [alias_name] [, expression [alias_name] ]… FROM table_name WHERE conditional_expr
conditional_expr:
relational_expr [{ AND | OR} relational_expr]...
The LIKE operation can be used to perform simple pattern matching. SQL defines two pattern matching symbols. The "%" can be specified to match zero or more characters. The "?" can be specified to match any single character.
For example, most of the short biographical sketches (column short_bio) contained in the author table specifies the nationality of the author. Hence, for example, the following query will retrieve only those authors in which "English" is included somewhere in the short_bio column.
select full_name from author where short_bio like "%English%"; FULL_NAME Austen, Jane Bacon, Francis Bronte, Charlotte Bronte, Emily Carroll, Lewis Chaucer, Geoffrey Chesterton, G. K. (Gilbert Keith) Coleridge, Samuel Taylor Conrad, Joseph Defoe, Daniel Dickens, Charles Eliot, George Hardy, Thomas Hobbes, Thomas Johnson, Samuel Milton, John Potter, Beatrix Raleigh, Walter Scott, Walter Shakespeare, William Tennyson, Alfred Trollope, Anthony Wells, H. G. (Herbert George) Woolf, Virginia
The next query returns those books that are priced over £100,000.
select bookid, price, title from book where price > 100000.00; BOOKID PRICE TITLE shakespeare01 175000.00 The Tragicall Historie of Hamlet, Prince of Denmarke. shakespeare02 135000.00 Midsummer night's dream shakespeare04 250000.00 Plays shakespeare05 225000.00 Romeo and Juliet
Books that have not been sold have a null date_sold column value. Issue the following query to list all those books that have sold.
select bookid, date_sold, price, title from book where date_sold is not null;
BOOKID DATE_SOLD PRICE TITLE
alcott01 2010-05-04 1200.00 Moods
alcott04 2010-01-11 1250.00 Little men : life at Plumfield with Jo's boys
alcott05 2010-08-14 850.00 Eight cousins;
alcott06 2010-01-06 875.00 Rose in bloom. A sequel to 'Eight cousins.'
austen03 2009-10-28 13500.00 Mansfield Park: a novel. In three volumes.
bacon03 2010-04-01 5000.00 Sylva sylvarum. French
bacon04 2010-02-13 2500.00 History natural and experimental, of life and death.
burns01 2009-07-12 1250.00 Poems, chiefly in the Scottish dialect...
carlyle03 2009-12-13 995.00 Chartism.
...
wells04 2006-12-15 3000.00 The war of the worlds,
wells05 2010-01-02 25000.00 The first men in the moon, by H.G. Wells ...
wharton03 2009-03-20 3250.00 Crucial instances,
wharton05 2010-04-04 4000.00 The descent of man, and other stories
wharton08 2010-07-13 2500.00 Ethan Frome
wharton09 2008-12-20 2500.00 The age of innocence
wharton11 2007-08-08 1500.00 The buccaneers
wilde04 2007-12-23 22500.00 The ballad of Reading gaol.
Note that the following query does not return any rows even though you might think that it should.
select bookid, date_sold, title from book where date_sold != null;
SQL uses three-valued conditional results: a condition can be true, or false, or indeterminate. The processing details are too complicated to get into here but in order to do null value comparisons you must use the IS NULL and IS NOT NULL operators.
The IN operator will return all rows in which the left hand expression evaluates to one of the values specified in the list as in the next example which lists those patrons from California and Washington.
select name, city, email from patron where state in ("CA","WA");
NAME CITY EMAIL
William Gates, III Redmond [email protected]
Stephen Jobs Cupertino [email protected]
Scrooge McDuck Anaheim [email protected]
Richie Rich San Diego [email protected]
Jed Clampett Beverly Hills [email protected]
Lucille Bluth Newport Beach [email protected]
Jean Luc Picard San Francisco [email protected]
Jeffrey Bezos Seattle [email protected]
The BETWEEN operator returns those rows where the left hand expression inclusively evaluates to a value between the two values on the right.
select last_name, publ_year, title from book where publ_year between 1810 and 1820; LAST_NAME PUBL_YEAR TITLE AustenJ 1813 Pride and prejudice: a novel ... AustenJ 1813 Sense and sensibility: a novel. AustenJ 1814 Mansfield Park: a novel. In three volumes. AustenJ 1816 Emma: a novel. In three volumes. CooperJ 1820 Precaution; a novel... IrvingW 1814 Biographical memoir of Capt-David Porter. ScottW 1810 The lady of the lake. A poem. ScottW 1811 The vision of Don Roderick: a poem. ScottW 1815 The field of Waterloo; a poem.