SQL Commands

SQL Commands:

There are 5 types of sql commands:-

1. DDL(Data Definition Language )
2. DML(Data Manipulation Language)
3. DCL(Data Control Language)
4. TCL(Transaction Control Language)
5. DQL(Data Query Language)

DDL(Data Definition Language)

---

• You can define the database structure or schema with the aid of data definition language. Let’s study the syntax of DDL commands.
• There are five different categories of DDL commands in SQL:

1. CREATE

• The database structure schema is defined using CREATE statements:
• Syntax:

CREATE TABLE TableName (Column_Name Datatype);

• For Example:

Create database HRMS;
Create table Employee;
Create view for_employee;

2. DROP

• Tables and databases are deleted from RDBMS via the drops commands.
• Syntax:

DROP Table;

• For Example:

Drop object_type object_name;
Drop database HRMS;
Drop table Employee_tbl;

3. ALTER

• You can change the database’s structure with the Alters command.
• Syntax:

Column Add In Table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To change a table’s existing column:

ALTER TABLE MODIFY(COLUMN DEFINITION);

• For Example:

Alter table employee_tbl add Address varchar;

4. TRUNCATE

• This command was used to remove every row from the table and clear up the area where the table was located.
• Syntax:

TRUNCATE TABLE table_name;

• For Example:

TRUNCATE table employee_tbl;

DML(Data Manipulation Language)

---

• By adding, changing, and deleting data, you can change the database instance using the Data Manipulation Language (DML). It is in charge of carrying out all forms of database data modification.
• The three fundamental building blocks that a database application and user can utilise to insert data and information are as follows:
• Several significant SQL DML commands are listed below:

1. INSERT

• A SQL query is contained in this sentence. Data can be inserted into a table row using this command.
• Syntax:

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

• For Example:

INSERT INTO Employee_tbl(EmployeeName, City, Country)
VALUES ('Xyz', 'Stavanger', 'Norway');

2. UPDATE

• This command is used to change or update a table column’s value.
• Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• For Example:

UPDATE Employee_tbl
SET Name='Juan'
WHERE Country='Mexico';

2. DELETE

• With this command, you can delete a row or several rows from a table.
• Syntax:

DELETE FROM table_name WHERE condition;

• For Example:

DELETE FROM Employee_tbl WHERE Name='Alfreds Futterkiste';

DCL(Data Control Language)

---

• To grant “rights & permissions,” the DCL (Data Control Language) includes commands like GRANT and REVOKE. The database system’s other permission controls have further controls.
• Example of DCL Commands:

1. Grant

• This command is used to grant a user database access rights.
• Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

• For Example:

GRANT SELECT ON Users TO'ABC'@'localhost;

2. Revoke

• The user’s permissions should be supported.
• Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

• For Example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

TCL(Transaction Control Language)

---

• The transactions within the database are handled via TCL commands, or transaction control language.

1. Commit

• This command is used to save all the transactions to the database.
• Syntax:

Commit;

• For Example:

DELETE FROM Employee_tbl
WHERE empId=25;  
COMMIT;

2. Rollback

• You can undo transactions that haven’t yet been saved to the database by using the rollback command.
• Syntax:

ROLLBACK;

• For Example:

DELETE FROM Employee_tbl
WHERE empId=25;

2. SAVEPOINT

• You can set a savepoint within a transaction with this command.
• Syntax:

SAVEPOINT SAVEPOINT_NAME;

• For Example:

SAVEPOINT empId;

DQL(Data Query Language)

---

• The data is retrieved from the database using the Data Query Language (DQL). There is only one command used:

1. SELECT

• This command aids in the selection of the attribute according to the criteria specified by the WHERE clause.
• Syntax:

SELECT * FROM table_name;

• For Example:

SELECT EmpName
FROM Employee_tbl  
WHERE Age > 20;