Sequence & Synonym

Sequence In Oracle

**Its a Numeric Value Generator
**Its Mostly used in Primary Key Columns
**Its One of the Schema Object
**Its a Sharable Object

NEXTVAL::


The Oracle NEXTVAL function is used to retrieve the next value in a sequence. The Oracle NEXTVAL function must be called before calling the CURRVAL function, or an error will be thrown.

Example::

SQL> create sequence Seq_1;

Sequence created.

SQL> select Seq_1.Nextval from dual;

NEXTVAL
----------
1

SQL> select Seq_1.Nextval from dual;

NEXTVAL
----------
2

SQL> select Seq_1.Nextval from dual;

NEXTVAL
----------
3

SQL> create sequence Seq_2
2  START WITH 10
3  INCREMENT BY 3
4  MAXVALUE 10000
5  CYCLE
6  CACHE 5;

Sequence created.

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
10

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
13

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
16

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
19


CURRVAL::


**It Returns the current value of a sequence.
**If the Currval Once executed the Nextval Should be the Next Value of the Currval.

Example::

SQL> select Seq_2.Nextval from dual;

NEXTVAL
----------
19
SQL> select Seq_2.CURRVAL from dual;

CURRVAL
----------
19

SQL> select Seq_2.Nextval from dual;

NEXTVAL
----------
22


SYNONYM::

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Example::

SQL> CREATE SYNONYM Emp For Employees;

SYNONYM CREATED.

SQL> select * From Emp;

Advantages of synonyms

Synonyms are often used for security and convenience.
for Example, they can do the following things.

• Mask the name and owner of an object.
• Provide location transparency for remote objects of a distributed database.
• Simplify SQL statements for database users

Private & Public Synonyms::

• A normal synonym is called private synonym whereas a public synonym is created by a keyword public.
• A private synonym is accessible within your schema and a public synonym is accessible to any schema in the database.

----------END----------

Read More

Pseudocolumn

Pseudocolumn In Oracle

A pseudocolumn behaves like a table column, but it's not actually stored in the table.

TYPES::

1.SYSDATE
2.SYSTIMESTAMP
3.USER
4.UID
5.ROWNUM
6.ROWID
7.NEXTVAL   --- Sequence Related Pseudo Columns
8.CURVAL --  Sequence Related Pseudo Columns


1.SYSDATE::

SYSDATE returns the current date set for the operating system on which the database resides. The datatype of the returned value is DATE.

Example::

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
---------
10-JUN-16

2.SYSTIMESTAMP::

SYSTIMESTAMP is the built in function which returns the current database system date including fractional seconds and region time zone.

Example::

SQL> SELECT SYSTIMESTAMP FROM Dual;

SYSTIMESTAMP
-----------------------------------
10-JUN-16 07.20.13.909000 PM +05:30

3.USER::

The USER indicate in Which USER We are Currently loged In.

Example::

SQL> SELECT USER FROM Dual;

USER
-----
HR

4.UID::

The Oracle UID is a pseudo-column containing a numeric value identifying the current user. Calling the UID() function will return the ID number, the user who is currently logged in.

Example::

SQL> SELECT UID FROM Dual;

       UID
----------
        33

SQL> SELECT USER,UID FROM Dual;

USER                                  UID
------------------------------ ----------
HR                                     33

5.ROWNUM::

ROWNUM is a pseudocolumn returning a sequential number along with the rows retrieved.

Example::

SQL> SELECT ROWNUM "S.NO",Department_Id,Department_Name From Departments;

------------------------------------------------------------------------
      S.NO     DEPARTMENT_ID         DEPARTMENT_NAME
---------- ------------- -----------------------------------------------
         1            10                                Administration
         2            20                                Marketing
         3            30                                 Purchasing
         4            40                                 Human Resources
         5            50                                 Shipping
         6            60                                 IT
         7            70                                 Public Relations
         8            80                                 Sales
         9            90                                 Executive
        10           100                               Finance
        11           110                               Accounting
        12           120                               Treasury
        13           130                               Corporate Tax
        14           140                               Control And Credit
        15           150                               Shareholder Services

Example::

To display &n(th ) Maximum Salary 
=======================================================
SELECT MIN(SALARY)
FROM
  (
   SELECT ROWNUM,SALARY FROM
             (
SELECT SALARY FROM EMPLOYEES ORDER BY 1 DESC)
WHERE ROWNUM <= nth
   );
=======================================================

6.ROWID::

An Oracle server assigns each row in each table with a unique ROWID(Address ID) to identify the row in the table.

