STRUCTURED QUERY LANGUAGE (SQL)

SQL is one of the most important tool a Data Scientist or a Data analyst should know of. SQL is a query language which is used to manipulate and retrieve information from a Database.

SQL is used with R-DBMS (Relationship Database Management System) for retrieving and querying purpose.

In this blog, we will look into various basic SQL queries , where and when those queries are used.

There are five types of SQL commands:

• DDL — Data Definition Language
• DQL — Data Query Language
• DML — Data Manipulation Language
• DCL — Data Control Language
• TCL — Transaction Control Language

DDL — Data Definition Language:

The DDL commands are used to define the structure of the database. It is used for creating and modifying the database objects such as table, indexes etc.. The basic DDL Commands are:

• CREATE - it is used for creation of a database or table.

CREATE TABLE <table_name> (
<column_name1> datatype,
<column_name2> datatype,
<column_name3> datatype,
….
);

• DROP - it is used to delete objects from the database.

DROP TABLE <table_name>;

• TRUNCATE - it is used to remove the contents/records in a table but not the table itself (i.e) the structure of the table remains as it is.

TRUNCATE TABLE <table_name>;

• ALTER - Alter command is used to alter the existing structure of the table. It is used to add, delete, or modify columns in an existing table as well as it can be used to add constraints to a table.

ALTER TABLE <table_name>
ADD <column_name> datatype;

ALTER TABLE <table_name>
DROP COLUMN <column_name>;

ALTER TABLE <table_name>
MODIFY COLUMN <column_name> datatype;

ALTER TABLE <table_name>
RENAME TO <new_table_name>;

DQL — Data Query Language

DQL command are query commands which are used to retrieve data from the database.

Most used DQL command is SELECT command

• SELECT - it is used to select a particular data from the table.

Various condition can be specified with the select command. We will look into that in another blog.

SELECT * FROM<table_name>;

SELECT <column_name1>, <column_name2>, …
FROM <table_name>;

‘*’ operator is used for selecting all the columns from a table.

DML — Data Manipulation Language

DML commands are used to manipulating the data present in the database. DML commands are used to modify the database. It is responsible for all form of changes in the database. Most commonly used DML commands are:

• INSERT - it is used to insert data to an existing table.

INSERT INTO <table_name>
VALUES (value1, value2, value3, …. valuen);

INSERT INTO <table_name> (column1, column2, column3)
VALUES ( value1, value2, value3);

• UPDATE - this command is used to update an existing command.

UPDATE <table_name>
SET <column1> = <value1>, <column2> = <value2>, …
WHERE condition;

* WHERE clause is used to select a particular condition for which a update operation needs to be made.

* If you omit the WHERE clause, ALL records will be updated

• DELETE - it is used to delete existing records in a table.

DELETE FROM <table_name> WHERE condition;

* If WHERE clause is omitted, all records in the table will be deleted

* To delete all the rows from a column,

DELETE FROM <table_name>;

DCL — Data Control Language

DCL commands are used for security purposes like giving permission of access to the database. It mainly deals with rights, permissions and other controls of the database system. Commonly used DCL commands are:

• GRANT - used to grant access privileges or other privileges for the database.
• REVOKE - used to revoke or remove the privileges given by the GRANT command.

* GRANT

GRANT <privilege_name>
ON <object_name>
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

* REVOKE

REVOKE <privilege_name>
ON <object_name>
FROM {user_name |PUBLIC |role_name}

TCL — Transaction Control Language

TCL commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. Commonly used TCL commands are:

• COMMIT - Used to save the changes made. It makes permanent changes during the transactions.

The syntax for the COMMIT command is :

COMMIT;

• ROLLBACK - ROLLBACK command restores database to original since the last COMMIT. It is used to restores the database to last committed state.

Syntax:

ROLLBACK;

• SAVEPOINT - It marks and saves the current point in the processing of a transaction.

* SAVEPOINT can be used to mark a particular point of transaction. That point can be retrieved using ROLLBACK.

* Syntax for SAVEPOINT:

SAVEPOINT < SAVEPOINT_name>;

* To rollback to the SAVEPOINT

ROLLBACK TO <SAVEPOINT_name>;

Important Note:

SQL commands are not case-sensitive.

Thanks for reading this blog. This blog is just about the basics of the SQL commands. Various commands and things can be done using the SQL commands. We will look into that in an another blog. Please follow my account. Feel free to ask questions about the blog in the comments and show appreciation through claps. Also, connect with me through my LinkedIn account.