SQL commands types with examples
Tags: sql, sqlcommands, computerscience, database
Published: 2023-02-19 17:45:16

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