Example::

SQL> SELECT ROWID,Department_Name From Departments;
------------------                            -----------------------------
ROWID                                      DEPARTMENT_NAME
------------------                         -----------------------------
AAAC8/AAEAAAAA3AAA        Administration
AAAC8/AAEAAAAA3AAB       Marketing
AAAC8/AAEAAAAA3AAC       Purchasing
AAAC8/AAEAAAAA3AAD       Human Resources
AAAC8/AAEAAAAA3AAE       Shipping
AAAC8/AAEAAAAA3AAF       IT
AAAC8/AAEAAAAA3AAG       Public Relations

-----END-----

Read More

Constraints

CONSTRAINTS

==>It Enforce the rule on table.
==>We can create Constrains while creating the table and After the table has been created.
1.For Column level(Cont Name  ...SYS_C000001)
2.For Table Level(We assign the OWN names)

Type Of Constraints::

There are five types of constraints are there.

1.Primary Key
2.Unique Key
3.Foreign Key
4.CHECK Constraint
5.NOT NULL
------------------------------------------------------------------------------------------
TYPES                      || DUPLICATE   ||              NULL
-------------------------------------------------------------------------------------------
1.Primary Key(PK) NOT ALLOW NOT ALLOW
--------------------------------------------------------------------------------------------
2.Unique Key(U)         NOT ALLOW              ALLOW
--------------------------------------------------------------------------------------------
3.Forign Key(RK)          ALLOW                   ALLOW
--------------------------------------------------------------------------------------------
4.CHECK(CK) It's Our Own Condition
---------------------------------------------------------------------------------------------
5.NOT NULL(NN) NOT ALLOW
---------------------------------------------------------------------------------------------

Naming Rules::

Examples::

Employee_Id ------------------------> emp_id_pk
email_Id ------------------------> emp_mail_uk
First_Name ------------------------> emp_fname_nn
Salary ------------------------> emp_Salary_ck
Department_Id ------------------------> emp_did_rk

Examples::
-

CREATE TABLE STUDENTS_DB
(
STU_ID NUMBER(5),
STU_NAME VARCHAR2(50) NOT NULL,
STU_GENDER CHAR,
STU_EMAIL VARCHAR2(50),
STU_DID NUMBER(10),

CONSTRAINT STU_ID_PK PRIMARY KEY(STU_ID),
CONSTRAINT STU_GENDER_CK CHECK(STU_GENDER IN ('M','F','m','f')),
CONSTRAINT STU_EMAIL_UK UNIQUE(STU_EMAIL),
CONSTRAINT STU_DID_RK FORIGN KEY(STU_DID) REFERENCES (DEPARTMENT_ID)
);

ALTER SYNTAX::

ALTER TABLE <TABLE_NAME...>
ADD CONSTRAINTS.........

USING ALTER::
-
ALTER TABLE STUDENT_DB
MODIFY STU_NAME VARCHAR2(50) NOT NULL;

CONDITIONS TO USE CONSTRAINTS::

*We can use more than one constraints for a single column.
*We cant alter or Modify constraint except (NOT NULL) Column
*If u want to change Drop and re create again
*We can Alter or Modify the NOT NULL column contraints
*While Create Primary & Unique Constraint automatically the "UNIX INDEX" will created.

NOTE::
-
*Using "DICT" table we can see the data dictionary tables.

Example::
-
SELECT * FROM DICT WHERE TABLE_NAME like '%CONST%';

To Find the Constraint Tables::
-
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('STUDENTS_DB');
SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('STUDENTS_DB');

ON DELETE CASCADE  & ON DELETE SET NULL::
-
If We use the "ON DELETE CASCADE" the related data's also deleted from the Child table.
If we us the "ON DELETE SET NULL" the related data's has changed into "NULL" in the CHILD table.

Examples::
-

CONSTRAINTS STU_DID_RK FORIGN KEY (STU_DID) REFERENCES                              Departments(Department_Id)
ON DELETE CASCADE;



CONSTRAINTS STU_DID_RK FORIGN KEY (STU_DID) REFERENCES Departments(Department_Id)
ON DELETE SET NULL;

ON DELETE CASCADE::

1.When we use ON DELETE CASCADE While Create the FOREIGN Key,If the Parent 
table Primary key Value is deleted 
then the Child table Value is also deleted
2.When we delete the FOREIGN key Column in a table it will be deleted. but 
its not affect the Parent table(Primary KEY
table)
3.In this same case if we try to Drop the Primary key Shows error
4.In this case if we Drop FOREIGN key it will be drop.

