SQL Cheat Sheet

By | March 18, 2022
SQL Cheat Sheet PDF

SQL is the popular database language. It helps to manage relational database management systems, such as MySQL and PostgreSQL. There are several SQL commands that a developer must know about while working with relational databases. Obviously, it is not possible to remember all of them. This SQL cheat sheet is meant to serve as a quick reference for the most used SQL commands.

Before we get started with the SQL cheat sheet, the following are some important points to take note of:

  • SQL stands for Structured Query Language.
  • A database is a collection of data.
  • In the database, we collect data in an organized way.
  • A database that stores data in the form of tables is known as a relational database.
  • SQL is used to create, update, retrieve and delete relational databases.

SQL databases are also known as relational databases. There are many popular SQL databases, and each one has its advantages and disadvantages. Some of the most popular relational databases are:

  • MySQL
  • SQLite
  • PostgreSQL
  • Oracle Database

SQL Cheat Sheet

SQL Commands

In SQL, we use the following types of commands:

  1. DDL (Data Definition Language): DDL commands are used to define the frame of a database, and these are CREATE, ALTER, and DROP.
  2. DML (Data Manipulation Language): DML commands are used to modify or edit the existing database, and these are INSERT, UPDATE, and DELETE.
  3. DCL (Data Control Language): DCL commands deal with the permissions of the database, and these are GRANT and REVOKE.
  4. DQL (Data Query Language)
  5. DTL (Data Transfer Language)

However, among these types of SQL commands, the first three are the most widely used, i.e., DDL, DML, and DCL.

SQL Datatypes

Data types are the types of data supported by SQL databases. We should know SQL data types before we start storing data in relational databases. The following table enumerates the various data types supported by SQL:

Vamware
Data Type Description
int Integer values
decimal Numeric values with decimal points.
numeric Numerical values.
float Real numbers or numeric values.
DateTime Data and time.
char(n) Characters with n length.
varchar(n) Character values of variable length.
text Strings or characters.
bit 0 or 1.
image Images.
real Real numbers.
binary Binary values.
timestamp Timestamp (time in seconds).
table Temporary tables.
XML XML values.

Creating a Table

To create a new table in a relational database, we use the CREATE TABLE command.

Code Example:

Vamware
CREATE TABLE STUDENT
(  roll_no int,
name varchar(30),
phone varchar(11)
);

SQL Constraints

In SQL, there are some rules that we need to follow to store data in an organized way, and SQL commands can work on it. We use constraints when we create a table and pass it along with the data types. Here are the SQL constraints we use while creating a table:

  • Primary key: It makes sure that all the data of the primary key constraint have unique values and no null values.
  • Not Null: There would be no null value in that column.
  • Check: It makes sure that all the values of the column satisfy the condition.
  • Foreign Key: Uniquely identifies a record in another table.
  • Default: It gives a default value to the column if the user does not give a value.
  • Unique: It makes sure that each value of the column is different.

Syntax to Write a Constraint:

CREATE TABLE table_name (
    column_name datatype constraint,
    column2_name datatype constraint,
    column3_name datatype constraint,
);

Insert Data Into a Table

INSERT…..VALUES:

Using the INSERT and VALUES commands, we can enter data into a table.

Code Example:

