Types of SQL Commands

-

Type of SQL statements are divided into five different categories: 

  1. Data definition language (DDL)
  2. Data manipulation language (DML), 
  3. Data Control Language (DCL), 
  4. Transaction Control Statement (TCS), 
  5. Session Control Statements (SCS).

DDL:(Create,Alter,Drop,Truncate,Rename)

StatementDescription
CREATECreate new database/table.
ALTERModifies the structure of database/table.
DROPDeletes a database/table.
TRUNCATERemove all table records including allocated table spaces.
RENAMERename the database/table.

DML:(Insert,Update,Delete,Merge,Lock Table,Call Explain Plan)

StatementDescription
SELECTRetrieve data from the table.
INSERTInsert data into a table.
UPDATEUpdates existing data with new data within a table.
DELETEDeletes the records rows from the table.
MERGEMERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not.
LOCK TABLELOCK TABLE statement to lock one or more tables in a specified mode. Table access denied to a other users for the duration of your table operation.
CALL
EXPLAIN PLAN		Statements are supported in PL/SQL only for executed dynamically. CALL a PL/SQL program or EXPLAIN PATH access the data path.

DCL Command:(Grant,Revoke,Analyze,Audit)


StatementDescription
GRANTGives privileges to user for accessing database data.
REVOKETake back for given privileges.
ANALYZEANALYZE statement to collect statistics information about index, cluster, table.
AUDITTo track the occurrence of a specific SQL statement or all SQL statements during the user sessions.

TCS:(Commit,Rollback,Savepoint)

StatementDescription
COMMITPermanent work save into database.
ROLLBACKRestore database to original form since the last COMMIT.
SAVEPOINTCreate SAVEPOINT for later use ROLLBACK the new changes.

SCS :(Alter Session,SET Role):


StatementDescription
ALTER SESSIONALTER SESSION statement to modify conditions or parameters that are affect to your database connection.
SET ROLESET ROLE statement to enable or disable the roles that are currently enabled for the session.

Comments

Post a Comment

Popular posts from this blog

Oracle PLSQL Create Table

-
Image
Create Table :                             The Create table statement is used to create a new table in a database. Syntax for Create Table:                            CREATE TABLE Table_name ( Column1 data type (), Column2 data type(),…. );   The data type Parameters specifies the type of data the column can hold (e.g varchar, number, integer etc). Example for Create Table:                             In this example we create table called emp that contains four columns: ename,   eid, DOJ, esal:                             CREATE TABLE EMP1 ( ename varchar2 (20), eid number(6), DOJ date(), esal number(6));
Read more

ORACLE VIEW

-
In Oracle, view is a virtual table that does not physically exist.View does not store any data and It is stored in Oracle data dictionary. A view is created by a query joining one or multiple tables. Syntax : CREATE   VIEW <view_name> as SELECT   columns FROM    tables WHERE    conditions; Example let's take an example to create a view.In this example,we are creating two table emp and dept. EMP table:     CREATE TABLE  EMP      (    EMP_ID NUMBER,    EMP_NAME VARCHAR2(4000),    DEPTNO NUMBER     ) ;  / DEPT table: CREATE TABLE  DEPT       (    DEPTNO NUMBER ,           DNAME VARCHAR2(100)     )  ; /   Execute the following query to create a view name EMP_DET. Create View Query: ...
Read more

Oracle/PLSQL Sequence

-
Description: Sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate Primary Key Values. Prerequisites : To create a sequence in your own schema,you must have CREATE SEQUENCE system privilege. To create a sequence in another user's schema,you must have CREATE ANY SEQUENCE system privilege. Syntax: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; Example: The following statement creates the sequence  CREATE SEQUENCE seq_empid MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; The first sequence number that it would use is 1 and each subsequent number would increment by 1(i.e..2,3,4,..).It will values for performance. If you omit the MAXVALUE when creating the Sequence,it will automatically default to :     MAXVALUE 999999999999999...
Read more