------Vitalie Bancu @vitallybankou
Listing 5.8 List the authors’ first and last names,
concatenated into a
single column and sorted by last
name/first name. See
Figure 5.8 for the result.
*/
SELECT au_fname || ' '
|| au_lname
AS "Author name"
FROM authors
ORDER BY au_lname
ASC, au_fname ASC;
------Vitalie Bancu @vitallybankou
Listing 5.9 List
biography sales by descending sales
order. Here, I need to
convert sales from an integer to
a string. See Figure 5.9
for the result.
*/
SELECT CAST(sales AS
CHAR(7))
|| ' copies sold of title '
|| title_id
AS "Biography sales"
FROM titles
WHERE type =
'biography'
AND sales
IS NOT NULL
ORDER BY sales
DESC;
------Vitalie Bancu @vitallybankou
Listing 5.10 List
biographies by descending publication
date. Here, I need to
convert pubdate from a datetime
to a string. See Figure
5.10 for the result.
*/
SELECT 'Title '
|| title_id
|| ' published on '
|| CAST(pubdate AS CHAR(10))
AS "Biography publication dates"
FROM titles
WHERE type =
'biography'
AND
pubdate IS NOT NULL
ORDER BY pubdate
DESC;
------Vitalie Bancu @vitallybankou
Listing 5.11 List all
the authors named Klee Hull. See
Figure 5.11 for the
result.
*/
SELECT au_id, au_fname,
au_lname
FROM authors
WHERE au_fname ||
' ' || au_lname
= 'Klee Hull';
------Vitalie Bancu @vitallybankou
Listing 5.12 Split the
publisher IDs into alphabetic
and numeric parts. The
alphabetic part of a publisher
ID is the first
character, and the remaining characters
are the numeric part.
See Figure 5.12 for the result.
*/
SELECT pub_id,
SUBSTR(pub_id, 1, 1)
AS "Alpha part",
SUBSTR(pub_id, 2)
AS "Num part"
FROM publishers;
------Vitalie Bancu @vitallybankou
Listing 5.13 List the
first initial and last name of the
authors from New York
State and Colorado. See
Figure 5.13 for the
result.
*/
SELECT SUBSTR(au_fname,
1 ,1)
|| '. '
|| au_lname
AS "Author name",
state
FROM authors
WHERE state IN
('NY', 'CO');
------Vitalie Bancu @vitallybankou
Listing 5.14 List the
authors whose area code is 415.
See Figure 5.14 for the
result.
*/
SELECT au_fname,
au_lname, phone
FROM authors
WHERE
SUBSTR(phone, 1, 3)='415';
------Vitalie Bancu @vitallybankou
Listing 5.15 List the
authors’ first names in lowercase
and last names in
uppercase. See Figure 5.15 for
the result.
*/
SELECT
LOWER(au_fname) AS "Lower",
UPPER(au_lname) AS "Upper"
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 5.16 List
the titles that contain the characters
MO, regardless of case.
All the letters in the LIKE
pattern must be
uppercase for this query to work. See
Figure 5.16 for the
result.
*/
SELECT title_name
FROM titles
WHERE
UPPER(title_name) LIKE '%MO%';
------Vitalie Bancu @vitallybankou
Listing 5.17 This query
strips leading, trailing, and
both leading and
trailing spaces from the string
‘ AAA ‘. The < and
> characters show the extent of
the trimmed strings. See
Figure 5.17 for the result.
*/
------Vitalie Bancu @vitallybankou
SELECT
'<' || '
AAA ' || '>'
AS
"Untrimmed",
'<' ||
TRIM(LEADING FROM ' AAA ') || '>'
AS
"Leading",
'<' ||
TRIM(TRAILING FROM ' AAA ') || '>'
AS
"Trailing",
'<' || TRIM('
AAA ') || '>'
AS
"Both";
*/
------Vitalie Bancu @vitallybankou
Listing 5.18 Strip
the leading H from the authors’ last
names that begin with H.
See Figure 5.18 for the result.
*/
SELECT au_lname,
TRIM(LEADING 'H' FROM au_lname)
AS "Trimmed name"
FROM authors;
------Vitalie Bancu @vitallybankou
Listing 5.19 List the
three-character title IDs that start
with T1, ignoring
leading and trailing spaces. See
Figure 5.19 for the
result.
*/
SELECT title_id
FROM titles
WHERE
TRIM(title_id) LIKE 'T1_';
