Set Operators

Bismillahir Rahmanir Raheem
There are four set operators union,union all,intersect,minus .
Set operators combine two or more separate select statement so that their o/p is merged in some way.

Set operators are ideal for a variety of situations where a select statement`s o/p can be combined with
other data that isn`t necessarily related through a structured key relationship.

Union-combines row sets.eliminate duplicate row sets
Union Alll-combines row sets.does not eliminate duplicate row sets
intersect-includes only those row sets present in both queries
minus-subtracts the rows of the second row set from the rows in the first row set.

Rules that ensure select statements will combine successfully with any of the set operators
1.Number of expressions selected in select lists must be identical in each select statement
2.Datatypes of each expression must share the same datatype group.It means datatypes that are identical or can be made
  identical through automatic datatype conversion
3.Clob and Blob datatypes can not be used
4.Order by clause can not be included in select statement except for the final select statement and only once.

Note---
Select statements are not required to have any sort of primary key/foreign key relationships.

Union--

Union All--

Intersect--

intersect operator

Minus--

Minus Operator

Notice the column heading of the o/p ,column heading is same as columns selected in the first select statement.

Control the order of Rows returned----
Their are two ways to sort rows of o/p resulting from a series of select statement combined with set operators
1.Order By-by position  
2.Order By-by reference

Note--You can not use column names of the second select statement while sorting because column names of first select statement are in force.

by position--select id,name
from k1
union
select class,section
from k2
order by name;
------Or you could have written it as
select id,name
from k1
union
select class,section
from k2
order by 2;

You will get the same answer.

by reference--
Order by reference is when you name one of the columns in the select statement`s expression list.

View and inline view

Bismillahir Rahmanir Raheem
Today we are going to work with database views(or specifically they are table views).

A view is a select statement.Generally Views are created to present the different view for different
users or to represent the table`s restricted view.

Like you have some confidential information in your table with other table columns that represent general information
and you want to hide that confidential information so that not every one can see that ,so what we do is we create
a view containing only general information.

Lets look at an example
Creating View--

create table persons(id number,name varchar2(20),salary varchar2(20));

now we want salary to be hidden.So we omit the salary column while creating view on the persons table.

create view persons_view as
select id,name
from persons;

This creates the view named persons_view with two columns id and name.
You can see the view structure by describing it.(desc view_name)
Now you can query the view as you do with any other table.

You can select from the view,insert in it,delete and update in view.
Although their are issues with insertion of row in view.
Lets discuss them

Inserting in a View--
The key thing to remember is all the operations that we perform are actually been done on the underlying table
of the view.So when we say inserting a row in a view it means inserting new row in the underlying table.
Deleting entry from the view ,deleting entry from the underlying table.
Updating entry in the view ,updating entry in the underlying table.

Now persons_view consists of just two columns id and name.So it means when we insert a row in the view
the column salary will get null value.Now this is ok because we don`t have not null constraint
on the salary column in the persons table.But if create a not null constraint then we can`t insert in view.
Now i`m going to delete the row where salary is null and then create not null constraint on salary.

So whenever insertion or updation in the view fail to satisfy the constraints that are created on the underlying table then in that case insert and update statement will fail.

Inline view---
When a select statement forms the from clause of an outer select statement.Then the inner select statement that
forms the from clause of outer select statement is called an inline view.

e.g select id,name
    from (select id,name,salary from persons);
   
Alter view---
Now consider this case.We created view     persons_view consisting of columns id and name.
Now what if later we drop the column id or rename it.Now in this case we you have to
recreate that view with appropriate column names.
Now what it means to recompile a view ?
If we performed some sort of modification on the underlying table or tables, depending upon the change we
make to the view`s source table the view may be rendered as invalid.
An invalid view can not be used and will require recompilation.

Here is how we recompile a view

alter view persons_view compile;

If you want to change the view`s select statement you can not change view`s definition.
Instead you must drop and recreate the view.
   

Adding Column in table and Renaming column

Bismillahir Rahmanir Raheem
Today we are going to learn how to add columns in a table after it have been created.

So lets get started.

Adding column in a table--
We use the alter table statement to add one or more columns to a table.

create table exm(id number,name varchar2(20));

a)alter table exm add (age number);
    or in case of adding only a single column ,you can avoid using parentheses
  alter table exm add age number;

Note-while adding more than one column you must have to use parentheses
alter table exm add(class number,phone varchar2(20));
 


General form of alter table statement

