SQL Cheat Sheet for Beginners

SQL is a 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. It is not possible to remember all of them.

This SQL cheat sheet serves 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 creates, updates, retrieves, and deletes relational databases.

SQL databases are also known as relational databases. There are many popular SQL databases, each with 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 database's permissions: 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:

Creating a Table

We use the CREATE TABLE command to create a new table in a relational database.

Code Example:

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 ensures that all the primary key constraint data have unique values and no null values.
• Not Null: There would be no null value in that column.
• Check: It ensures 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: We can enter data into a table using the INSERT and VALUES commands.

Code Example:

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 retrieves 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 table rows.

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 string functions. The following table enumerates the various strings functions in SQL:

Aggregate Functions

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

Comparison Operators

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

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 and 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

We can retrieve data in ascending or descending order with the ORDERED BY command. 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. After the cartesian product, the total number of rows 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 with 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 be an excellent reference while working with SQL databases.

People are also reading:

• DBMS Normalization
• How to Create a Database?
• Bootstrap Cheat Sheet
• Java Cheat Sheet
• Python Cheat Sheet
• CSS Cheat Sheet
• HTML Cheat Sheet
• Best SQL Interview Questions
• SQL Commands