Aug 24, 2023

Saksham Niroula

SQL Cheat Sheet

While SQL is a powerful data manipulation and management tool, it can also be quite complex. So, Whether you're just starting with SQL or looking to refresh your knowledge, this cheat sheet will be a valuable resource for quickly finding the information you need.

SQL Cheat Sheet

SQL (Structured Query Language) is the most widely-used language for interacting with relational databases.

This cheat sheet will cover some of the most common and essential SQL commands, statements, and concepts like constraints, keys, clauses, joins, and operators.

SQL Tables

A table is structured data in a database. It comprises rows (records) and columns (fields).

Creating a Table

To create a table in SQL, you use the CREATE TABLE statement. The basic syntax for creating a table is:

CREATE TABLE table_name (
	column_name1 data_type(size),
	column_name2 data_type(size),
	...
);

For example, to create a table called "employees" with columns "id", "name", and "salary", you would use the following SQL statement:

CREATE TABLE employees (
	id INTEGER PRIMARY KEY,
	name VARCHAR(255),
	salary NUMERIC(10,2)
);

Altering Tables

To modify an existing table in SQL, you use the ALTER TABLE statement. The basic syntax for altering a table is

ALTER TABLE table_name
{ ADD | DROP } [ COLUMN ] column_name data_type(size);

For example, to add a new column called "department" to the "employees" table, you would use the following SQL statement:

ALTER TABLE employees
ADD COLUMN department VARCHAR(255);

To drop a column from a table, you would use the DROP keyword instead of ADD:

ALTER TABLE employees
DROP COLUMN department;

Renaming Tables

To rename an existing table in SQL, you use the RENAME TABLE statement. The basic syntax for renaming a table is:

RENAME TABLE old_table_name TO new_table_name;

For example, to rename the "employees" table to "staff", you would use the following SQL statement:

RENAME TABLE employees TO staff;

Dropping Tables

To delete a table in SQL, you use the DROP TABLE statement. The basic syntax for dropping a table is:

DROP TABLE table_name;

For example, to delete the "employees" table, you would use the following SQL statement:

DROP TABLE employees;

SQL Constraints

While creating tables, you can specify constraints on the columns. Some typical constraints include:

NOT NULL: ensures that a column cannot contain a null value
UNIQUE: ensures that all values in a column are unique
PRIMARY KEY: a unique identifier for each row in a table
FOREIGN KEY: a reference to the primary key of another table

For example, to create a table with a primary key and a unique constraint, you would use the following SQL statement:

CREATE TABLE employees (
	id INTEGER PRIMARY KEY,
	email VARCHAR(255) UNIQUE,
	name VARCHAR(255),
	salary NUMERIC(10,2)
);

SQL DataTypes

Each column in a table has a specific data type, which determines the kind of data that can be stored in that column. Some common SQL data types include:

INTEGER: a whole number
VARCHAR: a variable-length string
DATE: a date in the format YYYY-MM-DD
NUMERIC: a decimal number

SQL Commands

SQL uses a variety of commands to perform different actions on a database. Some common SQL commands include

SELECT

We use SELECT to retrieve data from a database. The basic syntax for a SELECT statement is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

To retrieve all rows from the "employees" table, you can use the wildcard character (*):

SELECT * FROM employees;

To retrieve specific columns, specify the column names instead of using the wildcard (*):

SELECT name, salary FROM employees;

INSERT

We use INSERT to add new data to a database. The basic syntax for an INSERT statement is:

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

For example, to insert a new row into the "employees" table, you would use the following SQL statement:

INSERT INTO employees (name, salary)
VALUES ('John Smith', 60000);

UPDATE

We use UPDATE to modify existing data in a database.

The basic syntax for an UPDATE statement is:

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

For example, to update the salary of an employee with the name "John Smith" in the "employees" table, you would use the following SQL statement:

UPDATE employees
SET salary = 65000
WHERE name = 'John Smith';

DELETE

We can use the DELETE command to delete data from a database.

The basic syntax for a DELETE statement is:

DELETE FROM table_name WHERE condition;

For example, to delete all rows from the "employees" table, you would use the following SQL statement:

DELETE FROM employees;

To delete rows that meet a specific condition, you can use a WHERE clause:

DELETE FROM employees WHERE salary < 50000;

Important SQL Keywords

SQL uses a variety of keywords to perform different actions on a database. Some important SQL keywords include:

Keyword	Functions
SELECT	Retrieves data from a database
FROM	Specifies table(s) from which to retrieve data
WHERE	Specifies a condition for data retrieval
AND/OR	Combines multiple conditions in a query
GROUP BY	Groups data based on one or more columns
HAVING	Specifies a condition for the groups created
ORDER BY	Sorts the results of a query
LIMIT	Limits the number of rows returned by a query
OFFSET	Specifies the starting point for the rows returned by a query
AS	Gives a column or table a temporary name
DISTINCT	Returns only unique values
COUNT	Counts the number of rows in a table
SUM	Calculates the sum of values in a column
AVG	Calculates the average of values in a column
MIN	Find the minimum value in a column
MAX	To find the maximum value in a column

