Sonali Dash — May 14, 2022
Intermediate SQL

This article was published as a part of the Data Science Blogathon.

Introduction to SQL

SQL stands for the Structure Query Language. And it is the most popular RDBMS (Relational Database Management System) language used to create, access, and manage databases. It was developed in the year 1970 at IBM. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and the ISO(International Organisation for Standardisation ) in 1987. Initially, it was called SEQUEL which stands for Structured English Query Language, latter it changed into SQL but the pronunciation remained the same. Many RDBMS software like Oracle, MySQL, MS Access, and SQL Server use SQL as their standard query language.

Why SQL?

It is easier to view, add, delete and change data in a database by using simple queries. Apart from this, there are several other reasons why we use queries. Such as, It is helpful to find specific data by using filtering conditions in a short time. It is also helpful to summarise data by reviewing the current dataAll the query operations are done using a standard query language, SQL. To maintain and manage data in a database, SQL provides some sub-languages, including several commands.  Through which query operations can be done in a database.

* SQL is a case-insensitive language, so we can use both upper case and lower case letters when we write code.*

Sub Languages of SQL

SQL includes 5 types of sub-languages that are  DDL, DML, DQL/DRL, DCL, and TCL.

Sub Languages of SQL| SQL

DDL

Stands for Data Definition Language, DDL commands are used to change and modify the structure of the table. By default, DDL commands are auto-committed which means when one changes and modifies a table then the values are automatically saved in the database.

Here are the commands of DDL- CREATE, ALTER, DROP, TRUNCATE, RENAME

CREATE- This command is used to create a new table and index in a database.

Syntax: CREATE table table_name (col_1 datatype  size , col_ 2 datatype  size ,...........,col_n datatype  size);
Example: CREATE table Student (Roll_number INTEGER(5), Name VARCHAR(30), Age INTEGER(3),  City VARCHAR(30));
DDL| SQL

[DESC(DESCRIBE) command is used to retrieve the structure of a  table.  It may confirm that table is created.   ]

ALTER- To change the structure of a table in a database ALTER command is used. We can add a new column in a table and also can change the data type and size of a column.

Syntax: ALTER table table_name ADD column_name COLUMN definition;
               ALTER table table_name MODIFY(column_definition);
Example: ALTER TABLE Student ADD ( Ph_number INTEGER(12));
ALTER

[In the above example we added a new column in the existing table student.]

ALTER TABLE Student MODIFY City VARCHAR(50);

 

MODIFY City VARCHAR| SQL

[In the above example we changed the size of the City from 30 to 50.]

RENAME- This command is used to change the name of an existing table in a database. For this purpose, we use ALTER command.

Syntax: ALTER TABLE table_name RENAME  TO new_table_name;
Example: ALTER TABLE  Student  RENAME TO Student_details;

 

RENAME

In the above example, we changed the name of the table Student to Student_details. After that when we use the old table name then it displays the message “Table Student doesn’t exist in databases “. In this example, S denotes the name of the database. And when we use the new table name i.e Student_details then it displays all details of the table.

DROP- To remove a table permanently from a database DROP command is used.

Syntax: DROP table table_name;
Example: DROP TABLE Student;

TRUNCATE- This command is used to remove all rows from a table but the table structure remains the same.

Syntax: TRUNCATE table table_name;
Example: TRUNCATE TABLE Student;

 

TRUNCATE| SQL

In the above example first, we use the TRUNCATE command which deletes
all rows from the Student_details table. When we try to retrieve the table
details then it shows a message that ‘EMPTY set’.After that, we use the DROP
command which completely deletes the table Student_details from the database.
So when we again try to retrieve table details then it shows a message
‘Student_details’ doesn’t exist in the database.

DML

Stands for Data Manipulation Language. DML commands are used to manage and modify the database. Commands are not auto-committed which means it can’t save the data permanently in a database.

Here are the DML commands- INSERT, UPDATE, DELETE

INSERT– This  statement is used to add table row values

Syntax: INSERT INTO table_name(col 1, col 2, col 3,......,col n) VALUES(value 1, value 2, value 3.........., value n);  OR
INSERT INTO table_name VALUES(value 1, value 2, value 3.........., value n);
EXAMPLE: INSERT INTO Student VALUES (01, 'Rohini',20,' Delhi');

 

DML 

