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.
15,923 total views, 1 views today