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:
- Oracle Database
SQL Cheat Sheet
In SQL, we use the following types of commands:
- DDL (Data Definition Language): DDL commands are used to define the frame of a database, and these are CREATE, ALTER, and DROP.
- DML (Data Manipulation Language): DML commands are used to modify or edit the existing database, and these are INSERT, UPDATE, and DELETE.
- DCL (Data Control Language): DCL commands deal with the permissions of the database, and these are GRANT and REVOKE.
- DQL (Data Query Language)
- 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.
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:
|decimal||Numeric values with decimal points.|
|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.|
|timestamp||Timestamp (time in seconds).|
Creating a Table
To create a new table in a relational database, we use the CREATE TABLE command. Code Example:
CREATE TABLE STUDENT ( roll_no int, name varchar(30), phone varchar(11) );
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:
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;
The SELECT command is used to retrieve the attributes (columns with their values) of the table.
SELECT roll_no, name From Student;
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 is used along with the SELECT command, and it is used to eliminate duplicate values. Code Example:
SELECT DISTINCT name FROM Student;
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;
The DROP TABLE command is used to delete the complete table. Code Example:
DROP TABLE Students;
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;
With the help of the DELETE command, we can delete rows of the table. Code Example:
DELETE FROM Student WHERE roll_no = 7;
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:
|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 are used to summarize the attribute in one value.
|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.|
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.
|>=||Greater than or equal to|
|<=||Less than or equal to|
|!=||Not Equal to|
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”;
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;
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;
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;
With SQL, we can put a query inside another query statement. With this, we can perform multiple queries at once.
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” );
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” );
The GROUP BY statement groups rows that have the same values into summary rows. Code Example:
SELECT * FROM Customers GROUP BY name;
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;
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: