SQL STATEMENTS

SQL is divided into the following :-

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Retrieval Language (DRL)
  • Transaction Control Language (TCL)
  • Data Control Language (DCL)
  • DDL - Create, Alter, Drop, Truncate, Rename
  • DML - Insert, Update, Delete
  • DRL - Select
  • TCL Commit, Rollback, Savepoint
  • DCL - Grant, Revoke

 

DRL COMMANDS

CREATE TABLE

Syntax:

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);

Create table XXSTP_EMP_DETAILS

(

Cust_ID number primary Key,

Cust_Name  varchar2(200),

Cust_join_date date

);

 

DML COMMANDS

INSERT

This will be used to insert the records into table.

We have two methods to insert.

  • By value method
  • By address method
  1. a) USING VALUE METHOD

Syntax:

insert into <table_name> values (value1, value2, value3 …. Valuen);

insert into XXSTP_EMP_DETAILS values (1,’Rajesh’,’01-Jan-2016’);

insert into XXSTP_EMP_DETAILS values (2,’Amit’,’01-Feb-2016’);

UPDATE

This can be used to modify the table data.

Syntax:

Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Update XXSTP_EMP_DETAILS set Cust_Name=’Rajesh Singh’

Where cust_id=1;

DELETE

This can be used to delete the table data temporarily.

Syntax:

Delete <table_name> where <condition>;

Delete from XXSTP_EMP_DETAILS where cust_id=1;

Note: We have to explicitly commit the DML transactions

DDL COMMANDS

ALTER

This can be used to add or remove columns and to modify the precision of the datatype.

Syntax:

alter table <table_name> add <col datatype>;

alter table XXSTP_EMP_DETAILS add Cust_Father_Name varchar2(200);

TRUNCATE

This can be used to delete the entire table data permanently.

Syntax:

truncate table <table_name>;

Truncate table XXSTP_EMP_DETAILS;

DROP

This will be used to drop the database object;

Syntax:

Drop table <table_name>;

Drop table XXSTP_EMP_DETAILS;

RENAME

This will be used to rename the database object;

Syntax:

rename <old_table_name> to <new_table_name>;

Rename XXSTP_EMP_DETAILS to XXSTP_EMPLOYEES_DETAILS;

Note :- DDL statements are auto-commit. We don’t need to commit.

TCL COMMANDS

COMMIT

This will be used to save the work.

Syntax:

Commit;

ROLLBACK

This will undo the operation.

Syntax:

Rollback;

SAVEPOINT

You can use savepoints to rollback portions of your current set of transactions.

Syntax:

Savepoint <savepoint_name>;

DCL COMMANDS

DCL commands are used to granting and revoking the permissions.

GRANT

This is used to grant the privileges to other users.

Syntax:

Grant <privileges> on <object_name> to <user_name> [with grant option];

Grant all on XXSTP_EMPLOYEES_DETAILS to ORCL;

REVOKE

This is used to revoke the privileges from the users to which you granted the privileges.

Syntax:

Revoke <privileges> on <object_name> from <user_name>;

Revoke all on XXSTP_EMPLOYEES_DETAILS from ORCL;