Cacher is the code snippet organizer for pro developers

SQL Commands


SELECT
SELECT "column_name" FROM "table_name";
SELECT * FROM Student   // select all columns

SELECT StudentID, Name FROM Student       // select only two columns

SELECT DISTINCT 
SELECT DISTINCT "column_name" FROM "table_name";   // get only distinct values

WHERE
We can use the WHERE clause to filter the result set based on certain conditions.
SELECT "column_name" FROM "table_name" WHERE "condition";
SELECT Store_Name FROM Store_Information WHERE Sales > 1000;
SELECT * FROM Store_Information WHERE Sales > 1000 OR Txn_Date = 'Jan-08-1999';


IN
SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...);


BETWEEN
the BETWEEN operator is used to select a range.
SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2';
SELECT * FROM Store_Information WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';


Wildcard
There are times when we want to match on a string pattern. To do that, we will need to employ the concept of wildcard. In SQL, there are two wildcards:
(i) % (percent sign) represents zero, one, or more characters.
(ii) _ (underscore) represents exactly one character.

Wildcards are used with the LIKE keyword in SQL.


Examples:
A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not (because there are two characters between A and Z instead of one).
'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would both satisfy the condition.

'%AN%': All strings that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.


LIKE
LIKE is another keyword that is used in the WHERE clause. Basically, LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN).
SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN};

Note: {PATTERN} often consists of wildcards.


ORDER BY
SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY "column_name" [ASC, DESC];
//Default is ASC
It is possible to order by more than one column. 
ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

Assuming that we choose ascending order for both columns, the output will be ordered in ascending order according to column 1. If there is a tie for the value of column 1, we then sort in ascending order by column 2
In addition to column name, we may also use column position (based on the SQL query) to indicate which column we want to apply theORDER BY clause. The first column is 1, second column is 2, and so on.

SELECT Store_Name, Sales, Txn_Date FROM Store_Information ORDER BY 2 DESC;
The column(s) we use to sort the result do not need to be in the SELECT clause.
It is also possible to sort the result by an expression:
SELECT Product_ID, Price*Units Revenue FROM Product_Sales ORDER BY Price*Units DESC;



GROUP BY
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1";
Suppose We want to find total sales for each store.
SELECT Store_Name, SUM(Sales) FROM Store_Information GROUP BY Store_Name;
Group by multiple columns
The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement. Such operators include COUNT, SUM, MAX, MIN, and AVG. When that happens, we need to GROUP BY all the other selected columns, i.e., all columns except the one(s) operated on by the arithmetic operator. As such, it is important to note that we may have two columns or more associated with GROUP BY.

SELECT "column_name1", "column_name2", ... "column_nameN", Function("column_nameN+1")
FROM "table_name"
GROUP BY "column_name1", "column_name2", ... "column_nameN";
Group by month / day / week
A common use of the GROUP BY function is on a time period, which can be month, week, day, or even hour. This type of query is often combined with the ORDER BY keyword to provide a query result that shows a time series.
Example: Total daily sales
SELECT Txn_Date, SUM(Sales) FROM Store_Information GROUP BY Txn_Date;



HAVING
Another thing people may want to do is to limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is typically placed near the end of the SQL statement, and a SQL statement with the HAVING clause may or may not include the GROUP BY clause.



SELECT ["column_name1"], Function("column_name2") FROM "table_name"
[GROUP BY "column_name1"] HAVING (arithmetic function condition);



SELECT Store_Name, SUM(Sales) FROM Store_Information
GROUP BY Store_Name HAVING SUM(Sales) > 1500;



ALIAS
There are two types of aliases that are used most frequently: column alias and table alias.
In short, column aliases exist to help organizing output. In the previous example, whenever we see total sales, it is listed as SUM(Sales). While this is comprehensible, we can envision cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias would greatly make the output much more readable.

The second type of alias is the table alias. This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables (the technical term is 'perform joins'). 



SELECT "table_alias"."column_name1" AS "column_alias"
FROM "table_name" AS "table_alias";



INSERT INTO
In SQL, there are basically two ways to INSERT data into a table: One is to insert it one row at a time, the other is to insert multiple rows at a time.
The syntax for inserting data into a table one row at a time is as follows:



INSERT INTO "table_name" ("column1", "column2", ...) 

VALUES ("value1", "value2", ...);



INSERT INTO SELECT

Insert rows into a table from another table



INSERT INTO "table1" ("column1", "column2", ...) 
SELECT "column3", "column4", ...
FROM "table2";



UPDATE

update data in a row.



UPDATE "table_name" 
SET "column_1" = [new value] 

WHERE "condition";



DELETE FROM
to remove records from a table
DELETE FROM "table_name" WHERE "condition";



COUNT
SELECT COUNT("column_name") FROM "table_name";



INNER JOIN
An inner join in SQL returns rows where there is at least one match on both tables.

SELECT Sales.*, Product.* FROM Sales

INNER JOIN Product ON Sales.ProductID = Product.ProductID




LEFT OUTER JOIN 
In an left outer join, all rows from the first table mentioned in the SQL query is selected, regardless whether there is a matching row on the second table mentioned in the SQL query.




CROSS JOIN
A cross join (also called a Cartesian join) is a join of tables without specifying the join condition. In this scenario, the query would return all possible combination of the tables in the SQL query.