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