Showing posts with label Oracle SQL. Show all posts
Showing posts with label Oracle SQL. Show all posts

Saturday, 4 November 2017

CAST Function

CAST function converts one built-in data type into another built-in data type with the specified precision and length.

Syntax: CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

CAST Converts


Convert a string value to NUMBER leaving 2 digits after the decimal point:

SELECT CAST('345.213' AS NUMBER(5,2)) FROM DUAL;
CAST - Convert and Round Numbers:

SELECT CAST('246.206' AS NUMBER(5,2)) FROM DUAL;
CAST convert the date into a VARCHAR2(30) value:

 SELECT CAST( '22-Aug-2003' AS VARCHAR2(30) ) FROM DUAL;



Wednesday, 1 November 2017

TRUNC for Datetime


TO_DATE


TO_CHAR for Datetime


MONTHS_BETWEEN

MONTHS_BETWEEN function returns the number of months between two dates.

Syntax: MONTHS_BETWEEN(date_expression1,date_expression2);

Months Between Function Details

MONTHS_BETWEEN function returns an integer number if the days are the same.

SELECT MONTHS_BETWEEN (DATE '2012-02-12', DATE '2012-01-12') FROM DUAL; 
MONTHS_BETWEEN function returns an integer number if the both dates specify the last day of the month.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-01-31') FROM DUAL; 
MONTHS_BETWEEN function returns a decimal number if the days are different.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-02-01') FROM DUAL;

MONTHS_BETWEEN always calculates the fractional part as the difference in days divided by 31

In the example above, the fractional part is calculated as (29 - 1)/31 = 0.903225806, although there are 29 days in February 2012.

You can also see that MONTHS_BETWEEN does not return an integer result if you specify the first and last days of the same month.

EXTRACT Function

EXTRACT function gets the specified part (day, month, year, hours, minutes,second etc.) from a datetime value.

Syntax: EXTRACT(datetime_unit FROM datetime_expression1)

Although DATE contains time part but we can't extract the hour,minute and second from DATE.
HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

EXTRACT Function Details

Extract DAY from a specific DATE.

SELECT EXTRACT(DAY FROM DATE '2011-01-12') FROM dual;
Extract MONTH from a specific DATE.

SELECT EXTRACT(MONTH FROM DATE '2011-01-12') FROM dual;
Extract YEAR from a specific DATE.

SELECT EXTRACT(YEAR FROM DATE '2011-01-12') FROM dual;
We can't extract  hour,minute and second from DATE. We will get the error

SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;
HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2012-01-12 10:11:00') FROM dual;

Tuesday, 31 October 2017

ADD_MONTHS Function

The Oracle date function ADD_MONTHS returns a date with a given number of months added that you specify.This is most powerful Oracle function for computing future months and years.

Syntax: ADD_MONTHS(datetime_expression,N)


  • It returns the date 'D' plus OR minus'N' month.
  • The argument 'N' can be any positive OR negative integer.

How to Get Last Day Of Last Month


To find the last day of last month then we need to use ADD_MONTHS function

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1)) FROM DUAL;

To find the first monday of each quarter year we need to use ADD_MONTHS

SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'q'), 4), 'monday') FROM DUAL;

LAST_DAY Function

  • LAST_DAY function returns the last date of the specified month. 
  • LAST_DATE function returns date.
  • Mostly used to determine how many days are left in the current month.
Syntax:  LAST_DAY(datetime_expression)

To find the number of days we need to use LAST_DAY function

SELECT LAST_DAY(SYSDATE) -SYSDATE FROM dual;


Or we can use the EXTRACT and LAST_DAY  function to find the number of days left in this month

SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) FROM dual;


Sunday, 8 October 2017

Data Type

Oracles supports the following categories of data types:
Oracle Built-in Datatypes.
User-Defined Data Types.

There are 20 Oracle built-in data types, divided into 6 groups:
1.Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
2.Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
3.Long and Row Datatypes - LONG, LONG RAW, RAW
4.Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
5.Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
6.Row ID Datatypes - ROWID, UROWID

Difference between  CHAR and NCHAR:
Both CHAR and NCHAR are fixed length character data types. But they have the following differences:
CHAR's size is specified in bytes by default.
NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
NCHAR stores characters in Unicode.

Difference between  CHAR and VARCHAR2 :
The main differences between CHAR and VARCHAR2 are
CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
VARCHAR2 stores values in variable lengths. Values are not padded with any characters.

Difference between  NUMBER and BINARY_FLOAT :
The main differences between NUMBER and BINARY_FLOAT are
NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
BINARY_FLOAT stores values as single precision floating-point numbers.

