Monday 29 February 2016

Java Fundamentals -2

Object and Classes

Since Java is an object oriented language, complete java language is build on classes and object. Java is also known as a strong Object oriented programming language(oops).
OOPS is a programming approach which provides solution to problems with the help of algorithms based on real world. It uses real world approach to solve a problem. So object oriented technique offers better and easy way to write program then procedural programming model such as C, ALGOL, PASCAL.

Main Features of OOPS

  • Inheritence
  • Polymorphism
  • Encapsulation
  • Abstraction
As an object oriented language Java supports all the features given above. We will discuss all these features in detail later.

Class

In Java everything is encapsulated under classes. Class is the core of Java language. Class can be defined as a template/ blueprint that describe the behaviors /states of a particular entity. A class defines new data type. Once defined this new type can be used to create object of that type. Object is an instance of class. You may also call it as physical existence of a logical template class.
A class is declared using class keyword. A class contain both data and code that operate on that data. The data or variables defined within a class are called instance variables and the code that operates on this data is known as methods.

Rules for Java Class

  • A class can have only public or default(no modifier) access specifier.
  • It can be either abstract, final or concrete (normal class).
  • It must have the class keyword, and class must be followed by a legal identifier.
  • It may optionally extend one parent class. By default, it will extend java.lang.Object.
  • It may optionally implement any number of comma-separated interfaces.
  • The class's variables and methods are declared within a set of curly braces {}.
  • Each .java source file may contain only one public class. A source file may contain any number of default visible classes.
  • Finally, the source file name must match the public class name and it must have a .java suffix.

A simple class example

Suppose, Student is a class and student's name, roll number, age will be its property. Lets see this in Java syntax
class Student.
{
 String name;
 int rollno;
 int age;
}
When a reference is made to a particular student with its property then it becomes an object, physical existence of Student class.
Student std=new Student();
After the above statement std is instance/object of Student class. Here the new keyword creates an actual physical copy of the object and assign it to the std variable. It will have physical existence and get memory in heap area. The new operator dynamically allocates memory for an object
creation of object in java

Q. How a class is initialized in java?

A Class is initialized in Java when an instance of class is created using either new operator or using reflection using class.forName(). A class is also said to be initialized when a static method of Class is invoked or a static field of Class is assigned.

Q. How would you make a copy of an entire Java object with its state?

Make that class implement Cloneable interface and call clone() method on its object. clone() method is defined in Object class which is parent of all java class by default.

Constructors in Java

A constructor is a special method that is used to initialize an object.Every class has a constructor,if we don't explicitly declare a constructor for any java class the compiler builds a default constructor for that class. A constructor does not have any return type.
A constructor has same name as the class in which it resides. Constructor in Java can not be abstract, static, final or synchronized. These modifiers are not allowed for constructor.
class Car
{
 String name ;
 String model;
 Car( )    //Constructor 
 {
  name ="";
  model="";
 }
}  

There are two types of Constructor

  • Default Constructor
  • Parameterized constructor
Each time a new object is created at least one constructor will be invoked.
Car c = new Car()       //Default constructor invoked
Car c = new Car(name); //Parameterized constructor invoked

Constructor Overloading

Like methods, a constructor can also be overloaded. Overloaded constructors are differentiated on the basis of their type of parameters or number of parameters. Constructor overloading is not much different than method overloading. In case of method overloading you have multiple methods with same name but different signature, whereas in Constructor overloading you have multiple constructor with different signature but only difference is that Constructor doesn't have return type in Java.

Q. Why do we Overload constructors ?

Constuctor overloading is done to construct object in different ways.

Example of constructor overloading

class Cricketer 
{
 String name;
 String team;
 int age;
 Cricketer ()   //default constructor.
 {
  name ="";
  team ="";
  age = 0;
 }
 Cricketer(String n, String t, int a)   //constructor overloaded
 {
  name = n;
  team = t;
  age = a;
 }
 Cricketer (Cricketer ckt)     //constructor similar to copy constructor of c++ 
 {
  name = ckt.name;
  team = ckt.team;
  age = ckt.age;
 }
 public String toString() 
 {
  return "this is " + name + " of "+team;
 }
}

Class test:
{
 public static void main (String[] args)
 {
  Cricketer c1 = new Cricketer();
  Cricketer c2 = new Cricketer("sachin", "India", 32);
  Cricketer c3 = new Cricketer(c2 );
  System.out.println(c2);
  System.out.println(c3);
  c1.name = "Virat";
  c1.team= "India";
  c1.age = 32;
  System .out. print in (c1);
 }
}
 
 output:
this is sachin of india
this is sachin of india
this is virat of india
 

Method Overriding

When a method in a sub class has same name and type signature as a method in its super class, then the method is known as overridden method. Method overriding is also referred to as runtime polymorphism. The key benefit of overriding is the abitility to define method that's specific to a particular subclass type.

Example of Method Overriding

class Animal { public void eat() { System.out.println("Generic Animal eating"); } } class Dog extends Animal { public void eat() //eat() method overriden by Dog class. { System.out.println("Dog eat meat"); } } As you can see here Dog class gives it own implementation of eat() method. Method must have same name and same type signature. NOTE : Static methods cannot be overridden because, a static method is bounded with class where as instance method is bounded with object.

Covariant return type

Since Java 5, it is possible to override a method by changing its return type. If subclass override any method by changing the return type of super class method, then the return type of overriden method must be subtype of return type declared in original method inside the super class. This is the only way by which method can be overriden by changing its return type.
Example :
class Animal { Animal myType() { return new Animal(); } } class Dog extends Animal { Dog myType() //Legal override after Java5 onward { return new Dog(); } }

Difference between Overloading and Overriding

Method OverloadingMethod Overriding
Parameter must be different and name must be same.Both name and parameter must be same.
Compile time polymorphism.Runtime polymorphism.
Increase readability of code.Increase reusability of code.
Access specifier can be changed.Access specifier most not be more restrictive than original method(can be less restrictive).
difference between overloading and overriding

Q. Can we Override static method ? Explain with reasons ?

No, we cannot override static method. Because static method is bound to class whereas method overriding is associated with object i.e at runtime.


 

instanceof operator

In Java, instanceof operator is used to check the type of an object at runtime. It is the means by which your program can obtain run-time type information about an object. instanceof operator is also important in case of casting object at runtime. instanceof operator return boolean value, if an object reference is of specified type then it return true otherwise false.

Example of instanceOf

public class Test
{
    public static void main(String[] args)
    {
      Test t= new Test();
      System.out.println(t instanceof Test);
      }
}
output true

Downcasting

downcasting in java

Example of downcasting with instanceof operator

class Parent{ }

public class Child extends Parent
{
    public void check()
    {
        System.out.println("Sucessfull Casting");
    }

    public static void show(Parent p)
    {
       if(p instanceof Child)
       {
           Child b1=(Child)p;
           b1.check();
       }
    }
    
