Home / SQL / SQL - Commands

SQL - Commands

 
SQL commands are instructions used to communicate with a database. They allow you to perform specific tasks, functions, and queries on the data stored in the database. Some examples of tasks that can be performed using SQL include creating a table, adding data to tables, dropping a table, modifying a table, and setting permissions for users.

There are 5 types of SQL commands and they are as follows :- 





  • DDL Commands are used to modify the structure of a database table, such as creating a new table, deleting an existing table, or altering an existing table. 
  • When you execute DDL commands, the changes made are automatically committed, which means they are permanently saved in the database.
  • Commands under DDL :- 
    1. Create
    2. Drop
    3. Alter
    4. Truncate
    5. Comment 
    6. Rename

1. Create :- Used to create a new database or a new table in the SQL Server.
Create a Database
Syntax :- Create Database <Database_Name>;
Ex :- Create Database Orders ;

Create a Table
Syntax :- Create Table <Table_Name>(<Column_Name) <Data_Type>(Size),....);
Ex :- Create Table Items(Name Varchar(15), Quantity Int(3), Price Decimal(6,2));
Items
+------+----------+-------+
| Name | Quantity | Price |
+------+----------+-------+
|      |          |       |
|      |          |       |
+------+----------+-------+


             
2. Drop :- Deletes/Drops both the structure and the records stored in the table from the database permanently.
Syntax :- Drop Table <Table_Name>;
Ex :- Drop Table Items;



3. Alter :-  It is used to modify the structure of the table. This modification can involve changing the properties of an existing attribute or adding a new attribute. There are 3 sub-commands of alter :-


 i) Alter Column :- This command changes the datatype/size of a particular column.
Syntax :- Alter Table <Table_Name> Alter Column <Column_Name> <New_DataType>(New_Size));
Ex :- Alter Table Items Alter Column Name Varchar(20);


  ii) Alter Add :- This command adds a new column to the existing table.
Syntax :- Alter Table <Table_Name> Add <New_Col_Name> <New_DataType>(Size);
Ex :- Alter Table Items Add Address Varchar(30);
+------+----------+-------+---------+
| Name | Quantity | Price | Address |
+------+----------+-------+---------+
|      |          |       |         |
+------+----------+-------+---------+

                          
iii) Alter Drop :- This command drops a column from the table.
Syntax :- Alter Table <Table_Name> Drop Column <Column_Name>;
Ex :- Alter Table Items Drop Column Address;
+------+----------+-------+
| Name | Quantity | Price |
+------+----------+-------+
|      |          |       |
+------+----------+-------+

                 

3. Truncate :- This command is used to remove all the rows from a table while keeping its structure intact. Unlike the 'Delete' command, it does not support deleting specific rows based on a condition specified in the 'Where' clause.
Syntax :- Truncate Table <Table_Name>;
Ex :- Truncate Table Items;

+------+----------+-------+
| Name | Quantity | Price |
+------+----------+-------+
|      |          |       |
+------+----------+-------+


           
4. Comment :- Comments in SQL are similar to comments in other programming languages like Java, Python, and so on. In SQL, comments can be added to various objects, such as users, roles, warehouses, databases, and tables. Additionally, comments can also be added to specific columns within a table. The purpose of these comments is to provide additional context or explanations for the objects or columns. Comments can be of different types :-

            i) Single Line Comment
            ii) Multi-Line Comment
            iii) Inline Comment



5. Rename :- This command renames the column or the table name.
Renaming a Column
Syntax :- Sp_Rename '<Table_Name>.<Old_Col_Name>', '<New_Col_Name>';
Ex :- Sp_Rename 'Items.Price', 'Amount';
+------+----------+--------+
| Name | Quantity | Amount |
+------+----------+--------+
|      |          |        |
+------+----------+--------+
Renaming a Table
Syntax :- Sp_Rename '<Old_Table_Name>', '<New_Table_Name>';
Ex :- Sp_Rename 'Items','Items_List';

                

2) Data Query Language  (DQL):- DQL statements are used to retrieve data from database objects and arrange it in a specific order. This command enables you to extract data from a database in order to perform actions on it. There is only one command under DQL :- 

  • Select :- This command is used to retrieve specific attributes (columns) from a table based on certain conditions specified in the 'Where' clause. When a SELECT statement is executed on one or more tables, the result is gathered and stored in a temporary table, which is then displayed or used by the program.
Syntax :- Select <Col_Name>
          From <Table_Name>
          Where <Conditions>;
Ex :- Select Quantity
      From Items
      Where Price > 100;

