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	[e-mail]	17	Science
002	Varun	[e-mail]	19	Arts
003	Vikas	[e-mail]	18	Science
004	Preeti	[e-mail]	17	Arts
005	Sapna	[e-mail]	16	Commerce
006	Rajat	[e-mail]	19	Arts
007	Shweta	[e-mail]	17	Science
008	Deepak	[e-mail]	17	Commerce
009	Sneha	[e-mail]	18	Science

Query

SELECT *
FROM employee
WHERE ROWNUM <5;
 

Stud_id	Stud_name	Email_id	Age	Stream
001	Rahul	[e-mail]	17	Science
002	Varun	[e-mail]	19	Arts
003	Vikas	[e-mail]	18	Science
004	Preeti	[e-mail]	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