How to Create Table in Oracle...

-- creating table with constraints but without names to the constraint

create table t1

(empno number primary key,

ename varchar2(10) not null,

invno char(5) unique,

sal number check (sal >= 5000))

-- Displaying the inbuilt names of constraints

desc user_constraints

select constraint_name,constraint_type,table_name from user_constraints

where table_name='T1';

-- creating table with names to constraint

create table t2

(empno number constraint pk primary key,

 ename varchar2(10) not null,

 invno number constraint uinvno unique,

 sal number constraint chsal check(sal >= 5000));

-- Displaying the user defined names of constraints

select constraint_name,constraint_type,table_name from user_constraints

where table_name='T2';

--Table level constraints

create table t3

(empno number,

 invno varchar2(10),

 ename varchar2(10) not null,

 issuedate date,

 returndate date,

 constraint compk primary key (empno,invno),

 constraint un unique (ename),

 constraint chdate check (returndate >= issuedate));

 

-- Foreign key

create table X

(empno number constraint fk references t1(empno) on delete cascade,

 marks number);

How to work with Date Functions??

How to add days in current date?

 select sysdate + 3 from dual;

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

 select '07-dec-04' + 3 from dual; -- Gives error

select to_date('07-dec-04')  + 3 from dual;

select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value

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

date + number/24 --> Adds a number of hours to a date

Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.

select sysdate + 4/24 from dual;

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

How to do Months_between(d1,d2)

If d1 > d2 then +ve else -ve

Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;

select months_between(sysdate,hiredate) from emp

select round(months_between(sysdate,hiredate),0) As "Months Between" from emp

 Assignment - Find the years between using months_between

select months_between('13-dec-04','24-jul-04') from dual

select round(months_between('13-dec-04','24-jul-04'),0) from dual

Add_months(date,n) [n cand be +ve or -ve]

select add_months('4-dec-04',3) from dual; Gives 04-Mar-05

select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04

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

Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.

select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04

OR

Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2  ...Saturday as 7

select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04

 Note: The number cannot be negative.

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

Last_Day(date) - Returns the last date of the month.

select last_day(sysdate) from dual;

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

Round(Date,fmt)

If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.

E.g select round(to_date('04-dec-04'),'Month') from dual  ----> 01-DEC-04

select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04

select round(to_date('16-dec-04'),'Month') from dual; ---->  01-JAN-05

select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04

select round(to_date('30-Jun-04'), 'Year') from dual;  --> 01-JAN-04

 select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05

Trunc

select trunc(to_date('23-dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('07-Dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('23-Dec-04'),'Year') from dual;    Gives 01-Jan-04

select trunc(to_date('02-Feb-04'),'Year') from dual;     Gives 01-Jan-04

To_Char

To see all the records of employees joined after 1982

select * from emp

where to_char(hiredate,'yy') >= 82;

Or  where to_char(hiredate,'yyyy’) >= 1982

To see all the records of employees who have joined from October of any month.

select * from emp

where to_char(hiredate,'mm') >= 10; 

(Note à Only number 1 to 12 can be used for comparison)

To see all the records of employees who have joined from 21^st date.

select * from emp

where to_char(hiredate,'dd') >= 21;

To see all the records of employees who have joined from April-81

select * from emp

where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81

To see all the records of employees who have joined after 28-Sep-81

select * from emp

where hiredate >  '28-Sep-81'

 

DATE FORMAT MODEL

YEARS

select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;

MONTHS

select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')

 || '-' || TO_CHAR(hiredate,'MON') FROM EMP;

DAYS

select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')

 || '-' || TO_CHAR(hiredate,'DY') FROM EMP;

ADVANCED FORMATS -

CENTURY

SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;

select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"

FROM EMP;

SYEAR  The spelled out year.

select to_char(HIREDATE,'SYEAR') "YEAR"

FROM EMP

Q  To get the quarter of the year (1,2,3 and 4)

select to_char(HIREDATE,'Q') "Quarter"

FROM EMP;

 RM   Roman numeral month

select to_char(HIREDATE,'RM')"MONTH"

FROM EMP;

J Julian day - The number of days since 31Dec 4713 B.C.

select to_char(HIREDATE,'J')"JulianDay"

FROM EMP;

TIME

select to_char(sysdate,'hh:mi:ss') FROM DUAL;

To show AM or PM

select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;

SSSS Seconds past midnight (0-86399)  [ (60secs * 60 min * 24) - 1]

select to_char(sysdate,'ssss') from dual;

Suffixes -

select to_char(sysdate,'ddth') from dual;

select to_char(sysdate,'ddsp') from dual;

select to_char(sysdate,'ddspth') from dual;

 RR DATE FORMAT -

create table rryy

(name varchar2(10),

 dob date);

NAME	DOB
A	24-SEP-02
B	12-DEC-67
C	14-JAN-59
D	05-APR-04

  A AND D ARE IN 2000 CENTURY.

  B AND C ARE IN 1900 CENTURY.

To see the records who have dob of 19^th century.

Suppose yy format is used -

SELECT * FROM RRYY

WHERE TO_CHAR(DOB,'YY') < 99.

It assumes all the years are in the 20^th  century (2099). So all 4 records will come.

So to show only ,B and C records  --

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90',  'DD-MON-RR');

Here 90 it understands as 1990.

If  YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');

Here 90 it understands as 2090. So again all 4 records will come.

Similarly ---

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-RR');

Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.

A and D records will come.

If YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-YY');

Due to YY it takes 90 as 2090. It will not find any record above 2090.

How to use Case and DECODE function???

How to use Case and DECODE function???

CASE

--Case is used instead of Decode function

--Case is new in 9i

--Case is simple to read and maintain than in Decode

E.g 1) select job,

           case job

                    when 'ANALYST' then 4000

                    when 'MANAGER' then 5000

                    when 'CLERK' then   3000

                    else

                        2000

            end "Raise"

from emp;

E.g 2) select  sal,

                 case sal

                       when 1600 then 'A'

                      when 2850 then 'B'

                else

                     ‘C’

      end "Grade"

