I’ve been putting together a basic SQL cheat sheet that could be used as a reference guide. Here are a series of common procedures that should be of use for anyone who uses SQL to extract data. No explanations are provided as they should largely be known to the end user.
-- COUNT OF DISTINCT VALUES COUNT(DISTINCT year) AS years_count COUNT(DISTINCT month) AS months_count -- SELECT DUPLICATED ROWS SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1 SELECT name, email FROM users WHERE email in ( SELECT email FROM users GROUP BY email HAVING COUNT(*)>1 ) SELECT firstname, lastname, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address SELECT td.user_id, td.brand, td.order_dt, COUNT(*) FROM training_details AS td INNER JOIN users as u on u.user_id = td.user_id GROUP BY 1, 2, 3 HAVING COUNT(*) >= 2 ORDER BY td.order_date DESC; -- USING CASE WHEN - ONE SELECT rr.id, rr.created_at, rr.report_id, rr.account_id, rr.executed_by_id, rr.data_source_id, rr.state, MAX(CASE WHEN rs.id IS NOT NULL OR lsr.id IS NOT NULL THEN 1 ELSE 0 END) AS scheduled_run FROM report_runs rr LEFT JOIN report_schedule_runs rs ON rs.report_run_id = rr.id LEFT JOIN list_run_report_runs lrrr ON lrrr.report_run_id = rr.id LEFT JOIN list_schedule_runs lsr ON lsr.list_run_id = lrrr.list_run_id GROUP BY 1,2,3,4,5,6,7 -- SEE THE FIRST FEW ROWS OF A TABLE SELECT count(1) FROM table; SELECT * FROM table LIMIT 5; -- USING THE LIKE OPERATOR SELECT * FROM student WHERE name LIKE ‘d%n’; (returns dan or den) -- CHECKING QUERY PERFORMANCE EXPLAIN QUERY PLAN SELECT * FROM student; -- SUBQUERY - ONE SELECT SUM (Sales) FROM Store_Information WHERE Store_Name IN (SELECT Store_Name FROM Geography WHERE Region_Name = 'West'); -- SUBQUERY - TWO SELECT SUM (a1.Sales) FROM Store_Information a1 WHERE a1.Store_Name IN (SELECT Store_Name FROM Geography a2 WHERE a2.Store_Name = a1.Store_Name); -- SUBQUERY - TWO SELECT sub.* FROM ( SELECT * FROM table WHERE day_of_week = 'Friday' ) sub WHERE sub.resolution = 'NONE' -- SUBQUERY - THREE SELECT * FROM table WHERE Date IN (SELECT date FROM table ORDER BY date LIMIT 5 ) -- SUBQUERY - FOUR SELECT incidents.*, sub.incidents AS incidents_that_day FROM tutorial.sf_crime_incidents_2014_01 incidents JOIN ( SELECT date, COUNT(incidnt_num) AS incidents FROM tutorial.sf_crime_incidents_2014_01 GROUP BY 1 ) sub ON incidents.date = sub.date ORDER BY sub.incidents DESC, time -- SELECT RECORDS FROM A TIME FRAME SELECT * FROM users WHERE TO_DAYS(last_login) = ( TO_DAYS(NOW()) - 1 ) SELECT* FROM users DATE_SUB(NOW(),INTERVAL 90 MINUTE); -- UNION OPERATOR SELECT users.name FROM users WHERE (users.name BETWEEN 'A%' AND 'M%') UNION SELECT banned_users.name FROM banned_users WHERE (banned_users.name BETWEEN 'A%' AND 'M%'); -- CONCATENATE DATA INTO ONE COLUMN SELECT CONCAT(emp.firstname, '-', emp.lastname) AS emp_full_name FROM emp; -- INDEXING STRINGS SELECT LEFT(date, 10) AS cleaned_date, RIGHT(date, 17) AS cleaned_time FROM table SELECT SUBSTR(date, 4, 2) AS day FROM table -- OTHER Select database: use [database]; Show all tables: show tables; Show table structure: describe [table]; Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column]; Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%'; Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%'; Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]'; Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2]; Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; -- INNER, LEFT, RIGHT, AND OUTER JOIN INNER JOIN: returns rows when there is a match in both tables. LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. FULL JOIN: returns rows when there is a match in one of the tables. SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.