TO_CHAR AND TO_DATE CONVERSION IN ORACLE

ORACLE DATE TYPE CONVERSION

• TO_CHAR
• TO_DATE

TO_CHAR FUNCTION

The Oracle TO_CHAR() Function converts a number or Date to a String.

Input : Number or Date format
Output : It return a String.

Syntax:

TO_CHAR ( Value, [format])

Value : A Number or Date that will be converted to a string
Format  : This is the format it will be used to convert value to a string.


SQL> select to_char(1210.93, '9999.99') from dual;

TO_CHAR(
--------
 1210.93

SQL> select to_char(1210.93, '9999.99')ff from dual;

FF
--------
 1210.93

SQL> select to_char(789.93, '$9999.99')ff from dual;

FF
---------
  $789.93

SQL> select to_char(789.93, '999.9')ff from dual;

FF
------
 789.9

SQL> select to_char(789.93, '999.9') from dual;

TO_CHA
------
 789.9

SQL> select to_char(sysdate) from dual;

TO_CHAR(SYSDATE)
------------------
23-AUG-17

SQL> select to_char(sysdate,'mon dd yy') from dual;

TO_CHAR(SYSDATE,'M
------------------
aug 23 17

SQL> select to_char(sysdate,'Mon dd yy') from dual;

TO_CHAR(SYSDATE,'M
------------------
Aug 23 17

SQL> select to_char(sysdate,'Mon dd yy')today_date from dual;

TODAY_DATE
------------------
Aug 23 17

SQL> select to_char(sysdate,'Mon dd yy')today_date from dual;

TODAY_DATE
------------------
Aug 23 17

TO_DATE FUNCTION

The TO_DATE function converts a string into Date Format.

Input : String
Output : Date Format

Syntax

TO_DATE( string, format)

String : The string that will be converted to a date.
Format : The format that will be used to convert string to date.


SQL> select to_date('feb 08,2014','month DD,YYYY')from dual;

TO_DATE('
---------
08-FEB-14

Date Functions in Oracle

DATE FUNCTIONS

• ADD_MONTHS()
• MONTHS_BETWEEN()
• NEXT_DAY()
• LAST_DAY()

CONDITIONS

ACCEPT

DATE + NUMBER
DATE - NUMBER
DATE - DATE

NOT ACCEPT

DATE + DATE : ERROR

DATE

SQL> select sysdate from dual;

SYSDATE
---------
22-AUG-17

DATE + NUMBER
SQL> select sysdate+1 from dual;

SYSDATE+1
---------
23-AUG-17

DATE - NUMBER 

SQL> select sysdate-1 from dual;

SYSDATE-1
---------
21-AUG-17

DATE - DATE

SQL> select sysdate - to_date('12-aug-17','dd-mon-yy')from dual;

SYSDATE-TO_DATE('12-AUG-17','DD-MON-YY')
----------------------------------------
                              10.5595949

DATE + DATE

SQL> select sysdate + to_date('12-aug-17','dd-mon-yy')from dual;
select sysdate + to_date('12-aug-17','dd-mon-yy')from dual
               *
ERROR at line 1:
ORA-00975: date + date not allowed


ADD_MONTHS() : ADD MONTHS OF THE GIVEN DATE

SQL> select add_months(sysdate,4) from dual;

ADD_MONTH
---------
22-DEC-17

SQL> select add_months('21-aug-2017',4) from dual;

ADD_MONTH
---------
21-DEC-17

MONTHS_BETWEEN() : THIS FUNCTION USED TO FIND THE MONTHS BETWEEN THE TWO DATES

SQL> select months_between(sysdate,'16-aug-2016')from dual;

MONTHS_BETWEEN(SYSDATE,'16-AUG-2016')
-------------------------------------
                           12.2117955

SQL> select months_between('12-jan-2016','16-aug-2016')from dual;

MONTHS_BETWEEN('12-JAN-2016','16-AUG-2016')
-------------------------------------------
                                 -7.1290323

NEXT_DAY() : THIS FUNCTION USED TO FIND THE NEXT PARTICULAR DAY OF THE WEEK 

SQL> select next_day(sysdate,'friday')from dual;

NEXT_DAY(
---------
25-AUG-17

SQL> select next_day('25-aug-2017','friday')from dual;

NEXT_DAY(
---------
01-SEP-17

LAST DAY() : ITS USED TO FIND THE LAST DAY OF THE MONTHS.

SQL> select last_day('25-aug-2017')from dual;

LAST_DAY(
---------
31-AUG-17

SQL> select last_day(sysdate)from dual;

LAST_DAY(
---------
31-AUG-17

Foreign Key in Constraints

Foreign Key:

• Foriegn Key is referential integrity.
• It refers primary key on unique constraints on another table.
• It accept null values.
• It accept duplicates. 

Syntax

Create table t4(cid number, cname Varchar(10), constraints c12 primary key (cid));

Table Created;

insert into t4 values (1, 'alex');
insert into t4 values (2, 'arun');

select * from t4;

Cid Cname
1 Alex
2 Arun

create table t112 (sid number, sname varchar(10),cid number, constraints c14 foreign key(cid) references t4(cid));  

Table created

Here Cid is foreign key Its refers to the t4 table primary key.

insert into t112 values (10, 'alex',2 );

1 row inserted

cid= 2 is in the t4 table

insert into t112 values (10, 'alex',3 );

ORA-02291: integrity constraint (HR.C14) violated - parent key not found

Note : Here in the t4 table there is no cid value as 3 so its redirect to error

Check Constraints in Oracle

Check:

• Check is a domain integrity
• It should satisfy some condition defined by a user.
• Modify option is not done in the check constraints.

Syntax :

create table course456( CId Number, CName varchar(20), gender char(1) Not null, constraints c3 check(gender in ('m','f')));

Table Created;

insert into course456 values(1,'parthi','m');
insert into course456 values(2,'arthi','f');

Two Rows inserted

insert into course456 values(2,'arthi','fq');

Error: ORA-12899: value too large for column "HR"."COURSE456"."GENDER" (actual: 2, maximum: 1) 
Note : On creating the table char size defined as 1 but in the table declared value as 2.

insert into course456 values(2,'arthi','h');

Error: ORA-02290: check constraint (HR.C3) violated

Note : The check constraints check some conditions set by the user.Here user set the condition gender as m or f. 

Not Null Constraints

Not Null:

Not Null is a domain integrity.
It ignores Null Values.

Syntax:

create table course345( CId Number, CName varchar(20), gender char(1) Not null);

Table Created

insert into course345 values(1,'parthi','m');
insert into course345 values(2,'karthi','m');

Two Rows Created

insert into course345 values(1,'arthi',null);

When run this above query its redirect to the below error

ORA-01400: cannot insert NULL into ("HR"."COURSE345"."GENDER")

Unique Constraints in Oracle

Unique Key

• Its a entity and integrity.
• It ignores duplicates.
• It accept null values and multiple null values.
• Its automatically generates unique as index.

Scenario : 

consider one table name as course234 and column are course table is  CId and CName. Here set cid is unique key.

Syntax : 

create table course( CId Number, CName varchar(2) constraints c2 unique(CId)) ;

Table Created;

insert into course234 values(1,'parthi');
insert into course234 values(2,'karthi');

Two Rows Inserted

insert into course234 values(1,'prakash');

when inserting this third row face this error 

ORA-00001: unique constraint (HR.C1) violated 

insert into course234 values(null ,'prakash');

1 row inserted

Note: Here Cid value is 1 Its already inserted. The main thing in the unique constraints is ignores duplicate values but it accept null values.

select * from course234;

Output :

CID CNAME
1 parthi
2 karthi
- prakash

Primary Key in Oracle

Primary Key

• It is a entity integrity.
• It ignore Null values.
• It ignore duplicates.
• only one primary key accepted in a table.
• It automatically create unique index.

Primary Key = Unique Key + Not Null

Scenario : 

consider one table name as course and column are course table is  CId and CName. Here set cid is primary key.

Syntax : 

create table course123( CId Number, CName varchar(2) constraints c1 primary key(CId)) ;

Table Created;

insert into course123 values(1,'parthi');
insert into course123 values(2,'karthi');

Two Rows Inserted

insert into course123 values(1,'prakash');

when inserting this third row face this error 

ORA-00001: unique constraint (HR.C1) violated 

Note: Here Cid value is 1 Its already inserted). The main thing in the primary key constraints is ignores null values and duplicates.

Constraints in Oracle

Constraints

Its apply rule on a table.when time of creating a table.

Constraints can be applied

column    level: Column level constraints apply to a column
table level: Table level constraints apply to a Whole table.

Types of Constraints:

1. Primary Key
2. Unique
3. Foriegn Key
4. Check
5. Not Null

Primary Key

• It is a entity integrity.
• It ignore Null values.
• It ignore duplicates.
• only one primary key accepted in a table.
• It automatically create unique index.

Primary Key = Unique Key + Not Null

Scenario : 

consider one table name as course and column are course table is  CId and CName. Here set cid is primary key.

Syntax : 

create table course123( CId Number, CName varchar(2) constraints c1 primary key(CId)) ;

Table Created;

insert into course123 values(1,'parthi');
insert into course123 values(2,'karthi');

Two Rows Inserted

insert into course123 values(1,'prakash');

when inserting this third row face this error

ORA-00001: unique constraint (HR.C1) violated 

Note: Here Cid value is 1 Its already inserted). The main thing in the primary key constraints is ignores null values and duplicates.

Unique Key

• Its a entity and integrity.
• It ignores duplicates.
• It accept null values and multiple null values.
• Its automatically generates unique as index.

Scenario : 

consider one table name as course234 and column are course table is  CId and CName. Here set cid is unique key.

Syntax : 

create table course( CId Number, CName varchar(2) constraints c2 unique(CId)) ;

Table Created;

insert into course234 values(1,'parthi');
insert into course234 values(2,'karthi');

Two Rows Inserted

insert into course234 values(1,'prakash');

when inserting this third row face this error

ORA-00001: unique constraint (HR.C1) violated 

insert into course234 values(null ,'prakash');

1 row inserted

Note: Here Cid value is 1 Its already inserted). The main thing in the unique constraints is ignores duplicate values but it accept null values.

select * from course234;

Output :

CID CNAME
1 parthi
2 karthi
- prakash

Not Null:

• Not Null is a domain integrity.
• It ignores Null Values. 

Syntax:

create table course345( CId Number, CName varchar(20), gender char(1) Not null);

Table Created

insert into course345 values(1,'parthi','m');
insert into course345 values(2,'karthi','m');

Two Rows Created

insert into course345 values(1,'arthi',null);

When run this above query its redirect to the below error

ORA-01400: cannot insert NULL into ("HR"."COURSE345"."GENDER")

Check:

• Check is a domain integrity
• It should satisfy some condition defined by a user.
• Modify option is not done in the check constraints.

Syntax :

create table course456( CId Number, CName varchar(20), gender char(1) Not null, constraints c3 check(gender in ('m','f')));

Table Created;

insert into course456 values(1,'parthi','m');
insert into course456 values(2,'arthi','f');

Two Rows inserted

insert into course456 values(2,'arthi','fq');

Error: ORA-12899: value too large for column "HR"."COURSE456"."GENDER" (actual: 2, maximum: 1) 

Note : On creating the table char size defined as 1 but in the table declared value as 2.

insert into course456 values(2,'arthi','h');

Error: ORA-02290: check constraint (HR.C3) violated

Note : The check constraints check some conditions set by the user.Here user set the condition gender as m or f. 

Foreign Key:

• Foreign Key is referential integrity.
• It refers primary key on unique constraints on another table.
• It accept null values.
• It accept duplicates. 

Syntax

Create table t4(cid number, cname Varchar(10), constraints c12 primary key (cid));

Table Created;

insert into t4 values (1, 'alex');
insert into t4 values (2, 'arun');

select * from t4;

Cid Cname
1 Alex
2 Arun

create table t112 (sid number, sname varchar(10),cid number, constraints c14 foreign key(cid) references t4(cid));
Table created

Here Cid is foreign key Its refers to the t4 table primary key.

insert into t112 values (10, 'alex',2 );

1 row inserted

cid= 2 is in the t4 table

insert into t112 values (10, 'alex',3 );

ORA-02291: integrity constraint (HR.C14) violated - parent key not found

Note : Here in the t4 table there is no cid value as 3 so its redirect to error


SQL Composite Key

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.Columns that make up the composite key can be of different data types.

Syntax(Multiple Datatypes):

CREATE TABLE Compkey 
(COL1 integer, 
COL2 varchar(30), 
COL3 varchar(50), 
PRIMARY KEY (COL1, COL2));

Table Created;

insert into compkey values(1,'parthi','cse');
insert into compkey values(2,'karthi','cse');
insert into compkey values(1,'karthi','cse');

OutPut
Select * From comkey

COL1         COL2         COL3
1           parthi        cse
2           karthi        cse 
1           karthi        cse

Note: Both are different and unique Its not duplicate a duplicate of 1 

Syntax(Same Datatypes) 

CREATE TABLE compkey1 (
COL1 integer, 
COL2 varchar(30), 
COL3 varchar(50), 
PRIMARY KEY (COL2, COL3));

Here Col2 and Col3 set as a primary key

insert into compkey1 values(1,'par','cse');
insert into compkey1 values(1,'cse','par');
insert into compkey1 values(1,'ece','cse');
insert into compkey1 values(1,'mech',null);

Select * from comkey1
COL1        COL2           COL3
1           par            cse
1           cse            par
1           ece            cse

Note : when inserting null values it will not accepted
Key Points:
1. only one primary key is allowed in the table
2. modify option is not done in the check constraint
3. you can add N number of constraint with the help of modify column
4. you can add upto 32 column composite primary key

Table Copy

CREATE TABLE compkey1 
(COL1 integer, 
COL2 varchar(30), 
COL3 varchar(50), 
PRIMARY KEY (COL2, COL3));

Table Created;

insert into compkey1 values(1,'parthi','cse');
insert into compkey1 values(1,'cse','parthi');
insert into compkey1 values(1,'ece','cse');

create table copy as(select * from compkey1); -- compkey1 table having two composite primary keys.

insert into copy values( 1,'parthi','cse');
insert into copy values( 2,'parthi','cse');

It  does copy the data and table structure and it does  not copy the constraint. 

Data Dictionary table : Its contain all information about the data.

select *from user_constraints uc where uc.table_name = upper('Sts_Com_PKey');

select *from user_cons_columns ucc  where ucc.table_name =upper( 'Sts_Com_PKey');

CASCADE -- Cascade option in constraints

CREATE TABLE buildings 
(   building_no number  primary key ,
    building_name VARCHAR2(255) NOT NULL,
    Address_details VARCHAR2(255) NOT NULL
);

select * from user_cons_columns where table_name=upper('buildings');

CREATE TABLE rooms (
    room_no number PRIMARY KEY ,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

INSERT INTO buildings VALUES(1,'ACME Headquaters','3950 North 1st Street CA 95134');

INSERT INTO buildings VALUES(2,'ACME Sales','5000 North 1st Street CA 95134');

select * from buildings;

INSERT INTO rooms VALUES(1,'Amazon',1);
INSERT INTO rooms VALUES(2,'War Room',1);
INSERT INTO rooms VALUES(3,'Office of CEO',1);
INSERT INTO rooms VALUES(4,'Marketing',2);
INSERT INTO rooms VALUES(5,'Showroom',2);

select * from rooms;

DELETE FROM buildings 
WHERE
    building_no = 2;

After deleting buildings table value 2

INSERT INTO buildings VALUES(2,'ACME Sal','5000 South 1st Street CA 95134');

Then insert the values as 2. it will not be inserted in the second table.

Without Cascade option 

CREATE TABLE build 
(   building_no number  primary key ,
    building_name VARCHAR2(255) NOT NULL,
    Address_details VARCHAR2(255) NOT NULL
);

CREATE TABLE room12 (
    room_no number PRIMARY KEY ,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
);

INSERT INTO build VALUES(1,'ACME Headquaters','3950 North 1st Street CA 95134');

INSERT INTO build VALUES(2,'ACME Sales','5000 North 1st Street CA 95134');

INSERT INTO room12 VALUES(1,'Amazon',1);
INSERT INTO room12 VALUES(2,'War Room',1);
INSERT INTO room12 VALUES(3,'Office of CEO',1);
INSERT INTO room12 VALUES(4,'Marketing',2);
INSERT INTO room12 VALUES(5,'Showroom',2);

select * from build;

select * from room12;

DELETE FROM build 
WHERE
    building_no = 2;

ON DELETE SET NULL

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)
);

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

insert into products values(10,1);
insert into products values(20,1);
insert into products values(30,2);
insert into products values(40,1);
insert into products values(50,3);

Select * from supplier;
Select * from products;

Delete from supplier where supplier_id=1;
-- after deleting the record all will be changed into null in the foriegn key column

--ENABLE DISABLE NOVALIDATE

CREATE table t123
( 
s_id number(3),
Sname varchar2(20),
CONSTRAINT s_id_pk1 primary key (s_id),
CONSTRAINT Sname_uk1 unique (Sname));

INSERT into t123 VALUES(1,'parthi');

INSERT into t123 VALUES(2,'karthi');

ALTER table t123 DISABLE CONSTRAINT S_id_pk1;

insert into t123 VALUES(1,'keerthi');

SELECT * FROM t123;

ALTER table t123 ENABLE constraint S_id_pk1;

ORA-02437: cannot validate (HR.S_ID_PK1) - primary key violated

CREATE index idx1 on t123(s_id); 

ALTER table t123 ENABLE NOVALIDATE constraint S_id_pk1;


select * from t123;

SQL Composite Key

SQL Composite Key

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

Syntax(Multiple Datatypes):

CREATE TABLE Compkey
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));

Table Created;

insert into compkey values(1,'parthi','cse');
insert into compkey values(2,'karthi','cse');
insert into compkey values(1,'karthi','cse');

OutPut

Select * From comkey

COL1 COL2 COL3
1          parthi cse
2          karthi cse
1           karthi  cse

Note: Both are different and unique Its not duplicate a duplicate of 1 

Syntax(Same Datatypes) 

CREATE TABLE compkey1
COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL2, COL3));

Here Col2 and Col3 set as a primary key

insert into compkey1 values(1,'par','cse');

insert into compkey1 values(1,'cse','parthi');

insert into compkey1 values(1,'ece','cse');

insert into compkey1 values(1,'mech',null);

Select * from comkey1

COL1 COL2 COL3

1          par            cse

1          cse           par
1           ece            cse

Note : when inserting null values it will not accepted

Interview Questions:

1.How many primary key in one table?

Answer: only one primary key.

2.If i need two colomn unique record if there is any possible to create multiple primary key.

Answer: Yes by using composite key to create more than one primary key in table.

3.After composite primary key is set If Duplicate values and null values are added into the table what will happen?

Answer : It never accept the null values and duplicate values Its only support unique record.