from emp;

E.g 3)

 Drop table codes;

create table codes

(traincode varchar2(15));

insert into codes

values('M-D');

insert into codes

values('P-M');

insert into codes

values('P-D');

insert into codes

values('M-A');

insert into codes

values('C-B');

--Here the code values are ellaborated with full forms using case

select traincode,

          CASE traincode   

           when 'M-D' then 'Mumbai to Delhi'

           when 'P-M' then 'Pune to Mumbai'

           when 'P-D' then 'Pune to Delhi'

           when 'M-A' then 'Mumbai to Ahemdabad'

           else

                   'South trains'

            end    

      As "Description"

from codes;

Case in Comparison –

select sal,

case when sal >= 3000 then sal + 10000

     when sal <= 1000 then sal - 500

else

sal

end

from emp;

 

 

DECODE

select job,

            DECODE ( JOB, 'ANALYST','A','MANAGER','M','CLERK','C','ZZ') “CODE”

from emp;

Oracle SQL Subquery

Pairwise Comparison Subquery

select * from emp

where  (job, deptno)  =  (select job, deptno

                                          from emp

                                          where ename = 'SMITH');

select * from emp

where (job,mgr) in (select job, mgr

                                from emp

                                 where ename =  'ALLEN')

Non-Pairwise Comparison Subquery

select * from emp

where job = (select job

                       from emp

                       where ename =  'ALLEN')

and mgr = (select mgr

                    from emp

                     where ename =  'ALLEN')

 

 

 

 

 

 

 

 

 

 

Sub-Query in From Clause

To see names, salaries,deptno and average sal of those employees who earn more than the average salary in their departments.

SELECT a.ename, a.sal, a.deptno, b.salavg

FROM emp a, (SELECT deptno,

                           AVG(sal) salavg

                           FROM emp

                           GROUP BY deptno) b

WHERE a.deptno = b.deptno

AND a.sal > b.salavg;

Scalar Sub Queries

Sub Query in Case Statement

select empno,ename,sal,job,

(case when deptno = (select deptno from dept

                                     where DNAME = 'ACCOUNTING')

     then 'Acc-Dept'

     else 'Non Acc'

 end)  Dept_Details

from emp

Correlated Sub Query

To see employees who earn a salary less than the average salary of their own job

Parent query takes each row and submits it to child query.

Child query gets executed for each row

select ename,job,sal

 from emp E

 where sal < (Select avg(sal)

                        from emp

                         where job = E.job);

To see the first two salary values

select distinct sal

from emp E

where 2 >= (select count(*)

                      from emp

                       where  sal > E.sal)

order by sal desc

To see the fourth highest salary

select distinct sal

from emp E

where 4 =  (select count(distinct sal)

                      from emp

                       where  sal > E.sal)

Co related sub query involving two tables—

Drop table LFC;

create table LFC

(empno number,

 No_Of_Leaves number,

 from_date date,

 till_date date);

Insert into LFC

values(7369, 10, ‘8-aug-03’, ‘18-aug-04’);

Insert into LFC

values(7839, 20, ‘11-dec-04’, ‘31-Dec-04’);

Insert into LFC

values(7369, 7, ‘21-apr-05’, ‘26-apr-05’);

Insert into LFC

values(7902, 8, ‘5-may-04’, ‘10-may-04’);

Select * from LFC;

To see the employee names that have taken LFC benefit at least twice.

select e.empno, e.ename, e.job

from emp e

where 2 <= (select count(*)

                      from LFC

                       where empno = e.empno)

 

 

 

 

 

 

 

 

 

 

Correlated Update

Denormalize the emp table by adding dname column.

Then populate the dname column as per the dept table values for matching deptno.

Alter table emp add deptno varchar2(14)