    public static void main(String[] args)
    {
      
      Parent p=new Child();
      
      Child.show(p);
      
      }
}
Output
Sucessfull Casting

More example of instanceof operator

class Parent{}

class Child1 extends Parent{}

class Child2 extends Parent{}

class Test
{
  public static void main(String[] args)
  {
      Parent p =new Parent();
      Child1 c1 = new Child1();
      Child2 c2 = new Child2();
      
      System.out.println(c1 instanceof Parent);  //true 
      System.out.println(c2 instanceof Parent);  //true 
      System.out.println(p instanceof Child1);  //false 
      System.out.println(p instanceof Child2);  //false 
      
      p = c1;
      System.out.println(p instanceof Child1);  //true 
      System.out.println(p instanceof Child2);  //false 
      
      p = c2;
      System.out.println(p instanceof Child1);  //false 
      System.out.println(p instanceof Child2);  //true 
      
   }
    
}
Output
true
true
false
false
true
false
false
true 

this keyword

  • this keyword is used to refer to current object.
  • this is always a reference to the object on which method was invoked.
  • this can be used to invoke current class constructor.
  • this can be passed as an argument to another method.
Example :
class Box { Double width, weight, dept; Box (double w, double h, double d) { this.width = w; this.height = h; this.depth = d; } } Here the this is used to initialize member of current object.

The this is used to call overloaded constructor in java

class Car { private String name; public Car() { this("BMW"); //oveloaded constructor is called. } public Car(Stting n) { this.name=n; //member is initialized using this. } }

The this is also used to call Method of that class.

public void getName() { System.out.println("Studytonight"); } public void display() { this.getName(); System.out.println(); }

this is used to return current Object

public Car getCar() { return this; }
 

Friday 26 February 2016

Java Fundamnetals-1

What is Java

Java is a programming language and a platform.
Java is a high level, robust, secured and object-oriented programming language.
Platform: Any hardware or software environment in which a program runs, is known as a platform. Since Java has its own runtime environment (JRE) and API, it is called platform.

Java Example

Let's have a quick look at java programming example. A detailed description of hello java example is given in next page.
  1. class Simple{  
  2.     public static void main(String args[]){  
  3.      System.out.println("Hello Java");  
  4.     }  
  5. }                                                                                                                                              

    Types of Java Applications

    There are mainly 4 type of applications that can be created using java programming:

    1) Standalone Application

    It is also known as desktop application or window-based application. An application that we need to install on every machine such as media player, antivirus etc. AWT and Swing are used in java for creating standalone applications.

    2) Web Application

    An application that runs on the server side and creates dynamic page, is called web application. Currently, servlet, jsp, struts, jsf etc. technologies are used for creating web applications in java.

    3) Enterprise Application

    An application that is distributed in nature, such as banking applications etc. It has the advantage of high level security, load balancing and clustering. In java, EJB is used for creating enterprise applications.

    4) Mobile Application

    An application that is created for mobile devices. Currently Android and Java ME are used for creating mobile applications.

    Features of Java

    The prime reason behind creation of Java was to bring portability and security feature into a computer language. Beside these two major features, there were many other features that played an important role in moulding out the final form of this outstanding language. Those features are :

    1) Simple

    Java is easy to learn and its syntax is quite simple, clean and easy to understand.The confusing and ambiguous concepts of C++ are either left out in Java or they have been re-implemented in a cleaner way.
    Eg : Pointers and Operator Overloading are not there in java but were an important part of C++.

    2) Object Oriented

    In java everything is Object which has some data and behaviour. Java can be easily extended as it is based on Object Model.

    3) Robust

    Java makes an effort to eliminate error prone codes by emphasizing mainly on compile time error checking and runtime checking. But the main areas which Java improved were Memory Management and mishandled Exceptions by introducing automatic Garbage Collector and Exception Handling.

    4) Platform Independent

    Unlike other programming languages such as C, C++ etc which are compiled into platform specific machines. Java is guaranteed to be write-once, run-anywhere language.
    On compilation Java program is compiled into bytecode. This bytecode is platform independent and can be run on any machine, plus this bytecode format also provide security. Any machine with Java Runtime Environment can run Java Programs.
    Java is platform Independent Language

    5) Secure

    When it comes to security, Java is always the first choice. With java secure features it enable us to develop virus free, temper free system. Java program always runs in Java runtime environment with almost null interaction with system OS, hence it is more secure.

    6) Multi Threading

    Java multithreading feature makes it possible to write program that can do many tasks simultaneously. Benefit of multithreading is that it utilizes same memory and other resources to execute multiple threads at the same time, like While typing, grammatical errors are checked along.

    7) Architectural Neutral

    Compiler generates bytecodes, which have nothing to do with a particular computer architecture, hence a Java program is easy to intrepret on any machine.

    8) Portable

    Java Byte code can be carried to any platform. No implementation dependent features. Everything related to storage is predefined, example: size of primitive data types

    9) High Performance

    Java is an interpreted language, so it will never be as fast as a compiled language like C or C++. But, Java enables high performance with the use of just-in-time compiler.


     

    Data Types in Java

    Java language has a rich implementation of data types. Data types specify size and the type of values that can be stored in an identifier.
    In java, data types are classified into two catagories :
  6. Primitive Data type
  7. Non-Primitive Data type

1) Primitive Data type

A primitive data type can be of eight types :
Primitive Data types
charbooleanbyte shortintlongfloatdouble
Once a primitive data type has been declared its type can never change, although in most cases its value can change. These eight primitive type can be put into four groups

Integer

This group includes byte, short, int, long
byte : It is 8 bit integer data type. Value range from -128 to 127. Default value zero. example: byte b=10;
short : It is 16 bit integer data type. Value range from -32768 to 32767. Default value zero. example: short s=11;
int : It is 32 bit integer data type. Value range from -2147483648 to 2147483647. Default value zero. example: int i=10;
long : It is 64 bit integer data type. Value range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Default value zero. example: long l=100012;

Floating-Point Number

This group includes float, double
float : It is 32 bit float data type. Default value 0.0f. example: float ff=10.3f;
double : It is 64 bit float data type. Default value 0.0d. example: double db=11.123;

Characters

This group represent char, which represent symbols in a character set, like letters and numbers.
char : It is 16 bit unsigned unicode character. Range 0 to 65,535. example: char c='a';

Boolean

This group represent boolean, which is a special type for representing true/false values. They are defined constant of the language. example: boolean b=true;

2) Non-Primitive(Reference) Data type

A reference data type is used to refer to an object. A reference variable is declare to be of specific and that type can never be change. We will talk a lot more about reference data type later in Classes and Object lesson.

Identifiers in Java

All Java components require names. Name used for classes, methods, interfaces and variables are called Identifier. Identifier must follow some rules. Here are the rules:
  • All identifiers must start with either a letter( a to z or A to Z ) or currency character($) or an underscore.
  • After the first character, an identifier can have any combination of characters.
  • A Java keyword cannot be used as an identifier.
  • Identifiers in Java are case sensitive, foo and Foo are two different identifiers. 

