Oracle SQL Commands: Part 9

CLAUSES

1. CONSTRAINT CLAUSE
 
The Constraint clause applies specific rules to the data that restrict the values/data entered into the database columns. Constraints help to minimize the amount of validation done at the application level. and can be specified at the time of creation or after the creation of the table. Constraints can be defined at a specific column level called In Line Specification and also at the table level known as Out Of Line Specification.

In line Constraint Syntax

CONSTRAINT constraint_name {UNIQUE|PRIMARY KEY} constraint _state
CONSTRAINT constraint _name CHECK(condition) constraint _state
CONSTRAINT constraint _name [NOT] NULL constraint _state
CONSTRAINT constraint _name REFERENCES [schema.]table [(column)]
     [ON DELETE {CASCADE|SET NULL}]
constraint _state

Out of line Constraint Syntax

CONSTRAINT constraint _name {UNIQUE|PRIMARY KEY}(column [,column…]) constraint _state
CONSTRAINT constraint _name CHECK(condition) constraint _state
CONSTRAINT constraint _name FOREIGN KEY [schema.]table [(column)]
REFERENCES [schema.]table [(column)][ON DELETE {CASCADE|SET NULL}] constraint_state

In Oracle Constraints are divided into six parts.

  1. Check Constraints (CC)
  2. Primary Key Constraints (PK)
  3. Foreign Key Constraints (FK)
  4. Unique Key Constraints (UK)
  5. Not Null Constraints (NN)

2. DROP CLAUSE
 
DROP Table, DROP View, DROP Index, DROP User, DROP Trigger, DROP Procedure, DROP Schema, DROP Function, DROP Sequence and DROP Java; all of these are explained earlier in the 4th and 5th part of this article.
 
PSEUDOCOLUMNS

1. LEVEL PSEUDOCOLUMN

To organize the rows into a tree structure we use LEVEL PSEUDOCOLUMN with the SELECT CONNECTED BY statement. It returns the level number of nodes in a tree structure.

Example: Assume the following table:

Order_id

Cust_id

Cust_Name

Item_Ordered

City

11

0

AAA

H

Delhi

22

2

BBB

U

Jodhpur

33

1

CCC

R

Patiala

44

2

DDD

F

Jaipur

55

6

PPP

S

Haridwar

66

7

TTT

A

Lucknow

77

1

GGG

L

Agra

88

1

HHH

D

Amritsar

99

7

KKK

Y

Mathura

Query

SELECT LEVEL Order_id,Cust_id, Cust_name, City
FROM Customer
START With Order_id
CONNECT TO PRIOR Order_id = Cust_id
ORDER
BY LEVEL;
 

Level

Order_id

Cust_id

Cust_Name

City

1

11

0

AAA

Delhi

2

33

1

CCC

Patiala

2

77

1

GGG

Agra

2

88

1

HHH

Amritsar

3

22

2

BBB

Jodhpur

3

44

2

DDD

Jaipur

4

66

7

TTT

Lucknow

4

99

7

KKK

Mathura

5

55

6

PPP

Haridwar

2. ROWNUM PSEUDOCOLUMN

ROWNUM PSEUDOCOLUMN returns a number that indicates the order in which a row is selected from an existing database table. It returns the number of returned rows. ROWNUM is evaluated after records are selected from the database and before execution of the ORDER BY clause.

Example: Assume the following table:

Stud_id

Stud_name

Email_id

Age

Stream

001

Rahul

rahul@abc.com

17

Science

002

Varun

varunl@abc.com

19

Arts

003

Vikas

vikas@abc.com

18

Science

004

Preeti

preetil@abc.com

17

Arts

005

Sapna

sapna@abc.com

16

Commerce

006

Rajat

rajat@abc.com

19

Arts

007

Shweta

shweta@abc.com

17

Science

008

Deepak

deepak@abc.com

17

Commerce

009

Sneha

sneha@abc.com

18

Science

Query

SELECT *
FROM employee
WHERE ROWNUM <5;
 

Stud_id

Stud_name

Email_id

Age

Stream

001

Rahul

rahul@abc.com

17

Science

002

Varun

varunl@abc.com

19

Arts

003

Vikas

vikas@abc.com

18

Science

004

Preeti

preetil@abc.com

17

Arts

3. CURRVAL and NEXTVAL PSEUDOCOLUMN

In Oracle, CURRVAL PSEUDOCOLUMN is used with Oracle Sequence values. The current value of the sequence is returned by the CURRVAL.

Query

schema.sequence_name.CURRVAL 

In Oracle, NEXTVAL PSEUDOCOLUMN also can be used with Oracle Sequence values. It returns the next value of the sequence and causes the sequence to be incremented by one. NEXTVAL can only be referenced if it is associated with a Sequence.

Query

schema.sequence_name.NEXTVAL

4. ROWID PSEUDOCOLUMN

In Oracle, ROWID PSEUDOCOLUMN is the binary address of the row that contains the data object numbers. Each row in a table has a unique ROWID that identifies the row in the table.

Syntax

SELECT ROWID,  Col1, Col2, Col3, Col4
FROM table_name;

Now it's the end of this article, Thanks for reading.

Previous article: Oracle SQL Commands: Part 8