------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_';