DEV Community

Rahul Pratap Singh
Rahul Pratap Singh

Posted on • Updated on

SQL commands types with examples

Hello Folks😎, this is my first post✌.

Just for fun, I would like to share with you all the SQL command types with their examples.

*## What is SQL? *

1- The SQL language is one of the primary reasons for the success of relational databases in the corporate world.
Because it became a standard for relational
databases for executing queries & fetching data.

2 - SQL uses some of the technical jargon like table, row(tuple), column(attribute) & schema.

3 - A SQL table is the basic element of a relational database (RDS).

*What are SQL commands? *

Structured query language (SQL) commands are specific keywords or SQL statements that developers use to manipulate the data stored in a relational database. We can categorize SQL commands in 5 different ways.

a -> DDL = DDL, which stands for "Data Definition Language", which is generally responsible for the structure of the relation(table), we can also call it a "paper schema".
We can design our database through the following commands in SQL.

  • CREATE -> Used to create the table(schema). Creation of new
    table requires you to specify the table name
    columns & the data type of each column.

           Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME 
                   DATATYPES[,....]); 
    
  • DROP -> The DROP command can be used to drop named
    schema(table) elements, such as tables domains, or
    constraints. In layman's terms, it will delete the
    whole table along with the data stored inside it.

            Syntax: DROP TABLE table_name;  
    
  • ALTER -> The ALTER command allows you to add/delete or modify
    the columns(attributes) to an existing table in the
    database.

            Syntax: ALTER TABLE table_name ADD column_name 
                    COLUMN-definition;   
    
  • TRUNCATE -> The TRUNCATE command simply deletes the whole
    contents of the selected existing
    table. But it will not delete the table itself,
    only the data inside it, will be deleted.

             Syntax: TRUNCATE TABLE table_name;  
    
  • COMMENT -> The COMMENT command helps the developer to add
    extra information about their SQL queries, you can
    include a comment in a statement that describes
    the purpose of the statement within your
    application.
    Begin the comment with a slash and an asterisk
    (/) write your text or comment. End the comment
    with an asterisk and a slash (
    /).

    • RENAME -> There are some situations where we need to change
      the name of the table/ columns or rows. The
      The "RENAME" command is used to rename the
      existing table/columns or the rows of a table.

           Syntax: RENAME old_table _name To new_table_name;  
      

b ->** DQL = DQL, stands for "DATA QUERY LANGUAGE". DQL
consists of instructions, used to fetch data from
the database. The DQL only uses the "SELECT"
command.

- SELECT -> It is the most frequently used SQL command, used 
            to select the data from a database. The result or 
            records after executing the query will be stored 
            in a result-set table.

            Syntax: SELECT column1, column2, ...
                    FROM table_name;
            If you want to select all the fields at, once use 
            (*) asterisk.

            Syntax: SELECT * FROM table_name;
Enter fullscreen mode Exit fullscreen mode

c ->** DML = DML, stands for "DATA MANIPULATION LANGUAGE".
DML is used to modify or bring some changes in the
existing database table. We can update the new
information or modify existing database records in
the table.
The changes made here are not automatically
saved, it can be rollback when needed.
We can modify or update our database through the
following commands in SQL.

  • INSERT -> This command is used to insert new records in the
    database table.

           We can insert the data in two ways-
           a - By specifying column names (You don't need to 
               specify the column name where the data will be 
               inserted, you need only their values.)
    
               Syntax: INSERT INTO TABLE_NAME    
                       (col1, col2, col3,... col N)  
                       VALUES (value1, value2,..valueN);
    
           b - Without specifying column names (The second 
               the method needs us to specify both the column 
               name and values that you want to insert.)
    
                 Syntax: INSERT INTO TABLE_NAME    
                         VALUES (value1, value2,...valueN);   
    
    • UPDATE -> This command is used to update or change the row or
      column value of the table.

        Syntax: UPDATE table_name SET [column_name1= 
                value1,column_nameN = valueN] [WHERE 
                CONDITION]   
      
    • DELETE -> This command is used to delete one or more
      records(rows) from the database table.

        Syntax: DELETE FROM table_name [WHERE condition];
      
    • LOCK -> The command is used to gain Exclusive(X) or
      Shared(S) lock on the specified table. This lock
      persists until the end of the transaction.

        Syntax: LOCK TABLE table-Name IN {SHARE | EXCLUSIVE 
                MODE}
      
    • MERGE -> The "MERGE" command is popularly used to
      perform the insert, update & delete operation just
      in a single go. No need to write separate logic
      for each of the operations.

         Syntax: 
                MERGE target_table USING source_table
                ON merge_condition
                WHEN MATCHED
                THEN update_statement
                WHEN NOT MATCHED
                THEN insert_statement
                WHEN NOT MATCHED BY SOURCE
                THEN DELETE;
      

    d ->** DCL = "DCL", stands for "DATA CONTROL LANGUAGE". This
    command generally deals with the permission &
    rights of a user.
    The Database Administrator (DBA) uses the data
    control language (DCL) to manage or authorize
    the database access for other users in the table.
    We can grant or revoke the permissions in the

    database through the following commands in SQL.

    • GRANT -> "GRANT" command is used to give access &
      privilege to the user, by which he/she can make
      some changes in the table.

           Syntax: GRANT SELECT, UPDATE ON MY_TABLE TO 
                   SOME_USER, ANOTHER_USER;  
      
    • REVOKE -> "REVOKE" command is used to withdraw user access &

      the privilege of the user.

           Syntax: REVOKE SELECT, UPDATE ON MY_TABLE FROM 
                   USER1, USER2;  
      

    e ->** TCL = "TCL" stands for "TRANSACTION CONTROL LANGUAGE".
    Consecutive, Read/Write operations in the
    database is called "TRANSACTION".
    It is used to manage our transactions so that we
    can be sure that it is successfully carried out
    & has not violated the integrity of the
    database.
    The following commands come under the TCL are:

  • COMMIT -> "COMMIT", command is generally used to save all
    the transaction to a database, after performing
    all the operations on it.

               Syntax: COMMIT; 
    
    • ROLLBACK -> "ROLLBACK", command is used to undo all the
      transactions(operations) that have not already
      been saved(committed) to the database.

             Syntax: ROLLBACK; 
      
    • SAVEPOINTS -> "SAVEPOINTS", command works like a marker, used
      to roll the transaction back to a certain point

      without rolling back the entire transaction.

             Syntax: SAVEPOINT SAVEPOINT_NAME; 
      

I hope you've enjoyed😎😎 this simple post on SQL commands.
Thank You! 👍

Top comments (0)