--SELECT  08.01.2014 Vitalie Bancu  

  SELECT title_name

  FROM titles

  WHERE title_name LIKE '%!%%' ESCAPE '!';

--SELECT  08.01.2014 Vitalie Bancu  

  SELECT au_fname, au_lname, zip

  FROM authors

  WHERE zip NOT BETWEEN '20000' AND '89999';

--SELECT  08.01.2014 Vitalie Bancu

  SELECT title_id, price

  FROM titles

  WHERE price BETWEEN 10 AND 19.95;

--SELECT  08.01.2014 Vitalie Bancu

  SELECT title_id, pubdate

  FROM titles

  WHERE pubdate BETWEEN DATE '2000-01-01'

                AND     DATE '2000-12-31';  

  

--SELECT  08.01.2014 Vitalie Bancu  

  SELECT title_id, price

  FROM titles

  WHERE (price > 10)

    AND (price < 19.95);

  --SELECT  08.01.2014 Vitalie Bancu 

  SELECT au_fname, au_lname, state

  FROM authors

  WHERE state NOT IN ('NY', 'NJ', 'CA');

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT title_id, advance

  FROM royalties

  WHERE advance IN

        (0.00, 1000.00, 5000.00);

  

  --SELECT  08.01.2014 Vitalie Bancu      

  SELECT title_id, pubdate

  FROM titles

  WHERE pubdate IN

        (DATE '2000-01-01',

         DATE '2001-01-01',

         DATE '2002-01-01');

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT pub_id, city, state, country

  FROM publishers;

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT pub_id, city, state, country

  FROM publishers

  WHERE state = 'CA';    

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT pub_id, city, state, country

  FROM publishers

  WHERE state <> 'CA';

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT pub_id, city, state, country

  FROM publishers

  WHERE state <> 'CA'

     OR state IS NULL;

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT title_id, type, pubdate

  FROM titles

  WHERE type = 'biography'

    AND pubdate IS NOT NULL;

    

  --SELECT  08.01.2014 Vitalie Bancu

  

  SELECT 2 + 3;

--SELECT  08.01.2014 Vitalie Bancu  

  SELECT au_id, 2 + 3

  FROM authors;

--SELECT  08.01.2014 Vitalie Bancu  

  SELECT title_id,

       price,

       0.10 AS "Discount",

       price * (1 - 0.10) AS "New price"

  FROM titles;

--SELECT  08.01.2014 Vitalie Bancu  

SELECT title_id,

       -advance AS "Advance"

  FROM royalties;

--SELECT  08.01.2014 Vitalie Bancu 

  SELECT title_id,

       price * sales AS "Revenue"

  FROM titles

  WHERE type = 'biography'

  ORDER BY "Revenue" DESC;

--SELECT  08.01.2014 Vitalie Bancu

SELECT title_id,

       pages,

       pages/10   AS "pages/10",

       pages/10.0 AS "pages/10.0"

  FROM titles;

 --SELECT  08.01.2014 Vitalie Bancu

 SELECT 2 + 3 * 4   AS "2+3*4",

       (2 + 3) * 4 AS "(2+3)*4",

       6 / 2 * 3   AS "6/2*3",

       6 / (2 * 3) AS "6/(2*3)";

 --SELECT  08.01.2014 Vitalie Bancu

 SELECT au_fname || ' ' || au_lname

         AS "Author name"

  FROM authors

  ORDER BY au_lname ASC, au_fname ASC;

  

  

 --SELECT  08.01.2014 Vitalie Bancu 

  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;

--SELECT  08.01.2014 Vitalie Bancu

  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;

  

--SELECT  08.01.2014 Vitalie Bancu

 SELECT au_id, au_fname, au_lname

  FROM authors

  WHERE au_fname || ' ' || au_lname

        = 'Klee Hull';

--SELECT  08.01.2014 Vitalie Bancu

  SELECT pub_id,

       SUBSTRING(pub_id FROM 1 FOR 1)

         AS "Alpha part",

       SUBSTRING(pub_id FROM 2)

         AS "Num part"

  FROM publishers;

  

  --SELECT  08.01.2014 Vitalie Bancu

  

  SELECT SUBSTRING(au_fname FROM 1 FOR 1)

       || '. '

       || au_lname

         AS "Author name",

       state

  FROM authors

  WHERE state IN ('NY', 'CO');

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT au_fname, au_lname, phone

  FROM authors

  WHERE SUBSTRING(phone FROM 1 FOR 3)='415';

--SELECT  08.01.2014 Vitalie Bancu  

  SELECT LOWER(au_fname) AS "Lower",

       UPPER(au_lname) AS "Upper"

  FROM authors;  

  