CREATE TABLE supplier
    (      supplier_id     numeric(10)     not null,
           supplier_name   varchar2(50)    not null,
           contact_name    varchar2(50),
           CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
   );

Table created.

CREATE TABLE products
     (      product_id      numeric(10)     not null,
            supplier_id     numeric(10)     not null,
            supplier_name   varchar2(50)    not null,
            CONSTRAINT fk_supplier_comp
              FOREIGN KEY (supplier_id, supplier_name)
             REFERENCES supplier(supplier_id, supplier_name)
             ON DELETE CASCADE
     );
SQL> INSERT INTO SUPPLIER VALUES('100','Arul','XXX');

1 row created.

SQL> INSERT INTO SUPPLIER VALUES('101','XAVIER','YYY');

1 row created.

SQL> INSERT INTO PRODUCTS VALUES('1001','100','Arul');

1 row created.

SQL> INSERT INTO PRODUCTS VALUES('1002','101','XAVIER');

1 row created.

SQL> SELECT * FROM SUPPLIER;

SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -----------------------------------------------------
100                Arul         XXX
101                XAVIER YYY


SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- --------------------------------
      1001         100            Arul
      1002         101            XAVIER

SQL> DELETE FROM SUPPLIER WHERE SUPPLIER_ID=100;

1 row deleted.

SQL> SELECT * FROM SUPPLIER;

SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- --------------- -------------------------------------
 101 XAVIER                       YYY



SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- --------------------------------
 1002                        101 XAVIER

 iii)In Primary Key Case::
 SQL> ALTER TABLE SUPPLIER
  2  DROP CONSTRAINT supplier_pk;
DROP CONSTRAINT supplier_pk
                *
ERROR at line 2:
ORA-02273: this unique/primary key is referenced by some foreign keys

iv)In Foreign Key Case::

SQL> ALTER TABLE PRODUCTS
  2  DROP CONSTRAINT fk_supplier_comp;

Table altered.
=======================================================================

ON DELETE SET NULL::

1.When we Set the ON DELETE SET NULL in Child table ,
While delete the Parent table Primary column the refered Child
column values changed into NULL.
2.If we delete the Foreign kay table values it will be deleted.
3.if we try to Drop primary key Shows ERROR .
4.If we dro the foreign key it will dropped.


CREATE TABLE supplier
    (      supplier_id     numeric(10)     not null,
           supplier_name   varchar2(50)    not null,
           contact_name    varchar2(50),
           CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
   );

Table created.


CREATE TABLE products
     (      product_id      numeric(10)     not null,
            supplier_id     numeric(10)     ,
            supplier_name   varchar2(50)    ,
            CONSTRAINT fk_supplier_comp
              FOREIGN KEY (supplier_id, supplier_name)
             REFERENCES supplier(supplier_id, supplier_name)
             ON DELETE SET NULL
     );

SQL> DELETE FROM SUPPLIER WHERE SUPPLIER_ID=100;

1 row deleted.


SQL> SELECT * from SUPPLIER;

SUPPLIER_ID SUPPLIER_NAME              CONTACT_NAME
----------- ---------------- -----------------------------------------
        101                XAVIER                           YYY

SQL> SELECT * from PRODUCTS;

PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- ------------------------------
      1001
      1002                     101                XAVIER

CASE 3::
--------
SQL> ALTER TABLE SUPPLIER
  2  DROP CONSTRAINT supplier_pk;
DROP CONSTRAINT supplier_pk
                *
ERROR at line 2:
ORA-02273: this unique/primary key is referenced by some foreign keys

CASE 4::

SQL> ALTER TABLE PRODUCTS
  2  DROP CONSTRAINT fk_supplier_comp;

Table altered.
================================================================
UNIQUE KEY + NOT NULL ====>PRIMARY KEY:


Example::

CREATE TABLE College_Master(Clg_Id NUMBER NOT NULL,
UNIQUE (Clg_Id),
College_Name VARCHAR2(30)
);

SQL> INSERT INTO College_Master VALUES(1,'XYZ');

1 row created.

SQL> INSERT INTO College_Master VALUES(1,'ABC');
INSERT INTO College_Master VALUES(1,'ABC')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C004129) violated


SQL> INSERT INTO College_Master VALUES(NULL,'ABC');
INSERT INTO College_Master VALUES(NULL,'ABC')
                                  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."COLLEGE_MASTER"."CLG_ID")

TO ENABLE & DISABLE & DROP CONSTRAINTS::


