sql commands basic

SELECT * FROM <table name>;  // * (wild card) = all the column of the data.
e.g.	SELECT <column 1>, <column 2>, ... FROM <table name>;

SELECT <column 1> AS <new name of column 1> [, ...] FROM <table name>;  // AS -> act as an alias for column names
e.g. 	SELECT title AS Title, first_published AS "First Published" FROM <table name>;

SELECT <column 1>[, ...] FROM <table name> WHERE <condition>; // condition sintax is <column> <operator> <value>
								// date types must be written inside " "
e.g. 	SELECT title, author, first_published FROM books WHERE author = "J.K. Rowling";
	SELECT book_id FROM loans WHERE loaned_on = "2015-12-10";

operator used: =, !=, >, >=, <, <=,
e.g.	SELECT * FROM books WHERE first_published > 2005;

logical used: AND, OR
e.g. 	SELECT title FROM books WHERE author = "J.K. Rowling" AND first_published >= 2005;

SELECT <columns> FROM <table> WHERE <column> IN (<value 1>, <value 2>, ...);
e.g.	SELECT first_name, email FROM patrons WHERE library_id IN ("MCL1001", "MCL1100", "MCL1011")
	SELECT first_name, email FROM patrons WHERE library_id NOT IN ("MCL1001", "MCL1100", "MCL1011")

SELECT <columns> FROM <table> WHERE <column> BETWEEN <minimum> AND <maximum>;
e.g.	SELECT title, author FROM books WHERE first_published BETWEEN 1800 AND 1899;
	SELECT * FROM loans WHERE loaned_on BETWEEN "2015-12-13" AND "2015-12-19";

SELECT <columns> FROM <table> WHERE <column> LIKE <value>%; // search pattern are case insensitive
					       .. %<value> ..
					      .. %<value>% ..
e.g.	SELECT title FROM books WHERE title LIKE "harry potter%";
	SELECT title FROM books WHERE title LIKE "%universe%" AND genre = "Non Fiction"; // = must put the exact match, is case sensitive
	SELECT title FROM books WHERE title LIKE "%universe%" AND genre LIKE "non fiction";

= NULL // is wrong syntax
IS NULL // is correct syntax
SELECT <columns> FROM <table> WHERE <column> IS NULL;
e.g.	SELECT * FROM loans WHERE return_by > "2015-12-18" AND returned_on IS NULL; //  NULL => represent a missing value
	SELECT * FROM loans WHERE return_by > "2015-12-18" AND returned_on IS NOT NULL;