UPDATE- This statement is used to update the values of a column in a table. To update table values WHERE condition is necessary.

Syntax: UPDATE  table_name SET [column 1= value 1 , column n=value n] WHERE condition;
Example: UPDATE Student SET Name='Mahima' WHERE Roll_number=03;

 

UPDATE

DELETE– To remove a particular row from a table DELETE command is used. WHERE condition is necessary to perform delete operation.

Syntax: DELETE FROM table_name WHERE condition;
Example: DELETE FROM Student WHERE Roll_number =2;

 

DELETE| SQL

 

DQL

 Stands for Data Query LanguageDQL is used to fetch data from the database.

It uses only command i.e SELECT

SELECT–  This command is used to retrieve table data from the database based on the condition described by the WHERE condition.

Syntax: SELECT * FROM table_name WHERE condition;
             SELECT * FROM table_name;

Example: SELECT * FROM  Student WHERE Name= “Rohini”;
SELECT * FROM Student;

 

DQL

 

DCL

Stands for Data Control Language. DCL commands mainly deals with rights, permissions, and other controls of the database. It uses only 2 commands i.e GRANT and revokes.

GRANT– This command is used to GRANT access privileges to the user in the database.

Syntax: GRANT privileges_names ON object_name TO user ;
Example: GRANT ALL ON Salary_detail TO 'Ashish' '@' localhost ;

[By using this command, user Ashish got the permissions to access the Salary_detail table. ]

REVOKE This command is used to remove access privileges from the user in a database.

Syntax: REVOKE privileges_names ON object_name FROM user_name;
Example: REVOKE All ON  Salary_detail FROM Ashish ;

[By using this command,  user Ashish’s permissions on the Salary_detail table have been removed. ]

TCL

Stands for Transaction Control Language. Mainly these TCL commands deal with the transactions in a database.  TCL commands are only used with DML commands like INSERT, UPDATE, and DELETE  because these commands are not automatically committed in a database.

TCL commands are COMMIT, ROLLBACK, and  SAVEPOINT.

COMMIT – This command is used to save database transactions permanently.

Syntax: COMMIT;

Example: INSERT INTO Student_details VALUES(02,’Rajat’,21,’Delhi’,9874008);

         COMMIT;

 

TCL

ROLLBACK- This command is used to remove the recently updated transaction from the database.

Syntax: ROLLBACK;
Example: DELETE FROM Student_details WHERE Roll_number=4;
                ROLLBACK;

 

ROLLBACK| SQL

In the above example before using the ROLLBACK command first we have to disable auto-commit by using the command ‘SET AUTOCOMMIT=0’. For the transaction which is already committed ROLLBACK command doesn’t affect that transaction. One can be able to ROLLBACK a transaction if that transaction is not committed.

SAVEPOINT  This command creates a point within the group of transactions. So it helps to roll back the transaction at a certain point without rollback the entire transaction.

Syntax: SAVEPOINT savepoint_name;
              ROLLBACK TO savepoint_name;
Example: SAVEPOINT SP1;
INSERT INTO Student_details VALUES(05,'Suraj',21,'Goa',9974458); ;
    //deleted
    SAVEPOINT SP2;
    //Savepoint created.
    //Rollback completed.
    //Savepoint created.
    ROLLBACK TO SP1;
SAVEPOINT| SQL

** Before using SAVEPOINT and ROLLBACK in a database, you must confirm
that auto-commit is disabled. Because by default, auto-commit is enabled in database**

Conclusion to  SQL

SQL itself is a very vast concept; in this article, we learned about some basic concepts of SQL. The key takeaways from the article are,

  • We learned how to define and modify a database and a table by using DDL commands such as CREATE and ALTER and
  •  Removing a table and table rows by using DROP and TRUNCATE commands. 
  • Also learned how to insert, update and delete table data by using DML commands such as INSERT, UPDATE and DELETE.  
  • How to retrieve table values by using the SELECT command.
  • Covered commands that control a user’s access to data resources using DCL commands such as GRANT and REVOKE. 
  • And covered commands that control transactions in a database using TCL commands such as COMMIT, ROLLBACK, and SAVEPOINT. 

So, this was all about basic SQL commands to get you started with SQL. Hope you liked the article.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

About the Author

Sonali Dash

Our Top Authors

Download Analytics Vidhya App for the Latest blog/Article

Leave a Reply Your email address will not be published. Required fields are marked *