SQL Cheatsheet

Previous
Next

The SQL cheatsheet provides the basic to advanced level concepts of the SQL programming language. SQL stands for Standard Query Language, which stores and processes information in the relational database. The relational database is simply known as RDBMS, which defines the basics of SQL and supports all database systems, such as MySQL, Oracle, MS SQL Server, IBM DB2, and Microsoft Access. By learning this cheat sheet, one can be able to prepare for interviews and exams. Go through this cheat sheet and learn the SQL programming language.

1. Basic Overview of SQL

In the basic overview of SQL, we learn how to print the table using SQL queries −

SELECT * FROM table_name;

2. SQL Data Types

SQL data types are used to define the type of data that can be stored in a column of a table. Some of the common SQL data types contain −

  • Numeric: INT, FLOAT, DECIMAL
  • Character: CHAR, VARCHAR, TEXT
  • Date/Time: DATE, TIME, DATETIME
  • Binary: BINARY, VARBINARY
  • Boolean: BOOLEAN
CREATE TABLE table_name(column1 datatype, column2 datatype...)

3. SQL Comments

In SQL, comments are used to show the information about the SQL statements. Two ways to represent comments in SQL −

  • Single line comments − This is denoted by double colon(--).
  • Multi−line comments − This is represented using /*...*/.

4. Creating Databases

In SQL, the create database statement is used to create the new database.

CREATE DATABASE name_of_the_database;

5. Dropping Databases

The dropping database is used to delete the existing database.

DROP TABLE table_name;

6. Creating Tables

To create the table in SQL, use the below statements −

CREATE TABLE table_name;

7. Altering Tables

In SQL, an alter statement is used to add, delete, or update the existing statement.

ALTER TABLE table_name
ADD/DROP column_name data_type;

8. Dropping Tables

The drop statement of SQL is used to delete the table permanently.

DROP TABLE table_name;

9. Inserting Data into Tables

In SQL, an insert statement is used to add the rows in the table. Here, we have two ways to insert data into tables −

i. To specify column name with values.

INSERT INTO table_name (col_1, col_2, ...)
VALUES (val_1, val_2, ...);

ii. Adding values to all the columns in the tables.

INSERT INTO table_name
VALUES (val_1, val_2, ...);

10. Updating Data in Tables

In SQL, an update statement is used to change or modify the existing structure of the table.

UPDATE table_name
-- col means column and val means value
SET col_1 = val_1, col_2 = val_2, ...
WHERE condition;

11. Deleting Data from Tables

In SQL, the delete statement is used to remove one or more rows based on the applied condition.

DELETE FROM table_name where apply_condition;

12. Select and Distinct Select

In SQL, SELECT is used to select the data from the database, whereas SELECT DISTINCT is used to return the unique values.

i. The syntax of select in SQL is as follows −

SELECT col_1, col_2, ... FROM table_name;

ii. Below is the syntax of distinct select in SQL is as follows −

SELECT DISTINCT col_1, col_2, ...
FROM table_name;

13. Using WHERE Clause

In SQL, the WHERE clause is used to filter the data records from the table based on specific conditions.

SELECT col_1, col_2, ...
FROM table_name
WHERE condition;

14. Filtering with AND, OR, and NOT Operators

In SQL, the operators and & OR are used with the WHERE clause based on the specific condition. The AND operator is used to filter the data records based on one or more conditions. The OR operator also specifies the condition based on one or more conditions.

i. The syntax of AND operators −

SELECT col_1, col_2, col_3 ...
FROM table_name
WHERE condition_1 AND condition_2 AND condition_3 ...;
  • Note: The AND operator shows the table data if all the conditions are TRUE.

ii. The syntax of OR operators −

SELECT col_1, col_2, col_3 ...
FROM table_name
WHERE condition_1 OR condition_2 OR condition_3 ...;
  • Note: The OR operator shows the table data if any of the conditions are TRUE.

The NOT operator is used to reverse the result based on the SQL condition.

iii. The syntax of NOT operator in SQL is as follows −

SELECT col_1, col_2, ...
FROM table_name
WHERE NOT condition;

15. SQL − IN Operator

In SQL, the IN operator allows users to specify multiple values.

SELECT col_name FROM table_name
WHERE condition IN (condition_val1, condition_val2, condition_val3, ...);

16. SQL − BETWEEN Operator

The BETWEEN operator of SQL filters the table data by specifying a range.

SELECT col_name(s)
FROM table_name
WHERE col_name BETWEEN val_1 AND val_2;

17. SQL − LIKE Operator (Wildcard Characters)

The LIKE operator defines the pattern-based searches in a database. This is used with the WHERE clause to filter the data records.

SELECT col1, col2, ...
FROM table_name
WHERE col_name LIKE pattern;

18. NULL Values and IS NULL

In SQL, NULL values represent the various types of data such as missing, unknown, or undefined.

CREATE TABLE table_name (
    col1 datatype [constraints],
    col2 datatype [constraints],
    ...
);

The IS NULL is a condition that is used to check the null values.

SELECT col1, col2
FROM table_name
WHERE col_name IS NOT NULL;

19. Sorting with ORDER BY

In SQL, the keyword ORDER BY sorts the result based on ascending (ASC) or descending (DESC) order.

SELECT col_1, col_2, col_3 ...
FROM table_name
ORDER BY col_1, col_2, col_3 ... ASC|DESC;

20. Limiting Results with LIMIT

In SQL, the limit restricts the number of rows to return the result of table records.

i. The basic syntax of LIMIT to display the table data.

SELECT * FROM table_name