ENABLE::

ALTER TABLE TABLE_NAME
ENABLE CONSTRAINT CONSTRAINT_NAME....

DISABLE::

ALTER TABLE TABLE_NAME
DISABLE CONSTRAINT CONSTRAINT_NAME....

DROP::

ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME....

-------END CONSTRAINTS------

Read More

DDL & DML

 DDL & DML

DDL-(DATA DEFINITION LANGUAGE)

1.CREATE

2.ALTER
     1.ADD
     2.MODIFY
     3.RENAME
     4.DROP
These four for Only the column level Operations

3.DROP
4.TRUNCATE

DML-(DATA MANIPULATION LANGUAGE)::

1.INSERT
2.UPDATE
3.DELETE
4.MERGE

DCL-(DATA CONTROL LANGUAGE)::

1.GRANT
2.REVOKE

TCL-(TRANSACTIONAL CONTROL LANGUAGE)::

1.COMMIT
2.ROLLBACK
3.SAVE POINT

CREATE::

Its mainly used for Create a New table.

SYNTAX::

CREATE TABLE TABLE_NAME (COL1 DATATYPE,COL2 DATATYPE.....);
EXAMPLE::

CREATE TABLE Students
(
STU_ID NUMBER(4),
STU_NAME VARCHAR(20),
STU_GENGER CHAR,
STU_DPT_ID NUMBER(10)
);

ALTER::

The ALTER is mainly used of done the column level changes like
1.ADD NEW COLUMNS
2.MODIFY THE EXISTING DATATYPES
3.RENAME THE EXISTING COLUMNS
4.DROP THE UNWANTED COLUMNS

1.ADD NEW COLUMNS::

Using ADD keyword we can add new columns in existing table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
ADD <COL1_NAME DATATYPE..........>;

Example::


ALTER TABLE students
ADD Feedback VARCHAR2(50);

2.MODIFY THE EXISTING DATATYPES::

Using MODIFY keyword we can Modify existing column datatypes.

SYNTAX::

ALTER TABLE <TABLE_NAME>
MODIFY <COL1_NAME DATATYPE..........>;

Example::


ALTER TABLE students
MODIFY Feedback VARCHAR2(100);

3.RENAME THE EXISTING COLUMNS::

RENAME is mainly used for RENAME the existing column in a table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
RENAME COLUMN <COL_NAME(OLD) TO COL_NAME(NEW)......>;

Example::


ALTER TABLE students
RENAME Column Feedback TO FEEDBACKS;

4.DROP THE UNWANTED COLUMNS::

DROP IN ALTER CASE is mainly used for DROP the particular unwanted columns in a table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
DROP COLUMN <COL1_NAME.>;

Example::


ALTER TABLE students
DROP COLUMN FEEDBACKS;

INSERT::

The INSERT keyword is maily used for INSERT a new record into a table.

SYNTAX::

INSERT INTO TABLE_NAME VALUES('Col1_Date','Col2_Date','...','...')

Examples::

INSERT INTO Students Values('1','Arul','M','10');
INSERT INTO Students Values('2','Xavier','M','20');
INSERT INTO Students Values('4','Anu','F','30');
INSERT INTO Students Values('4','Vino','M','40');
INSERT INTO Students Values('5','Mathi','M','50');

DELETE::

The Delete is Works in Two Methods

1.We can delete Whole date from the table
2.We can Check the Condition in WHERE and delete the Particular data's.

SYNTAX_1::

DELETE FROM TABLE_NAME;
(Deletes Whole data's from the table)

SYNTAX_2::(USING WHERE CLAUSE)

DELETE FROM TABLE_NAME WHERE Col_Name='';

DELETE FROM TABLE WHERE COL_NAME IN ('','','')

Example_1::

DELETE FROM Students;
This query deletes all the data's from the table.

Example_2::

DELETE FROM Students WHERE STU_ID = '1';

Example_3::

DELETE FROM Students WHERE STU_ID IN ('2','3','4');

4.TRUNCATE::

TRUNCATE is same as the DELETE but here we cant check the WHERE Condition.It delete all the Data's from the table.
We cant roll back the data's again.

SYNTAX::

TRUNCATE TABLE TABLE_NAME;

Example::

TRUNCATE TABLE Students;

5.DROP::

DROP Is mainly used delete the Whole table and data's(Structure and data).

SYNTAX::

DROP TABLE TABLE_NAME;

Example::

DROP TABLE Students;

=====>DDL & DML END<=====

Read More