Variable

Java Programming language defines mainly three kind of variables.
  1. Instance variables
  2. Static Variables
  3. Local Variables

1) Instance variables

Instance variables are variables that are declare inside a class but outside any method,constructor or block. Instance variable are also variable of object commonly known as field or property.
class Student
{
 String name;
 int age;
}
Here name and age are instance variable of Student class.

2) Static variables

Static are class variables declared with static keyword. Static variables are initialized only once. Static variables are also used in declaring constant along with final keyword.
class Student
{
 String name;
 int age;
 static int instituteCode=1101; 
}
Here instituteCode is a static variable. Each object of Student class will share instituteCode property.

3) Local variables

Local variables are declared in method constructor or blocks. Local variables are initialized when method or constructor block start and will be destroyed once its end. Local variable reside in stack. Access modifiers are not used for local variable.
float getDiscount(int price)
{
 float discount;
 discount=price*(20/100);
 return discount; 
}
Here discount is a local variable. 

Concept of Array in Java

An array is a collection of similar data types. Array is a container object that hold values of homogenous type. It is also known as static data structure because size of an array must be specified at the time of its declaration.
An array can be either primitive or reference type. It gets memory in heap area. Index of array starts from zero to size-1.

Array Declaration

Syntax :
datatype[] identifier;
or
datatype identifier[]; 
 

Initialization of Array

new operator is used to initialize an array.
Example :
int[] arr = new int[10]; //10 is the size of array. or int[] arr = {10,20,30,40,50};

Accessing array element

As mention ealier array index starts from 0. To access nth element of an array. Syntax
arrayname[n-1]; Example : To access 4th element of a given array
int[] arr={10,20,30,40};
System.out.println("Element at 4th place"+arr[3]);

The above code will print the 4th element of array arr on console.

foreach or enhanced for loop

J2SE 5 introduces special type of for loop called foreach loop to access elements of array. Using foreach loop you can access complete array sequentially without using index of array. Let us see an exapmle of foreach loop.

class Test { public static void main(String[] args)
{ int[] arr={10,20,30,40};
 for(int x:arr) { System.out.println(x); } } }

 output: 10 20 30 40

Java Operators

Java provides a rich set of operators enviroment. Java operators can be devided into following categories
  • Arithmetic operators
  • Relation operators
  • Logical operators
  • Bitwise operators
  • Assignment operators
  • Conditional operators
  • Misc operators

Arithmetic operators

Arithmetic operators are used in mathematical expression in the same way that are used in algebra.
operatordescription
+adds two operands
-subtract second operands from first
*multiply two operand
/divide numerator by denumerator
%remainder of division
++Increment operator increases integer value by one
--Decrement operator decreases integer value by one

Relation operators

The following table shows all relation operators supported by Java.
operatordescription
==Check if two operand are equal
!=Check if two operand are not equal.
> Check if operand on the left is greater than operand on the right
< Check operand on the left is smaller than right operand
>= check left operand is greater than or equal to right operand
<= Check if operand on left is smaller than or equal to right operand

Logical operators

Java supports following 3 logical operator. Suppose a=1 and b=0;
operator descriptionexample
&& Logical AND(a && b) is false
|| Logical OR(a || b) is true
! Logical NOT(!a) is false

Bitwise operators

Java defines several bitwise operators that can be applied to the integer types long, int, short, char and byte
operator description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR
<< left shift
>> right shift
Now lets see truth table for bitwise &, | and ^
a ba & ba | ba ^ b
0 0000
0 1011
1 0011
1 1110
The bitwise shift operators shifts the bit value. The left operand specifies the value to be shifted and the right operand specifies the number of positions that the bits in the value are to be shifted. Both operands have the same precedence.Example
a = 0001000
b= 2
a << b= 0100000
a >> b= 0000010 

Assignment Operators

Assignment operator supported by Java are as follows
operator descriptionexample
= assigns values from right side operands to left side operanda=b
+= adds right operand to the left operand and assign the result to lefta+=b is same as a=a+b
-= subtracts right operand from the left operand and assign the result to left operanda-=b is same as a=a-b
*= mutiply left operand with the right operand and assign the result to left operanda*=b is same as a=a*b
/= divides left operand with the right operand and assign the result to left operanda/=b is same as a=a/b
%= calculate modulus using two operands and assign the result to left operanda%=b is same as a=a%b

Misc operator

There are few other operator supported by java language.

Conditional operator

It is also known as ternary operator and used to evaluate Boolean expression
epr1 ? expr2 : expr3
If epr1Condition is true? Then value expr2 : Otherwise value expr3

instanceOf operator

This operator is used for object reference variables. The operator checks whether the object is of particular type (class type or interface type)

 

Tuesday 23 February 2016

Oracle SQl -5

INTEGRITY CONSTRAINTS

Integrity Constraints are used to prevent entry of invalid information into tables. There are five Integrity Constraints Available in Oracle. They are :
  • Not Null
  • Primary Key
  • Foreign Key
  • Check
  • Unique

Not Null

By default all columns in a table can contain null values. If you want to ensure that a column must always have a value, i.e. it should not be left blank, then define a NOT NULL constraint on it.
Always be careful in defining NOT NULL constraint on columns, for example in employee table some employees might have commission  and some employees might not have any commission. If you put NOT NULL constraint on COMM column then you will not be able insert rows for those employees whose commission is null. Only put NOT NULL constraint on those column which are essential for example in EMP table ENAME column is a good candidate for NOT NULL constraint.

Primary Key

Each table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key:
  • Whenever practical, use a column containing a sequence number. It is a simple way to satisfy all the other guidelines.
  • Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.
  • Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table.
  • Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.
  • Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null in any column that is part of the primary key.
  • Choose a column that is short and numeric. Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys.
For example in EMP table EMPNO column is a good candidate for PRIMARY KEY.
To define a primary key on a table give the following command.
alter table emp add constraint emppk primary key (empno);
The above command will succeed only if the existing values are compliant i.e. no duplicates are there in EMPNO column. If EMPNO column contains any duplicate value then the above command fails and Oracle returns an error indicating of non compliant values.
Whenever you define a PRIMARY KEY Oracle automatically creates a index on that column. If an Index already exist on that column then Oracle uses that index.

FOREIGN KEY

On whichever column you put FOREIGN KEY constraint then the values in that column must refer to existing values in the other table. A foreign key column can refer to primary key or unique key column of other tables. This Primary key and Foreign key relationship is also known as PARENT-CHILD relationship i.e. the table which has Primary Key is known as PARENT table and the table which has Foreign key is known as CHILD table. This relationship is also known as REFERENTIAL INTEGRITY.
The following shows an example of parent child relationship.

Here EMPNO in attendance table is a foreign key referring to EMPNO of EMP table.
alter table attendance add constraint empno_fk
     foreign key (empno) references emp(empno);
