SQL Cheat Sheet

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.

Turning Data Into Awesome With sqldf and pandasql

Both R and Python possess libraries for using SQL statements to interact with data frames. While both languages have native facilities for manipulating data, the sqldf and pandasql provide a simple and elegant interface for conducting tasks using an intuitive framework that’s widely used by analysts.

Screenshot from 2015-04-29 11:23:02

Screenshot from 2015-04-29 11:23:44

 

 

 

 

R and sqldf

sqldf("SELECT COUNT(*) FROM df2 WHERE state = 'CA'")
 
  COUNT(*)
1        4
 
sqldf("SELECT 
            df2.firstname, 
            df2.lastname, 
            df1.var1, 
            df2.state 
      FROM df1
      INNER JOIN df2 ON df1.personid = df2.id 
      WHERE df2.state = 'TX'")
 
  firstname lastname  var1 state
1     David    Spade -2.09    TX
2       Joe  Montana  1.16    TX
 
sqldf("SELECT 
            df2.state, 
            COUNT(df1.var1) 
      FROM df1
      INNER JOIN df2 ON df1.personid = df2.id WHERE df1.var1 > 0
      GROUP BY df2.state")
 
   state COUNT(df1.var1)
1     AZ               1
2     CA               1
3     GA               1
4     IL               1
5     NC               1
6     NY               1
7     OK               1
8     SC               1
9     TX               1
10    VT               1

Python and pandasql

import pandasql as ps
 
 
q1 = """SELECT COUNT(*) FROM df2 WHERE state = 'CA'"""
 
print ps.sqldf(q1, locals())
 
   COUNT(*)
0         4
 
q2 = """
    SELECT 
        df2.firstname, 
        df2.lastname, 
        df1.var1, 
        df2.state 
    FROM df1 INNER JOIN df2 ON df1.personid = df2.id 
    WHERE df2.state = "TX";
    """
 
print ps.sqldf(q2, locals())
 
  firstname lastname  var1 state
0     David    Spade -2.09    TX
1       Joe  Montana  1.16    TX
 
q3 = """SELECT 
            df2.state, 
            COUNT(df1.var1) 
      FROM df1
      INNER JOIN df2 ON df1.personid = df2.id WHERE df1.var1 > 0
      GROUP BY df2.state"""
 
print ps.sqldf(q3, locals())
 
  state  COUNT(df1.var1)
0    AZ                1
1    CA                1
2    GA                1
3    IL                1
4    NC                1
5    NY                1
6    OK                1
7    SC                1
8    TX                1
9    VT                1