SQL Operators

SQL uses various operators to perform different data operations. Some common SQL operators include

Operators	Tasks
=	Tests for equality
<>	Tests for non-equality
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
IN	Tests if a value is in a list of values
BETWEEN	Tests if a value is within a range
LIKE	Searches for a specific pattern in a string
IS NULL	Tests if a value is null
EXISTS	Tests if a subquery returns any rows

Keys in SQL

In SQL, a key is a field in a table that is used to identify a specific row. There are several types of keys in SQL like:

Primary Key

To create a primary key in SQL, we can use the PRIMARY KEY constraint. The basic syntax for creating a primary key is:

CREATE TABLE table_name (
column_name1 data_type(size) PRIMARY KEY,
...
);

For example, to create a table with a primary key on the "id" column, you would use the following SQL statement:

CREATE TABLE employees (
	id INTEGER PRIMARY KEY,
	name VARCHAR(255),
	salary NUMERIC(10,2)
);

Foreign Key

To create a foreign key in SQL, we can use the FOREIGN KEY constraint. The basic syntax for creating a foreign key is:

CREATE TABLE table_name (
column_name1 data_type(size),
...
FOREIGN KEY (column_name1) REFERENCES other_table_name(other_column_name)
);

For example, to create a table with a foreign key on the "department_id" column that references the "id" column in the "departments" table, you would use the following SQL statement:

CREATE TABLE employees (
	id INTEGER PRIMARY KEY,
	department_id INTEGER,
	name VARCHAR(255),
	salary NUMERIC(10,2),
	FOREIGN KEY (department_id) REFERENCES departments(id)
);

Candidate Key

We can use the UNIQUE constraint to create a candidate key in SQL. The basic syntax for creating a candidate key is:

CREATE TABLE table_name (
	column_name1 data_type(size) UNIQUE,
	...
);

For example, to create a table with a candidate key on the "email" column, you would use the following SQL statement:

CREATE TABLE employees (
	id INTEGER PRIMARY KEY,
	email VARCHAR(255) UNIQUE,
	name VARCHAR
};

Functions in SQL

SQL provides a number of built-in functions that can be used to perform operations on data. Some common SQL functions include:

Functions	Tasks
COUNT	Counts the number of rows in a table
SUM	Calculates the sum of values in a column
AVG	Calculates the average of values in a column
MIN	Finds the minimum value in a column
MAX	Find the maximum value in a column
CONCAT	Concatenate strings
LENGTH	Find the length of a string
UPPER/LOWER	Convert a string to uppercase/lowercase
SUBSTR	Extract a substring from a string
TRIM	To remove leading and trailing spaces from a string
NOW()	To get the current date and time

Joins in SQL

In SQL, a join is a way to combine data from multiple tables based on a related column between them.

INNER JOIN

The INNER JOIN keyword returns only the rows with matching values in both tables.

Here's the syntax:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN

The LEFT JOIN or LEFT OUTER JOIN keyword (which are the same) is used to return all the rows from the left table and the matching rows from the right table. NULL values are returned for non-matching right table rows.

Here's the syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN

The RIGHT JOIN, or RIGHT OUTER JOIN keyword, returns all the rows from the right table and the matching rows from the left table. It returns the NULL values for non-matching left table rows.

Here's the syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL JOIN

The FULL JOIN, or FULL OUTER JOIN keyword, returns all rows from both tables, with NULL values for non-matching rows.

The basic syntax for a FULL JOIN is:

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Triggers in SQL

In SQL, a trigger is a set of instructions that are automatically executed in response to a specific event, such as an INSERT, UPDATE, or DELETE operation on a table.

We can use triggers to enforce data integrity and maintain data consistency.

Creating Triggers

The CREATE TRIGGER statement creates a new trigger. The basic syntax for creating a trigger is:

CREATE TRIGGER trigger_name
AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
-- trigger instructions
END;

Dropping a Trigger

The DROP TRIGGER statement is used to delete an existing trigger. The basic syntax for dropping a trigger is:

DROP TRIGGER trigger_name;

SQL Stored Procedures

In SQL, a stored procedure is a pre-compiled collection of SQL statements that can be executed with a single call.

Stored procedures perform complex operations, such as data validation or complex business logic, in a single call.

Creating Procedures

The CREATE PROCEDURE statement returns a new stored procedure. Here is the basic syntax for creating procedure:

CREATE PROCEDURE procedure_name
(
	IN/OUT/INOUT parameter_name data_type,
	...
)
BEGIN
-- SQL statements
END;

Dropping Procedures

The DROP PROCEDURE statement is used to delete an existing stored procedure. The basic syntax for dropping a stored procedure is:

DROP PROCEDURE procedure_name;

For example, to drop the stored procedure "get_employee_info" created before, you would use the following SQL statement:

DROP PROCEDURE get_employee_info;

Executing Procedures

The EXEC statement is used to execute a stored procedure. The basic syntax for running a stored procedure is

EXEC procedure_name parameter_value, ...;