The above command succeeds only if EMPNO column in ATTENDANCE table contains values which are existing in EMPNO column of EMP table. If any value is not existing then the above statement fails and Oracle returns an error indicating non compliant values.
Some points to remember for referential integrity
  • You cannot delete a parent record if any existing child record is there. If you have to first delete the child record before deleting the parent record. In the above example you cannot delete row of employee no. 101 since it’s child exist in the ATTENDANCE table. However, you can delete the row of employee no. 103 since no child record exist for this employee in ATTENDANCE table.  If you define the FOREIGN KEY with ON DELETE CASCADE option then you can delete the parent record and if any child record exist it will be automatically deleted.
To define a foreign key constraint with ON DELETE CASCADE option give the following command.
ALTER TABLE attendance ADD CONSTRAINT empno_fk
          FOREIGN KEY (empno) REFERENCES emp(empno)
              ON DELETE CASCADE;

From Oracle version 9i, Oracle has also given a new feature i.e. ON DELETE SET NULL . That is it sets the value for foreign key to null whenever the parent record is deleted.
To define a foreign key constraint with ON DELETE SET NULL option give the following command.

ALTER TABLE attendance ADD CONSTRAINT empno_fk
          FOREIGN KEY (empno) REFERENCES emp(empno)
              ON DELETE SET NULL;
  • You also cannot drop the parent table without first dropping the FOREIGN KEY constraint from attendance table. However if you give CASCADE CONSTRAINTS option in DROP TABLE statement then Oracle will automatically drop the references and then drops the table.

CHECK

Use the check constraint to validate values entered into a column. For example in the above ATTENDANCE table, the DAYS column should not contain any value more than 31. For this you can define a CHECK constraint as given below
alter table attendance add constraint dayscheck
             check (days <= 31);
Similarly if you  want the salaries entered in to SAL column of employee table should be between 1000 and 20000 then you can define a CHECK constraint on EMP table as follows
alter table emp add constraint sal_check
                   check (sal between 1000 and 20000);
You can define as  many check constraints on a single column as you want there is no restrictions on number of check constraints.

UNIQUE KEY

Unique Key constraint is same as primary key i.e. it does not accept duplicate values, except the following differences
  • There can be only on Primary key per table. Whereas, you can have as many Unique Keys per table as you want.
  • Primary key does not accept NULL values whereas, unique key columns can be left blank.
  • You can also refer to Unique key from Foreign key of other tables.
On which columns you should put Unique Key Constraint ?

It depends on situations, first situation is suppose you have already defined a Primary key constraint on one column and now you have another column which also should not contain any duplicate values, Since a table can have only one primary key,  you can define Unique Key constraint on these columns.  Second situation is when a column should not contain any duplicate value but it should also be left blank. For example in the EMP table IDNO is a good candidate for Unique Key because all the IDNO’s are unique but some employees might not have ID Card so you want to leave this column blank.

To define a UNIQUE KEY constraint on an existing table give the following command.
alter table emp add constraint id_unique unique (idno);

Again the above command will execute successfully if IDNO column contains complying values otherwise you have to remove non complying values and then add the constraint.

Views

Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.
Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.
Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views

Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.
select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

        From emp e, dept d where e.deptno=d.deptno;

So everytime we want to see emp details and department names where they are working we have to give a long join query. Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below

create view emp_det as select e.empno,
e.ename,e.sal,e.deptno,d.dname,d.loc
        from emp e, dept d where e.deptno=d.deptno;
 
Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

select * from emp_det;

This will show same result as you have type the long join query.  Now you can treat this EMP_DET view same as  any other table.

For example, suppose all the employee working in Department No. 10 belongs to accounts department and most of the time you deal with these people. So every time you  have to give a DML or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid this, you can create a view as given below:-

CREATE VIEW accounts_staff AS
    SELECT Empno, Ename, Deptno
    FROM Emp
    WHERE Deptno = 10
    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Now to see the account people you don’t have to give a query with where condition you can just type the following query.
select * from accounts_staff;

select sum(sal) from accounst_staff;

select max(sal) from accounts_staff; 

As you can see how views make  things easier.

The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select.
Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);

Friday 19 February 2016

Oracle Sql-4

Joins

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.
For example the following query returns empno,name,sal,deptno and department name and city from department table.
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;
The above query can also be written like, using aliases, given below.
select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;
The above query can also be written like given below without using  table qualifiers.
select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;
And if you want to see all the columns of both tables then the query can be written like this.
select * from emp,dept where emp.deptno=dept.deptno;

Non EquiJoins.

Non equi joins is used to return result from two or more tables where exact join is not possible.
For example we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s
     where e.sal between s.lowsal and s.hisal

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.
For example the following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename  “Manager” from emp e,
      emp m where e.mgrid=m.empno

Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
  • To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B. 
  •  
  • To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
  • To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.
For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d
    where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.

Data Manipulation Language (DML) Statements

Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.
The following are the DML statements available in Oracle.
  • INSERT          :Use to Add Rows to existing table.
  • UPDATE        :Use to Edit Existing Rows in tables.
  • DELETE         :Use to Delete Rows from tables.
  • MERGE          :Use to Update or Insert Rows depending on condition.

Insert

Use the Insert Statement to Add records to existing Tables.
Examples.
To add a new row to an emp table.
Insert into emp values (101,’Sami’,’G.Manager’,
             ’8-aug-1998’,2000);

If you want to add a new row by supplying values for some columns not all the columns then you have to mention the name of the columns in insert statements.

For example the following statement inserts row in emp table by supplying values for empno, ename, and sal columns only. The Job and Hiredate columns will be null.
Insert into emp (empno,ename,sal) values (102,’Ashi’,5000);

Suppose you want to add rows from one table to another i.e. suppose we have Old_Emp table and emp table with the following structure
Insert rows from one table to another
Now we want to add rows from old_emp table to emp table. Then you can give the following insert statement:-

Insert into emp (empno, ename, sal)
      select empno, ename, sal from old_emp;

Multitable Insert

Suppose we have sales table with the following structure.

Sales
sales table
Now we want to add the rows from SALES table  Weekly_Sales Table in the following Structure.
weekly sales table
To achieve the above we can give a multi table INSERT statement given below

Insert all
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Mon’,mon_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Tue’,tue_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Wed’,wed_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Thu’,thu_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Fri’,fri_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Sat’,sat_amt)
        Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
            Fri_amt,sat_amt from sales;

Update

Update statement is used to  update rows in existing tables which is in your own schema or if you have update privilege on them.
For example to raise the salary by Rs.500 of employee number 104. You can give the following statement.
update emp set sal=sal+500 where empno = 104;

In the above statement if we did not give the where condition then all employees salary will be raised by Rs. 500. That’s why always specify proper WHERE condition if don’t want to update all employees.

For example We want to change the name of employee no 102 from ‘Sami’ to ‘Mohd Sami’ and to raise the salary by 10%. Then the statement will be.
update emp set name=’Mohd Sami’,
  sal=sal+(sal*10/100) where empno=102;