ii. The syntax of LIMIT clause with ORDER BY clause.

SELECT * FROM table_name
ORDER BY Grade DESC
LIMIT row_count;

21. Grouping Data with GROUP BY

In SQL, the GROUP BY statement is used with the aggregate function. The functions are min(), max(), count(), etc.

SELECT col_1, col_2, aggregate_function(col_3)
FROM table_name
WHERE condition
GROUP BY col_1, col_2
HAVING condition
ORDER BY col_1, col_2;

22. Having Clause

The HAVING clause is used to work with the "WHERE" keyword. The WHERE clause doesn't support the aggregate functions.

SELECT col_1, aggregate_function(col_2)
FROM table_name
WHERE condition
GROUP BY col_1
HAVING aggregate_condition
ORDER BY col_1;

23. SQL Aggregate Functions

In SQL, an aggregate function is a set of calculated values that returns a single value, often used with the GROUP BY clause.

  • MIN(): This returns the minimum value by the column selection.
  • MAX(): This returns the highest value by the column selection.
  • COUNT(): This returns the number of rows in a set and contains the NULL values.
  • SUM(): This returns the total sum of a numerical column.
  • AVG(): This returns the average value of a numerical column.

24. SQL Aliases

In SQL, an alias is created using the "AS" keyword.

SELECT col_name AS alias_name
FROM table_name;

25. Joining Tables

In SQL, joining tables defines the combination of data from two or more tables into a single result.

i. INNER JOIN

The inner join matches the rows from both tables.

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

ii. LEFT JOIN (Outer Join)

In a left join, all the rows from the left table are matched with rows from the right.

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

iii. RIGHT JOIN (Outer Join)

In a right join, all the rows from the right table are matched with rows from the left.

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

iv. FULL JOIN (Outer Join)

In a full join, it returns all the rows when there is a match in either the left or right table. If there is no match, NULL values are returned for the columns of the table that do not have a match.

SELECT col_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.col_name = table2.col_name
WHERE condition;

v. Self Joins

A table joined with itself is known as a self join.

SELECT col_name(s)
FROM table1 A1, table1 A2
WHERE condition;

vi. Cross Joins

In cross joins, all combinations of rows from both tables.

SELECT * FROM table1
CROSS JOIN table2;

26. Union and Union All

In SQL, by default, the UNION operator selects only distinct values. In the case of a UNION ALL statement, it doesn't remove the duplicate from the end result.

i. The syntax of union in SQL −

SELECT col_name(s) FROM table1
UNION
SELECT col_name(s) FROM table2;

ii. The syntax of union all in SQL −

SELECT col_name(s) FROM table1
UNION ALL
SELECT col_name(s) FROM table2;

27. SQL Views

A SQL view is a virtual table based on the result of a query.

CREATE VIEW view_name AS
SELECT col_1, col_2, ...
FROM table_name
WHERE condition;

28. Indexing in SQL

The index in SQL is a special structure that enhances the speed of data searching within database tables.

CREATE INDEX index_name ON table_name;

29. Constraints

Constraints define the rules of data in a table.

i. Primary key

The primary key only contains unique values and does not have the null value.

CREATE TABLE TableName (
    ColName datatype PRIMARY KEY
);

ii. Foreign Key

In SQL, a foreign key is a field in a table that refers to the primary key of another table.

CREATE TABLE TableName (
    ColName datatype,
    FOREIGN KEY (ColName) REFERENCES ParentTable(ParentCol)
);

iii. NOT NULL

This NOT NULL constraint is a data integrity rule that ensures a column cannot have null values.

CREATE TABLE TableName (
    ColName datatype NOT NULL
);

iv. Unique Constraint

The UNIQUE constraint refers to all values in a column being unique. This means no two rows can have the same value for the specified column(s).

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

v. Check Constraint

The CHECK constraint refers to all values in a column meeting a specific condition.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2) CHECK (Price > 0)
);

vi. Default Values

The DEFAULT constraint provides a default value for a column when no value is specified.

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Department VARCHAR(100) DEFAULT 'NA'
);

In the above section of code, if no value is specified for the Department column, the default value 'NA'.

30. SQL Transactions (BEGIN, COMMIT, ROLLBACK)

Transactions are used to group multiple operations into a single logical unit of work, ensuring data consistency. Use BEGIN to start, COMMIT to save, and ROLLBACK to undo changes.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT;

31. Using Triggers in SQL

Triggers are special stored procedures that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

CREATE TRIGGER trigger_name
ON table_name
[AFTER|INSTEAD OF|BEFORE] [INSERT|UPDATE|DELETE]
AS
BEGIN
    -- Trigger logic here
END;

32. Stored Procedures

Stored procedures are precompiled SQL statements that can be executed multiple times and improve the code's reusability.

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype, ...
AS
BEGIN
    -- SQL statements
END;

33. Functions in SQL

In SQL, functions are reusable blocks of code that return a value based on given parameters.

CREATE FUNCTION function_name (@parameter1 datatype, @parameter2 datatype, ...)
RETURNS return_datatype
AS
BEGIN
    -- Function logic
    RETURN return_value;
END;

34. Using CASE Statements

CASE statements allow conditional logic within SQL queries that is similar to if-else statements in programming.

SELECT col1,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ...
           ELSE result
       END AS alias_name
FROM table_name;

35. SQL Operators

SQL operators are symbols or keywords used to perform operations on data. Some of the common examples are arithmetic, comparison, and logical operators.

SELECT * FROM Users WHERE col1 >= value AND col2 = 'str_value'/int_value/...;
Print Page
Previous
Next
Advertisements