GROUP BY Clause in Oracle

GROUP BY Clause in Oracle

 Hello Friends, 

                     Today I am going to tell you some interesting facts about oracle queries. SQL and Oracle Queries are seems to be same. Query which run fine on Mysql are expected to give same results on oracle , But some constraints are different for Oracle. I am explaining few of them :

GROUP BY clause :  

                                   In Oracle all the fields which are selected , should be in Group By clause  

Take an example to understand this :

QUERY :    Select  st_name, marks, semester from student group by st_name;

In MySql   : query runs fine and give the resultset . 

Oracle :    Select  st_name, marks, semester from student group by st_name;

Query Result : ORA-00979: *not a group by expression

Solution :  Select  st_name, marks, semester from student group by st_name, marks, semesters;

 But Group By clause does not contains group function arguments such as SUM, MIN, MAX or COUNT.

Example :  Select  st_name, sum(marks), semester from student group by st_name, semesters;

Example2 : 

                  select department, count(*) as no_of_result from employee where salary >25000 group  by department;

 

CREATING AND DELETING USER IN ORACLE

CREATING AND DELETING USER IN ORACLE

Oracle with Linux,  Commands

Command for creating a new user in oracle : 

CREATE USER <<user name>> IDENTIFIED BY <<user name>> DEFAULT TABLESPACE <<table space name >> TEMPORARY TABLESPACE  temp QUOTA 50M ON <<table-space name>>;

 In oracle users can be created by the above syntax  ,but before creating a new user one have to create table space .

Table space : Space which is obtained by tables for particular  user.

Syntax for creating TableSpace :

CREATE TABLESPACE  <<table space name >> DATAFILE  'diskb:tbs_<<table space name >>.dat' SIZE 50M REUSE  AUTOEXTEND ON NEXT 50M MAXSIZE 100M;

Now your table space and user space is defined. while connecting your user-name is name of user which you have given in create user command and default password is also same as user- name.

Now you have to give some permissions to the new user :

GRANT create session TO <<user-name>>;

GRANT create table TO <<user-name>>;
GRANT create view TO <<user-name>>;

Now if you want to delete the existing user , the command is :

DROP USER <<user name>> CASCADE;

and if you to delete the user including all its table-space and data files :

firstly drop the user :

DROP USER <<user name>> CASCADE;

 Now after droping user for each including table-space you have to take the table-space offline and then drop it.

ALTER TABLESPACE <<table space name >> OFFLINE;
DROP TABLESPACE <<table space name >> INCLUDING CONTENTS;

Now the user and all of its table -spaces are deleted. and the space assigned to them are free to be re-used.

 

INNER CLASSES (static, local and anonymous)

INNER CLASSES (static, local and anonymous)

Inner class (Nested Classes) :  as the name sounds a class which is define inside a class .

syntax for creating inner class : 

[modifiers] class OuterClassName {

    code...

    [modifiers] class InnerClassName {

        code....

    }

}

 

 

Some key points about Inner class :

 

A) An object of an inner class has an implicit reference to the outer class object that instantiated it. Through this pointer, it gains access to any variable of the outer object.

 

B) Inner classes are actually a phenomenon of the compiler and not the JVM.

 

C) The outer class (the class containing the inner class) can instantiate as many number of inner class objects as it wishes, inside it’s code.

 

D) No inner class objects are automatically instantiated with an outer class object.

 

E) If the inner class is static, then static inner class can be instantiated without an outer class instance, otherwise, the inner class object must be associated with an instance of the outer class.

 

F) Inner class code has free access to all elements of the outer class object that contains it, by name, if the inner class has a varible with same name then the outer class’s variable can be accesse like this:

 

<OuterClassName>.this.<variableName>

 

 Inner classes may be defined with following access modifiers : public, protected, private, or with default package access.

Based on these modifiers our next category of inner class is :

 

Static Inner Class




syntax : 




<access-specifier> class OuterClassName {

    public static class <StaticInnerClassName> {

        . . .

    }

    . . .

} 

Difference b/w static and non static inner class :




A) Static members of the outer class are visible to the static inner class, what ever their access level be but Non-static members of the outer class are not available, because there is not instance of the outer class.

B) A static inner class is just like any other inner class, but it dose not have the reference to its outer class object that generated it.    

  

Local Inner Classes: 

 

 Nested class which is define inside the body of a method of outer class is known as local inner classs.  

 

Syntax :  

 

<access-specifier> class <OuterClassName> {

    code...

    <access-specifier> <return-type> <MethodName>(<arguments>){

        class <LocalInnerClassName>{

            code...

        }

        code...

    }

    code...

}

 Since local inner classes are define inside a method scope of these classes are always restricted to the block in which they are declared.  They are completely hidden from the outside world.

 

Anonymous Inner Classes

Local inner class which is define without name .

 

Syntax :

new SuperType(construction parameters) {

    inner class methods and data

}

 Here, SuperType can be an interface, such as ActionListener; then, the inner class implements that interface. Or SuperType can be a class; then, the inner class extends that class.

Summary of all above Inner classes and compilation :

// Main class

public class Main {

 

    // Inner class Test1

    class Test1 {

    }

 

    // Inner class Test2

    class Test2 {

    }

 

    public static void main(String [] args) {

 

        // Anonymous inner class 1

        new Object() {

        };

 

        // Anonymous inner class 2

        new Object() {

        };

 

        System.out.println("Hello World");

    }

}

  

After Compilation by JVM

Main.class

Main$Test1.class

Main$Test2.class

Main$1.class

Main$2.class

 

Diffrent .class files are generated.