Now we want to raise the salary of all employees by 5%.
update emp set sal=sal+(sal*5/100);

Now to change the names of all employees to uppercase.
update emp set name=upper(name);
Suppose We have a student table with the following structure.
student table
Now to compute total which is sum of Maths,Phy and Chem and average.
update student set total=maths+phy+chem,

    average=(maths+phy+chem)/3;

Using Sub Query in the Update Set Clause.
Suppose we added the city column in the employee table and now we want to set this column with corresponding city column in department table which is join to employee table on deptno.

update emp set city=(select city from dept
                          where deptno= emp.deptno);

Delete

Use the DELETE statement to delete the rows from existing tables which are in your schema or if you have DELETE privilege on them.
For example to delete the employee whose empno is 102.
delete from emp where empno=102;
If you don’t mention the WHERE condition then all rows will be deleted.
Suppose we want to delete all employees whose salary is above 2000. Then give the following DELETE statement.
delete from emp where salary > 2000;
The following statement has the same effect as the preceding example, but uses a subquery:

DELETE FROM (SELECT * FROM emp)
    WHERE sal > 2000;
To delete all rows from emp table.
delete from emp;

Merge

Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is a new feature of Oracle Ver. 9i. It is also known as UPSERT i.e. combination of UPDATE and INSERT.
For example suppose we are having sales and sales_history table with the following structure.

sales and sales history table

Now we want to update sales_history table from sales table i.e. those rows which are already present in sales_history, their amount should be updated and those rows which are not present in sales_history table should be inserted.

merge into sales_history sh
  using sales s
  on (s.prod=sh.prod and s.month=sh.month)
   when matched then update set sh.amount=s.amount
   when not matched then insert values (prod,month,amount);

After the statement is executed sales_history table will look like this.


Data Definition Language (DDL) Statements

Data definition language (DDL) statements enable you to perform these tasks:
  • Create, alter, and drop schema objects
  • Grant and revoke privileges and roles
  • Analyze information on a table, index, or cluster
  • Establish auditing options
  • Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle implicitly commits the current transaction before and after every DDL statement.
Many DDL statements may cause Oracle to recompile or reauthorize schema objects.

DDL Statements are
CREATE        :Use to create objects like CREATE TABLE, CREATE FUNCTION,
                        CREATE SYNONYM, CREATE VIEW. Etc.
ALTER           :Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER
                         TABLESPACE, ALTER DATABASE. Etc.
DROP             :Use to Drop Objects like DROP TABLE, DROP USER, DROP
                        TABLESPACE, DROP FUNCTION. Etc.
REPLACE      :Use to Rename table names.
TRUNCATE   :Use to truncate (delete all rows) a table.

Create

To create tables, views, synonyms, sequences, functions, procedures, packages etc.

Example
To create a table, you can give the following statement
create table emp (empno number(5) primary key,
                   name varchar2(20),
                   sal number(10,2),
                   job varchar2(20),
                   mgr  number(5),
                   Hiredate  date,
                   comm number(10,2));
Now Suppose you have emp table now you want to create a TAX table with the following structure and also insert rows of those employees whose salary is above 5000.
Tax
Empno          
Tax
Number(5)               
Number(10,2)
To do this we can first create TAX table by defining column names and datatypes and then use INSERT into EMP SELECT …. statement to insert rows from emp table. like given below.

create table tax (empno number(5), tax number(10,2));
insert into tax select empno,(sal-5000)*0.40
                     from emp where sal > 5000;

Instead of executing the above two statements the same result can be achieved by giving a single CREATE TABLE AS statement.
create table tax as select empno,(sal-5000)*0.4
   as tax from emp where sal>5000

You can also use CREATE TABLE AS statement to create copies of tables. Like to create a copy EMP table as EMP2 you can give the following statement.
create table emp2 as select * from emp;

To copy tables without rows i.e. to just copy the structure give the following statement
create table emp2 as select * from emp where 1=2;



Transaction Control Language (TCL)

Transaction control statements manage changes made by DML statements.

What is a Transaction?

A transaction is a set of SQL statements which Oracle treats as a Single Unit. i.e. all the statements should execute successfully or none of the statements should execute.
To control transactions Oracle does not made permanent any DML statements unless you commit it. If you don’t commit the transaction and power goes off or system crashes then the transaction is roll backed.
TCL Statements available in Oracle are
COMMIT       :    Make changes done in  transaction permanent.
ROLLBACK  :    Rollbacks the state of database to the last commit point.
SAVEPOINT :    Use to specify a point in transaction to which later you can rollback.

COMMIT

To make the changes done in a transaction permanent issue the COMMIT statement.
The syntax of COMMIT Statement is
COMMIT  [WORK]  [COMMENT ‘your comment’];
WORK is optional.
COMMENT is also optional, specify this if you want to identify this transaction in data dictionary DBA_2PC_PENDING.
Example
insert into emp (empno,ename,sal) values (101,’Abid’,2300);
commit;

ROLLBACK

To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point.
Example :
delete from emp;
rollback;          /* undo the changes */

SAVEPOINT

Specify a point in a transaction to which later you can roll back.\

Example

insert into emp (empno,ename,sal) values (109,’Sami’,3000);
savepoint a;
insert into dept values (10,’Sales’,’Hyd’);
savepoint b;
insert into salgrade values (‘III’,9000,12000);
Now if you give
rollback to a; 

Then  row from salgrade table and dept will be roll backed. At this point you can commit the row inserted into emp table or rollback the transaction.

If you give
rollback to b;
Then row inserted into salgrade table will be roll backed. At this point you can commit the row inserted into dept table and emp table or rollback to savepoint a or completely roll backed the transaction.
If you give
rollback; 

Then the whole transactions is roll backed.

If you give
commit;
Then the whole transaction is committed and all savepoints are removed.

Data Control Language (DCL) Statements

Data Control Language Statements are used to grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.
The DCL statements are
GRANT          :Use to grant privileges to other users or roles.
REVOKE       :Use to take back privileges granted to other users and roles.
Privileges are of two types :
  • System Privileges
  • Object privileges
System Privileges are normally granted by a DBA to users. Examples of system privileges are CREATE SESSION, CREATE TABLE, CREATE USER etc.
Object privileges means privileges on objects such as tables, views, synonyms, procedure. These are granted by owner of the object.
Object Privileges are
ALTER Change the table definition with the ALTER TABLE statement.
DELETE Remove rows from the table with the DELETE statement.
Note: You must grant the SELECT privilege on the table along with the DELETE privilege.
INDEX Create an index on the table with the CREATE INDEX statement.
INSERT Add new rows to the table with the INSERT statement.
REFERENCES Create a constraint that refers to the table. You cannot grant this privilege to a role.
SELECT Query the table with the SELECT statement.
UPDATE Change data in the table with the UPDATE statement.

Note: You must grant the SELECT privilege on the table along with the UPDATE privilege.

Grant

Grant is use to grant privileges on tables, view, procedure to other users or roles

Examples

