Unknown

















Oracle 11g -(Class 4)-Oracle 11g -SQL Statement



Oracle operator





Single comparison operators :

=

<

>

=>

=<

<>

**Single comparison operator where clause must end with single single comparison operator.

SQL> select ename, sal from emp

where sal = (select max(sal) from emp);





ENAME SAL Omar 30000





SQL> select ename, deptno, max(sal)from emp

group by ename, deptno

having max(sal) > (select sal from emp where ename = 'SMITH')

and deptno = (select deptno from emp where ename = 'SCOTT');







ENAME DEPTNO MAX(SAL) JONES 20 2975 FORD 20 3000 SCOTT 20 3000 ADAMS 20 1100

=' equal is single comparison operator 'IN' is multiple comparison operator

======******JOINS with multiple tables:******========

JOIN / Natural Join InnerJoin EquiJoin Left outer join / Right outer join / Full outer join

Today we will discuss about join, Natural join, Inner join & Equi join





Equijoin:-

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality. You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check

equality.

Actually there is no difference between EQUI JOIN and INNER, because INNER JOIN is a part of EQUI JOIN.



Equijoins(Primary & Foreign Key) must be with Parent and Child.

SQL> select * from emp;





EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-Dec-80 800

20 7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30 7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30 7566 JONES MANAGER 7839 02-Apr-81 2975

20 7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-May-81 2850

30 7782 CLARK MANAGER 7839 09-Jun-81 2450

10 7788 SCOTT ANALYST 7566 19-Apr-87 3000

20 7839 KING PRESIDENT

17-Nov-81 5000

10

SQL> select * from dept;





DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Let's use Prefix: For Table alias:



SQL> select e.ename, e.deptno, d.loc from emp e, dept d

where e.deptno = d.deptno;

ENAME DEPTNO LOCATION CLARK 10 NEW YORK MILLER 10 NEW YORK KING 10 NEW YORK JONES 20 DALLAS SMITH 20 DALLAS SCOTT 20 DALLAS FORD 20 DALLAS ADAMS 20 DALLAS WARD 30 CHICAGO TURNER 30 CHICAGO ALLEN 30 CHICAGO JAMES 30 CHICAGO

e.ename username ==This is using prefix and alias together.

SQL> select e.ename anthony, e.deptno sales, d.loc location from emp e, dept d

where e.deptno = d.deptno;

ANTHONY SALES LOCATION CLARK 10 NEW YORK MILLER 10 NEW YORK KING 10 NEW YORK JONES 20 DALLAS SMITH 20 DALLAS SCOTT 20 DALLAS FORD 20 DALLAS ADAMS 20 DALLAS WARD 30 CHICAGO TURNER 30 CHICAGO ALLEN 30 CHICAGO JAMES 30 CHICAGO Omar 30 CHICAGO MARTIN 30 CHICAGO





NATURAL JOIN == same as above

The join is based on all the columns in the two tables that have the same name and data types. The join creates, by using the NATURAL JOIN keywords. It selects rows from the two tables that have equal values in all matched columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

SQL> select ename, deptno, loc from emp

natural join dept;

ENAME DEPTNO LOC CLARK 10 NEW YORK MILLER 10 NEW YORK KING 10 NEW YORK JONES 20 DALLAS SMITH 20 DALLAS SCOTT 20 DALLAS FORD 20 DALLAS ADAMS 20 DALLAS WARD 30 CHICAGO TURNER 30 CHICAGO

****If one column is on both tables, oracle will figure out by Natural Join.



What if multiple columns to Join?



SQL> select ename, deptno, loc from emp join dept

using (deptno);





If more than one column and and they're related --ex. using (deptno)

ENAME DEPTNO LOC CLARK 10 NEW YORK MILLER 10 NEW YORK KING 10 NEW YORK JONES 20 DALLAS SMITH 20 DALLAS SCOTT 20 DALLAS FORD 20 DALLAS ADAMS 20 DALLAS WARD 30 CHICAGO TURNER 30 CHICAGO

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

if related(parent/child) without same name. or all situation

ENAME DEPTNO LOC CLARK 10 NEW YORK MILLER 10 NEW YORK KING 10 NEW YORK JONES 20 DALLAS SMITH 20 DALLAS SCOTT 20 DALLAS FORD 20 DALLAS ADAMS 20 DALLAS WARD 30 CHICAGO TURNER 30 CHICAGO

************Thank You*******************