ALTER TABLE table_name clause;

For each column clause specifies one or more of the following three elements

1.Datatype and datatype specification-required
2.DEFAULT and default value-optional
3.Constraint definition-optional

So in the above table exm we have added columns using just first element which is must
But we could have specified default value for that column and any constraint associated at the time of adding column.

So now lets do that.
alter table exm add enroll varchar2(20) default 'Fresher';
alter table exm add backlogs varchar2(20) default 'No' not null;

Note-------
1.while creating not null constraint(without default statement) with column definition ,it will only be possible
  if their is no data (no row) in the table

2.When their is already entries in the table then you must use default clause with some default value 

Renaming a column--
alter table exm rename column id to identity;

Inner and Outer Join

Starting with the name of Allah the most merciful

Today we are going to see the various ways of joining tables.
Broadly their are two ways in which we can characterize these join types.

Inner Join--Inner join connects rows in two or more table if and only if
here are matched rows in all the tables being joined.In most of the cases their are only two tables
but its not always the case

Outer join--Outer join connects rows in two or more table in a way that is more inclusive,it means that unmatched row
will be included in the output.

First lets take on Inner join.
1)Inner join--
create table t11(id1 number,name1 varchar2(20));
create table t22(id2 number,name2 varchar2(20));

then you can join tables t1 and t2 like below

select id1,id2,name1,name2
from t11 inner join t22
on id1=id2;

--or you can say--

select id1,id2,name1,name2
from t11 join t22
on id1=id2;

By default join is inner join.So when you write only join it means inner join
So we are joining tables t11 and t22 based on the id1 and id2 values.

you can see that only two rows are in the resulting output after join.
since only two values of id1 from t11 and id2 from t22 (that is 1 and 2) matches.

There is another way to join tables without using the join keyword that is by using where clause.
select id1,id2,name1,name2
from t11,t22
where id1=id2;

--or with using table alias--

select tab1.id1,tab2.id2,tab1.name1,tab2.name2
from t11 tab1,t22 tab2
where tab1.id1=tab2.id2;

Both will produce the same output.

Note-- that only two rows are in the output ,there is no unmatched row in the output

now lets see how outer join is different from inner join

Outer Join)
Left Outer Join--It will show the unmatched rows of left side table of the join(left_table left outer join right_table)
Right Outer Join--It will show unmatched rows of right side table of the join(left_table right outer join right_table)
Full Outer Join--It will show both tables unmatched rows.

a)Left Outer Join
select id1,id2,name1,name2
from t11 left outer join t22    [you can omit the keyword outer here]
on id1=id2;

b)Right Outer Join
select id1,id2,name1,name2
from t11 right outer join t22    [again you can omit the keyword outer here]
on id1=id2;

c)Full Outer Join
select id1,id2,name1,name2
from t11 full outer join t22    [as before you can omit the keyword outer here]
on id1=id2;

Deprecated Outer Join Syntax(using + operator)
1)for left join
select id1,id2,name1,name2
from t11,t22
where id1=id2(+);

2)for right join
select id1,id2,name1,name2
from t11,t22
where  id1(+)=id2;

Creating database Constraints -Part 2

Bismillahir Rahmanir Raheem
Now we are going to see the other way of creating constraints with Alter table statement.
And with that we will also look at both the approach in-line and outof-line.

Lets start creating constraints one by one.
1. Primary Key constraint
DML ststement for the table creation is
create table xyz(x number);

a)in-line
alter table xyz
modify x primary key;
    or
alter table xyz
modify x constraint cons1 primary key;   

b)outof-line
alter table xyz
add primary key(x);
    or
alter table xyz
add constraint c1 primary key(x);

2. Unique Key Constraint   
DML ststement for the table creation is
create table xyz(x number);

a)in-line
alter table xyz
modify x unique;
    or
alter table xyz
modify x constraint unique;

b)outof-line
alter table xyz
add unique(x);
    or
alter table xyz
add constraint cvh unique(x);

3. Check Constraint   
Again DML ststement for the table creation is
create table xyz(x number);

a)in-line
alter table xyz
modify x check(x in (1,2));
    or
alter table xyz
modify x constraint check1 check(x in(1,2));

b)outof-line
alter table xyz
add check(x in(1,2));
        or
alter table xyz
add constraint cer check(x in(1,2));

4. Foreign key constraint
Here are the DML statements of the tables used below.

create table f1(id number primary key);
create table f2(name varchar2(20),assignment number);

