components of SQL

Components of SQL:

Oracle SQL complies with industry accepted standards. The SQL Contains 5 Sub lnguages.

1) DRL or DQL.

2) DML.

3) DDL.

4) DCL.

5) TCL.

1).Data Retrieval/Query Language (DRL/DQL):

It includes SELECT statement. Using it one can query in database.

SELECT:  It used to retrieve the information from database objects for read only purpose.

Syntax: Select * from   <table_name>;

2).Data Manipulation Language(DML):

DML statements are used for managing data within schema objects. SQL commands which comes under Data Manipulation Language (DML) are :

i) INSERT              ii) UPDATE           iii) DELETE

i) INSERT(new content): Insert data or rows into a table.

                Syntax: INSERT INTO  <table_name> [list of columns] 

                                VALUES(column_name DATATYPE,..........);

ii) UPDATE(modify): Updates existing data or rows within a table.

                Syntax: UPDATE  <table_name>

                                SET column1=value1,column2=value2,... 

                                WHERE some_column=some_value;

iii) DELETE(remove): Deletes all records or data from a table, the space occupied will remain.

                Syntax: DELETE FROM table_name

                                WHERE some_column=some_value;

3).Data Definition Language(DDL):

DDL statements are used to define the database structure or schema. SQL commands which comes under Data Definition Language (DDL) are :

i) CREATE             ii) ALTER                               iii) DROP                               iv)TRUNCATE

i) CREATE – To create objects in the database.

                Syntax: CREATE TABLE  <table_name>(

                                column_name1 data_type(size),

                                column_name2 data_type(size),

                                 column_name3 data_type(size),

                                ....

                                );

ii) ALTER – Alters the structure of the database. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

                a) ADD:  To add a column in a table, use the following syntax:

                Syntax: ALTER TABLE  <table_name>

                                ADD column_name datatype;

                b) DROP: To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

                Syntax: ALTER TABLE  <table_name>

                                DROP COLUMN column_name;

                c) MODIFY: To change the data type of a column in a table, use the following syntax:

                Syntax: ALTER TABLE  <table_name>

                                MODIFY COLUMN column_name datatype;

                d) RENAME: To rename column name to the old column name to new column name in a  table, use the following syntax:

                Syntax: ALTER TABLE  <table_name>

                                RENAME COLUMN  old_column_name to new_column_name;

iii) DROP – Delete command is used to delete the tables from database.

                Syntax: DROP table  <table_name>;

iv) TRUNCATE – Remove all records or rows from a table, also free the space occupied by those records.

                Syntax: TRUNCATE table  <table_name>;

4).Data Control Language(DCL):

It used to share the information between the users.

i) GRANT.            ii) REVOKE.

i) GRANT(give permission): The GRANT command is used to allow another schema access to a privilege. GRANT command can be issued not only on TABLE OBJECT, but also on VIEWS, SYNONYMS, SEQUENCES Etc.

                Syntax: GRANT <privilege1 [, privilege2....]

                                ON  <object_Name>

                                TO <user1> [,user | role, PUBLIC....];

ii) REVOKE(cancle): It used to remove the access allowed by GRANT. REVOKE privileges is assigned not only on TABLE OBJECT, but also on VIEWS, SYNONYMS, SEQUENCES  etc.

                Syntax: REVOKE <privilege1 [, privilege2...]

                                 ON <object_name>

                                FROM <user> [,user | role, PUBLIC...];

5).Transaction Control Language(TCL):
It used to share the information between users.

i) COMMIT          ii) ROLLBACK      iii) SAVEPOINT

i)COMMIT: The COMMIT statement ends the current transaction, making any changes made during that transaction permanent, and visible to other users.

                Syntax: COMMIT;

ii) ROLLBACK: The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data. If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over.

                Syntax: ROLLBACK [savepoint name];

iii) SAVEPOINT: SAVEPOINT names and marks the current point in the processing of a transaction. Savepoints let you roll back part of a transaction instead of the whole transaction.

                Syntax: SAVEPOINT TO savepoint_name;