Hey guys, In the last article we discuss about What is SQL. Let’s get know more about SQL. Today we will learn about the basic commands in SQL. As we learnt in pervious blog, SQL is used for storing, manipulating and retrieving data stored in a relational database. But now the Question is “How we can store, manipulate and retrieve Data”. For doing this we use SQL commands. Which is also know as SQL Query. let’s understand today’s topic “What are the basic SQL Command? “
As we know Commands are the interactions to a computer to perform a specific task. In easier way we can say, “Commands are used to communicate with Computer”. Same as we use SQL Commands to communicate with database to perform tasks, functions and queries with data.
Types of SQL command
There are five types of SQL commands :
- DDL – Data Definition Language
- DML -Data Manipulation Language
- DCL – Data Control Language
- TCL – Transaction Control Language
- DQL- Data Query Language
Data Definition Language
By using DDL SQL command we can design the structure of the database. It handles the schema description of database and change the structure of the database objects in database. Such as creating and modifying the table. All the DDL Commands are auto-committed, that means it permanently store all the changes in the database..
Here are some commands that come under DDL:
- CREATE
- ALTER
- DROP
- TRUNCATE
CREATE:- We use this command to create a new table in the database. It allows us to define the name of the table and the name of each column in the table.
Syntax:
CREATE TABLE table_name (column_1 datatype,
column_2 datatype,
column_3 datatype);DROP:-To delete both the structure and record stored in the table .
Syntax:
DROP TABLE table_name; ALTER:– To alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
Syntax:
To add a new column
ALTER TABLE table_name ADD column_name COLUMN-definition; TRUNCATE:– When we want to delete all the rows from the table and free the space containing the table we use TRUNCATE command .
Syntax:
TRUNCATE TABLE table_name;Data Manipulation Language
In SQL command, It Customize the database, and it is responsible for all forms of changes in the database. As the DDL commands, DML command is not auto-committed that means it can’t permanently store all the changes in the database. They can be rollback.
Here are some of commands that come under DML:
- INSERT
- UPDATE
- DELETE
INSERT: To add a new row to the table, We use INSTERT command.
Syntax:
INSERT INTO table_name
(column_1, column_2, column_3)
VALUES
(value_1, value_2, value_3);UPDATE: To update or modify the value of a column in the table we use UPDATE command.
Syntax:
UPDATE table_name
SET column_name = value
WHERE condition;DELETE: To remove one or more row from a table , we use DELETE command .
Syntax:
DELETE FROM table_name [WHERE condition]; Data Control Language
It deals with the permissions and privileges of the objects. Here are some commands that come under DCL:
- Grant
- Revoke
Grant: It Provides user access to the database or its objects.
Example:
GRANT SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USERRevoke: To take back permissions from the user.
Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2; Transaction Control Language
Transaction Control Language SQL command, as its name suggests, to control the actions by other non-auto-committed commands, such as INSERT, DELETE and UPDATE. TCL commands can only use with DML.
These operations are automatically committed in the database that’s reason they cannot be used while creating tables or dropping them. Here are some commands that come under TCL:
- COMMIT
- ROLLBACK
- SAVEPOINT
Commit: Commit command, save all the transactions to the database.
Syntax:
COMMIT; Rollback: Rollback command is used to undo transactions that have not already been saved to the database.
Syntax:
ROLLBACK; SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME; Data Query Language
We use DQL commands to fetch the data from the database. SELECT is the only and essential command widely and commonly used by all data analysts and scientists. In DQL there is only select command.
- SELECT
SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions; Thank you, for reading this article. Hope it helps. If you have any question or query regarding this article or other please feel free to ask in comment section.
Recommended Posts:
Java Basic interview questions and answers .