------Vitalie Bancu @vitallybankou
Listing 4.4 List
each publisher’s city, state, and country. See Figure 4.4 for the result.
*/
SELECT city, state,
country
FROM publishers;
------Vitalie Bancu @vitallybankou
Listing 4.5 The
AS clause specifies a column alias to display in results. This statement shows
alternative constructions
for AS syntax. In your
programs, pick one construction and use it consistently. See Figure 4.5 for the
result.
*/
SELECT au_fname
AS "First name",
au_lname AS "Last name",
city AS "City",
state,
zip AS "Postal code"
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 4.6 List
the states in which the authors live.
See Figure 4.6 for the
result.
*/
SELECT state
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 4.7 List the
distinct states in which the authors
live. The keyword
DISTINCT eliminates duplicate rows
in the result. See
Figure 4.7 for the result.
*/
SELECT DISTINCT
state
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 4.8 List
the cities and states in which the
authors live. See Figure
4.8 for the result.
*/
SELECT city,
state
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 4.9 List
the cities and states in which the
authors live. See Figure
4.8 for the result.
*/
SELECT DISTINCT
city, state
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 4.10 List the
authors’ first names, last names,
cities, and states,
sorted by ascending last name.
ORDER BY performs
ascending sorts by default, so the
ASC keyword is optional.
(In practice, ASC typically is
omitted.) See Figure
4.10 for the result.
*/
SELECT au_fname,
au_lname, city, state
FROM authors
ORDER BY au_lname
ASC;
------Vitalie Bancu @vitallybankou
Listing 4.11 List the
authors’ first names, last names,
cities, and states,
sorted by descending first name. The
DESC keyword is
required. See Figure 4.11 for the result.
*/
SELECT au_fname,
au_lname, city, state
FROM authors
ORDER BY au_fname
DESC;
------Vitalie Bancu @vitallybankou
Listing 4.12 List
the authors’ first names, last names,
cities, and states,
sorted by descending city within
ascending state. See
Figure 4.12 for the result.
*/
SELECT au_fname,
au_lname, city, state
FROM authors
ORDER BY state
ASC,
city DESC;
------Vitalie Bancu @vitallybankou
Listing 4.13 List each
author’s first name, last name,
city, and state, sorted
first by ascending state (column
4 in the SELECT clause)
and then by descending last
name within each state
(column 2). See Figure 4.13
for the result.
*/
SELECT au_fname,
au_lname, city, state
FROM authors
ORDER BY 4 ASC, 2
DESC;
------Vitalie Bancu @vitallybankou
To sort results by
specific parts of a string,
use the functions
described in "Extracting
a Substring with
SUBSTRING()” in Chapter
5. For example, this
query sorts by the last
four characters of
phone:
*/
SELECT au_id,
phone
FROM AUTHORS
ORDER BY
substr(phone,length(phone)-3);
------Vitalie Bancu @vitallybankou
Listing 4.14 Nulls in a
sort column are listed first or
last, depending on the
DBMS. See Figure 4.14 for the
result.
*/
SELECT pub_id, state,
country
FROM publishers
ORDER BY state
ASC;
------Vitalie Bancu @vitallybankou
Listing 4.15 zip doesn’t
appear in the list of columns
to retrieve. See Figure
4.15 for the result.
*/
SELECT city, state
FROM authors
ORDER BY zip ASC;
------Vitalie Bancu @vitallybankou
Listing 4.16 This query
uses column aliases in the
ORDER BY clause. See
Figure 4.16 for the result.
*/
SELECT au_fname
AS "First name",
au_lname AS "Last name",
state
FROM authors
ORDER BY state
ASC,
"Last name" ASC,
"First name" ASC;
------Vitalie
Bancu @vitallybankou
To sort based on
conditional logic, add a
CASE expression to the
ORDER BY clause
(see "Evaluating
Conditional Values with
CASE” in Chapter 5). For
example, this
query sorts by price if
type is "history”;
otherwise, it sorts by
sales:
SELECT title_id, type,
price, sales
FROM titles
ORDER BY CASE WHEN type
= 'history'
THEN price ELSE sales
END;
*/
SELECT title_id,
type, price, sales
FROM titles
ORDER BY CASE
WHEN type = 'history'
THEN price ELSE
sales END;
