Oracle Points -- Part2

Q1) Inner join vs equi join

Equi join is a traditional Oracle join. We use = operator in it

select * from emp , dept where emp.deptno = dept.deptno

Inner join is a ANSI standard.

select * from emp inner join dept  using (deptno)

Q2) primary key vs foreign key vs unique key

	PK	FK	Unique
More than one in Table	No	Yes	Yes
Can have NULLs	No	Yes	Yes

Q3) where vs having clauses

Where clause doesn’t work with aggregate functions like SUM, MAX etc… but having clause can be used with aggregate functions

Where clause is a row filter and having clause can work as a additional filter when use in combination with where

Q4) Why Index? How Many Types of Indexes?

Most commonly used index is B-tree index. B-tree index stores column values.

Hash table indexes works efficiently in case of string comparison using equality operator. Hash indexes are not stored values in sorted order. We can’t use Hash indexes with other than = operator

Bitmap index can be used for small tables

Q5) self join vs inner join vs left outer join

Self join

select *

from emp_s e, emp_s m

where e.mgr = m.empno   à doesn’t display employees with their manager as null

inner join

select *

from emp_s e inner join emp_s m

on( e.mgr = m.empno)   à doesn’t display employees with their manager as null

outer join

select *

from emp_s e left join emp_s m

on (e.mgr = m.empno)   à display employees with their manager as null + not null

Q6) How to get distinct records

            Using DISTINCT

                        select distinct customer_no, count(distinct customer_no) over ()

from oeorder_header

            Using UNIQUE

                        select unique customer_no, count(unique customer_no) over ()

from oeorder_header

            Using Group By

                        select customer_no

from oeorder_header

group by customer_no