SQL Commands

SQL Commands are instructions to the database. The SQL commands are used to establish communication with the database. It interacts with a database using some operations. It is used to perform tasks, queries, and functions over the database.SQL performs various tasks such as table creation, stored procedure, views, function creation as well as setting permission to the users.

SQL is a database language that is used by various databases such as Microsoft SQL, Oracle, MySQL, etc. to do database operations. SQL uses various SQL commands such as CREATE, INSERT, UPDATE, DELETE, etc. to do required tasks. The SQL commands follow the defined SQL Syntax to carry out required tasks.

Types of SQL Commands

Below are the SQL command  categories:

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

 

 

SQL Commands

Data Definition Language(DDL)

DDL statements define the data structures. It consists of SQL commands that define the schema for the database. It changes the database object structure such as creating, deleting, and altering the table structure.DDL commands are auto-committed. The changes made to the database are permanently saved. These commands are generally not used by users who access the database via applications.

List of DDL Commands

DROP: Used to remove the objects from the database.
ALTER: Used to modify the structure of the database.
CREATE: Used to create the database or its objects (like views, store procedures, triggers, tables, indexes, and functions).
TRUNCATE: removes delete or all records from a table, Also deletes space allocated to the database object that is for the table.
COMMENT: Adds  comment to the data dictionary.
RENAME: Renames an existing database object.

CREATE example

Create table productdetails
(
Product varchar(50),
productid int,
startdate date
)

ALTER example

Alter Table productdetails add (store varchar(50));

 

DROP example

DROP table productdetails

 

TRUNCATE example

TRUNCATE table productdetails

 

Data Query Language (DQL)

DQL is used the fetch the data from the database. It uses only one SQL command which is SELECT. This command is used to fetch  data for further operations.

List of DQL Commands
  1. SELECT: Used to retrieve data from the database.

SELECT example

select * from productdetails;
Data Manipulation Language(DML)

DML statements affect the information stored in the database. These SQL commands manipulate data that is available in the database. It is responsible for all changes made in the database. The changes cant be saved permanently in the database they can be rolled back.

List of DML Commands
  1. INSERT: Used to insert data into a table.
  2. UPDATE: Used to update existing data within a table.
  3. DELETE: Used to delete records from a database table.

INSERT example

insert into  productdetails ( Product, productid,startdate) 
values ('book',1,'2022-06-23')

 

UPDATE example

update productdetails
set Product='Ebook'
where productid=1

 

DELETE example

delete from productdetails where productid=1

 

Data Control Language(DCL)

DCL commands are used to provide rights, permissions, and other controls of the database system.

List of DCL Commands

GRANT: This command is used to provide access privileges to the database.
REVOKE: This command removes access privileges given by using the GRANT command.

GRANT example

Grant select,insert,update on productdetails to writeuser

 

REVOKE example

REVOKE  insert,update on productdetails FROM writeuser;
 
Transaction Control Language (TCL)

TCL commands are used along with the DML statements. The transaction group contains a list of DML statements that need to be executed. The DML statements are written within begin and end blocks. If a single statement fails in the code block the entire transaction fails. All database changes are rolled back when there is a failure.

Begin– Opens transaction block

End-Close transaction block

List of TCL Commands

COMMIT: Used to Commit a Transaction.
ROLLBACK:  In case of any error transaction  is rolled back to previous state.
SAVEPOINT: Sets a save point within a transaction.

 

 

1 thought on “SQL Commands”

Comments are closed.