SQL Cheat Sheet

Posted in /   /  

SQL Cheat Sheet

Pankaj Bhadwal
Last updated on November 15, 2022

    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:

    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:

    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:

    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:

    Leave a Comment on this Post

    0 Comments