Learning SQL can often feel overwhelming due to its extensive range of commands, functions, and syntax rules.
To simplify this process and make SQL more accessible, we’ve created a comprehensive chart designed to streamline your learning experience.
This chart serves as an all-in-one reference tool, helping you grasp and apply SQL concepts more effectively.
With this chart, you can quickly access key SQL commands, functions, and operators.
Boost Your Website Speed!
If you want your website to run as fast as ours, consider trying Cloudways. Their powerful cloud infrastructure and optimized stack deliver exceptional performance. Free migration!It eliminates the need to memorize every detail, allowing you to focus on learning and applying SQL more efficiently.
Table of Contents
SQL Commands
- Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE
- Data Definition Language (DDL): CREATE, ALTER, DROP
- Data Control Language (DCL): GRANT, REVOKE
SQL Functions
- String Functions: LENGTH(), CONCAT(), SUBSTRING()
- Date Functions: NOW(), CURDATE(), DATE_FORMAT()
- Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN()
SQL Operators
- Arithmetic Operators: +, -, *, /
- Comparison Operators: =, !=, <, >, <=, >=
- Logical Operators: AND, OR, NOT
SQL Clauses
- WHERE: Filters records based on conditions
- GROUP BY: Groups rows sharing a property
- ORDER BY: Sorts the result set
- JOIN: Combines rows from multiple tables
SQL Constraints
- NOT NULL: Ensures a column cannot have NULL values
- UNIQUE: Ensures all values in a column are distinct
- PRIMARY KEY: Uniquely identifies each row in a table
- FOREIGN KEY: Maintains referential integrity between tables
- CHECK: Ensures values satisfy specific conditions
Querying Data From Table ✔️
These code blocks are used to access the data from a single table. Certainly, it has a SELECT and FROM statement, but along with that there are multiple use-cases as you can see below.
Query data in columns column_1, column_2 from a table table_1
SELECT column_1, column_2
FROM table_1;
Query all rows and columns from a table table_1
SELECT *
FROM table_1;
Query data and filter rows with a condition
SELECT column_1, column_2
FROM table_1
WHERE condition;
Query distinct rows from a table
SELECT DISTINCT column_1
FROM table_1
WHERE condition;
Sort the result set in ascending or descending order
SELECT column_1, column_2
FROM table_1
ORDER BY column_1 DESC;
-- The default order is ascending, the keyword DESC in ORDER BY stands for descending
Skip offset of rows and return the next n rows
SELECT column_1, column_2
FROM table_1
ORDER BY column_1
LIMIT n OFFSET offset;
Group rows using an aggregate function
SELECT column_1, aggregate(column_2)
FROM table_1
GROUP BY column_1;
Filter groups using the HAVING clause
SELECT column_1, aggregate(column_2)
FROM table_1
GROUP BY column_1
HAVING condition;
Querying From Multiple Tables ✔️
These queries are used to pull the data from multiple tables by joining them together. Therefore, these queries involve joins as given below,
Inner join tables: table_1 and table_2
SELECT column_1, column_2
FROM table_1
INNER JOIN table_2 ON condition;
Left join tables: table_1 and table_2
SELECT column_1, column_2
FROM table_1
LEFT JOIN table_2 ON condition;
Right join tables: table_1 and table_2
SELECT column_1, column_2
FROM table_1
RIGHT JOIN table_2 ON condition;
Perform full outer join
SELECT column_1, column_2
FROM table_1
FULL OUTER JOIN table_2 ON condition;
Produce a Cartesian product of rows in tables
SELECT column_1, column_2
FROM table_1
CROSS JOIN table_2;
Produce a Cartesian product of rows in tables without cross join
SELECT column_1, column_2
FROM table_1, table_2;
Join table to itself using INNER JOIN clause
SELECT column_1, column_2
FROM table_1 A
INNER JOIN table_1 B ON condition;
Using SQL Operators ✔️
These queries use mathematical and logical operators to extract data based on a condition as shown below.
Combine rows from two queries
SELECT column_1, column_2
FROM table_1
UNION ALL
SELECT column_1, column_2
FROM table_2;
Return the intersection of two queries
SELECT column_1, column_2
FROM table_1
INTERSECT
SELECT column_1, column_2
FROM table_2;
Subtract a result set from another result set
SELECT column_1, column_2
FROM table_1
MINUS
SELECT column_1, column_2
FROM table_2;
Query rows using pattern matching %, _
SELECT column_1, column_2
FROM table_1
WHERE column_1 LIKE pattern;
-- LIKE: Case-sensitive search for a pattern with a wildcard operator (%)
-- NOT LIKE: Returns the rows which are not matching with Case-sensitive pattern
Query rows in a list
SELECT column_1, column_2
FROM table_1
WHERE column_1 IN (value_list);
-- IN: Returns the rows when Column_1 value is present in value_list
-- NOT IN: Returns the rows when Column_1 value is not present in value_list
Query rows between two values: low and high
SELECT column_1, column_2
FROM table_1
WHERE column_1 BETWEEN low AND high;
Check if values in a table are NULL or not
SELECT column_1, column_2
FROM table_1
WHERE column_1 IS NULL;
-- NULL: Returns the rows when Column_1 value is NULL
-- NOT NULL: Returns the rows when Column_1 value is not NULL
Managing Tables ✔️
These queries are used to perform operations for managing the tables such as create, update, delete, and many more as shown below.
Create a new table with three columns: column_1, column_2, column_3
CREATE TABLE table_1 (
column_1 INT PRIMARY KEY,
column_2 VARCHAR NOT NULL,
column_3 INT DEFAULT 0
);
Delete the table from the database
DROP TABLE table_1;
Add a new column — column_1 — to the table
ALTER TABLE table_1 ADD column_1;
Drop column — column_1 — from the table
ALTER TABLE table_1 DROP COLUMN column_1;
Add a constraint
ALTER TABLE table_1 ADD CONSTRAINT constraint_name;
Drop a constraint
ALTER TABLE table_1 DROP CONSTRAINT constraint_name;
Rename a table from table_1 to table_2
ALTER TABLE table_1 RENAME TO table_2;
Rename column column_1 to column_2
ALTER TABLE table_1 RENAME COLUMN column_1 TO column_2;
Remove all data in a table
TRUNCATE TABLE table_1;
Using SQL Constraints ✔️
These queries are used to specify rules for the data in a table. Constraints limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. This includes statements as below,
Set column_1 and column_2 as a primary key
CREATE TABLE table_1(
column_1 INT, column_2 INT, column_3 VARCHAR,
PRIMARY KEY (column_1, column_2)
);
Set column — column_2 — as a foreign key
CREATE TABLE table_1(
column_1 INT PRIMARY KEY,
column_2 INT,
FOREIGN KEY (column_2) REFERENCES table_2(column_2)
);
Ensure column_1 > 0 and values in column_1 >= column_2
CREATE TABLE t(
column_1 INT, column_2 INT,
CHECK(column_1 > 0 AND column_1 >= column_2)
);
Set values in column — column_2 — not NULL
CREATE TABLE t(
column_1 INT PRIMARY KEY,
column_2 VARCHAR NOT NULL
);
Modifying Data ✔️
These queries are used to deal with already present tables. This includes operations such as inserting, changing, and deleting values as shown below,
Insert one row into a table
INSERT INTO table_1(column_list)
VALUES(value_list);
Insert multiple rows into a table
INSERT INTO table_1(column_list)
VALUES (value_list),
(value_list), ...;
Insert rows from table_2 into table_1
INSERT INTO table_1(column_list)
SELECT column_list
FROM table_2;
Update the new value in the column — column_1 — for all rows
UPDATE table_1
SET column_1 = new_value;
Update values in the columns column_1, column_2 that match the condition
UPDATE table_1
SET column_1 = new_value,
column_2 = new_value
WHERE condition;
Delete all data in a table
DELETE FROM table_1;
Delete subset of rows in a table
DELETE FROM table_1
WHERE condition;
Managing Views ✔️
A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database. These queries are used to perform operations to manage views as shown below,
Create a new view that consists of column_1 and column_2
CREATE VIEW view_1(column_1, column_2)
AS
SELECT column_1, column_2
FROM table_1;
Create a new view with the check option
CREATE VIEW view_1(column_1, column_2)
AS
SELECT column_1, column_2
FROM table_1
WITH [CASCADED | LOCAL] CHECK OPTION;
Create a recursive view
CREATE RECURSIVE VIEW view_1
AS
select-statement -- anchor part
UNION ALL
select-statement; -- recursive part
Create a temporary view
CREATE TEMPORARY VIEW view_1
AS
SELECT column_1, column_2
FROM table_1;
Delete a view
DROP VIEW view_name;