Difference between  DATE and TIMESTAMP :
The main differences between DATE and TIMESTAMP are
DATE stores values as century, year, month, date, hour, minute, and second.
TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

Difference between  INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND:
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:
INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.

Difference between  BLOB and CLOB :
The main differences between BLOB and CLOB are:
BLOB stores values as LOB (Large OBject) in bitstreams.
CLOB stores values as LOB (Large OBject) in character steams.



SQL Introduction

SQL stands for Structured Query Language(or SEQUEL-Structured English Query Language). SQL
is use for accessing and manipulating databases.SQL is not case sensitive but SQL Data is a case sensitive.

SQL was developed by IBM Corporation.
SQL became an ANSI standard, called SQL-87, in 1986.
ISO made a major revision, called SQL-92, in 1992.
The latest revision is SQL:2008.

Before starting SQL, relational databases have several point that are important to keep in mind.

  • RDBMS stands for Relational Database Management System.
  • Data Integrity : Store data only once and avoiding data duplication.
  • SQL Constraints : Constraints are the rules which are apply to table columns to store valid data and prevents the user to storing/entering invalid data into table columns.
  • Better security : Assign grant or privilege to a individual User. Using this grant user can store confidential data into table by using username or password.
  • Database Normalization : Database normalization is the process to store database data very efficiently. No need to store same data more then one time and reduce the Data redundancy.
  • Different types of relationships : One to one, One to many, Many to many

                One to one relationship : merging for two tables.
                One to many relationship : create a foreign key from an parent table to the child table.
                Many to many relationship : create a new relation table.


Rules of Semicolon(;) or Forward Slash(/) in SQL

There is a huge difference in SQL between the meaning of a (/) and a (;) because they work differently.

SQL database systems require semicolon(;) at the end of statement to know it's ending.
Use forward slash(/) once at the end of each script, to tell SQL that there is not more lines of code. you can't use forward slash(/) at the middle of the script.
The (;) means terminate the current statement execute it and store it to the "SQL buffer", whereas the / executes whatever statement script is in the current "SQL buffer".





Saturday, 7 October 2017

Oracle JOINS

  • Join is a query that combines rows from two or more table or views.
  • Join is performed whenever multiple tables appear in the FROM clause.
  • The common column name with in the table should qualify all references.

Join Condition:

  • Many Join queries contain WHERE clause , which compares two columns, each column from different table.
  • The column in the join condition need not be part of the SELECT list.
  • The LOB Columns cannot be specified in the WHERE clause, when the where clause contains any join.

Guidelines:
  • To join n tables together, we need minimum of [n-1] join condition.
  • If same column name appears in more than one table , the column name must be prefix with the table name.

Qualifying Ambiguous Column Names:

  • The name of the column should be Qualified in the WHERE clause with the table name to avoid the Ambiguous.
  • If there is no common column name between two tables than Qualified is not necessary but it is better.

Example:

SELECT ENP.ENAME,EMP.EMPNO,DEPT.DEPTNO
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;


Table Aliases:
  • Table Alias is nothing but alternate name of table.
  • Table Alias is specified in the FORM clause.

Example:

SELECT E.ENAME,E.EMPNO,D.DEPTNO
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Guidelines:
  • Table alias max length is 30 characters.
  • A table alias should be meaningful and should be maintained as short as possible.
  • Table Alias is valid only for that current SELECT statement.

Columns Alias:
  • Temporary name of a column.
  • Column alias used to make column names more readable.
  • Alias is valid only for that current SELECT statement.

Example:
SELECT E.ENAME as Employee_Name,E.EMPNO as EmployeeNumber,D.DEPTNO as Department_Number
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Type Of Joins:
  1. Cartesian Product
  2. Simple join or Inner join or Equi join
  3. Self join
  4. Non Eqie join
  5. Outer join







REF Constraints

It will describe the relationship between a column of type REF and the object it references.

See The Example:

REF CONSTRAINT

CHECK and DEFAULT Constraint

Create table with Check Constraint


Create a Table with CHECK and DEFAULT Constraint
CREATE TABLE MyDept1
 (
Deptno NUMBER(2)
   CONSTRAINT MyDept_Deptno_PK1 PRIMARY KEY
   CONSTRAINT MyDept_Deptno_CHK01 CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), DName VARCHAR2(16)
 DEFAULT 'Not Given'
 CONSTRAINT MyDept_DName_NN1 NOT NULL
 CONSTRAINT MyDept_DName_UNQ UNIQUE
 CONSTRAINT MyDept_DName_CHK01 CHECK(DName = UPPER(DName)),