Suppose you own emp table. Now you want to grant select,update,insert privilege on this table to other user “SAMI”.
grant select, update, insert on emp to sami;

Suppose you want to grant all privileges on emp table to sami. Then
grant  all on emp to sami;

Suppose you want to grant select privilege on emp to all other users of the database. Then
grant select on emp to public;

Suppose you want to grant update and insert privilege on only certain columns not on all the columns then include the column names in grant statement. For example you want to grant update privilege on ename column only and insert privilege on empno and ename columns only. Then give the following statement
grant update (ename),insert (empno, ename)  on emp to sami;

To grant select statement on emp table to sami and to make sami be able further pass on this privilege you have to give WITH GRANT OPTION clause in GRANT statement like this.
grant select on emp to sami with grant option;

REVOKE

Use to revoke privileges already granted to other users.
For example to revoke select, update, insert privilege you have granted to Sami then give the following statement.
revoke select, update, insert on emp from sami;
To revoke select statement on emp granted to public give the following command.
revoke select on emp from public;
To revoke update privilege on ename column and insert privilege on empno and ename columns give the following revoke statement.
revoke update, insert on emp from sami;
Note :You cannot take back column level privileges. Suppose you just want to take back  insert privilege on ename column then you have to first take back the whole insert privilege and then grant privilege on empno column.

ROLES

A role is a group of Privileges. A role is very handy in managing privileges, Particularly in such situation when number of users should have the same set of privileges.

For example you have four users :Sami, Scott, Ashi, Tanya in the database. To these users you want to grant select ,update privilege on emp table, select,delete privilege on dept table. To do this first create a role by giving the following statement

create role clerks
Then grant privileges to this role.

grant select,update on emp to clerks;
grant select,delete on dept to clerks;
Now grant this clerks role to users like this
grant clerks to sami, scott, ashi, tanya ;

Now Sami, Scott, Ashi and Tanya have all the privileges granted on clerks role.
Suppose after one month you want grant delete on privilege on emp table all these users then just grant this privilege to clerks role and automatically all the users will have the privilege.
grant delete on emp to clerks;

If you want to take back update privilege on emp table from these users just take it back from clerks role.
revoke update on emp from clerks;

To Drop a role
Drop role clerks;

Wednesday 17 February 2016

Oracle SQl-2

SQL Queries, SELECT Statement

Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views
For example to retrieve all rows from emp table.

SQL> select  empno, ename, sal  from emp;
          Or (if you want to see all the columns values
You can also give * which means all columns)
SQL> select * from emp;

If you want to see only employee names and their salaries then you can type the following statement
SQL> select name, sal from emp;

Filtering Information using Where Conditions

 You can filter information using where conditions like suppose you want to see only those employees whose salary is above 5000 then you can type the following query with where condition
SQL>select * from emp where sal > 5000;
To see those employees whose salary is less than 5000 then the query will be
SQL> select * from emp where sal < 5000;

Logical Conditions

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table below lists logical conditions.
Condition  Operation  Example 
NOT   Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.  SELECT * FROM emp WHERE NOT (sal IS NULL);

SELECT * FROM emp WHERE NOT (salary BETWEEN  1000 AND 2000);  
AND   Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.  SELECT * FROM employees WHERE ename ='SAMI' AND sal=3000;  
OR   Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.  SELECT * FROM emp WHERE ename = 'SAMI' OR sal >= 1000;  

 Membership Conditions

A membership condition tests for membership in a list or subquery
The following table lists the membership conditions.
Condition Operation Example
IN "Equal to any member of" test. Equivalent to "= ANY".  SELECT * FROM emp WHERE deptno IN (10,20);

SELECT * FROM emp   WHERE deptno IN (SELECT deptno FROM dept WHERE city=’HYD’)
NOT IN Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL.  SELECT * FROM emp  WHERE ename NOT IN  ('SCOTT', 'SMITH');

Null Conditions

A NULL condition tests for nulls.
What is null?
If a column is empty or no value has been inserted in it then it is called null. Remember 0 is not null and blank string ‘ ’ is also not null.
The following example lists the null conditions.
Condition Operation Example
IS [NOT]
NULL

Tests for nulls. This is the only condition that you should use to test for nulls.


SELECT ename  FROM emp  WHERE deptno  IS NULL; 
SELECT * FROM emp WHERE ename IS NOT NULL;


EXISTS Conditions

An EXISTS condition tests for existence of rows in a subquery.
The following example shows the EXISTS condition.
Condition Operation Example
EXISTS

TRUE if a subquery returns at least one row.

SELECT deptno   FROM dept d  WHERE EXISTS
    (SELECT * FROM emp e
          WHERE d.deptno = e.deptno);

LIKE Conditions

The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set.
For example you want to see all employees whose name starts with S char. Then you can use LIKE condition as follows
SQL> select * from emp where ename like ‘S%’ ;

Similarly you want to see all employees whose name ends with “d”
SQL>select * from emp where ename like ‘%d’;
 
You want to see all employees whose name starts with ‘A’ and ends with ‘d’ like ‘Abid’, ’Alfred’, ’Arnold’.
 
SQL>select * from emp where ename like ‘A%d’;

You want to see those employees whose name contains character ‘a’ anywhere in the string.
SQL> select * from emp where ename like ‘%a%’;

To see those employees whose name contains ‘a’ in second position.
SQL>select * from emp where ename like ‘_a%’;

To see those employees whose name contains ‘a’ as last second character.
SQL>select * from emp where ename like ‘%a_’;

To see those employees whose name contain ‘%’ sign.  i.e. ‘%’ sign has to be used as literal not as wild char.

SQL> select * from emp where ename like ‘%\%%’ escape ‘\’;

The UNION [ALL], INTERSECT, MINUS Operators

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows.
select empno,ename,sal from emp
UNION
select empno,ename,salary from oldemp
What if you need to select rows from two tables, but tables have different columns?
In this situation you have to use TO_CHAR function to fill up missing columns.
For Example
This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:
select empno, ename, sal, to_char(null) as “Transfer Date” from emp
 UNION
select empno,ename,to_char(null) as “Sal”,tdate from oldemp;
EMPNO     ENAME     SAL       Transfer Date
-----     -----     ------    -------------
101       Sami      5000     
102       Smith              11-jul-2000
201       Tamim              10-AUG-2000
209       Ravi      2400     

UNION ALL Example

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

select empno,ename from emp
union all
select empno,ename from oldemp;

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT empno FROM emp
INTERSECT
SELECT empno FROM oldemp;

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT empno FROM emp
MINUS
SELECT empno FROM oldemp;

SORTING QUERY RESULTS

To sort query result you can use ORDER BY clause in SELECT statement. Sorting Examples.
The following query sorts the employees according to ascending order of salaries.
select * from emp order by sal;

The following query sorts the employees according to descending order of salaries.
 
select * from emp order by sal desc;

The following query sorts the employees according to ascending order of names.
select * from emp order by ename;

The following query first sorts the employees according to ascending order of names.If names are equal then sorts employees on descending order of salaries.