a)in-line   
alter table f2
modify assignment references f1(id);
    or
alter table f2
modify assignment constraint fk_cons references f1(id);

b)outof-line
alter table f2
add foreign key(assignment)    references f1(id);
    or
alter table f2
add constraint fk_cons foreign key(assignment) references f1(id);

5.    Not null constraint
DML ststement for the table creation is
create table xyz(x number);
a)in-line
alter table xyz
modify x not null;
    or
alter table xyz
modify x constraint nyc not null;

b)outof-line
As the case with create table statement same is true for alter table statement
you can not use outof-line approach for not null constraint    .




   

Creating Database constraints Part1

Bismillahir Rahmanir Raheem
Now lets talk about constraints.
In oracle sql their are five constraints.

1. Primary key constraint
2. Not null constraint
3. Foreign key constraint
4. Unique key constraint
5. Check Constraint

Most of the students are aware of primary constraint,not null and Foreign key constraint.
We will look at each of them in detail.And how to craete them in database.

Not null constraint --
When we add a not null constraint on a table column it means that it must be assigned a value
when a new row is added to the table


Primary key and Unique constraint--
Primary key constraint uniquely identifies a tuple in a table.Primary key can be composed of one or more table column.
One important thing to note that primary key constraint is not the same as unique constraint.
Unique constraint also uniquely identifies a tuple in table.

So what is the difference between primary key and unique constraint.
Following are the important notes to remember
1. When we create primary constraint on a table column then their must be some value entered in that column
   it can not be null while this is not the case with unique constraint.when you create a unique constraint
   on a table column it can have a null value(means no value).
2. So primary key constraint is combination of two constraints(not null constraint+ unique constraint)  

Foreign key constraint--
Their comes situations in which we require that the value entered in a table column(column1) must be one of those that are in some other table column (column2).This type of constraint is said a foreign key constraint on column1.foreign key (column1) references column1 of table1.
If you don`t understand it clearly look to the below heading where we create foreign key constraint.


Check constraint--
Check constraint when applied on a table column ,then it checks that the value entered in that column
must be one of the one that you have specified while creating check constraint

Now lets See how to create all these constraints in different ways.Their are two ways

1.Creating constraint with Create Table statement
2.Creating constraint with Alter Table statement

Now with both these ways associated two ways one is called in-line and the other one is outof-line approach.
Now you will be wondering what is meant by in-line and outof-line.
In-line means that the constraint is created while defining the column
while in outof-line approach you define constraint after the column definition.

Now lets get ready to this part of constraint creation
Creating constraints with Create table statement--
1. Primary key constraint
   a)in-line
   create table my_table(id number(2) primary key,name varchar2(20));[constraint will be given system     assigned name]
                  or
   create table my_table(id number(2)  constraint cons1 primary key,name varchar2(20));    [constarint is given name cons1]

   You can see that in in-line approach we are creating the constraint with column definition  

   b)outof-line
   create table my_table(id number(2),name varchar2(20) ,primary key(id)); [constraint will be given system assigned name]
                or
   create table my_table(id number(2),name varchar2(20),constraint prime_cons primary key(id)); [constarint is given name prime_cons]
  
2. Foreign key constraint
   a)in-line
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20) references tab1(department));
         or
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20) constraint c1 references tab1(department));   
  
   b) outof-line
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20),constraint c1 foreign key(dept) references tab1(department));
                or
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20),foreign key(dept) references tab1(department));
   
    Now when you insert a row in table tab2, then the value you assign to column dept of tab2 must be one of the values already present
    in column department of tab1
   
3. Check constraint--
   a)in-line
    create table xy(id number check (id in (1,2)));
         or
    create table xy(id number constraint id_cons check(id in (1,2)));
   b)outof-line
   create table xy(id number,check (id in (1,2)) );
            or
   create table xy(id number,constraint id_check check(id in (1,2)));

4. Unique constraint--
   a)in-line
    create table abc(id number unique);
      or
    create table abc(id number constraint id_unique);
    b)outof-line
    create table abc(id number,unique(id));
          or
    create table abc(id number,constraint id_unique unique(id));          
5. Not null constraint--
      a)in-line
      create table op(id number not null);
           or
      create table op(id number constraint id_cons    not null);
     
      b) You can not create not null constraint with outof-line approach
        So you must always create not null constraint while defining the column     


We will see the other way around of creating constraints with Alter table statement or creating constraint after table creation in the next post.