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;

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! 👍