SQL (Structured Query Language) has been around for many years and is a very useful tool to access data in small or large scale. If you are like me, that may not be using SQL everyday, but need to use it once in a while. What I like to see is just a cheat sheet that give me the commonly used SQLs that I can quickly refresh my memory on their syntax.
Below is a short list, it is not meant to be extensive, it meant to be a reminder cheat sheet, and pretty much self explanatory with just a few comments.
If you are not familiar with SQL and like to learn more, I find w3schools provide very good tutorial and references for a few common languages, here is a link to SQL. What I like about w3schools and how it differentiate itself from some of the other site is that it provide interactive code editor that you can try out any example live.
-- Get all the fields in all the rows from a table
SELECT * FROM Products;
-- LIMIT or SELECT TOP - return only the first few rows, usually good to sample the data
SELECT * FROM films LIMIT 10; -- note that some database does not support LIMIT
SELECT TOP 10 * FROM Products; -- you can try TOP instead
-- DISTINCT - get only unique values
SELECT DISTINCT CategoryID FROM Products;
-- COUNT - return total number of rows
SELECT count(*) FROM Products;
-- WHERE - apply a filter to drill down to data you are looking for
SELECT ProductName, Price FROM Products WHERE Price < 10;
SELECT count(*) FROM Products where price < 10;
SELECT ProductName, Price, CategoryID FROM Products
WHERE (Price >= 10 and Price <= 20) and (CategoryID = 1 or CategoryID = 5);
SELECT ProductName, Price, CategoryID FROM Products
WHERE Price between 10 and 20 and CategoryID in (1, 5);
SELECT ProductName FROM Products WHERE Price is null;
SELECT count(ProductName) FROM Products WHERE Price is null;
-- Wildcards
-- % wildcard will match zero, one, or many characters
SELECT ProductName FROM Products WHERE ProductName like 'C%';
-- _ wildcard will match a single character
-- this will match any word that has h in the second character
SELECT ProductName FROM Products WHERE ProductName like '_h%';
-- this will match any product name that does not start with A
SELECT ProductName FROM Products WHERE ProductName not like 'A%';
-- Aggregate Functions
SELECT avg(price) FROM Products;
SELECT sum(price) FROM Products;
SELECT min(price) FROM Products;
SELECT max(price) FROM Products;
-- AS for aliasing for each access
SELECT max(price) as max_price FROM Products;
-- ORDER BY - sort result, default sort is ascending.
SELECT ProductName, Price FROM Products ORDER BY Price;
SELECT ProductName, Price FROM Products ORDER BY Price DESC;
SELECT ProductName, SupplierID, Price FROM Products ORDER BY SupplierID, Price;
-- GROUP BY - return subtotals by the group by field
SELECT SupplierID, count(*) FROM Products Group BY SupplierID;
-- HAVING
SELECT SupplierID, count(*) FROM Products Group BY SupplierID HAVING count(SupplierID) > 3;
-- JOINS - Inner Join
SELECT ProductName, Products.SupplierID, Suppliers.SupplierName, Suppliers.Country
FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID;
Hope you find this 1-pager handy and useful.