Wednesday, 27 January 2016

Oracle Commands

SQL Support the following categories of commands:
Data Definition Language(DDL)
Data Manipulation Language(DML)
Data Control Language(DCL)
Transaction Control Language(TCL)Statements

Data definition Language(DDL) Statements:
Data definition statements define,maintain and drop schema objects when they are no longer needed. DDL statements also include statements that permit a user to grant other user the privileges , or right, to access the database and specific object with in the database.
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
Data Manipulation Language(DML) Statements:
The data Manipulation language statements manipulate the database's data. For examples querying,inserting,updating and deleting rows of a table are all DML operations. Locking a table or view and examining the execution plan of an SQL statements are also DML operations.

SELECT :retrieve data from the a database
INSERT : insert data into a table
UPDATE : updates existing data within a table
DELETE : deletes all records from a table.
MERGE : UPSERT operation (insert or update)
CALL -:call a PL/SQL or Java subprogram
EXPLAIN PLAN : explain access path to data
LOCK TABLE : control concurrency
Data Control Language(DCL) Statements:
Data control language provides users with privilege commands. The owner of the database objects, say tables, has the sole authority over them. The owner cal allow other database users access to the objects as for the description. Granting privileges(insert,select..) to others.
GRANT : gives user's access privileges to database
REVOKE : withdraw access privileges given with the GRANT command
Transaction Control Language(TCL)Statements:
Transaction control Statements are manage the changes made by DML statements. The enable the user or application developer to group changes into logical transactions. Examples include COMMIT,ROLLBACK and SAVEPOINT.
COMMIT : save work done
SAVEPOINT :identify a point in a transaction to which you can later roll back
ROLLBACK : restore database to original since the last COMMIT
SET TRANSACTION :Change transaction options like isolation level and what rollback segment to use