Saturday, January 2, 2010

SQL Statements / Commands:

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL

1.   DDL - Data Definition Language.

This includes changes to the structure of the table like creation of table, altering table, deleting a table etc. All DDL commands are auto-committed that means it saves all the changes permanently in the database. Here are some commands that come under DDL:
  • CREATE : It is used to create objects (tables, views) in the database
    • Syntax CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
    Example CREATE TABLE EMPLOYEE ( ID NUMBER, Name VARCHAR2(20), Email VARCHAR2(100),DOB DATE);

  • ALTER : It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
    • Syntax "To add a new column in the table use" ALTER TABLE table_name ADD column_name COLUMN-definition;
    "To modify existing column in the table use" ALTER TABLE MODIFY(COLUMN DEFINITION....);
    Example ALTER TABLE EMPLOYEE ADD(ADDRESS VARCHAR2(20));
    ALTER TABLE EMPLOYEE MODIFY (NAME VARCHAR2(100)):

  • DROP : It is used to delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints).
    • Syntax DROP TABLE TABLE_NAME;
    Example DROP TABLE EMPLOYEE;

  • TRUNCATE : Remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark) .
    • Syntax TRUNCATE TABLE table_name;
    Example TRUNCATE TABLE EMPLOYEE;

2.   DML - Data Manipulation Language.

DML commands are used for manipulating the data stored in the table and not the table itself. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back:
  • INSERT : The INSERT statement is a SQL query. It is used to insert data into the row of a table.
    • Syntax INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... valueN);

    INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
    Example INSERT INTO employee (id, name) VALUES (1, 'admin');

  • UPDATE : This command is used to update or modify the value of a column in the table.
    • Syntax UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
    Example UPDATE employee SET name = 'admin' WHERE id = '1';

  • DELETE : It is used to remove one or more row from a table.
    • Syntax DELETE FROM table_name [WHERE condition];
    Example DELETE FROM employee WHERE id = 1;

3.   DCL - Data Control Language.

Data control language are the commands to grant and take back authority from any database user.
  • GRANT : It is used to give user access privileges to a database.
    • Syntax GRANT SELECT, UPDATE ON TABLE_NAME TO USER_NAME, ANOTHER_USER_NAME;
    Example GRANT SELECT, UPDATE ON EMPLOYEE TO HR, APPS;

  • REVOKE : It is used to take back permissions from the user.
    • Syntax REVOKE SELECT, UPDATE ON TABLE_NAME FROM USER1, USER2;
    Example REVOKE SELECT, UPDATE ON EMPLOYEE FROM HR, APPS;

4.   TCL - Transaction Control Language.

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.
  • COMMIT : Commit command is used to save all the transactions to the database.
    • Syntax COMMIT;
    Example DELETE FROM EMPLOYEE WHERE ID = 1;
    COMMIT;

  • ROLLBACK : Rollback command is used to undo transactions that have not already been saved to the database.
    • Syntax ROLLBACK;
    Example DELETE FROM EMPLOYEE WHERE ID = 1;
    ROLLBACK;

  • SAVEPOINT : It is used to roll the transaction back to a certain point without rolling back the entire transaction.
    • Syntax SAVEPOINT SAVEPOINT_NAME;

5.   DQL - Data Query Language.

Data query language is used to fetch data from tables based on conditions that we can easily apply.
  • SELECT : This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.
    • Syntax SELECT expressions FROM TABLE(S)_NAME WHERE conditions;
    Example SELECT * FROM EMPLOYEE WHERE ID = 1;

Posted by at