Update emp e

Set deptno = (select dname

                       From dept d

                       Where e.deptno = d.deptno)

DRAWBACKS OF CORRELATED SUB QUEIRES

--To list the Sum of Salaries for departments comprising more than

--1/3 of the firm's annual salary.

select dname,sum(sal) as DEP_TOTAL

from emp,dept

where emp.deptno = dept.deptno

group by dname

having sum(sal)  >

                                (select sum(sal) * 1/3

                                  from emp,dept

                                  where emp.deptno  = dept.deptno);

-- Drawbacks of this mechanism are as follows --

-- 1) Each record of group from parent query is taken and compared with sub --query.

--2) In the sub query again the entire calculation is done.

--3)Due to this the performance overheads are increased.

ORACLE 9I  has a new concept of with clause in subqueries to solve this problem

--To list the Sum of Salaries for departments comprising more than

--1/3 of the firms's annual salary.

with summary as

(select dname,sum(sal) as  DTOTAL

from emp,dept

where emp.deptno = dept.deptno

 group by dname)

select dname, DTOTAL

from summary

where DTOTAL   >

                                    (select sum(DTOTAL) * 1/3

                                       from summary);

 -- Advantages of using with clause and  subquery                                

--1) From the parent query the summarized and grouped data is taken into

-- an temporary table called summary.

--2) Now the data from the summary table is compared with the sub query .

-- In the sub query also the same data from summary table is taken for --calculations.

--  So the child query gets executed only once and then it references to the parent query.

   -- Explanation pertaining to current example

-- The sum(sal) for each deptno is taken in the summary table

--Then sub query gets executed only once to see what is the value one third of the sum of all the

-- total salaries

-- So for the each record from summary table the sub query was executed only once!!!

-- It means that there are less performance overheads
 

Exists and Not Exists

Drop  table s;

Create table s(roll number, name varchar2(40));

Insert into s values(1,'A');

Insert into s values(2,'B');

Insert into s values(3,'C');

Insert into s values(4,'D');

Drop table r;

create table r(roll number,marks number);

 Insert into r values(2,90);

 Insert into r values(3,98);

To see records from s table for the students who have given exam –

select * from s

where  Exists

                       (select *

                        from r

                        where s.roll = r.roll);

To see records from s table for the students who not  have given exam –

select * from s

where Not Exists

                              (select *

                                from r

                                where s.roll = r.roll);

Tips of Oracle SQL Performance Tuning 02

Tips of Oracle SQL Performance Tuning

These are the tips that needs to be followed to performance tune your SQL scripts;

·    WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
·    WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
·    In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
·    Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
·    Avoid writing where project_category is not null. Nulls can prevent the optimizer from using an index.
·    Consider using IN or UNION in place of OR on indexed columns. OR’s on indexed columns causes the optimizer to perform a full table scan.
·    Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt/3 > 26000.
·    Avoid this: SUBSTR(haou.attribute1,1,LENGTH(':p_otc')) = :p_otc). Consider this: WHERE  haou.attribute1 like :p_otc||'%'
·    Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don't assume that an index was defined. Check and talk to your DBA if you don't find any.
·    Consider replacing outer joins on indexed columns with UNION. A nested loop outer takes more time than a nested loop un-joined with another table access by index.
·    Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
·    Consider NOT EXISTS instead of NOT IN.
·    If a query is going to read most of the records in a table (more than 60%), use a full table scan.
·    Try to group multiple sub queries into one.
·    If you want to actually understand what you are doing, here are a few things that you need to start playing with:
·    Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user friendly is to simply issue this in SQL*Plus: explain plan set statement_id = 'HDD1' for ;
·    Look at the trace from Oracle Reports. It tells you how much time it spends on each query.
·    Use the SQL Trace by issuing an alter session set sql_trace=true; then look at it with TKPROF .trc
·    Do not use functions on indexed columns in WHERE clauses Unless specifically using a function based index.

·    Beware of implicit datatype conversions occuring on indexed columns (e.g. comparing a character column with a number expression will invoke an implicit
TO_NUMBER operation on the indexed column, preventing use of the index - in this case it would be better to us an explicit TO_CHAR operation around thenumber expression being compared against

·    Make sure that you have the correct indexes on the tables:-
·    Ensure that the first column of the index is selective ( e.g. more than around 15 distinct values with a near uniform distribution
·    Avoid using insufficiently selective indexes (i.e. each index value applies to at least 15% of the rows).  In this case, a full table scan will be more efficient.
·    Aim to have no more than 4 indexes per table. ( each insert into the table has to also modify data in the indexes )

·    To use compound indexes ensure that the WHERE clause includes conditions on  the leading column(s) of the index

·    NEVER use SELECT * FROM. in application code

·    Try to avoid use of the DISTINCT clause as it always requires a sort operation.  Excessive use of DISTINCT clause may point to an underlying data model problem (e.g a missing table).