Changing NLS_Language NLS_Territory NLS_Calendar

Bismillahir Rahmanir Raheem

When I installed my Oracle database I probably selected US or I think default installation is US.
But you can change it as you like later
We will  see how to change it

You can see all your NLS_parameters from a dynamic view called NLS_Parameters

select *
from NLS_Parameters;

You will see in the results parameters like
NLS_Language    AMERICAN
NLS_Calendar    GREGORIAN
NLS_Territory     AMERICA

and others

so lets change the default values

alter session set NLS_Language='Arabic';

alter session set NLS_Territory='United Arab Emirates';

alter session set NLS_Calendar='Arabic Hijrah';


 Similarly you can change any NLS_parameters as you like

You might  want to know what are the values that you can assign to these NLS parameters

here is link to oracle documentation to help you

Oracle Database Globalization Support

Working with oracle timezone format TZR TZH TZM

Bismillahir Rahmanir Raheem

Today we will look to some format that are used to convert or outputting more precise information.

TZH--
This gives the hour from the timezone and is only valid with timestamp and interval formats

TZM----
This gives the minute from the timezone and is only valid with timestamp and interval formats

TZR---
This gives the time zone region information and is only valid with timestamp and interval formats

alter session set time_zone='asia/calcutta';

select to_char(systimestamp,'TZR-TZH-TZM')
from dual;

this will give us

Asia/calcutta-+05-30

NOte-- remember all these formats are only valid with timestamp and interval types not with date type

INSERTING ROWs INTO TABLE FROM SELECTING ROWS FROM ANOTHER TABLE

Bismillahir Rahmanir Raheem

We will insert rows into a table from retreiving rows from another table

Create table table1(id number,name varchar2(20));

insert into table1 values(111,'Mahtab Alam');
insert into table1 values(222,'Aftab Alam');
insert  into table1 values(333,'Sdre Alam');

Now we have three rows in table1 .
Lets add these rows into table2

create table table2(RollNo number,Student_name varchar2(20));

insert into table2
select id,name
from table1;

or

create table table3(pid number);

insert into table3(pid)
select id
from table1;

Oracle To_date() function for converting into date datatype

Bismillahir Rahmanir Raheem

Suppose I have table Entry as

Create table Entry(Entered number);

insert into Entry values(12122012);
insert into Entry values(10102013);
insert into Entry values(11112013);

select * from Entry;





Now lets change it to some meaningful  way , lets change it to date which it actually represents

SELECT  ENTERED,TO_DATE(ENTERED,'DDMMYYYY')
FROM     ENTRY;

HOW TO FIND OUT THE AVAILABLE TIMEZONES IN ORACLE SQL

BISMILLAHIR RAHMANIR RAHEEM

You can query the dynamic view named  as   v$timezone_names to get the list  of
available timezones .

select *
from v$timezone_names;

This will give you a huge list  of timezone names .
Now you can use these names to change your timezone

You can change your timezone either by giving timezone offset or timezone regional name.

Its better to use timezone regional names

Here is the query

alter session set time_zone='ASIA/CALCUTTA';

Using where clause with group by

Bismillahir Rahmanir Raheem

Consider the table student where  I have three columns
student
dept
gender

select dept,count(student)
from   student
where gender='M'
group by dept
having count(student) >= 2;

In the above query I have used where before group by what it does is ,it ignores those rows from the
consideration of group by clause which does not satisfy the where condition.

So group by only makes group that satisfy the condition , this gives you a way to filter your group before they are made and by using having clause with group by we can filter goups after they have been made.

Correlated SubQuery -The best way irrespective of database you are working with

Bismillahir Rahmanir Raheem

Now I am going to talk about the best way to deal with Queries Like finding Second max or min or any other Salary position and the best thing about this approach is that it is irrespective of database.
So it will work with MySQL,Oracle,MS SQL and any other that you probably know.

this is our table account

Name                           Salary
A                                  1000
B                                   500
C                                   800
D                                  1500
E                                  2000
F                                  3000

Before this we were using independent subqueries but in this approach we will use Correlated SubQuery.

So what is the difference between independent subquery and correlated subquery.
Independent subquery can execute even if the outer query is not present
But Correlated subquery will only execute when there is an outer query present .
So Correlated Subquery is only meaningful when there is an outer query.


