SQL Cheatsheet


CREATE TABLE: Create a new table with a primary key and a foreign key

CREATE TABLE table1 (
    column1_table1 INT PRIMARY KEY,
    column2_table1 VARCHAR(255) NOT NULL,
    column3_table1 INT,
    FOREIGN KEY (column3_table1) REFERENCES table2(column1_table2)
);

Basic Commands

SELECT: Retrieve data from a database

SELECT column1, column2 
FROM table_name;

SELECT * 
FROM table_name; -- Select all columns

INSERT: Insert data into a table

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

UPDATE: Update existing data within a table

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

DELETE: Delete data from a table

DELETE FROM table_name 
WHERE condition;

Filtering and Sorting

WHERE: Filter records

SELECT column1, column2
FROM table_name
WHERE condition;

AND, OR, NOT: Combine multiple conditions

SELECT * FROM table_name
WHERE condition1 AND condition2;

SELECT * FROM table_name
WHERE condition1 OR condition2;

SELECT * FROM table_name
WHERE NOT condition;

ORDER BY: Sort the result set

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC; -- Ascending order

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC; -- Descending order

Aggregate Functions

COUNT(): Count the number of rows

SELECT COUNT(column_name) 
FROM table_name;

SUM(): Calculate the sum

SELECT SUM(column_name) 
FROM table_name;

AVG(): Calculate the average

SELECT AVG(column_name) 
FROM table_name;

MIN() and MAX(): Find the minimum and maximum value

SELECT MIN(column_name) 
FROM table_name;

SELECT MAX(column_name) 
FROM table_name;

Grouping

GROUP BY: Group rows that have the same values

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

HAVING: Filter groups

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

Joins

INNER JOIN: Return records with matching values in both tables

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field;

LEFT JOIN: Return all records from the left table, and the matched records from the right table

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field;

RIGHT JOIN: Return all records from the right table, and the matched records from the left table

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;

FULL JOIN: Return all records when there is a match in either left or right table

SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_field = b.common_field;

Subqueries

Subquery in SELECT: Use a query inside another query

SELECT column1,
       (SELECT COUNT(*) FROM table2 WHERE table2.column = table1.column) as count
FROM table1;

Subquery in WHERE: Use a query in a WHERE clause

SELECT column1
FROM table_name
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);

String Functions

CONCAT(): Concatenate two or more strings

SELECT CONCAT(column1, ' ', column2) AS new_column 
FROM table_name;

SUBSTRING(): Extract characters from a string

SELECT SUBSTRING(column_name, start_position, length) FROM table_name;

UPPER() and LOWER(): Convert to uppercase or lowercase

SELECT UPPER(column_name) 
FROM table_name;

SELECT LOWER(column_name) 
FROM table_name;

REPLACE(): Replace occurrences of a specified string

SELECT REPLACE(column_name, 'old_string', 'new_string') FROM table_name;

Date Functions

CURRENT_DATE(): Get the current date

SELECT CURRENT_DATE();

DATE_ADD(): Add an interval to a date

SELECT DATE_ADD(date_column, INTERVAL 7 DAY) 
FROM table_name;

DATEDIFF(): Calculate the difference between two dates

SELECT DATEDIFF(end_date, start_date) 
FROM table_name;

DATE_FORMAT(): Format a date

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') 
FROM table_name;

Case Statements

CASE: Create conditional logic

SELECT column1,
       CASE
         WHEN condition1 THEN 'Result1'
         WHEN condition2 THEN 'Result2'
         ELSE 'Result3'
       END as alias_name
FROM table_name;

Explanation: the CASE statement is a conditional expression that allows you to create new calculated fields based on different conditions. The query above specifies that when condition1 is true, the value 'Result1' will be returned. If the condition2 is true, the ‘Result2’ will be returned. If none of the WHEN conditions are met, the ELSE part will return 'Result3'. Where the CASE expression ends, the result is given an alias (column name) called alias_name.