select * from emp order by ename, sal desc;

You can also specify the positions instead of column names. Like in the following query,which shows employees according to ascending order of their names.

select * from emp order by 2;

The following query first sorts the employees according to ascending order of salaries.
If salaries are equal then sorts employees on ascending order of names

select * from emp order by 3, 2;

Monday 15 February 2016

Oracle SQL-3

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.
Oracle SQL Functions can be divided into following categories
  • Number Functions

  • Character Functions

  • Miscellaneous Single Row Functions

  • Aggregate Functions

  • Date and Time Functions

Here are the explanation and example of these functions

Number Functions (also known as Math Functions)

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits.
The number functions available in Oracle are:
ABS  ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG
MOD POWER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number)

ABS

ABS returns the absolute value of n.
The following example returns the absolute value of -87:
SELECT ABS(-87) "Absolute" FROM DUAL;

  Absolute
  ----------
        87

ACOS

ACOS returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.
The following example returns the arc cosine of .3:
SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.26610367
Similar to ACOS, you have ASIN (Arc Sine), ATAN (Arc Tangent) functions.

CIEL

Returns the lowest integer above the given number.Example:
The following function return the lowest integer above 3.456;
select ciel(3.456) “Ciel” from dual;

Ciel
---------
        4

FLOOR         

Returns the highest integer below the given number.
Example:
The following function return the highest integer below 3.456;
select floor(3.456) “Floor” from dual;

Floor
------------
        3

COS

Returns the cosine of an angle (in radians).
Example:
The following example returns the COSINE angle of 60 radians.
select  cos(60) “Cosine” from dual;

SIN

Returns the Sine of an angle (in radians).
Example:
The following example returns the SINE angle of 60 radians.
select  SIN(60) “Sine” from dual;

 

TAN

Returns the Tangent of an angle (in radians).
Example:
The following example returns the tangent angle of 60 radians.
select  Tan(60) “Tangent” from dual;
Similar to SIN, COS, TAN  functions hyperbolic functions  SINH, COSH, TANH are also available in oracle.

MOD

Returns the remainder after dividing m with n.
Example
The following example returns the remainder after dividing 30 by 4.
Select mod(30,4) “MOD” from dual;

MOD
---------
        2

POWER

Returns the power of m, raised to n.
Example
The following example returns the 2 raised to the power of 3.
select  power(2,3) “Power” from dual;

POWER
---------
        8

EXP

Returns the e raised to the power of n.
Example
The following example returns the e raised to power of 2.
select exp(2) “e raised to 2” from dual;

E RAISED TO 2
-------------
       

LN

Returns natural logarithm of n.
Example
The following example returns the natural logarithm of 2.
select ln(2) from dual;

LN
------------

LOG

Returns the logarithm, base m, of n.
Example
The following example returns the log of 100.
select log(10,100) from dual;

LOG
---------
        2

ROUND

Returns a decimal number rounded of to a given decimal positions.
Example
The following example returns the no. 3.4573 rounded to 2 decimals.
select round(3.4573,2) “Round” from dual;

Round
------------
        3.46

TRUNC

Returns a decimal number Truncated to a given decimal positions.
Example
The following example returns the no. 3.4573 truncated to 2 decimals.
select round(3.4573,2) “Round” from dual;

Round
------------
        3.45

SQRT

Returns  the square root of a given number.
Example
The following example returns the square root of  16.
select  sqrt(16) from dual;

SQRT
---------
        4 

Character Functions

Character functions operate on values of dataype  CHAR or VARCHAR.

LOWER

Returns a given string in lower case.
select LOWER(‘SAMI’) from dual;

LOWER
-------------
sami

UPPER

Returns a given string in UPPER case.
select UPPER(‘Sami’) from dual;

UPPER
------------------
SAMI
                    

INITCAP

Returns a given string with Initial letter in capital.
select INITCAP(‘mohammed sami’) from dual;

INITCAP
------------------
Mohammed Sami

LENGTH

Returns the length of a given string.
select length(‘mohammed sami’) from dual;

LENGTH
------------
        13

SUBSTR

Returns a substring from a given string. Starting from position p to n characters.
For example the following query returns “sam” from the string “mohammed sami”.
select substr('mohammed sami',10,3) from dual;

Substr
--------
sam

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.
Example
The following query tests whether the character “a” occurs in string “mohammed sami”
select instr('mohammed sami','a') from dual;

INSTR
--------
4

REPLACE

Replaces a given set of characters in a string with another set of characters.
Example
The following query replaces “mohd” with “mohammed” .
select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE
---------
ali mohammed khan

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.
Example
The following query tests whether the character “a” occurs in string “mohammed sami”
select instr('mohammed sami','a') from dual;

INSTR
--------
4

REPLACE

Replaces a given set of characters in a string with another set of characters.
Example
The following query replaces “mohd” with “mohammed” .
select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE
---------
ali mohammed khan

 

TRANSLATE

This function is used to encrypt characters. For example you can use this function to replace characters in a given string with your coded characters.
Example
The following query replaces characters A with B, B with C, C with D, D with E,...Z with A, and a with b,b with c,c with d, d with e ....z with a.
select translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
      'BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from dual;

Encrypt
-----------
joufsgbdf

SOUNDEX

This function is used to check pronounciation rather than exact characters. For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.
Example
The following example compare those names which are spelled differently but are pronouced as “smith”.
Select ename from emp where soundex(ename)=soundex('smith');

ENAME
---------
Smith
Smyth
Smythe

RPAD

Right pads a given string with a given character to n number of characters.
Example
The following query rights pad ename with '*'  until it becomes 10 characters.
select rpad(ename,'*',10) from emp;

Ename
----------
Smith*****
John******
Mohammed**
Sami******

LPAD

Left pads a given string with a given character upto n number of characters.
Example
The following query left pads ename with '*'  until it becomes 10 characters.
select lpad(ename,'*',10) from emp;

Ename
----------
*****Smith
******John
**Mohammed
******Sami

LTRIM

Trims blank spaces from a given string from left.
Example
The following query returns string “       Interface        “ left trimmed.
select ltrim('       Interface       ') from dual;

Ltrim
--------------
Interface 

RTRIM

Trims blank spaces from a given string from Right.
Example
The following query returns string “       Interface        “ right trimmed.
select rtrim('       Interface       ') from dual;

Rtrim
------------
   Interface 

TRIM

Trims a given character from left or right or both from a given string.
Example
The following query removes zero from left and right of a given string.
Select trim(0 from '00003443500') from dual;

Trim
----------
34435

CONCAT

Combines a given string with another string.
Example
The following Query combines ename with literal string “ is a “ and jobid.
Select concat(concat(ename,' is a '),job) from emp;

Concat
----------------
Smith is a clerk
John is a Manager
Sami is a G.Manager

Aggregate Functions

Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
The important Aggregate functions are :
Avg     Sum     Max     Min      Count     Stddev     Variance

