------Vitalie Bancu @vitallybankou

  Listing 5.20 List the lengths of the authors’ first

names. See Figure 5.20 for the result.

  */

  

  SELECT au_fname,

       LENGTH(au_fname) AS "Len"

  FROM authors;

  

  ------Vitalie Bancu @vitallybankou

  Listing 5.21 List the books whose titles contain fewer

than 30 characters, sorted by ascending title length.

See Figure 5.21 for the result.v

  */

/

 SELECT title_name,

       LENGTH(title_name) AS "Len"

  FROM titles

  WHERE LENGTH(title_name) < 30

  ORDER BY LENGTH(title_name) ASC; 

  

  ------Vitalie Bancu @vitallybankou  

Listing 5.22 List the position of the substring e in the

authors’ first names and the position of the substring

ma in the authors’ last names. See Figure 5.22 for

the result.

*/

 

    SELECT

    au_fname,

    INSTR( au_fname,'e') AS "Pos e",

    au_lname,

    c (au_lname,'ma') AS "Pos ma"

    FROM authors;

  

  ---//---

  ------Vitalie Bancu @vitallybankou

  Listing 5.23 List the books whose titles contain the

letter u somewhere within the first 10 characters,

sorted by descending position of the u. See Figure

5.23 for the result.

*/

  

  

  SELECT title_name,

       INSTR (title_name, 'u') AS "Pos"

  FROM titles

  WHERE INSTR(title_name, 'u')

        BETWEEN 1 AND 10

  ORDER BY INSTR(title_name,'u') DESC;

  

  

  

  ------Vitalie Bancu @vitallybankou

Listing 5.24 List the books published in the first half

of the years 2001 and 2002, sorted by descending

publication date. See Figure 5.24 for the result.

*/

  

  SELECT

    title_id,

    pubdate

  FROM titles

  WHERE EXTRACT(YEAR FROM pubdate)

          BETWEEN 2001 AND 2002

    AND EXTRACT(MONTH FROM pubdate)

          BETWEEN 1 AND 6

  ORDER BY pubdate DESC;

  

  

    ------Vitalie Bancu @vitallybankou

  Listing 5.25 Print the current date, time, and

timestamp. See Figure 5.25 for the result.

  */

 

SELECT  

    SYSDATE AS "Date"

    FROM DUAL;

 

  ------Vitalie Bancu @vitallybankou    

    TIME AS "Time",

    CURRENT_TIMESTAMP AS "Timestamp";

    

 

  ------Vitalie Bancu @vitallybankou

Listing 5.26 List the books whose publication date

falls within 90 days of the current date or is unknown,

sorted by descending publication date (refer to

Figure 5.25 for the "current” date of this query). See

Figure 5.26 for the result.

*/

 

  SELECT title_id, pubdate

  FROM titles

  WHERE pubdate 

        BETWEEN SYSDATE - 90 

            AND SYSDATE + 90 

     OR pubdate IS NULL

  ORDER BY pubdate DESC;

  

  

  SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

  FROM DUAL;

  

    ------Vitalie Bancu @vitallybankou-------------------------------------------------------------------*/

  ------Vitalie Bancu @vitallybankou

Listing 5.27 Print the current user. See Figure 5.27 for

the result.

*/ 

  

  SELECT USER AS "User"

  FROM DUAL;

  

  

    ------Vitalie Bancu @vitallybankouListing 5.28 Convert the book prices from the DECIMAL

data type to INTEGER and CHAR(8) data types. The <

and > characters show the extent of the CHAR(8)

strings. Your result will be either Figure 5.28a or

5.28b, depending on whether your DBMS truncates or

rounds integers.*/

 

SELECT

    price

      AS "price(DECIMAL)",

    CAST(price AS INTEGER)

      AS "price(INTEGER)",

    '<' || CAST(price AS CHAR(8)) || '>'

      AS "price(CHAR(8))"

  FROM titles;

  

   ------Vitalie Bancu @vitallybankouListing 5.29 List history and biography book sales

with a portion of the book title, sorted by descending

sales. The CHAR(20) conversion shortens the title to

make the result more readable. See Figure 5.29 for

the result.*/

 

SELECT

    CAST(sales AS CHAR(8))

     || ' copies sold of '

     || SUBSTR (title_name, 1, 20)

      AS "History and biography sales"

  FROM titles

  WHERE sales IS NOT NULL

    AND type IN ('history', 'biography')

  ORDER BY sales DESC;

 

  

SELECT price

FROM titles

WHERE price < 20.00;

  

SELECT price

FROM titles

WHERE price < CAST(20.00 AS

DECIMAL);