+----------+
| Quantity | 
+----------+
|    5     |       
+----------+
          


  • DML commands allow us to make changes to the data stored in an SQL database.
  • We can easily retrieve, store, modify, update, and delete existing records from the database using these commands. DML commands are responsible for all types of changes in the database. 
  • However, it's important to note that DML commands do not automatically save all changes permanently in the database. The changes can be rolled back.
  • Commands under DML :- 
                1. Insert
                2. Update
                3. Delete

                
1. Insert :-  This command is used to insert a new row into the table. There are 2 methods to insert rows into the table. They are :-
        
i) Implicit Method :- Inserts values for all the columns in the table.
Syntax :- Insert into <Table_Name> Values(Col1_Value, Col2_Value,....);
Ex :- Insert into Items Values('Pencil',5,100);
+------+----------+--------+
| Name | Quantity | Amount |
+------+----------+--------+
|Pencil|    5     |  100   |
+------+----------+--------+


ii) Explicit Method :- Inserts values for specified/required columns only.
Syntax :- Insert into <Table_Name> (Req_Col_Name) Values(Val1, Val2, ...);
Ex :- Insert into Items (Name) Values('Eraser', 'Scale', 'Pen');
+------+----------+--------+
| Name | Quantity | Amount |
+------+----------+--------+
|Pencil|    5     |  100   |
+------+----------+--------+ 
|Eraser|          |        |
+------+----------+--------+
            
            
Inserting Multiple rows into a table :- 
Syntax :- Insert into <Table_Name> Values(Row1_Values),(Row2_Values),...;
Ex :- Insert into Items Values('Scale',6,45),('Pen',10,85);
+------+----------+--------+
| Name | Quantity | Amount |
+------+----------+--------+
|Scale |    6     |   45   |
+------+----------+--------+ 
| Pen  |    10    |   85   |
+------+----------+--------+
        

                                             
2. Update :- This command is used to modify/update the value of a column in the table.
Syntax :- Update <Table_Name> 
          Set <Col1_Name>=<Val1>,<Col2_Name>=<Val2> 
          Where <Conditions
Ex :- Update Items
      Set Name='Books',Quantity=8,Price=100
      Where Name='Pen';

+------+----------+--------+
| Name | Quantity | Amount |
+------+----------+--------+
|Scale |    6     |   45   |
+------+----------+--------+ 
|Books |    8     |   100  |
+------+----------+--------+



3. Delete :- This command is used to delete one or more rows from the table.
Syntax :- Delete from <Table_Name> Where <Condition>;
Ex :- Delete from Items Where Name='Books';
+------+----------+--------+
| Name | Quantity | Amount |
+------+----------+--------+
|Scale |    6     |   45   |
+------+----------+--------+

                

4) Data Control Language (DCL) :-  DCL commands primarily handles the rights, permissions, and other controls within a database system. They are used to grant or revoke authority from a database user. Commands under DCL :-

           1. Grant
           2. Revoke


 1. Grant :- This command is used to provide users with access privileges to the database.
Syntax :- Grant <Permissions> on <Table_Name> to <User1>,<User2>;
Ex :- Grant Select, Update on Items to Vrushabh, Impana;
            
   
2. Revoke :- This command is used to withdraw or remove permissions from a user.
Syntax :- Revoke <Permissions> on <Table_Name> from <User>;
Ex :- Revoke Select, Insert on Items from Vrushabh;
          


  • Transactions group a set of tasks together, and they are treated as a single unit of execution. 
  • A transaction starts with a particular task and concludes when all the tasks in the group are successfully completed. 
  • If any of the tasks fail, the entire transaction is considered a failure. Therefore, a transaction can only have two outcomes: success or failure. 
  • TCL Commands can only be used in conjunction with DML (Data Manipulation Language) commands like insert, update, and delete. 
  • Transactions in a database are automatically committed, which means their changes are permanently saved. This is why transactions cannot be used when creating or dropping tables.
  • Commands under TCL :-
                1. Commit
                2. Rollback
                3. Savepoint
                4. Set Transaction

            
 1. Commit :-  This command is used to save all the transactions to the database.
Syntax :- Commit;
Ex :- Delete From Items Where Price=85;
      Commit;


  2. Rollback :- This command rollbacks/undo all the transactions that have not been saved to the     database.
Syntax :- Rollback;
Ex :- Delete From Items Where Price=85;
      Rollback;
               

 3. Savepoint :- This command sets a savepoint within a transaction. It is used to roll the                  transaction back to certain point, instead of rolling back the entire transaction.
Syntax :- Savepoint <Savepoint_Name>;


 4. Set Transaction :- This command is used to start a transaction in a database. It allows you to specify certain properties for the transaction. For instance, you can indicate whether the transaction should only allow reading from the database or if it can also include writing or modifying data.
Syntax :- Set Transaction [ Read Write | Read Only ];
     




No comments

Subscribe to: Post Comments ( Atom )
darkmode