SQL is one of the most powerful programming interfaces in the market. It enables you to ask a database for specific data in an interface optimized for efficient data access. However, writing SQL can be a cumbersome process, especially as it is not very similar to existing programming languages or business workflows such as Excel. This piece aims to explain the basics of SQL in a fun cheat sheet form that enables you to come away with a basis for writing your own SQL.

Finding SQL Meaning: A SQL Cheat Sheet Overview

‍

The below section helps to define the meaning of different terms in SQL and should help you find meaning in the phrases that, on first glance, may seem confusing!

Basics

SELECT: Retrieve data from one or more tables.some text

• SELECT column1, column2 FROM table_name;
• SELECT * FROM table_name; (Selects all columns)

WHERE: Specify criteria to filter rows.some text

• SELECT column1 FROM table_name WHERE condition;

ORDER BY: Sort the result set.some text

• SELECT column1 FROM table_name ORDER BY column1 ASC|DESC;

SQL Meaning of CRUD Operations

CREATE TABLE: Create a new table.some text

• CREATE TABLE table_name (column1 datatype constraint, column2 datatype constraint, ...);

INSERT INTO: Insert new data into a table.some text

• INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;

UPDATE: Update existing data.some text

• UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE: Delete data.some text

• DELETE FROM table_name WHERE condition;

Working with Tables

ALTER TABLE: Modify an existing table structure.some text

• ALTER TABLE table_name ADD column_name datatype; (Add a new column)
• ALTER TABLE table_name DROP COLUMN column_name; (Remove a column)

DROP TABLE: Delete a table and its data.some text

• DROP TABLE table_name;

SQL Meaning of Constraints

PRIMARY KEY: Uniquely identifies each row in a table.

FOREIGN KEY: A key used to link two tables together.

NOT NULL: Ensures that a column cannot have a NULL value.

UNIQUE: Ensures all values in a column are different.

CHECK: Ensures that all values in a column satisfy a specific condition.

DEFAULT: Sets a default value for a column when no value is specified.

SQL Meaning of Functions and Aggregates

COUNT: Counts the number of rows.some text

• SELECT COUNT(column_name) FROM table_name WHERE condition;

SUM: Sums up the numeric values.some text

• SELECT SUM(column_name) FROM table_name WHERE condition;

AVG: Calculates the average value.some text

• SELECT AVG(column_name) FROM table_name WHERE condition;

MIN/MAX: Finds the minimum/maximum value.some text

• SELECT MIN(column_name) FROM table_name;
• SELECT MAX(column_name) FROM table_name;

SQL Meaning of Joins

INNER JOIN: Returns rows when there is at least one match in both tables.some text

• SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table.some text

• SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table.some text

• SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.some text

• SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

Miscellaneous

GROUP BY: Groups rows sharing a property so that an aggregate function can be applied to each group.some text

• SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

HAVING: Specify a filter condition for groups created by GROUP BY.some text

• SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > 1;

CASE: if-then-else logic in SQLsome text

• Select Case when {condition} then column1 else column2 end as genericColumn FROM table_name;

LIKE: similarity testing in SQLsome text

• Select * FROM table_name where column1 LIKE ‘J%’;
  —- matches any column1 starting with J

COALESCE: alternative value if nullsome text

• Select coalesce(column1, ‘Unknown’) FROM table_name;

IN: Checks if a value matches any value in a list.some text

• SELECT * FROM table_name WHERE column_name IN ('value1', 'value2');

UNION: Combines results from multiple SELECT statements into a single result set, removing duplicates.some text

• SELECT column_name FROM table1 UNION SELECT column_name FROM table2;

CAST: Converts a value from one data type to another.some text

• SELECT CAST(column_name AS datatype) FROM table_name;

DATE MANIPULATION: SQL DATABASE SPECIFIC

‍

Take Home SQL Cheat Sheet

Below is a compact version of the above cheat sheet pieces. If you are new, intermediate, or even an expert in SQL, the below cheat sheet can be a helpful tool to have next to you as you write SQL.

SQL Cheat Sheet Conclusion

SQL Cheat sheets can be incredibly helpful for getting started in writing SQL. Once we have written the basics for table creation, manipulating, and retrieval, the next step is to optimize that experience. This piece hopefully was a good overview on that first step of the SQL writing and optimization process. With these tools, hopefully you are able to write your own SQL and begin the process of data extraction in your own organization.