Loc VARCHAR2(14)
 DEFAULT 'NOT GIVEN'
 CONSTRAINT MyDept_Loc_NN1 NOT NULL
 CONSTRAINT MyDept_Loc_CHK01 CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'NOT GIVEN','DALLAS'))
);




Insert Data into MyDept1 Table
INSERT INTO MyDept1 VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO MyDept1 VALUES(1, 'ACCOUNTING', 'NEW YORK');
INSERT INTO MyDept
 *
ERROR at line 1: ORA-02290: check constraint (SCOTT.MYDEPT_DEPTNO_CHK01) violated.


This Record we are Inserting for DEFAULT Constraint
INSERT INTO MyDept1 (Deptno, DName) VALUES(20, 'OPERATIONS');


Now check your table data, while Inserting deptno=20 record we didn't provide the LOC value but while creating the table we set  DEFAULT value as 'NOT GIVEN'.








CHECK Constraint

It defines the conditions that each row must be satisfy.CHECK constraint is used to limit the value
range that can be placed in a column.


Restrictions:
  • CHECK Constraint can refer to any column in same table but it can't refer any column from other table
  • In CHECK Constraint we can't use pseudo columns.
  • A Single column can have multiple CHECK Constraint.
  • CHECK Constraint can be define column and table level.


DEFAULT option:
  • The DEFAULT option is given to maintain a default value in column.
  • The DEFAULT value can be leteral or any expression or sql function.

Syntax:

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name CHECK (column_name BETWEEN start_rng AND end_rng ) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name CHECK (column_name=UPPER(column_name)) DISABLE,
 COLUMN_NAMEn <DATA_TYPE>(WIDTH) DEFAULT SYSDATE);


See The Example:

CHECK CONSTRAINT EXAMPLE

FOREIGN KEY Constraint

  • A FOREIGN KEY is a key used to establishes link between two tables (as parent child relationship).The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
  • A FOREIGN KEY in one table that refers to the PRIMARY KEY in another table.
  • Composite FOREIGN KEY should be declare in Table level.
  • The FOREIGN KEY and the REFERENCED KEY can be same  table or view.

Restrictions :
  • The FOREIGN KEY columns cannot be applied on LOB,LONG,VARRAY,NASTED TABLE,OBJECT AND TIMESTAMP WITH TIME ZONE.
  • Composite FOREIGN KEY  cannot contain more than 32 columns.
  • Child and parent tables must be in same data base.
  • The referenced key(unique or primary) constraint on the parent table or view must already be defined. 
  • REFERENCES Clause should be used when the FOREIGN KEY constraint is INLINE.

ON DELETE Clause :

If you want to delete the record from the parent and that parent having child then oracle will not allow you to delete the parent record. To delete the parent record we need to yes ON DELETE clause , by using ON DELETE clause oracle manage the referential integrity if you remove a referenced key .

  • CASCADE:  If you want to delete dependent parent with child then use CASCADE
  • SET NULL:    If you want to delete the only parent and child table dependent  FOREIGN KEY values set as NULL .

Syntax :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name REFERENCES <reference_table_name>[reference_table PK column_name],
 COLUMN_NAMEn <DATA_TYPE>(WIDTH));





PRIMARY KEY Constraint Example

Type Of PRIMARY KEY Constraint

Type Of PRIMARY KEY Constraint Example
Column Level PRIMARY KEY Constraint  CREATE TABLE SamplePK01(SampID NUMBER(2) CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY,SampName VARCHAR2(10),SampDate DATE);
Table Level PRIMARY KEY Constraint  CREATE TABLE SamplePK02(SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SamplePK02_SampID_PK PRIMARY KEY(SampID));
Composite PRIMARY KEY Constraint ,always declare in Table level CREATE TABLE SamplePK05(SampID NUMBER(2), SampName VARCHAR2(10), SampDate DATE, CONSTRAINT SamplePK05_SampIDName_PK PRIMARY KEY(SampID, SampName) );

Now i am trying to insert data in  Column level PK Constraint table SamplePK01, PK is  SampID .



