--SQL tricks. (SELECT.) 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors
ORDER BY au_lname;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT *
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city, state, country
FROM publishers;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname AS "First name",
au_lname AS "Last name",
city AS "City",
state,
zip AS "Postal code"
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT DISTINCT state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city, state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT DISTINCT city, state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY au_lname ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY au_fname DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY state ASC,
city DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY 4 ASC, 2 DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT pub_id, state, country
FROM publishers
ORDER BY state ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city, state
FROM authors
ORDER BY zip ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname AS "First name",
au_lname AS "Last name",
state
FROM authors
ORDER BY state ASC,
"Last name" ASC,
"First name" ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_id,
price,
sales,
price * sales AS "Revenue"
FROM titles
ORDER BY "Revenue" DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_id, au_fname, au_lname
FROM authors
WHERE au_lname <> 'Hull' OR au_lname <> 'Bancu';
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, contract
FROM titles
WHERE contract = 0;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, pubdate
FROM titles
WHERE pubdate >= DATE '2001-01-01';
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name,
price * sales AS "Revenue"
FROM titles
WHERE price * sales > 1000000;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, type, price
FROM titles
WHERE type = 'biography' AND price < 20;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, state
FROM authors
WHERE au_lname >= 'H'
AND au_lname <= 'Zz'
AND state <> 'CA';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
WHERE (state = 'NY')
OR (state = 'CO')
OR (city = 'San Francisco');
--SELECT 08.01.2014 Vitalie Bancu
SELECT pub_id, pub_name, state, country
FROM publishers
WHERE (state = 'CA')
OR (state <> 'CA');
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, state
FROM authors
WHERE NOT (state = 'CA');
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, sales, price
FROM titles
WHERE NOT (price < 20)
AND (sales > 15000);
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_id, type, price
FROM titles
WHERE type = 'history'
OR type = 'biography'
AND price < 20;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_id, type, price
FROM titles
WHERE (type = 'history'
OR type = 'biography')
AND price < 20;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors
WHERE au_lname LIKE 'Kel%';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors
WHERE au_lname LIKE '__ll%';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state, zip
FROM authors
WHERE zip LIKE '94___';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, phone
FROM authors
WHERE phone NOT LIKE '212-___-____'
AND phone NOT LIKE '415-___-%'
AND phone NOT LIKE '303-%';
--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 au_fname, au_lname
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors
ORDER BY au_lname;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT *
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city, state, country
FROM publishers;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname AS "First name",
au_lname AS "Last name",
city AS "City",
state,
zip AS "Postal code"
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT DISTINCT state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city, state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT DISTINCT city, state
FROM authors;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY au_lname ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY au_fname DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY state ASC,
city DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY 4 ASC, 2 DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT pub_id, state, country
FROM publishers
ORDER BY state ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT city, state
FROM authors
ORDER BY zip ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname AS "First name",
au_lname AS "Last name",
state
FROM authors
ORDER BY state ASC,
"Last name" ASC,
"First name" ASC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_id,
price,
sales,
price * sales AS "Revenue"
FROM titles
ORDER BY "Revenue" DESC;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_id, au_fname, au_lname
FROM authors
WHERE au_lname <> 'Hull' OR au_lname <> 'Bancu';
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, contract
FROM titles
WHERE contract = 0;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, pubdate
FROM titles
WHERE pubdate >= DATE '2001-01-01';
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name,
price * sales AS "Revenue"
FROM titles
WHERE price * sales > 1000000;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, type, price
FROM titles
WHERE type = 'biography' AND price < 20;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, state
FROM authors
WHERE au_lname >= 'H'
AND au_lname <= 'Zz'
AND state <> 'CA';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state
FROM authors
WHERE (state = 'NY')
OR (state = 'CO')
OR (city = 'San Francisco');
--SELECT 08.01.2014 Vitalie Bancu
SELECT pub_id, pub_name, state, country
FROM publishers
WHERE (state = 'CA')
OR (state <> 'CA');
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, state
FROM authors
WHERE NOT (state = 'CA');
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_name, sales, price
FROM titles
WHERE NOT (price < 20)
AND (sales > 15000);
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_id, type, price
FROM titles
WHERE type = 'history'
OR type = 'biography'
AND price < 20;
--SELECT 08.01.2014 Vitalie Bancu
SELECT title_id, type, price
FROM titles
WHERE (type = 'history'
OR type = 'biography')
AND price < 20;
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors
WHERE au_lname LIKE 'Kel%';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname
FROM authors
WHERE au_lname LIKE '__ll%';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, city, state, zip
FROM authors
WHERE zip LIKE '94___';
--SELECT 08.01.2014 Vitalie Bancu
SELECT au_fname, au_lname, phone
FROM authors
WHERE phone NOT LIKE '212-___-____'
AND phone NOT LIKE '415-___-%'
AND phone NOT LIKE '303-%';