Vamware
INSERT INTO Student VALUES (1,'Ash’,’9999991119’);

Multiple Insertion of Data Using a Single INSERT Command:

INSERT INTO Student
VALUES (2,’Kakashi’,’2020202020’),
        (03,‘Naruto’,’3030303030’),
        (4,‘sam,’4040404040’),
        (5,‘Goku’,’5050505050’),
        (6,‘Gohan’,’6060606060’),
        (7,‘sam,’1010101010’);

Copy Using the Insert Command:

INSERT INTO Student
SELECT * FROM Student_backup_Table
WHERE roll_no < 30; 

SELECT

The SELECT command is used to retrieve the attributes (columns with their values) of the table.

SELECT roll_no, name
From Student;

From

From tells from which table the select command will retrieve the attributes and values. There could be more than one table associated with the FROM statement.

Code Example:

SELECT roll_no, name
From Student ,  Student_backup_Table;

DISTINCT

DISTINCT is used along with the SELECT command, and it is used to eliminate duplicate values.

Code Example:

SELECT DISTINCT name
FROM Student;

ALTER

With the ALTER command, we can perform various operations such as adding new columns, modifying the data type, and adding or removing constraints.

Code Example:

To add a new column in the table:

ALTER TABLE Student,
ADD rank;

For deleting a column from the table:

ALTER TABLE Student
DROP COLUMN rank;

To change the data type of a table:

ALTER TABLE Student
ALTER COLUMN Name varchar(100);

Rename the table:

With the help of the RENAME command, we can rename the table.

Code Example:

RENAME TABLE Student TO Students;

DROP TABLE

The DROP TABLE command is used to delete the complete table.

Code Example:

DROP TABLE Students;

TRUNCATE TABLE

The SQL command is used to delete the table content. If we use the truncate table command, it will not delete the table. Instead, it just deletes the content or data from the table.

Code Example:

TRUNCATE TABLE Students;

Update Data of the Table

With the help of the SET command, we can update the data we have saved previously. We can also put some conditions to SET update for specific data.

Code Example:

SELECT Student
SET  phone = ‘7070707070’
WHERE roll_no = 7;

DELETE

With the help of the DELETE command, we can delete rows of the table.

Code Example:

DELETE FROM Student
WHERE roll_no = 7;

Strings Functions

In SQL, we have special functions that we can apply to our string data to customize it. These are known as strings functions. The following table enumerates the various strings functions in SQL:

String Function Example
left SELECT left

(‘SAMO TARLI’, 5)

len SELECT len

(‘length’)

lower SELECT lower (‘HELLO’)
reverse SELECT reverse (‘esrever’)
right SELECT right (‘RIGHT’ ,4)
space SELECT ‘Well’ + space(2) + ‘Done’
str SELECT str (2389,6,2)
substring SELECT substring (‘Hello’ ,2,2)
upper SELECT upper (‘hello’)

Aggregate Functions

Aggregate functions are used to summarize the attribute in one value.

Function Name Description
avg Provides the average of all values.
count Counts the total number of values present in the attribute.
min Gives the minimum value present in an attribute.
max Gives the maximum value present in the attribute.
sum Sums all the values of the attribute.

Comparison Operators

In SQL, we have some standard comparison operators that we use along with the WHERE statement to retrieve or update some specific rows or data.

Comparison Operator Name
> Greater than
< Smaller than
>= Greater than or equal to
<= Less than or equal to
!= Not Equal to
= Equal to

Code Example:

SELECT * FROM Student
WHERE roll_no = 4;

SELECT * FROM Student
WHERE roll_no > 10;

SELECT * FROM Student
WHERE roll_no != 4;

SELECT * FROM Student
WHERE roll_no >= 5;

Logical Operators in SQL

In SQL, we have three logical operators. We use logical operators between two conditional expressions.

1. OR Operator

SELECT * FROM  Student
WHERE roll_no= 40 OR roll_no=56;

2. AND Operator

SELECT * FROM  Student
WHERE roll_no > 10 AND roll_no <56;

3. NOT Operator

SELECT * FROM  Student
WHERE NOT name = “Naruto”;

BETWEEN

With the help of the BETWEEN command, we can obtain or retrieve a set of ranges.

Code Example:

SELECT * FROM Student
WHERE roll_no BETWEEN 5 AND 10;

LIKE ……..%

With the LIKE command, we can match string patterns. We use the % symbol along with the LIKE command to specify the pattern we want to search.

Code Example:

SELECT * FROM Student
WHERE name LIKE ‘s%’ ;

It will show all those records where the name starts from s.

SELECT * FROM Student
WHERE name LIKE ‘%s’ ;

It will show all those records where the name ends with s.

SQL Sequence ORDER BY

With the ORDERED BY command, we can retrieve data in ascending or descending order. By default, SQL retrieves data in ascending order.

Code Example:

SELECT * FROM Student
ORDER BY roll_no DESC;

JOIN

The JOIN command is used to join two tables together and provide a single table as a result.

Types of JOIN

1) INNER JOIN

INNER JOIN is similar to a simple JOIN. It returns rows when there is a match between the tables.

Code Example:

SELECT * FROM s1.Student INNER JOIN s2.Student_backup_table
ON  s1.Student = s2.Student_backup_table;

2) OUTER JOIN

OUTER JOIN shows all the rows of the first table and the matching rows of another table.

Code Example:

SELECT * FROM s1.Student OUTER JOIN s2.Student_backup_table
ON  s1.Student = s2.Student_backup_table;

3) CROSS JOIN

It generates a table by the cartesian product of one table with another table. The total number of rows after the cartesian product would be rows of the first table and columns of the second table.

Code Example:

SELECT * FROM Students CROSS JOIN Students_backup_table;

Subqueries

With SQL, we can put a query inside another query statement. With this, we can perform multiple queries at once.

IN

With the IN command, we can check whether the data is present in the table or not.

Code Example:

SELECT * FROM Students
WHERE name IN
(SELECT name FROM Students
WHERE name = ”Sam” );

EXISTS

The EXISTS command is used to check whether the record exists or not.

Code Example:

SELECT * FROM Students
WHERE EXISTS
(SELECT name FROM Students
WHERE name = ”Sam” );

GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows.

Code Example:

SELECT *
FROM Customers
GROUP BY name;

HAVING

The HAVING statement is similar to the WHERE statement. However, HAVING is used along with the GROUP BY statement.

Code Example:

SELECT *
FROM Customers
GROUP BY name
HAVING COUNT(name) > 5;

Conclusion

That sums up our SQL cheat sheet. Knowing SQL commands is essential to working with relational database management systems. The SQL cheat sheet mentioned here discusses the most used SQL commands and statements that will serve as an excellent reference while working with SQL databases.

People are also reading:

Author: Pankaj Bhadwal

I'm a digital marketing expert with more than 7 years of experience in creating a wide variety of web content. I also hold a Master's degree in Business Administration with specialization in Finance and Marketing.

Leave a Reply

Your email address will not be published.