Quick Introduction to types of SQL Statements

#sql #database #webdev

Types of SQL Statements

DDL – Data Definition Language
- Used to define Database (creating or modifying) objects like Table, View, Index, Sequence
- Commands ⇒ CREATE, ALTER, DROP, TRUNCATE, RENAME
  
  Samples
  
  CREATE TABLE STUDENTS (
  ID INT NOT NULL PRIMARY KEY,
  Name VARCHAR2(10),
  Address VARCHAR2(20)
  );
  
  DROP TABLE STUDENTS;
DML – Data Manipulation Language.
- Used to manipulate the data in Database objects like Table, View, Index
- Commands ⇒ INSERT, UPDATE, DELETE
  
  Samples
  
  INSERT INTO STUDENTS
  (ID, Name, Address)
  VALUES (1, "Mohamed Harmoush", "Alexandria, Egypt");
  
  DELETE FROM Student WHERE ID = 3
DRL/DQL – Data Retrieval Language/Data Query Language.
- Used to retrieve information from the database objects. It is for read only purpose.
- Commands ⇒ SELECT
  
  Samples
  
  SELECT Name, Address FROM STUDENTS
TCL- Transaction Control Language or TCS - Transaction Control Statement.
- TCL ⇒ manage changes made by DML statements
- Commands ⇒ COMMIT, ROLLBACK, SAVEPOINT,SET TRANSACTION
  
  Samples
  
  DELETE FROM STUDENTS
  WHERE ID = 3;
  COMMIT;
  
  SAVEPOINT SAVEPOINT_NAME;
DCL – Data Control Language.
- In DCL, we control access to data. This is used for permission management and who can access the data.
- Commands ⇒ GRANT, REVOKE
  
  Samples
  
  GRANT SELECT, UPDATE ON STUDENTS TO Mohamed, ANOTHER_USER;
  
  REVOKE SELECT, UPDATE ON STUDENTS FROM Mohamed, USER2;
SCL – Session Control Language or SCS - Session Control Statement
- Session Control Statements dynamically manage the properties of a user session
- Commands ⇒ ALTER SESSION, SET ROLE