SQL COMMANDS

 SQL COMMANDS:

SQL Commands are instructions. It is used to communicate with the database to perform tasks, functions, and queries with data.

 TYPES OF SQL COMMANDS:

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 (DDL):

These commands are used to define the database schema (create or modify the structure of database objects).

• Data definition language modifies the structure of the tables by adding, removing, or changing tables, among other things.
• All DDL commands are automatically committed, which permanently saves all database modifications.

DDL commands:

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE: It is used to create a new table in the database.

Syntax: CREATE TABLE Table_name (COLUMN_NAME DATATYPES);

Example: CREATE TABLE STUDENT (Name VARCHAR2(20), Roll No VARCHAR2(20), Email  

                   VARCHAR2(30), DOB DATE);

DROP: It is used to delete both the structure and record stored from the table.

Syntax: DROP TABLE table_name;

Example: DROP TABLE STUDENT;

ALTER: It is used to change the database's structure. This update might be to modify the properties of an existing property or introduce a new attribute.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN definition;

To modify the existing column in the table:

ALTER TABLE table_name MODIFY (column_definitions);

EXAMPLE: ALTER TABLE STUDENTS ADD (ADDRESS VARCHAR2(20));

TRUNCATE: It is used to delete all the rows from the table.

Syntax: TRUNCATE TABLE table_name;

EXAMPLE: TRUNCATE TABLE STUDENTS;

DATA MANIPULATION LANGUAGE (DML):

• Data Manipulation Language commands are used to modify the database. It is responsible for all forms of changes in the database.
• DML commands are not auto-committed, which means they cannot save all changes to the database permanently. They are reversible.

DML COMMANDS:

• INSERT
• UPDATE
• DELETE

INSERT: It is used to insert data into the row of a table:

Syntax: INSERT INTO table_name

(Col1, Col2, …. Col N)

VALUES (value1, value2, …. Value N);

Example: INSERT INTO STUDENTS (Name, Address) VALUES (“JAYAKUMAR”, Nellore”);

UPDATE: This command is used to update or modify the value of a column in the table.

Syntax: UPDATE table_name SET [Column_name1= Value1, Column_name2= Value2,

…. ColumnN= ValueN] [WHEREcondition]

Example: UPDATE students

SET Name= ‘JAYAKUMAR’

WHERE Roll_No= ‘17G21***1’

DELETE: It is used to remove one or more rows from a table.

Syntax: DELETE FROM table_name [WHERE condition];

Example: DELETE FROM students

WHERE Roll_No= ”17G21***1”;

DATA CONTROL LANGUAGE (DCL):

Data Control Language commands are used to grant and take back database user authority.

• To control user access in a database, related to security issues.
• It allows restricts the user from accessing data in the database.

DCL Commands:

• GRANT
• REVOKE

GRANT: It is used to give user access privileges to the database.

Syntax: GRANT <privilege List>

             ON < relation name or view name>

             TO < user/role list>;

Example: GRANT email ON student TO XYZ;

REVOKE: It is used to cancel previously granted permissions or denied permissions.

Syntax: REVOKE <privilege List>

              ON < relation name or view name>

              FROM <user name>;

Example: REVOKE UPDATE email ON student FROM XYZ;

TRANSACTION CONTROL LANGUAGE (TCL):

TCL language is used to control various transactions performed in the database.

• Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands.
• These operations cannot be utilized while creating or deleting tables since they are automatically committed in the database.

TCL Commands:

• COMMIT
• ROLLBACK
• SAVEPOINT

COMMIT: Commit command is used to save all transactions to the database.

Syntax: Commit;

Example: DELETE FROM students

                  WHERE Email = Jaya@gmail.com;

                  COMMIT;

ROLLBACK: The Rollback command is used to undo transactions that have not yet been stored in the database.

Syntax: ROLLBACK;

Example: DELETE FROM students

                  WHER Email = jaya@gmail.com;

                  ROLLBACK;

SAVEPOINT: It is used to revert/roll the transaction to a specific point without reverting/rolling the entire transaction.

Syntax: SAVEPOINT SAVEPOINT_NAME

Example: DELETE FROM students

     WHERE Email = jaya@gmail.com;

     SAVEPOINT;

DATA QUERY LANGUAGE(DQL): DQL is used to fetch the data from the database and find information.

DQL Commands:

• SELECT

SELECT:

• SELECT is the most commonly used command in SQL.
• It retrieves data from one or more tables or operations.
• This is the same as the relational algebra projection operation. It is used to choose the attribute depending on the condition specified in the WHERE clause.

Syntax: SELECT expressions

              FROM Table_name

              WHERE condition;

Example: SELECT name

                  FROM students

                  WHERE age>15;

CONCLUSION:

These are the five SQL commands, it will help you create, modify, and delete the database.