Commands in SQL

In this blog, I’m going to discuss commands in SQL. All the SQL commands are divided into 5 categories. Discover essential Commands in SQL, including SELECT, INSERT, UPDATE, DELETE, and more, to manage and manipulate databases effectively and efficiently.

 

1. DDL commands

DDL stands for Data Definition/structure Language. It consists of commands like create, drop, truncate, alter. All of these commands work on definition, which means the outer structure of a table like create table is used to create the database or table, the drop is used to drop the database or table, truncate is used to delete all the records from the table without deleting its structure and alter is used to add, drop, modify and rename columns of the table. Let’s see all of these commands one by one in detail :

 

• Create Command

• Creating a database:  used to create a database

  E.g    create database demo;

 

• Creating a table:  used to create table

  E.g    create table student(id int, name varchar(88),marks double, address varchar(100));

 

• drop command

• Dropping database:  used to drop database

  E.g   drop database demo;

 

• Dropping table:  used to drop table

  E.g   drop table student;

 

• Truncate command

• Truncating table:  used to delete all records from the table but it does not delete the outer structure of the table

  E.g   truncate table student;

 

• Alter command

• Alter table  :    used to perform an operation on columns of the table

like adding column into existing table,removing column from the table modifying datatype of column renaming column or table name 

 

1. Adding a column

       i . Adding column into existing table(at the end of all columns)

      E.g alter table student ADD column course varchar(66);

 

       ii.adding a column into an existing table  (at the start of all columns)  

       E.g alter table student ADD column rollno int FIRST; 

 

      iii. adding a column into an existing table (at the middle position)

      E.g alter table student ADD column address varchar(100)  AFTER marks;       

 

      iv.adding multiple columns through single alter command

       E.g alter table student ADD column math int ,

                    ADD column sci int ,

                    ADD column eng int;

 

         2.Dropping column

          i.dropping column from table

          E.g alter table student DROP column  isPresent;

 

          ii.dropping multiple columns

          E.g alter table student DROP column  math,

                DROP column  sci,

                DROP column  eng;

 

         3.Modifying column

         E.g alter table student MODIFY column marks int;

 

          4.renaming columnname

          E.g alter table student RENAME column phoneno to contactNO ;

 

         5.renaming tablename

         E.g alter table student RENAME to stud;

 

2. DML commands

DML stands for Data Manipulation Language. It consists of commands like insert, update, and delete. All of these commands work on the inner structure, meaning data of a table like inserting data into a table, updating data of the table, and deleting data from a table. Let’s see all of these commands one by one in detail :

 

       a.Insert command

 

• Inserting data :  used to insert data in table

 

1.inserting data into all columns

  E.g    insert into student values(1,”ram”,89.2,”pune”);

 

                      2.inserting data into SPECIFIC columns

                       E.g insert into student(id,name,isPresent) values( 3,”yash”,false);

 

                      3.inserting data into all columns by changing sequence 

 E.g insert into student(id,marks,phoneno,name,isPresent) values( 7, 56.8,7776767656,”priya kumari”,true);

 

4.inserting multiple records/rows using single insert statement

insert into student values(8,”jack”,66.9,9292828282,true),

        (9,”harry”,56.9,858828282,false),

                              (10,”jenny”,72.9,71222828282,true),

                              (11,”john”,45.9,9982828282,false);

 

1. Update command

 

• Updating data :  used to update data in table

 

1.updating all records

update without where clause can update all the records from table

 

E.g update student set isPresent=true ;    

 

2.update with where clause

updating multiple records

 

E.g update student set isPresent=false where name=”harry”; 

 

3.updating single record

 

E.g update student set isPresent=true where id=13;

 

4.updating null values

 

E.gupdate student set phoneno=0 where phoneno is null;

 

 

            C.Delete command

 

• Deleting data :  used to delete data in the table

                      1.deleting all data

                      Delete without where clause can delete all the records

                      E.g delete from student;

 

                       2.deleting multiple data

                      E.g delete from student where id=2 or id=5;

 

                       2.deleting single data

                       E.g delete from student where id=2;

 

For Free, Demo classes Call: 020 7117 1500

Registration Link: Click Here!

 

3. DQL commands

DQL stands for Data Query Language. It consists of commands like select. Select command is used to read or access single or multiple records from the table  .Let’s see select command in detail :

                  

                 Selecting data :  used to access data from table

                      1.selecting all data

                       E.g select * from student;

 

                      2.selecting all data

                       E.g select * from student;

 

4. TCL commands

TCL stands for Transaction Control Language. It consists of commands like commit rollback and savepoint. Transaction means one transaction is a set of multiple operations and if all of those operations execute successfully and it will update our account/database then we can say we have done one transaction. 

By default, all transactions in the database are autocommitted means automatically saved data permanently. We can control the transactions by using commands like Commit, rollback, and savepoint.

 

We can execute transactions on the database with the help of insert, update, and delete commands

 

1. commit means  to save the changes permanently ina  table/database

 

set autocommit=0 ;     #0=> false

select * from stud;

insert into stud(id,name) values(30,”nitin”);

update stud set name=concat(“ujwal”,”patil”),marks=89 where id=13;

select * from stud;

Commit;                          #commit will save all the above transactions permanently in the database

 

2. rollback means undo the changes that we have made

 

set autocommit=0 ;     #0=> false

select * from stud;

insert into stud(id,name) values(31,”prisha”);

delete from stud where id=19;

select * from stud;

rollback;         

 

#if we got the incorrect/wrong result for  our insert/update/delete operation then we can ROLLBACK that transaction

#commit           #if we got the correct result for  our insert/update/delete operation then we can COMMIT that transaction

select * from stud;

 

3. Savepoint means  is point till and  that point we can rollback/commit particular transaction

 

set autocommit=0 ;     #0=> false

select * from stud;

insert into stud(id,name) values(31,”yash”);

insert into stud(id,name) values(33,”rohit”);

savepoint s1;        #creating savepoint

insert into stud(id,name) values(31,”pushpa”);

insert into stud(id,name) values(33,”doremon”);

commit to s1;            

select * from stud;

 

5.  DCL commands

DCL stands for Data Control Language. It consists of commands like grant and revoke. Grant means to give specific permissions on our data to another user and revoke means to take back that permissions. Let’s see these commands one by one.

 

E.g

grant select ,insert ON stud to testUser1;

 

grant update ,delete,alter ON stud to testUser1;

 

revoke update,insert,delete ON stud From testUser1;

 

Author:-

Pooja Nandode-Bhavsar

Call the Trainer and Book your free demo class for SQL now!!!

© Copyright 2020 | Sevenmentor Pvt Ltd.