Create a new schema/new user in oracle 11g

Generally a schema in oracle is the same as an user. Oracle Database automatically creates a schema when you create a user. A file with the DDL file extension is an SQL Data Definition Language file.

Show already created tablespaces:

SQL> select tablespace_name from dba_tablespaces;

 Create tablespace:

SQL> create tablespace edcfc_tabspace
     datafile 'edcfc_tabspace.dat'
     size 10M autoextend on;
   

Create temporary tablespace (Temporaty tablespace is an allocation of 
space in the database that can contain transient data that persists only
for the duration of a session. This transient data cannot be recovered 
after process or instance failure.):

Create temporary tablespace :-

SQL> create temporary tablespace edcfc_tabspace_temp
     tempfile 'edcfc_tabspace_temp.dat'
     size 5M autoextend on;

Create User:-

 SQL > create user eDCFC
       identified by edcfc
       default tablespace EDCFC_TABSPACE
       temporary tablespace EDCFC_TABSPACE_TEMP; 

Grant some privileges:- 

  SQL > grant create session to edcfc;
        grant create table to edcfc;
        grant unlimited tablespace to edcfc;

Login as edcfc and check what privileges he has:- 

  SQL > select * from session_privs;

       PRIVILEGE
     ----------------------------------------
      CREATE SESSION
      UNLIMITED TABLESPACE
      CREATE TABLE 

With create table privilege the user can create tables:

SQL > CREATE TABLE "EMP" (emp_id .............);

SQL > INSERT INTO EMP VALUES (...........) ;

More information:

DDL

• http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_1001.htm

DBMS_METADATA

• http://www.dba-oracle.com/t_1_dbms_metadata.htm
• http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS026
• http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm#ADMIN11562

Schema objects

• http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm

Differences between schema and user

• http://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
• difference between a User and a Schema in Oracle?

Privileges

• http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#TTSQL338

Creating user/schema

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
• http://www.techonthenet.com/oracle/schemas/create_schema.php

Creating tablespace

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

SQL Plus commands

• http://ss64.com/ora/syntax-sqlplus.html

JOINS IN ORACLE-different joins in oracle with examples

The purpose of a join is to combine the data across tables. 

A join is actually performed by the where clause which combines the specified rows of tables.

If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES

1. Equi join  

2. Non-equi join   

3. Self join   

4. Natural join   

5. Cross join

6. Outer join 

     1. Left outer 

     2. Right outer 

     3.Full outer 

7. Inner join  

8. Using clause     

9. On clause

Assume that we have the following tables.

SQL> select * from dept;

          DEPTNO DNAME        LOC

          ------         ----------         ----------

          10            mkt               hyd

          20            fin                 bang

          30            hr                 Bombay

SQL> select * from emp;

       EMPNO   ENAME      JOB       MGR     DEPTNO

      ----------     ----------      ----------   ---------- ----------

       111          saketh      analyst           444         10

       222          sudha      clerk                333         20

       333          jagan       manager         111         10

       444          madhu     engineer         222         40

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex: SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

          EMPNO     ENAME      JOB    DNAME      LOC

          ----------      ----------     ---------- ----------    ----------

            111           saketh    anal yst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha     clerk        fin         bang

USING CLAUSE

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

          ----------      ----------     ----------   ---------- ----------

            111           saketh    analyst     mkt          hyd

            333           jagan      manager  mkt         hyd

            222           sudha     clerk        fin          bang

ON CLAUSE

SQL>select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex: SQL>select empno, ename, job, dname, loc from emp e,dept d where   e.deptno > d.deptno;

          EMPNO     ENAME    JOB      DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

       222    sudha      clerk          mkt        hyd

       444    madhu     engineer   mkt        hyd

       444    madhu     engineer   fin          bang

       444    madhu     engineer   hr           bombay

SELF JOIN

Joining the table itself is called self join.

Ex:

     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where

             e1.empno=e2.mgr;

     EMPNO     ENAME    JOB      DEPTNO

     ---------- ---------- ---------- ----------

       111          jagan      analyst         10

       222          madhu      clerk           40

       333          sudha      manager      20

       444          saketh     engineer      10

NATURAL JOIN

Natural join compares all the common columns.

Ex:

     SQL> select empno,ename,job,dname,loc from emp natural join dept;

     EMPNO   ENAME      JOB      DNAME    LOC

    ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst     mkt        hyd

       333          jagan      manager   mkt        hyd

       222          sudha      clerk         fin          bang

CROSS JOIN

This will gives the cross product.

Ex:

     SQL> select empno,ename,job,dname,loc from emp cross join dept;

 EMPNO  ENAME    JOB        DNAME      LOC

---------- ---------- ---------- ---------- ----------

       111     saketh   analyst      mkt        hyd

       222     sudha    clerk          mkt        hyd

       333     jagan     manager   mkt        hyd

       444     madhu   engineer   mkt        hyd

       111     saketh   analyst      fin          bang

       222     sudha    clerk          fin          bang

       333     jagan     manager   fin          bang

       444     madhu   engineer   fin          bang

       111     saketh   analyst      hr           bombay

       222     sudha    clerk          hr           bombay

       333     jagan     manager   hr           bombay

       444     madhu   engineer   hr           bombay

OUTER JOIN

Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d

             on(e.deptno=d.deptno);

Or

     SQL> select empno,ename,job,dname,loc from emp e,dept d where

             e.deptno=d.deptno(+);

                             EMPNO     ENAME   JOB       DNAME      LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh    analyst       mkt        hyd

       333          jagan      manager    mkt        hyd

       222          sudha     clerk           fin          bang

       444          madhu    engineer

RIGHT OUTER JOIN

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d

              on(e.deptno=d.deptno);

Or

      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =

             d.deptno;

     EMPNO    ENAME     JOB      DNAME      LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst      mkt        hyd

       333          jagan       manager   mkt        hyd

       222          sudha      clerk          fin          bang

                                                       hr           bombay

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d

              on(e.deptno=d.deptno);

 EMPNO   ENAME    JOB        DNAME      LOC

---------- ---------- ---------- ---------- ----------

       333     jagan     manager    mkt        hyd

       111     saketh   analyst       mkt        hyd

       222     sudha    clerk           fin        bang

       444     madhu   engineer     hr         bombay

INNER JOIN

This will display all the records that have matched.

Ex:

     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst      mkt       hyd

       333          jagan       manager   mkt       hyd

       222          sudha      clerk          fin         bang

What is the order of execution?

select, from, join, where, group by, having, order by

When a query is submitted to the database, it is executed in the following order:

1.FROM clause
2.WHERE clause
3.GROUP BY clause
4.HAVING clause
5.SELECT clause
6.ORDER BY clause

So why is it important to understand this?
When a query is executed,
First all the tables and their join conditions are executed filtering out invalid references between them.
Then the WHERE clause is applied which again filters the records based on the condition given.
Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result.
As soon as it is completed, the columns mentioned are selected from the corresponding tables.
And finally sorted using ORDER BY clause.
So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.