AVG

AVG( ALL /DISTINCT  expr)

Returns the average value of expr.
Example
The following query returns the average salary of all employees.
select avg(sal) “Average Salary” from emp;

Average Salary
------------------------
2400.40

SUM

SUM(ALL/DISTINCT  expr)

Returns the sum value of expr.
Example
The following query returns the sum salary of all employees.
select sum(sal) “Total Salary” from emp;

Total Salary
------------------------
26500

MAX

MAX(ALL/DISTINCT expr)

Returns maximum value of expr.
Example
The following query returns the max salary from the employees.
select max(sal) “Max Salary” from emp;

Maximum Salary
------------------------
4500

MIN

   MIN(ALL/DISTINCT  expr)

Returns minimum value of expr.
Example
The following query returns the minimum salary from the employees.
select min(sal) “Min Salary” from emp;

Minimum Salary
------------------------
1200

COUNT

  COUNT(*) OR COUNT(ALL/DISTINCT expr)

Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.
Example
The following query returns the number of  employees.
Select count(*) from emp;

COUNT
------
14
The following query counts the number of employees whose salary is not null.
Select count(sal) from emp;

COUNT
------
12

STDDEV

STDDEV(ALL/DISTINCT  expr)

STDDEV returns sample standard deviation of expr, a set of numbers.
Example
The following query returns the standard deviation of salaries.
select stddev(sal) from emp;

Stddev
-------
 1430

VARIANCE

VARIANCE(ALL/DISTINCT  expr)

Variance returns the variance of expr.
Example
The following query returns the variance of salaries.
select variance(sal) from emp;

Variance
-------  
1430

Aggregate Functions

Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
The important Aggregate functions are :
Avg     Sum     Max     Min      Count     Stddev     Variance

AVG

        AVG( ALL /DISTINCT        expr)
Returns the average value of expr.
Example
The following query returns the average salary of all employees.
select avg(sal) “Average Salary” from emp;

Average Salary
------------------------
2400.40

SUM

     SUM(ALL/DISTINCT           expr)
Returns the sum value of expr.
Example
The following query returns the sum salary of all employees.
select sum(sal) “Total Salary” from emp;

Total Salary
------------------------
26500

MAX

    MAX(ALL/DISTINCT          expr)
Returns maximum value of expr.
Example
The following query returns the max salary from the employees.
select max(sal) “Max Salary” from emp;

Maximum Salary
------------------------
4500

MIN

   MIN(ALL/DISTINCT           expr)
Returns minimum value of expr.
Example
The following query returns the minimum salary from the employees.
select min(sal) “Min Salary” from emp;

Minimum Salary
------------------------
1200

COUNT

  COUNT(*) OR COUNT(ALL/DISTINCT expr)

Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.
Example
The following query returns the number of  employees.
Select count(*) from emp;

COUNT
------
14
The following query counts the number of employees whose salary is not null.
Select count(sal) from emp;

COUNT
------
12

STDDEV

 STDDEV(ALL/DISTINCT   expr)

STDDEV returns sample standard deviation of expr, a set of numbers.
Example
The following query returns the standard deviation of salaries.
select stddev(sal) from emp;

Stddev
-------
 1430

VARIANCE

VARIANCE(ALL/DISTINCT  expr)

Variance returns the variance of expr.
Example
The following query returns the variance of salaries.
select variance(sal) from emp;

Variance
-------  
1430

Date Functions and Operators.

To see the system date and time use the following functions :
CURRENT_DATE    :returns the current date in the session time zone, in a value in the Gregorian calendar of datatype
                                   DATE
SYSDATE                 :Returns the current date and time.
SYSTIMESTAMP    :The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone
                                    of the database. The return type is TIMESTAMP WITH TIME ZONE.

SYSDATE Example

To see the current system date and time give the following query.
select sysdate from dual;

SYSDATE
-------
8-AUG-03
The format in which the date is displayed depends on NLS_DATE_FORMAT parameter.
For example set the NLS_DATE_FORMAT to the following format
alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH:MIpm’;
Then give the give the following statement
select sysdate from dual;

SYSDATE
------------------
8-AUG-2003 03:05pm
The default setting of NLS_DATE_FORMAT is DD-MON-YY


CURRENT_DATE Example

To see the current system date and time with  time zone use CURRENT_DATE function
ALTER SESSION SET TIME_ZONE = '-4:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-04:00          22-APR-2003 14:15:03

ALTER SESSION SET TIME_ZONE = '-7:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-07:00          22-APR-2003 09:15:33

SYSTIMESTAMP Example

To see the current system date and time with fractional seconds with time zone give the following statement
select systimestamp from dual;

SYSTIMESTAMP
-------------------------------
22-APR-03 08.38.55.538741 AM -07:00

DATE FORMAT MODELS

To translate the date into a different format string  you can use TO_CHAR function with date format. For example to see the current day you can give the following query
Select to_char(sysdate,’DAY’)”Today” FROM DUAL;

TODAY
-------
THURSDAY
To translate a character value, which is in format other than the default date format, into a date value you can use TO_DATE function with date format to date.

TO_DATE Example

To_Date function is used to convert strings into date values. For example you want to see what was the day on 15-aug-1947. The use the to_date function to first convert the string into date value and then pass on this value to to_char function to extract day.
select to_char(to_date(’15-aug-1947’,’dd-mon-yyyy’),’Day’)
                                         from dual;

TO_CHAR(
--------
Friday
To see how many days have passed since 15-aug-1947 then give the following query
select sysdate-to_date(’15-aug-1947’,’dd-mon-yyyy’) from dual;
Now we want to see which date will occur after 45 days from now
select sysdate+45 from dual;

SYSDATE
-------
06-JUN-2003


ADD_MONTHS

To see which date will occur after 6 months from now, we can use ADD_MONTHS function
Select ADD_MONTHS(SYSDATE,6) from dual;

ADD_MONTHS
----------
22-OCT-2003

MONTHS_BETWEEN

To see how many months have passed since  a particular date, use the MONTHS_BETWEEN function.

For Example, to see how many months have passed since 15-aug-1947, give the following query.
select months_between(sysdate,to_date(’15-aug-1947’))
                             from dual;

Months
------
616.553
To eliminate the decimal value use truncate function
select trunc(months_between(sysdate,to_date(’15-aug-1947’)))
                             from dual;

Months
------
616

LAST_DAY

To see the last date of the month of a given date, Use LAST_DAY function.
select LAST_DAY(sysdate) from dual;

LAST_DAY
--------
31-AUG-2003

NEXT_DAY

To see when a particular day is coming next ,  use the NEXT_DAY function.

For Example to view when next Saturday is coming, give the following query
select next_day(sysdate) from dual;

NEXT_DAY
-----------
09-AUG-2003

EXTRACT

An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation
The syntax of EXTRACT function is
EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

Example
The following demonstrate the usage of EXTRACT function to extract year from current date.

select extract(year from sysdate) from dual;

EXTRACT
-------
2003