SQLData Science

SQL Cheat Sheet (Basics to Advanced SQL Cheat Sheet)

Last Updated: 20th July, 2024

Arunav Goswami

Data Science Consultant at almaBetter

Master SQL with this cheat sheet, covering querying, commands, filtering, aggregation and basics to advance. Perfect for coding interviews and tech job prep

SQL (Structured Query Language) is the backbone of database management systems. Whether you’re preparing for an interview, writing complex queries, or looking to safeguard your applications against SQL injection, this SQL cheat sheet will provide you with a comprehensive guide to SQL Joins, commands, and advanced techniques.

SQL Commands Cheat Sheet

Basic SQL Commands:

SELECT: Retrieves data from the database.

SELECT * FROM table_name;
SELECT column1, column2 FROM table_name;

INSERT: Adds new data to the database.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Modifies existing data in the database.

UPDATE table_name SET column1 = value1 WHERE condition;

DELETE: Removes data from the database.

DELETE FROM table_name WHERE condition;

DDL SQL Commands:

CREATE TABLE: Creates a new table.

CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
...
);

ALTER TABLE: Modifies an existing table.

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;

DROP TABLE: Deletes a table.

DROP TABLE table_name;

SQL Queries Cheat Sheet

Filtering and Sorting

WHERE: Filter records

SELECT column1, column2 FROM table_name WHERE condition;

ORDER BY: Sort the result set

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

LIMIT: Limit the number of rows returned

SELECT column1, column2 FROM table_name LIMIT number;

Grouping

GROUP BY: Group rows that have the same values in specified columns into summary rows

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

HAVING: Filter records that work on grouped data

SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > value;

Aliases

AS: Rename a column or table with an alias

SELECT column_name AS alias_name FROM table_name;

Aggregate Functions

COUNT: Count the number of rows

SELECT COUNT(column_name) FROM table_name;

SUM: Calculate the sum of a numeric column

SELECT SUM(column_name) FROM table_name;

AVG: Calculate the average value of a numeric column

SELECT AVG(column_name) FROM table_name;

MAX: Find the maximum value in a column

SELECT MAX(column_name) FROM table_name;

MIN: Find the minimum value in a column

SELECT MIN(column_name) FROM table_name;

SQL Joins Cheat Sheet

SQL joins are used to combine rows from two or more tables based on a related column.

INNER JOIN: Returns records with matching values in both tables.

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

LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table.

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

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table.

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

FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table.

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

Advanced SQL Cheat Sheet

Complex Queries and Functions:

Subqueries: A query within another query.

SELECT column1 FROM table_name WHERE column2 IN (SELECT column2 FROM table_name WHERE condition);

Common Table Expressions (CTEs):

WITH CTE_name (column1, column2) AS (
SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM CTE_name;

Window Functions: Perform calculations across a set of table rows related to the current row.

SELECT column1,
ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3) AS row_num
FROM table_name;

Indexes

CREATE INDEX: Create an index on a table

CREATE INDEX index_name ON table_name (column1, column2);

DROP INDEX: Delete an index

DROP INDEX index_name;

Views

CREATE VIEW: Create a new view

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

DROP VIEW: Delete a view

DROP VIEW view_name;

SQL Injection Cheat Sheet

SQL Injection is a code injection technique that exploits a vulnerability in an application’s software by manipulating SQL queries. It allows attackers to access, modify, or delete data from the database.

Preventing SQL Injection:

Use Prepared Statements and Parameterized Queries:

-- Example in Python using SQLite
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

Stored Procedures:

CREATE PROCEDURE GetUser
@username VARCHAR(50)
AS
BEGIN
SELECT * FROM users WHERE username = @username
END

Input Validation and Sanitization:

Always validate and sanitize user inputs.

SQL Cheat Sheet for Interview Preparation

Key Topics to Review:

Basic SQL Syntax and Commands: Understand the basic commands like SELECT, INSERT, UPDATE, DELETE.
SQL Joins: Be prepared to explain and write queries using different types of joins.
Aggregate Functions: Be familiar with COUNT, SUM, AVG, MIN, MAX.
Normalization and Denormalization: Understand the principles of database design.
Indexing: Know how and why to use indexes to optimize query performance.
Transactions: Understand the concept of transactions and ACID properties.

Useful Tips

Always back up your database before running DELETE or UPDATE statements.
Use JOIN instead of subqueries for better performance.
Always specify the LIMIT clause when testing queries to avoid large data fetches.
Use indexes wisely; while they speed up read operations, they can slow down write operations.

By mastering these SQL concepts and commands, you’ll be well-prepared for interviews and equipped to handle advanced SQL queries. This SQL cheat sheet is designed to be a quick reference guide for both beginners and experienced SQL users.