Commonly Used SQL in 1 page

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,709 total views, 2 views today

No Comments

Post a Comment