SQL> INSERT INTO SamplePK01 VALUES(1, 'SAMPLE01', SYSDATE);
1 row created.
SQL> INSERT INTO SamplePK01 VALUES(1, 'SAMPLE02', SYSDATE);
INSERT INTO SamplePK01 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPLEPK01_SAMPID_PK) violated
SQL> INSERT INTO SamplePK01 VALUES(NULL, 'SAMPLE02', SYSDATE);
VALUES(NULL, 'SAMPLE02', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK01"."SAMPID")

Insert data Into  Table level PK Constraint. Use above insert script by changing the table name.


Insert data Into Composite PRIMARY KEY  Constraint table SamplePK05, PK is SampID, SampName.

SQL> INSERT INTO SamplePK05 VALUES(1, 'SAMPLE01', SYSDATE);
1 row created.
SQL> INSERT INTO SamplePK05 VALUES(1, 'SAMPLE02', SYSDATE);
1 row created.
SQL> INSERT INTO SamplePK05 VALUES(2, 'SAMPLE02', SYSDATE);
1 row created.
SQL> INSERT INTO SamplePK05 VALUES(NULL, 'SAMPLE03', SYSDATE);
VALUES(NULL, 'SAMPLE03', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPID")
SQL> INSERT INTO SamplePK05 VALUES(3, NULL, SYSDATE);
VALUES(3, NULL, SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPNAME")






FOREIGN KEY Constraint Example



StepsScript
Create a table with PK constraintCREATE TABLE SamplePK01(SampID NUMBER(2) CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY,SampName VARCHAR2(10),SampDate DATE);
Create a table with FK ConstraintCREATE TABLE SampleFK01(SampID NUMBER(2) CONSTRAINT SampleFK01_SampID_PK PRIMARY KEY, SampName VARCHAR2(10), SampDate DATE, SampIDFK NUMBER(2) CONSTRAINT SampleFK01_SampIDFK_FK REFERENCES SamplePK01(SampID));


Insert Below Records
INSERT INTO SampleFK01 VALUES(20, 'SAMPLE20', SYSDATE, NULL);
INSERT INTO SamplePK01 VALUES(10, 'SAMPLE10', SYSDATE);
INSERT INTO SamplePK01 VALUES(11, 'SAMPLE11', SYSDATE);
INSERT INTO SamplePK01 VALUES(12, 'SAMPLE12', SYSDATE);
INSERT INTO SamplePK01 VALUES(13, 'SAMPLE13', SYSDATE);
INSERT INTO SampleFK01 VALUES(21, 'SAMPLE21', SYSDATE, 10);
INSERT INTO SampleFK01 VALUES(22, 'SAMPLE22', SYSDATE, 10)
INSERT INTO SampleFK01 VALUES(23, 'SAMPLE23', SYSDATE, 12);


While Inserting below record we are getting error
INSERT INTO SampleFK01 VALUES(22, 'SAMPLE22', SYSDATE, 15);
INSERT INTO SampleFK01
*
ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - parent key not found

We are unable to inser the row because in parent table does not have any parent for this child (SampID=1).

 Now Check the data in parent and child table

 SELECT * FROM SamplePK01;
 SELECT * FROM SampleFK01;


Now we will try to delete the record from the parent and child table
 DELETE FROM SamplePK01 WHERE SampID = 13;
DELETE FROM SamplePK01 WHERE SampID = 12;
DELETE FROM SamplePK01
ERROR at line 1:ORA-02292: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - child record found

We are deleting the record from parent table but this parent record have child record so it will not allow you to delete the parent record. For that first we need to delete from child table then delete from parent table or can we use ON DELETE CASCADE or we can use ON DELETE SET NULL


 

PRIMARY KEY Constraint

  • A PRIMARY KEY is the candidate key that is used by the database designer for identifying an entity.
  • There is no fiex rule for choosing the primary key, but generally it is the key that can be controlled by the user.
  • Primary Key columns do not accept null values
  • Primary Key columns do not accept duplicate values, that mean uniqueness of data.
  • Oracle recommended Primary Key columns should be short and numeric.
  • Primary Key constraint is a combination of NOT NULL+UNIQUE.

Restrictions :
  • A table or view can have only one primary key.
  • A composite primary key cannot have more than 32 columns.
  • the same column or combination of columns cannot be declare as  primary key and a unique key.
  • Primary key con't be implemented on columns having  LOB, LONG, VARRAY, NESTED TABLE, OBJECT, TIMESTAMP WITH TIME ZONE.
Syntax :

Column Level PRIMARY KEY Constraint :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name PRIMARY KEY,
 COLUMN_NAMEn <DATA_TYPE>(WIDTH));


Table Level PRIMARY KEY Constraint :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH),
 COLUMN_NAMEn <DATA_TYPE>(WIDTH),
 CONSTRAINT Constraint_Name PRIMARY KEY(Column_name));

