Archive for April 2015

Types of Views

Friday, 17 April 2015
Posted by Akshay Patil
Types of Views
  1. Simple View
  2. Complex View
  3. Materialized View
  4. Force View
Simple View ...
- Simple View is the view that is created based on a single  table
- Derives data from only one table.
- Contains no functions or groups of data.
- Can perform DML operations through the view.

Syntax :
CREATE [OR REPLACE] VIEW <view name>
AS
<select statement using single table>;
/*View that fetches data from single database table is called Simple View.*/

Dropping A View :
Syntax :
Drop View  View-name ;

Example :
        CREATE VIEW staff AS  SELECT employee_id, last_name, job_id, manager_id,department_id
FROM employees;
 Complex View ...
- Complex view is the view that is created on multiple tables.
- We can not perform DML operations directly on complex View.
- Derives data from many tables.
- Contains functions or groups of data.
Syntax :  
CREATE [OR REPLACEVIEW <view name> AS  <select ,.., from table1,table2 .. Table n where ..>;
Examples :
- With Join
CREATE OR REPALCE VIEW na_emp_v
AS SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
FROM emp e,dept d WHERE e.deptno = d.deptno;

- With Group By
CREATE OR REPLACE VIEW na_emp_v
AS SELECT deptno, COUNT (*) total
FROM emp GROUP BY deptno;  
DML Operations …
On Simple Views :
CREATE TABLE na_dept (deptno NUMBER (10), dname  VARCHAR2(10), loc VARCHAR2(10));
        CREATE VIEW na_dept_view AS SELECT *FROM na_dept;
INSERT INTO na_dept_view VALUES      (10,'ACCOUNTING','ENGLAND');
SELECT *FROM na_dept;
SELECT FROM na_dept_view;

On Complex Views :
CREATE VIEW na_complex_v AS SELECT e.empno, e.ename, e.sal ,d.deptno,d.dname, d.loc  FROM emp e, dept d WHERE e.deptno = .deptno;
INSERT INTO na_complex_v VALUES (7625,'BLAKE', 12000,20,'TESTING','CHICAGO');
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view 

USING INSTEAD OF TRIGGERS:-
-Instead of triggers can be used only with views.
-Effective for views in which there are multiple tables involved.
-Let’s create INSERTED OF TRIGGER on ‘na_complex_v’. Inside trigger, we will write a plsql code to insert data into EMP and DEPT separately as our view is based on these two tables. 
BEGIN
INSERT INTO EMP (empno, ename, Sal) VALUES (:NEW.empno, NEW.ename,:NEW.sal);  
INSERT INTO DEPT (deptno,dname,loc) VALUES (:NEW.deptno, :NEW.dname, :NEW.LOC);
END;

INSERT INTO na_complex_v VALUES (7625,’BLAKE’, 12000,20,’TESTING’,’CHICAGO’);
SELECT *FROM dept;

SELECT * FROM EMP;
 
Materialized View…
-Merialized views are query results that have been stored or "materialized" in advance as schema objects. The FROM clause of the query can name tables, views, and materialized views. 
-Materialized View Replication
-They contain actual data and consume storage space.
-They can be refreshed when the data in their master tables changes.
Example :
CREATE MATERIALIZED VIEW sales_mv AS 
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales 
FROM times t, products p, sales s 
WHERE t.time_id = s.time_id 
AND p.prod_id = s.prod_id 
GROUP BY t.calendar_year, p.prod_id;

DROP TABLE sales;
SELECT * FROM sales_mv WHERE ROWNUM < 4; 

    CALENDAR_YEAR    PROD_ID    SUM_SALES
    ------------------------     ------------   ---------------- 
    1998                           13                 936197.53 
    1998                           26                 567533.83 
          
Built Options :
- BUILD IMMEDIATE: view is built at creation time
- BUILD DEFFERED: view is built at a later time
- ON PREBUILT TABLE: use an existing table as view source
Refresh Options : 
- COMPLETE 
- FAST
- FORCE 

Force View…
We can create views (i.e. view with errors) by using the FORCE option in the CREATE VIEW command:

CREATE [OR REPLACE] [FORCE] VIEW <view name> AS <select statement>;
When Force command is used in the view syntax then even if select statement is invalid VIEW gets created successfully.
Example :
CREATE  OR  REPLACE  FORCE  VIEW na_view AS SELECT * FROM dummy_table;

Oracle SQL View

Posted by Akshay Patil
What is View?
  • A view is a named and validated SQL query which is stored in the Oracle data dictionary. 
  • View does not have storage of its own (i.e. View does not hold any data) .
  • It is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables.
  • A view is a logical representation of the data that can be used just like a table in SELECT statement.
  • They represent the data of one of more tables and derives its data from the tables on which it is based.
  • DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. These tables are called base tables. Views can be based on actual tables or another view also.
  • Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
Views are useful for security and information hiding, but can cause problems if nested too deeply. 
Some of the advantages of using views:
  1. Reduce the complexity of SQL statements
  2. Share only specific rows in a table with other users
  3. Hide the NAME and OWNER of the base table
Types of Views
  1. Simple View
  2. Complex View
  3. Materialized View
  4. Force View
Simple View ...
- Simple View is the view that is created based on a single  table
- Derives data from only one table.
- Contains no functions or groups of data.
- Can perform DML operations through the view.

Syntax :
CREATE [OR REPLACE] VIEW <view name>
AS
<select statement using single table>;
/*View that fetches data from single database table is called Simple View.*/

Dropping A View :
Syntax :
Drop View  View-name ;

Example :
        CREATE VIEW staff AS  SELECT employee_id, last_name, job_id, manager_id,department_id
FROM employees;
 Complex View ...
- Complex view is the view that is created on multiple tables.
- We can not perform DML operations directly on complex View.
- Derives data from many tables.
- Contains functions or groups of data.
Syntax :  
CREATE [OR REPLACE] VIEW <view name> AS  <select ,.., from table1,table2 .. Table n where ..>;
Examples :
- With Join
CREATE OR REPALCE VIEW na_emp_v
AS SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
FROM emp e,dept d WHERE e.deptno = d.deptno;

- With Group By
CREATE OR REPLACE VIEW na_emp_v
AS SELECT deptno, COUNT (*) total
FROM emp GROUP BY deptno;  
DML Operations …
On Simple Views :
CREATE TABLE na_dept (deptno NUMBER (10), dname VARCHAR2(10), loc VARCHAR2(10));
        CREATE VIEW na_dept_view AS SELECT *FROM na_dept;
INSERT INTO na_dept_view VALUES     (10,'ACCOUNTING','ENGLAND');
SELECT *FROM na_dept;
SELECT * FROM na_dept_view;

On Complex Views :
CREATE VIEW na_complex_v AS SELECT e.empno, e.ename, e.sal ,d.deptno,d.dname, d.loc  FROM emp e, dept d WHERE e.deptno = .deptno;
INSERT INTO na_complex_v VALUES (7625,'BLAKE', 12000,20,'TESTING','CHICAGO');
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view 

USING INSTEAD OF TRIGGERS:-
-Instead of triggers can be used only with views.
-Effective for views in which there are multiple tables involved.
-Let’s create INSERTED OF TRIGGER on ‘na_complex_v’. Inside trigger, we will write a plsql code to insert data into EMP and DEPT separately as our view is based on these two tables. 
BEGIN
INSERT INTO EMP (empno, ename, Sal) VALUES (:NEW.empno, NEW.ename,:NEW.sal);  
INSERT INTO DEPT (deptno,dname,loc) VALUES (:NEW.deptno, :NEW.dname, :NEW.LOC);
END;

INSERT INTO na_complex_v VALUES (7625,’BLAKE’, 12000,20,’TESTING’,’CHICAGO’);
SELECT *FROM dept;

SELECT * FROM EMP;
 
Materialized View…
-Merialized views are query results that have been stored or "materialized" in advance as schema objects. The FROM clause of the query can name tables, views, and materialized views. 
-Materialized View Replication
-They contain actual data and consume storage space.
-They can be refreshed when the data in their master tables changes.
Example :
CREATE MATERIALIZED VIEW sales_mv AS 
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales 
FROM times t, products p, sales s 
WHERE t.time_id = s.time_id 
AND p.prod_id = s.prod_id 
GROUP BY t.calendar_year, p.prod_id;

DROP TABLE sales;
SELECT * FROM sales_mv WHERE ROWNUM < 4; 

    CALENDAR_YEAR    PROD_ID    SUM_SALES
    ------------------------     ------------   ---------------- 
    1998                           13                 936197.53 
    1998                           26                 567533.83 
          
Built Options :
- BUILD IMMEDIATE: view is built at creation time
- BUILD DEFFERED: view is built at a later time
- ON PREBUILT TABLE: use an existing table as view source
Refresh Options : 
- COMPLETE 
- FAST
- FORCE 

Force View…
We can create views (i.e. view with errors) by using the FORCE option in the CREATE VIEW command:

CREATE [OR REPLACE] [FORCE] VIEW <view name> AS <select statement>;
When Force command is used in the view syntax then even if select statement is invalid VIEW gets created successfully.
Example :
CREATE  OR  REPLACE  FORCE  VIEW na_view AS SELECT * FROM dummy_table;

Access the current value and next value from a sequence

Thursday, 16 April 2015
Posted by Akshay Patil

NEXTVAL and CURRVAL Pseudo columns
  • NEXTVAL returns the next available sequence value.
  • It returns a unique value every time it is referenced, even for different users.
  • CURRVAL obtains the current sequence value.
  • NEXTVAL must be issued for that sequence before CURRVAL contains a value.
  • The CURRVAL pseudo column returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.  

Accessing Sequence Values 

Example : 
CREATE TABLE  sales_order (order_number NUMBER(9),      order_amount NUMBER(9),      constraint pk_sales_order PRIMARY KEY (order_number);
/* Table created.*/

The INSERT commands shown below insert three rows into the SALES_ORDER table. The INSERT commands reference the ORDER_NUMBER_SEQUENCE.NEXTVAL pseudo column.

INSERT INTO sales_order values (order_number_sequence.nextval, 156 );
INSERT INTO sales_order values (order_number_sequence.nextval,450 );
INSERT INTO sales_order values (order_number_sequence.nextval, 17);

Example : 
SELECT * from sales_order 
Output : 


ORDER_NUMBER  ORDER_AMOUNT
-------------------------------------------------------------
1 156
2 450
3 17
Confirming Sequences

  • Verify the sequence values by querying the USER_SEQUENCES system view with a SELECT command. 
  • This view is part of the database's data dictionary.
SELECT sequence_name, min_value, max_value,increment_by, last_number FROM user_sequences; 
The LAST_NUMBER column displays the next available sequence number.

Using a Sequence
  • Caching sequence values in memory allows faster access to those values.
  • Gaps in sequence values can occur when:
  • A rollback occurs
  • The system crashes
  • A sequence is used in another table.
  • View the next available sequence, if it was created with NOCACHE, by querying the USER_SEQUENCES table. 



Oracle SQL Synonyms

Posted by Akshay Patil
What are Synonyms?
  • Synonyms are aliases for tables, views, sequences.
  • Simplify access to objects by creating a synonym (another name for an object).
  • Shorten lengthy object names.
  • Creating a Synonym for an Object
  • To refer to a table owned by another user, you need to prefix the table name with the name of the user who created it followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views.
There are two categories of synonyms: PUBLIC and PRIVATE :
  • A PUBLIC synonym can be accessed by any system user.
  • The individual creating a PUBLIC synonym does not own the synonym.
  • Rather, it will belong to the PUBLIC user group that exists.
  • PRIVATE synonyms, on the other hand, belong to the system user that creates them and reside in that user's schema. 
  • A system user can grant the privilege to use private synonyms that they own to other system users.
  • In order to create synonyms, we need to have the CREATE SYNONYM privilege.
  • We must have the CREATE PUBLIC SYNONYM privilege in order to create public synonyms.
Guidelines :
  • The object cannot be contained in a package.
  • A private synonym name must be distinct from all other objects owned by the same user.
Creating Synonyms :
Syntax : 
CREATE [PUBLIC] SYNONYM synonym_nameFOR    object_name; 
In the syntax:
PUBLIC :-  creates a synonym accessible to all users
synonym_name :-  is the name of the synonym to be created
object_name     :-      identifies the object for which the synonym is created
Create a shortened name for the SALES_ORDER table.
CREATE SYNONYM SYN_SOFOR    SALES_ORDER;/*Synonym Created.*/
Removing Synonyms :
  • Drop a synonym.
DROP SYNONYM SYN_SO;/*Synonym dropped.*/
  • A PRIVATE synonym can be deleted by its owner.
  • A PUBLIC synonym can only be deleted by a user with DBA privileges.
  • In order to drop a public synonym you must include the PUBLIC keyword in the DROP SYNONYM command.  
  • In order to drop a public synonym, you must have the DROP PUBLIC SYNONYM privilege. 
DROP PUBLIC  SYNONYM synonym_name;/*Synonym dropped.*/
Renaming Synonyms :
  • Private synonyms can be renamed with the RENAME SYNONYM command.
  • All existing references to the synonym are automatically updated.
  • Any system user with privileges to use a synonym will retain those privileges if the synonym name is changed.  
Syntax : 
RENAME old_synonym_name TO new_synonym_name;/*Synonym renamed.*/
  • The RENAME SYNONYM command only works for private synonyms.
  • If we attempt to rename a public synonym such as the TBLSPACES synonym, Oracle will return an ORA-04043: object TBLSPACES does not exist error message as is shown here.
RENAME TBLSPACES TO TS;Error report:SQL Error: ORA-04043: object TBLSPACES does not exist04043. 00000 -  "object %s does not exist"
Advantages of Synonyms
  • A synonym provides what is termed location transparency because the synonym name hides the actual object name and object owner from the user of the synonym.  
  • A public synonym can be used to allow easy access to an object for all system users.  
  • The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time.
  • Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object. 
To be removed
  • For example, consider a synonym, MyContacts, that references the Person.Contact table in Adventure Works. If the Contact table is dropped and replaced by a view named Person.Contact, MyContacts now references the Person.Contact view.
Disadvantages of Synonyms :
  • There is no ALTER SYNONYM statement, we first have to drop the synonym, then re-create the synonym with the same name. 
  • SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object. 



Oracle SQL Sequence

Posted by Akshay Patil

What is Sequence?


  • Sequences are used to generate unique, sequential integer values that are used as primary key values in database tables.
  • Speeds up the efficiency of accessing sequence values when cached in memory.
  • The sequence of numbers can be generated in either ascending or descending order.  

Create SEQUENCE Statement
Syntax : 
CREATE SEQUENCE sequence_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
START WITH  
Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

INCREMENT BY 
Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits.
If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

MAXVALUE 
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

NOMAXVALUE  
Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.

MINVALUE 
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE  
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.

CYCLE  
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE  
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

CACHE 
Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.

NOCACHE  
Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default. 

Creating a Sequence

  • Create a sequence named ORDER_NUMBER_SEQUENCE to be used for the primary key of the DEPT table.
  • Do not use the CYCLE option.
CREATE SEQUENCE ORDER_NUMBER_SEQUENCE INCREMENT BY 1 START WITH 1 MAXVALUE 100 NOCACHE NOCYCLE;/*Sequence created.*/
Confirming Sequences

  • Verify the sequence values by querying the USER_SEQUENCES system view with a SELECT command. 
  • This view is part of the database's data dictionary.

SELECT sequence_name, min_value, max_value,increment_by, last_number FROM user_sequences; 
The LAST_NUMBER column displays the next available sequence number.

Modifying a Sequence
Can alter the increment value, maximum value, minimum value, cycle option, or cache option by the Alter command.
ALTER SEQUENCE order_number_sequence INCREMENT BY 1 MAXVALUE 999999 NOCACHE NOCYCLE  /*Sequence altered.*/
Guidelines for Modifying a Sequence

  • You must be the owner or have the ALTER privilege for the sequence.
  • Only future sequence numbers are affected.
  • The sequence must be dropped and re-created to restart the sequence at a different number.
  • Some validation is performed.

Removing a Sequence

  • Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
  • Once removed, the sequence can no longer be referenced.
DROP SEQUENCE order_number_sequence;
/*Sequence dropped.*/

Accessing Sequence Values 
Example : 
CREATE TABLE sales_order (order_number NUMBER(9),     order_amount NUMBER(9),     constraint pk_sales_order PRIMARY KEY (order_number);
/* Table created.*/


The INSERT commands shown below insert three rows into the SALES_ORDER table. The INSERT commands reference the ORDER_NUMBER_SEQUENCE.NEXTVAL pseudo column.
INSERT INTO sales_order values (order_number_sequence.nextval, 156 );
INSERT INTO sales_order values (order_number_sequence.nextval,450 );
INSERT INTO sales_order values (order_number_sequence.nextval, 17);

Example : 
SELECT * from sales_order 
Output : 

ORDER_NUMBER ORDER_AMOUNT
-------------------------------------------------------------
1 156
2 450
3 17


NEXTVAL and CURRVAL Pseudo columns


  • NEXTVAL returns the next available sequence value.
  • It returns a unique value every time it is referenced, even for different users.
  • CURRVAL obtains the current sequence value.
  • NEXTVAL must be issued for that sequence before CURRVAL contains a value.
  • The CURRVAL pseudo column returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.  

Using a Sequence

  • Caching sequence values in memory allows faster access to those values.
  • Gaps in sequence values can occur when:
  • A rollback occurs
  • The system crashes
  • A sequence is used in another table.
  • View the next available sequence, if it was created with NOCACHE, by querying the USER_SEQUENCES table. 


Oracle SQL Indexes

Posted by Akshay Patil

What is Index?

  • Allows users to quickly locate specific records.
  • It is a performance-tuning method that is used to speed up retrieval of records by using a pointer. 
  • An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
  • Can reduce disk I/O by using rapid path access method to locate the data quickly.
  • Is independent of the table it indexes.
  • Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row. 
  • The users cannot see the indexes, they are just used to speed up searches/queries.
How Are Indexes Created?
Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
Manually: Users can create non - unique indexes on columns to speed up access time to the rows.

Creating an Index :

Syntax : 
CREATE INDEX index_name ON table (column[, column]...);
Example : 
CREATE INDEX idx_cust_id_ ON customers_info (customer_id);
/*---Index Created.---*/
When To Create Index?
  • The column is used frequently in the WHERE clause or in a join condition.
  • The column contains a wide range of values.
  • The column contains a large number of null values.
  • Two or more columns are frequently used together in a WHERE clause or a join condition.
  • The table is large and most queries are expected to retrieve less than 2–4% of the rows.
When not to create Index?
  • The table is small.
  • The columns are not often used as a condition in the query.
  • Most queries are expected to retrieve more than 2–4% of the rows.
  • The table is updated frequently.
Removing an Index

Remove an index from the data dictionary.
DROP INDEX index;
Remove the EMP_ENAME_IDX index from the data dictionary.
DROP INDEX idx_cust_id;/* Index dropped. */
To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.

Set Default value to LOV

Tuesday, 14 April 2015
Posted by Akshay Patil
    Select list item in oracle apex always have two values that is display value and return value.
Syntax :
SELECT Display_Value,Return_Value FROM Table_Name;
E.g.:
 SELECT EXPENSE_START_DATE,PERIOD_ID FROM U_EXPENSE_PERIODS;

    To set the default value for a Select List item, set Default Value Type to PL/SQL Function Body, then enter your SQL query (that returns a single value) in the Default attribute for the item, with suitable PL/SQL around it.
Single value column specifyied in sql query must be same as return value column specified in source query of select list.
Syntax:
DECLARE
  v_value VARCHAR2(<your data max length>);
BEGIN
  SELECT <yourcolumn>
  INTO v_value
  FROM <yourquery>;
  RETURN v_value;
END;

E.g.:
DECLARE
  v_value number;
BEGIN
  SELECT PERIOD_ID
  INTO v_value
  FROM U_EXPENSE_PERIODS
  WHERE TO_CHAR(EXPENSE_START_DATE, 'Mon-YY') = TO_CHAR(SYSDATE, 'Mon-YY') ;
  RETURN v_value;
END;

Introduction to SQL

Posted by Akshay Patil
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.
       SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.
       Initially, It was named as SEQUEL(Structured English Query Language) as it is very familiar with English language that is Human readable and understandable language which is used to perform interaction with database.SEQUEL later became SQL.


Why SQL?

SQL allows to perform various operations like :
  • Execute queries against a database
  • Retrieve data from a database
  • Insert records in a database
  • Update records in a database
  • Delete records from a database
  • Create new databases
  • Create new tables in a database
  • Create stored procedures in a database
  • Create views in a database
  • Set permissions on tables, procedures, and views


 Types of SQL commands :


SQL provides 4 types of SQL commands or statements as given below :


DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. 

Some examples:
CREATE - to create objects like Table,Views,Sequences,Indexes,Synonyms etc. in the database

ALTER - alters the structure of the database
DROP - delete objects like Table,Views,Sequences,Indexes,Synonyms etc. 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

    DML
    Data Manipulation Language (DML) statements are used for accessing and managing data within relational database tables. 
    Some examples:
    SELECT - retrieve data from the a database tables
    INSERT - insert data into a table
    UPDATE - updates existing data within a table
    DELETE - deletes specific or all records from a table, the space for the records remain
    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

      DCL
      Data Control Language (DCL) statements are used to control privileges in database.
      To perform any operation in database such as creating tables,views or perform insert,delete to table we need privileges.
      Some examples:
      GRANT - gives user's access privileges to database
      REVOKE - withdraw access privileges given with the GRANT command

      The operations for which privileges may be granted to or revoked from a user or role may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.

      In the Oracle database, executing a DCL command issues an implicit commit. Hence you cannot roll back the command.

        TCL
        Transaction Control Language (TCL) statements are used to manage the changes made by DML statements. 
        It allows statements to be grouped together into logical transactions.
        COMMIT - save work done
        SAVEPOINT - identify a point in a transaction to which you can later roll back or return
        ROLLBACK - restore database to original since the last COMMIT
        SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

        Welcome to My Blog

        Study Basics

        Oracle APEX Oracle SQL Oracle PL/SQL

        Popular Post

        Blogger templates

        Total Pageviews

        Powered by Blogger.

        Unordered List

        Follow us on Facebook!

        - Copyright © TechnicalBits -Robotic Notes- Powered by Blogger - Designed by Johanes Djogan -