Oracle Tutorial - 2 (Oracle Constraints, Joins, Subqueries)

What will I learn ?

You will learn What is oracle Constraints.
You will learn about oracle join statements.
You will learn about sub-queries.

OS and Software Requirements :

Windows (7/ 8.1 /10)
Oracle 11g Installed.

Resources:

https://www.oracle.com

Defficulty:

intermediate.

Tutorial Description :

This tutorial is the second tutorial about oracle. If you miss first tutorial please read my first tutorial from https://steemit.com/utopian-io/@arif019/oracle-tutorial-1-oracle-ddl-and-dml-statements. In this tutorial you know about Oracle joins statements and Constraints. These are very important to make a strong database.

Constraints :

Constraints are categorized as follows.

Not null.
Check.
Unique .
Primary key.
Foreign key.

Not null :

This is used to avoid null values.
Syntax: create table ‘table-name’(column1 not null,column2,column3..);
Ex : create table students_info (id number(20) , name varchar2(50),roll number(30) not null);

Check :

This is used to insert the values based on specified condition.
syntax : create table ‘table-name’ ( column1,column2,column3,constraint ‘constraint-name’ check (condition));
Ex : create table marks (id number(20),name varchar2(50),roll number(30),bangla number(20),English number(20),math number(20),constraint ch check(math>50) );

Unique :

This is used to avoid duplicates but it allows nulls.
syntax : create table ‘table-name’ (column1,column2,column3, constraint ‘constraint-name’ unique(column-name));
Ex : create table students_info (id number(20), roll number(20),name varchar2(50) ,constraint un unique(roll));

Primary key :

This is used to avoid duplicates and nulls. This will work as combination of unique and not null.
syntax : create table ‘table-name’ (column1,column2,column3.. constraint ‘constraint-name’ primary key(‘column-name’)); Ex : create table students_info (id number(20), roll number(20),name varchar2(50) ,constraint pk primary key (id));

Foreign key :

This is used to reference the parent table primary key column which allows duplicates.
syntax : create table ‘table-name’ (column1,column2,column3.. constraint ‘constraint-name’ foreign key(‘column-name’) references ‘table-name’(column-name));
Ex : create table marks (id number(20), roll number(20),name varchar2(50),bangle number(20),English number(20), math number(20) ,constraint fk foreign key (id) references students_info(id));