Composite PRIMARY KEY Constraint (always declare in Table level):

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH),
 COLUMN_NAMEn <DATA_TYPE>(WIDTH),
 CONSTRAINT Constraint_Name PRIMARY KEY(Column_name1,Column_name2));


See The Example:








UNIQUE KEY Constraint

Since one table can have at the most one primary key constraint , to avoid the duplicates from other columns we can use UNIQUE key constraint
  • UNIQUE key column do not accept duplicate value.It can accept NULL value.
  • One table can have a number of UNIQUE key constraint
  • A composite UNIQUE key constraint is always declare in table level.
  • Oracle create an Index Implicitly on the UNIQUE key column.

Restrictions :
  • UNIQUE key con't be implemented on columns having  LOB, LONG, VARRAY, NESTED TABLE, OBJECT, TIMESTAMP WITH TIME ZONE.
  • A composite UNIQUE key constraint cannot have more then 32 columns
  • Same column or combination of columns we cant be design as both PRIMARY key and UNIQUE key constraint

Syntax :

Column Level UNIQUE Constraint declaration.

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) UNIQUE,
COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT constraint_name UNIQUE,
COLUMN_NAMEn <DATA_TYPE>(WIDTH));

Table level UNIQUE Constraint declaration.

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH),
COLUMN_NAME2 <DATA_TYPE>(WIDTH),
COLUMN_NAMEn <DATA_TYPE>(WIDTH),
CONSTRAINT constraint_name UNIQUE (COLUMN_NAME2));

Composite UNIQUE key constraint declaration.

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH),
COLUMN_NAME2 <DATA_TYPE>(WIDTH),
COLUMN_NAMEn <DATA_TYPE>(WIDTH),
CONSTRAINT constraint_name UNIQUE (COLUMN_NAME1,COLUMN_NAME2));


See The Example:

UNIQUE CONSTRAINT EXAMPLE





UNIQUE Constraint Example

Type Of UNIQUE Constraint

Type Of UNIQUE ConstraintExample
Column Level UNIQUE Constraint CREATE TABLE SampUNQ01(SampID NUMBER(2) CONSTRAINT SampUNQ01_SampID_UNQ UNIQUE,SampName VARCHAR2(10),SampDate DATE);
Table Level UNIQUE Constraint CREATE TABLE SampUNQ02 (SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SampUNQ02_SampID_UNQ UNIQUE(SampID));
Composite UNIQUE Constraint(Always declare in Table level)CREATE TABLE SampUNQ05(SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SampUNQ05_SampIDName_UNQ UNIQUE(SampID, SampName));

Now i am trying to insert data in Column level UNIQUE Constraint table SampUNQ01 , UNIQUE
key is SampID .


SQL> INSERT INTO SampUNQ01 VALUES(1, 'SAMPLE01', SYSDATE);
1 row created.

SQL> INSERT INTO SampUNQ01 VALUES(1, 'SAMPLE02', SYSDATE);
INSERT INTO SampUNQ01
 *
 ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPUNQ01_SAMPID_UNQ) violated

We can insert NULL into UNIQUE key column


SQL>INSERT INTO SampUNQ01 VALUES(NULL, 'SAMPLE02', SYSDATE);

SQL>INSERT INTO SampUNQ01 VALUES(NULL, 'SAMPLE03', SYSDATE);

Now i am trying to insert data in Table level UNIQUE Constraint table SampUNQ02 , UNIQUE
key is SampID .

Insert same data by changing the table name.

Now i am trying to insert data in Composite UNIQUE Constraint table SampUNQ05, Composite UNIQUE key SampID, SampName .

SQL>INSERT INTO SampUNQ05 VALUES(1, 'SAMPLE01', SYSDATE);
1 row created.
SQL>INSERT INTO SampUNQ05 VALUES(1, 'SAMPLE02', SYSDATE);
1 row created.
SQL>INSERT INTO SampUNQ05 VALUES(2, 'SAMPLE02', SYSDATE);
1 row created.
SQL>INSERT INTO SampUNQ05 VALUES(NULL, 'SAMPLE03', SYSDATE);
1 row created.
SQL>INSERT INTO SampUNQ05 VALUES(NULL, 'SAMPLE04', SYSDATE);
1 row created.
SQL>INSERT INTO SampUNQ05 VALUES(NULL, 'SAMPLE04', SYSDATE);
INSERT INTO SampUNQ05 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPUNQ05_SAMPIDNAME_UNQ) violated
SQL>INSERT INTO SampUNQ05 VALUES(NULL, NULL, SYSDATE);
1 row created.
SQL>INSERT INTO SampUNQ05 VALUES(NULL, NULL, SYSDATE);
1 row created.