Youtube Channel

SQL languages

SQL is subdivided into four sub-languages they are-
  1. Data Definition Language -DDL
  2. Data Manipulation Language -DML
  3. Transaction Control Lanuage -TCL
  4. Data Control Language -DCL
Data Definition Language

Data definition language is used to deal with  the structure of the table .
There are five commands in DDL they are-
  1. DROP
  2. RENAME
  3. CREATE
  4. ALTER
  5. TRUNCATE
Trick:
To memorize all the commands use DR CAT.
where 
D= DROP
R=RENAME
C=CREATE
A=ALTER
T=TRUNCATE

Lets, see the examples of above command, for that consider the following table named student with attributes roll_no, name and course.


CREATE
  • This command is used to create a table in database
SYNTAX

SQL> CREATE TABLE TABLE_NAME(COLUMN_1 DATATYPE(SCALE),COLUMN_2 DATATYPE(SCALE),....COLUMN_N DATATYPE (SCALE));

EXAMPLE :

SQL> CREATE TABLE STUDENT (ROLL_NO NUMBER(3), NAME VARCHAR(10),COURSE VARCHAR(10));

DROP
  • This command is used to drop or delete the table from database.
SYNTAX

SQL> DROP TABLE TABLE_NAME;

EXAMPLE

SQL> DROP TABLE STUDENT;

RENAME
  • This command is used to rename a table or column.
SYNTAX

SQL>RENAME TABLE OLD_TABLE_NAME TO NEW_TABLE_NAME;

EXAMPLE

SQL> RENAME TABLE STUDENT TO STUDENT_INFO;

ALTER
  • This command is used to modify,add,drop the size of the columns of a table
SYNTAX

SQL> ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME DATATYPE();

EXAMPLE

 SQL> ALTER TABLE STUDENT MODIFY COLUMN NAME VARCHAR(20);



TRUNCATE
  • This command is used to delete the all the data from the table
SYNTAX

SQL> TRUNCATE TABLE TABLE_NAME;

EXAMPLE

 SQL> TRUNCATE TABLE STUDENT;

Data manipulation Language 

DML is used to deal with  the CURD Operation on table .
There are four commands in DML they are-
  1. SELECT
  2. UPDATE
  3. INSERT
  4. DELETE
Trick:
To memorize all the commands use SUDI.
where 
S= SELECT
U=UPDATE
D=DELETE
I=INSERT

SELECT
  • This command is used to select the rows of a table
SYNTAX

SQL> SELECT * FROM TABLE_NAME;
SQL> SELECT COLUMN_NAME FROM TABLE_NAME;


EXAMPLE

SQL>SELECT * FROM STUDENT;
SQL> SELECT ROLL_NO,NAME, COURSE FROM STUDENT;

 UPDATE
  • This command is used to modify the data of the table.
SYNTAX

SQL> UPDATE TABLE_NAME SET COUMN_NAME=VALUE WHERE CONDTION;

 EXAMPLE
SQL> UPDATE STUDENT SET NAME='RAKESH' WHERE ROLL_NO=101;

INSERT
  • This command is used to insert the data to table 
  • It can be done by inserting a particular row or by multiple rows
SYNTAX

SQL> INSERT INTO TABLE_NAME VALUES(VALUE1,VLAUE2...VALUEN);
SQL >INSERT INTO TABLE_NAME VALUES(&VALUE1,&VALUE2,..&VALUEN);

EXAMPLE
SQL>  INSERT INTO STUDENT VALUES(104,'RANJAN','BSC');
SQL> INSERT INTO STUDENT VALUES(&ROLL_NO,'&NAME','&COURSE');
Note : for inserting multiple rows press '/' after second example;

DELETE 
  • This command is used to delete the row from table
  • Temporary deletion of data.
SYNTAX

SQL> DELETE FROM TABLE_NAME WHERE CONDTIONS;

EXAMPLE 
SQL>DELETE FROM STUDENT WHERE ROLL_NO=101; 


Transaction control language

  • TCL commands are used to manage the changes made by DML commands.
There are three commands in TCL
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
COMMIT
  • The commit command saves all transactions to the database.
SYNTAX
 
 SQL>COMMIT;

ROLLBACK
  • This command is used to undo transactions that have not already been saved to database
SYNTAX
 
 SQL>ROLLBACK;

SAVEPOINT
  • This command offers a mechanism to roll back portions of transactions.
SYNTAX
 
SQL> SAVEPOINT Name_of_Savepoint;
 
Data Control Language
  • DCL command are used to provide privileges and permissions to DBA or User.
There are two main command in DCL
  • GRANT
  • REVOKE
GRANT
  • This command is used to grant permission to database user.
SYNTAX
  • SQL>GRANT privilege_name TO USER_NAME; 
REVOKE
  • This command is used to take back the privilege from user.
SYNTAX
 
SQL> REVOKE privilege_name FROM USER;