--SELECT  08.01.2014 Vitalie Bancu  

  SELECT title_name

  FROM titles

  WHERE UPPER(title_name) LIKE '%MO%';

  

  --SELECT  08.01.2014 Vitalie Bancu  

  SELECT

  '<' || '  AAA  ' || '>'

    AS "Untrimmed",

  '<' || TRIM(LEADING FROM '  AAA  ') || '>'

    AS "Leading",

  '<' || TRIM(TRAILING FROM '  AAA  ') || '>'

    AS "Trailing",

  '<' || TRIM('  AAA  ') || '>'

     AS "Both";

   --SELECT  08.01.2014 Vitalie Bancu    

     SELECT au_lname,

       TRIM(LEADING 'H' FROM au_lname)

         AS "Trimmed name"

     FROM authors;

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT title_id

  FROM titles

  WHERE TRIM(title_id) LIKE 'T1_';

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT au_fname,

       CHARACTER_LENGTH(au_fname) AS "Len"

  FROM authors;

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT title_name,

       CHARACTER_LENGTH(title_name) AS "Len"

  FROM titles

  WHERE CHARACTER_LENGTH(title_name) < 30

  ORDER BY CHARACTER_LENGTH(title_name) ASC;

  --SELECT  08.01.2014 Vitalie Bancu

  

  SELECT

    au_fname,

    POSITION('e' IN au_fname) AS "Pos e",

    au_lname,

    POSITION('ma' IN au_lname) AS "Pos ma"

  FROM authors;

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT title_name,

       POSITION('u' IN title_name) AS "Pos"

  FROM titles

  WHERE POSITION('u' IN title_name)

        BETWEEN 1 AND 10

  ORDER BY POSITION('u' IN title_name) DESC;

  

  --SELECT  08.01.2014 Vitalie Bancu

  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;

  

  --SELECT  08.01.2014 Vitalie Bancu

  

  SELECT

    CURRENT_DATE AS "Date",

    CURRENT_TIME AS "Time",

    CURRENT_TIMESTAMP AS "Timestamp";

  

  --SELECT  08.01.2014 Vitalie Bancu  

  SELECT title_id, pubdate

  FROM titles

  WHERE pubdate 

        BETWEEN CURRENT_TIMESTAMP

                - INTERVAL 90 DAY

            AND CURRENT_TIMESTAMP

                + INTERVAL 90 DAY

     OR pubdate IS NULL

  ORDER BY pubdate DESC;

  

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT CURRENT_USER AS "User";

  --SELECT  08.01.2014 Vitalie Bancu

  SELECT

    price

      AS "price(DECIMAL)",

    CAST(price AS INTEGER)

      AS "price(INTEGER)",

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

      AS "price(CHAR(8))"

  FROM titles;

  --SELECT  08.01.2014 Vitalie Bancu

  

  SELECT

    CAST(sales AS CHAR(8))

    || ' copies sold of '

    || CAST(title_name AS CHAR(20))

      AS "History and biography sales"

  FROM titles

  WHERE sales IS NOT NULL

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

  ORDER BY sales DESC;

  

  --SELECT  08.01.2014 Vitalie Bancu

  

  SELECT

    title_id,

    type,

    price,

    CASE type

      WHEN 'history'

        THEN price * 1.10

      WHEN 'psychology'

        THEN price * 1.20

      ELSE price

    END

      AS "New price"

  FROM titles

  ORDER BY type ASC, title_id ASC;

 

 --SELECT  08.01.2014 Vitalie Bancu

 

 SELECT

    title_id,

    CASE

      WHEN sales IS NULL

        THEN 'Unknown'

      WHEN sales <= 1000

        THEN 'Not more than 1,000'

      WHEN sales <= 10000 

        THEN 'Between 1,001 and 10,000'

      WHEN sales <= 100000

        THEN 'Between 10,001 and 100,000'

      WHEN sales <= 1000000

        THEN 'Between 100,001 and 1,000,000'

      ELSE 'Over 1,000,000'

    END

      AS "Sales category"

  FROM titles

  ORDER BY sales ASC; 

    

  --SELECT  08.01.2014 Vitalie Bancu  

  SELECT

    pub_id,

    city,

    COALESCE(state, 'N/A') AS "state",

    country

  FROM publishers;  

  

  --SELECT  08.01.2014 Vitalie Bancu  

  SELECT

    title_id,

    contract,

    NULLIF(contract, 0) AS "Null contract"

  FROM titles;  

  

  --SELECT  08.01.2014 Vitalie Bancu  

  

  SELECT MIN(price) AS "Min price"

  FROM titles;

  SELECT MIN(pubdate) AS "Earliest pubdate"

  FROM titles;

  SELECT MIN(pages) AS "Min history pages"

  FROM titles

  WHERE type = 'history';