So How can we find Maximum salary using Correlated subquery

How many salary is higher than Maximum salary ?
Answer : 0

Here is the solution query

Select salary
from account x
where 0=( select count(*)
                 from account y
                 where y.salary > x.salary);

So what about finding Minimum salary
How many salary is higher than Minimum Salary ?
Answer : count(*)-1

So first find number of rows inn the database using

Select count(*) from account;

So lets calculate second Highest salary
How many salary is higher than Second Highest Salary ?
Answer : 1

Select salary
from account x
where 1=( select count(*)
                 from account y
                 where y.salary > x.salary);

Using rank() and dense_rank() to find any salary rank in SQL

Bismillahir Rahmanir Raheem

Table sal
name                                salary
A 1000
B               500
C 600
D 200
E 700
F                                       500

Difference Between -rank and dense_rank

select name,salary,rank() over(order by salary desc)Rank
from sal;

name salary Rank
A 1000 1
E 700 2
C 600 3
B 500 4
F 500 4
D 200 6


select name,salary,dense_rank() over(order by salary desc)Rank
from sal;

name salary Rank
A 1000 1
E 700 2
C 600 3
B 500 4
F 500 4
D 200 5


Now you can find out any rank as your requirement by using a where clause and specifying the rank you desire.

say third most highest

select name,salary
from
(
  select name,salary,rank() over(order by salary desc)Rank
  from sal
)
where Rank=3;

Finding Second Maximum or Second Minimum in SQL

Bismillahir Rahmanir Raheem

Ok we will be finding the second Maximum and second Minimum salary in this post.

So any Idea how we will go about this problem ?

Think about the problem we have to find the second maximum ,If we can remove the first Maximum and then find Maximum
Then We will get the second Maximum Similarly the case for Second Minimum.

Ok this is the table account

name            salary
A                10000
B                2000
C                500
D                1500
E                1000
F                800


That is how we can solve this question

Select Max(salary) as Second_Maximum
from account
where salary != ( Select Max(salary)
                  from account
                 );

Output will be

Second_Maximum
--------------
2000


Now using same logic we can get Second Minimum

Select Min(salary) as Second_Minimum
from account
where salary != ( Select Min(salary)
                  from account
                   );

Output will  be

Second_Minimum
---------------  
800

Retrieving Minimum Salary from the database without using Min function

Bismillahir Rahmanir Raheem

How to retrieve Minimum salary without using Min function

As we have retrieved the maximum salary the concept is same Use self join.

name                                    salary
A                                         4000              
B                                         3000    
C                                         5000
D                                         2500                                        
E                                         1000
F                                         1500

Select a.salary
from account a,account b
where a.salary > b.salary;

Now the  minimum salary is the only one which is not greater than any other salary
All other salary is greater to at least  minimum salary.

This will give result containing all salaries except minimum salary

Now we will write an outer query to select salary that is not inn salaries returned by inner query

Select salary as Minimum_Salary
from account
where salary not in (Select a.salary
                              from account a,account b
                              where a.salary > b.salary);

This will give you the required result

Minimum_Salary
-----------------
1000

Remember one thing Corelated queries is different than independent qurey.

Retrieving Maximum salary without using Max function in SQL

Bismillahir Rahmanir Raheem

Today We are going to find the maximum salary from the database
without using Max function.

So how to do that , we will use Self Join to do that.

Say this is our table Account

name                        salary
A                             4000
B                             3000
C                             5000
D                             2500
E                             1000
F                             1500

Note one thing that maximum salary is the only one that is not less than any other salary in the database
All other salaries are less than maximum salary and other.
Like 3000 is less than 4000 and 5000
        1000 is less than 1500,2500,3000,4000,5000
        Similarly for other salaries But 5000 is not less than any other salarie

Select a.salary
from account a,account b
where a.salary < b.salary;

This will give list of salary having all salary values except the Maximum Salary which is 5000 in this case.

Now we are just one step behind our answer , All we have to do is to nest this query in an outer query
The Outer query will say give me salary that is not in the result of the inner query

select salary as Maximum_Salary
from account
where salary not in ( Select a.salary
                               from account a,account b
                               where a.salary < b.salary);

Maximum_Salary
----------------
5000

This is your result.

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.