76. Examples - 9

PL/SQL programs in oracle

Write a PL/SQL block to find out the square of a number.

sql>ed  //to open a file

Q. Set Serveroutput on

   Declare

      x number := &x;

      s number;

   Begin

      s := x*x;

      Dbms_output.put_line('Square of'||x||'='||s);

   end;

   /

sql>@  //to run the PL/SQL block

Finding areas using PL/SQL blocks

Write a PL/SQL block to compute the area of the circle

and square.

Q. Set Serveroutput on

   Declare

       r number(5,2) := &r;

       s number(5,2) := &s;

       ca number(5,2);

       sa number(5,2);

   Begin

       ca := (3.14)*r*r;

       sa := s * s;

       Dbms_Output.put_line('area of circle='||ca);

       Dbms_Output.put_line('area of square='||sa);

   End;

   / 

PL/SQL program for temparature conversion

Write a PL/SQL block to convert celsius to fahrenheat and 

viceversa.

Q. set serveroutput on

declare

  ct number(5,2) := &ct;

  ftc number(5,2);

  ft number(5,2) := &ft;

  ctc number(5,2);

begin

  ftc := (ct*9/5)+32;

  dbms_output.put_line('Faran heat temparature='||ftc);

  ctc := (ft-32)*5/9;

  dbms_output.put_line('Celsius temparature='||ctc);

end;

/

PL/SQL program to find big among 3 nos

Write a PL/SQL block to find big among 3 nos.

Q. set serveroutput on

declare

   a number := &a;

   b number := &b;

   c number := &c;

begin

   if(a>b and a>c)

   then

        dbms_output.put_line('big number='||a);

   elsif(b>c)

   then

          dbms_output.put_line('big number='||b);

   else

         dbms_output.put_line('big number='||c);

   end if;

 end;

/

PL/SQL program to find factorial of a number

Write a PL/SQL block to find factorial of a number.

set serveroutput on

declare

   n number := &n;

   fact number :=1;

   i number :=1;

begin

   for i in 1..n

   loop

     fact := fact*i;

   end loop;

   dbms_output.put_line('factorial='||fact);

end;

/ 

PL/SQL program to find leap year or not 

Write a PL/SQL block to find leap year or not.

set serveroutput on

declare

    year number:= &year;

begin

    if(mod(year,4) = 0)

    then

      dbms_output.put_line('Leap year');

    else

      dbms_output.put_line('Not leap year');

    end if;

end;

/

PL/SQL block to display first 20 odd numbers 

To display first 20 odd numbers.

set serveroutput on

declare

   ctr number(2) :=1;

   i number :=1;

begin

   while ctr <=20 loop

    if(mod(i,2)!=0)

     then

      ctr := ctr+1;

      dbms_output.put_line(i);

    end if;

    i := i+1;

   end loop;

end;

/ 

PL/SQL block to display first 10 numbers divisible by 3 and 5

To display first 10 numbers divisible by 3 and 5.

set serveroutput on

declare

   ctr number(2) :=1;

   i number :=1;

begin

   while ctr <=10 loop

    if(mod(i,3)=0 and mod(i,5)=0)

     then

      ctr := ctr+1;

      dbms_output.put_line(i);

    end if;

    i := i+1;

   end loop;

end;

/

PL/SQL block to display student result

To display student result

set serveroutput on

declare

sub1 number := &sub1;

sub2 number := &sub2;

sub3 number := &sub3;

total number;

avge number(5,2);

begin

total := sub1+sub2+sub3;

avge := total/3;

dbms_output.put_line('total='||total);

dbms_output.put_line('average='||avge);

if(avge<50 o:p="">

then

   dbms_output.put_line('Fail');

elsif(avge>=50 or avge<60 o:p="">

then

   dbms_output.put_line('Second division');

elsif(avge>=60 or avge<75 o:p="">

then

   dbms_output.put_line('First division');

elsif(avge>=75)

then

   dbms_output.put_line('Distinction');

end if;

end;

/

Procedures and functions in oracle

Function for cube of number in oracle 

Create a function for cube of any number.

create or replace function fncube(n number)

return number

is

begin

  return n*n*n;

end;

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Function to find simple interest in oracle 

Create a function to find simple interest.

create or replace function simple(p number,n number,r number)

return number

is

begin

  return(p*n*r/100);

end;

/

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Finding factorial using recurssion in oracle

Finding factorial using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

Fibnacci series using recurssion in oracle 

Fibnacci series using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

Fibnacci series using recurssion in oracle 

Fibnacci series using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

Procedures and functions in oracle

Function for cube of number in oracle 

Create a function for cube of any number.

create or replace function fncube(n number)

return number

is

begin

  return n*n*n;

end;

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Function to find simple interest in oracle 

Create a function to find simple interest.

create or replace function simple(p number,n number,r number)

return number

is

begin

  return(p*n*r/100);

end;

/

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Finding factorial using recurssion in oracle

Finding factorial using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

75. Examples - 8

PL/SQL programs in oracle

PL/SQL blocks:

·         PL/SQL block to find out the square of a number

·         PL/SQL block to compute the area of the circle and square

·         PL/SQL block to conversion of celsius to faranheat

·         and viceversa

·         PL/SQL block to find largest from the three nos

·         PL/SQL block to find factorial value of any no

·         PL/SQL block to find whether the entered year is

·         leap year or not

·         PL/SQL block to display first 20 odd nos

·         PL/SQL block to display first 10 nos divisible by 3 and 5

·         PL/SQL block for displaying student grade

Alter Command

1. Add a column "Telephone_no" of data type 'number' and size ='10' to the 

   employee table.

Q. alter table employee add(telephone_no number(10));

2. Add a column "country" of datatype 'char' and size='15' to the

   client_mast table.

Q. alter table client_mast add (country char(15));

3. Increase the size of "description" 20 to 25 in product_mast table.

Q. alter table product_mast modify (description varchar2(25));

4. Modify the "product_no" key as a primary key from product_mast table.

Q. alter table product_mast add primary key(product_no);

5. Drop the primary key of client_mast table.

Q. alter table client_mast drop primary key;

6. Define the "deptno" as not null constraint from dept table.

Q. alter table depttable modify deptno not null;

7. Display empno, ename, and annual salary of each employee from employee table

   with a column name "Annual Salary" on the basis of basic_sal.

Q.  select empno as "Employee Number", ename as "Employee 

    Name", basic_sal*12 as "Annual salary"  from employee;

8. Add two columns"HOD" and "Strength" of datatype varchar2 and size=15 to the

   dept table.

Q. alter table depttable add (hod varchar2(15),strength varchar2(15));

9. Add a check constraint for deptno field to the dept table check deptno from 

   the 'D001', 'D002' and 'D003'. 

Q.

Numeric And Character Functions 

Oracle Queries on Functions

1. Select those clients whose bal_due is greater than value

   2000 from client_mast table.

Q. select name,bal_due from client_mast where bal_due>2000;

2. Calculate the average price of all the products from

   Product_mast table.

Q. select avg(cost_price) from product_mast;

3. calculate the maximum and minimum product prices and

   rename the output as max_price and min_price respectively

   from product_mast table.

Q. select max(cost_price) as "max_price",

   min(cost_price) as "min_price" from product_mast ;

4. Count the number of products having price greater than

   or equal to 1200.

Q. select count(*) from product_mast where cost_price>=1200;

5. Display employee number and day on which employee hired

   from employee table.

Q. select empno,to_char(to_date(hiredate),'DAY') as Day 

   from employee;

6. Display the total no.of working days of each employee

   from employee table.

Q. select ename as "Name",months_between(sysdate,hiredate) as

   "No.of days" from employee;

7. Display the date, 20 days after today's date.

Q. select sysdate+20 from dual;

8. Count all distinct job types from employee table. 

Q. select count(distinct job) from employee;

9. Using LPAD function pad the job types with 15 characters

   by '*' symbol

Q. select lpad(job,15,'*') job from employee;

10. Display the length of each employee name from employee 

    table. 

Q.  select length(ename) from employee;

11. Display the value of 3 raised to 4 using power function

Q.  select power(4,3) from dual;

12. Display the square root of 81

Q.  select sqrt(81) from dual;

13. Display the number of bytes used by ename from employee

    table.

Q.  select vsize(ename) from employee;

14. Display the ename those are having sound like Kim from

    employee table.

Q.  select soundex('Kim') from employee;

views in oracle

1. Create a view as empview from employee table where

   basic_sal=3000 and job='Clerk'

Q. create view empview as select ename from employee where 

   basic_sal=3000 and job='Clerk     ';

2. Display the result using above view

Q. select * from empview;

3. Drop the above created view

Q. drop view empview;

4. Create a view as empview from employee table with

   basic_sal,empno,ename.

Q. create view empview as select basic_sal,empno,ename

   from employee;

5. Display the result using the above create view

Q. select * from empview;

6. create a view as clientview from client_mast with all

   columns of table

Q. create view clientview as select * from client_mast;

7. Drop the above create empview

Q. drop view empview;

74. Examples - 7

views in oracle

1. Create a view as empview from employee table where

   basic_sal=3000 and job='Clerk'

Q. create view empview as select ename from employee where 

   basic_sal=3000 and job='Clerk     ';

2. Display the result using above view

Q. select * from empview;

3. Drop the above created view

Q. drop view empview;

4. Create a view as empview from employee table with

   basic_sal,empno,ename.

Q. create view empview as select basic_sal,empno,ename

   from employee;

5. Display the result using the above create view

Q. select * from empview;

6. create a view as clientview from client_mast with all

   columns of table

Q. create view clientview as select * from client_mast;

7. Drop the above create empview

Q. drop view empview;

73. Examples - 6

Numeric And Date Functions

Oracle Queries on Functions

1. select the average salaries of each department where deptno

   is either 'D001' or 'D002' from employee table.

Q.  select avg(basic_sal) from employee where dept_no

                                  in('D001','D002');

2. select the name of the lowest paid employee from

   the employee table.

Q. select ename from employee where basic_sal=

   (select min(basic_sal) from employee);

3. Select the highest paid employee int the employee table.

Q. select ename from employee where basic_sal=

   (select max(basic_sal) from employee);

4. Display the total no.of rows in the employee table.

Q. select count(*) from employee;

5. Display only those jobs where max sal>=3000 in the

   employee table.

Q. select job from employee where basic_sal>=

   (select max(basic_sal) from employee);

6. List the names and hiredates of the employees in

   deptno='D002'display the dateformat as 12/03/03'.

Q. select ename,to_char(hiredate,'dd/mm/yy') from employee

   where dept_no='D002';

7. List the name of the employees whose hiredate is in 

   the month of the December using substr function

Q. select ename from employee where substr(hiredate,4,3)

                                      ='DEC';

8. Count the number of people in departnumber 'D003' 

Q. select count(*) from employee where dept_no='D003';

9. Produce the following output using employee table 

              Employee

              Kim(Manager)

              Bruce(Analyst)   

Q. select ename ||'(' ||job||')' as "Employee" from employee

   where ename='Kim' or ename='Bruce';

10. Find out the difference between highest and lowest

    salaries

Q.  select max(basic_sal)-min(basic_sal) from employee;

11. How many months has the manager worked for the company

    (Round value)

Q.  select count( round(to_date(hiredate),'Month')) 

        from employee where job='Manager';

12. Display the top three earners with name and salary in

    the company

Q.

13. Display the sin and sinh value of 45

Q.  select sin(45),sinh(45) from dual;

14. Display the ascii value of 'ORACLE'

Q.  select ascii('ORACLE') from dual;

15. Display all job types in lower case

Q.  select lower(job) from employee;

16. Select those clients whose bal_due is greater than value

   2000 from client_mast table.

Q. select name,bal_due from client_mast where bal_due>2000;

17. Calculate the average price of all the products from

   Product_mast table.

Q. select avg(cost_price) from product_mast;

18. calculate the maximum and minimum product prices and

   rename the output as max_price and min_price respectively

   from product_mast table.

Q. select max(cost_price) as "max_price",

   min(cost_price) as "min_price" from product_mast ;

19. Count the number of products having price greater than

   or equal to 1200.

Q. select count(*) from product_mast where cost_price>=1200;

20. Display employee number and day on which employee hired

   from employee table.

Q. select empno,to_char(to_date(hiredate),'DAY') as Day 

   from employee;

21. Display the total no.of working days of each employee

   from employee table.

Q. select ename as "Name",months_between(sysdate,hiredate) as

   "No.of days" from employee;

22. Display the date, 20 days after today's date.

Q. select sysdate+20 from dual;

23. Count all distinct job types from employee table. 

Q. select count(distinct job) from employee;

24. Using LPAD function pad the job types with 15 characters

   by '*' symbol

Q. select lpad(job,15,'*') job from employee;

25. Display the length of each employee name from employee 

    table. 

Q.  select length(ename) from employee;

26. Display the value of 3 raised to 4 using power function

Q.  select power(4,3) from dual;

27. Display the square root of 81

Q.  select sqrt(81) from dual;

28. Display the number of bytes used by ename from employee

    table.

Q.  select vsize(ename) from employee;

29. Display the ename those are having sound like Kim from

    employee table.

Q.  select soundex('Kim') from employee;

72. Examples - 5

Date Functions

• add_months
• last_day
• months_between
• new_time
• next_day
• round
• sysdate

sysdate And add_months Functions

sysdate:

1. Display the system date

Q. select sysdate from dual;

2. Display 10 days after the system date

Q. select sysdate+10 from dual;

add_months:

1. Display 3 months after from the system date

Q. select add_months(sysdate,3) from dual;

2. Display 12 before from the date '12-mar-1990'

Q. select add_months('12-mar-1990',-12) from dual;

3. Using the fractional number

Q. select add_months('12-mar-1990',1.5) from dual;

  [add_months() converts the fractional number as whole number]

   select add_months('12-mar-1990',1)  from dual;

  [The above two queries are same]

Some Examples:

Q1. select add_months('28-feb-1989',1.9999) from dual;

                        (or)

    select add_months('28-feb-1989',1) from dual;

Q2. select add_months('28-feb-1989',-12.9999) from dual;

                        (or)

    select add_months('28-feb-1989',-12) from dual;

Q3. select add_months('28-feb-1989',0.5) from dual;

                        (or)

    select add_months('28-feb-1989',0) from dual;

last_day Function:

last_day:

1. Display the last day of the system date

Q. select last_day(sysdate) from dual;

2. Display the last day of 31st May 2010

Q. select last_day('31-may-10') from dual;

3. Display last day of the month 3 months after from the system date

Q. select last_day(add_months(sysdate,3)) from dual;

4. select the last day of '16-feb-2010'

Q. select last_day('16-feb-10') from dual;

months_between Function

1. Display months between and system date and '28-jan-2010'

Q. select months_between(sysdate,'28-jan-2010') from dual;

2. Calculate months when two dates fall on the same month.

Q. select months_between('16-feb-2010','11-feb-2010') from dual;

3. Calculate months between '31-mar-1995' and '28-feb-1994'

Q. select months_between('31-mar-1995','28-feb-1994') from dual;

4. Calculate months between '31-mar-1994' and '1-mar-1994'

Q. select months_between('31-mar-1994','1-mar-1994') from dual;

new_time Function

The following example shows the time how it is different from one zone to 

another

Q. select to_char(new_time(to_date('09151994 12:30 AM','MMDDYYYY HH:MI AM'),

   'CST','hdt'),'Month DD,YYYY HH:MI AM') from dual;

next_day Function

1. Display the first monday in the year 2010

Q. select next_day('01-jan-2010','monday') from dual;

2. Display first thrusday in the year 2011

Q. select next_day('01-jan-2011','thursday') from dual;

3. Display next friday of the system date

Q. select next_day(sysdate,'friday') from dual;

to_date and to_char with round function

round():

1. Write a query to round back to current century

Q. select to_char(round(to_date('22-may-2010'),'cc'),'DD-mon-yyyy') from 

   dual;

2. Write a query to round up to next century

Q. select to_char(round(to_date('22-sep-2094'),'cc'),'DD-mon-yyyy') from 

   dual;

3. Write a query to round down to the first of the year

Q. select round(to_date('22-may-2010'),'yyyy') from dual;

4. Write a query to round up to the first of the year

Q. select round(to_date('22-sep-2010'),'year') from dual;

5. Write a query to round up to the quarter(first date in the quarter)

Q. select round(to_date('12-mar-1994'),'Q') from dual;

6. Write a query to round to the quarter

Q. select round(to_date('12-aug-2010'),'Q') from dual;

   select round(to_date('12-dec-2010'),'Q') from dual;

7. Write a query to print the day of the particular date

Q. select to_char(to_date('22-may-2010'),'day') from dual;

   select to_char(to_date(sysdate,'day') from dual;

8. Write a query round to date of nearest sunday for '22-may-2010'

Q. select round(to_date('22-may-2010'),'ww') from dual;

9. write a query round back to nearest day(time always midnight).

Q. select to_char(round(to_date('11-sep-2010 10:00 AM','DD-Mon-yy HH:MI AM'),

   'DD'),'DD-Mon-yy HH:MI AM') from dual;

10. write a query to round forward to the nearest day

Q.  select to_char(round(to_date('11-sep-2010 4:00 PM','DD-Mon-yy HH:MI AM'),

    'DD'),'DD-Mon-yy HH:MI AM') from dual;

11. Write a query to round back to the nearest hour

Q.   select to_char(round(to_date('11-sep-2010 4:17PM','DD-Mon-yy HH:MI AM'),

     'HH'),'DD-Mon-yy HH:MI AM') from dual;

sysdate And add_months Functions

sysdate:

1. Display the system date

Q. select sysdate from dual;

2. Display 10 days after the system date

Q. select sysdate+10 from dual;

add_months:

1. Display 3 months after from the system date

Q. select add_months(sysdate,3) from dual;

2. Display 12 before from the date '12-mar-1990'

Q. select add_months('12-mar-1990',-12) from dual;

3. Using the fractional number

Q. select add_months('12-mar-1990',1.5) from dual;

  [add_months() converts the fractional number as whole number]

   select add_months('12-mar-1990',1)  from dual;

  [The above two queries are same]

Some Examples:

Q1. select add_months('28-feb-1989',1.9999) from dual;

                        (or)

    select add_months('28-feb-1989',1) from dual;

Q2. select add_months('28-feb-1989',-12.9999) from dual;

                        (or)

    select add_months('28-feb-1989',-12) from dual;

Q3. select add_months('28-feb-1989',0.5) from dual;

                        (or)

    select add_months('28-feb-1989',0) from dual;

71. Examples - 4

Alter Command

1. Add a column "Telephone_no" of data type 'number' and size ='10' to the 

   employee table.

Q. alter table employee add(telephone_no number(10));

2. Add a column "country" of datatype 'char' and size='15' to the

   client_mast table.

Q. alter table client_mast add (country char(15));

3. Increase the size of "description" 20 to 25 in product_mast table.

Q. alter table product_mast modify (description varchar2(25));

4. Modify the "product_no" key as a primary key from product_mast table.

Q. alter table product_mast add primary key(product_no);

5. Drop the primary key of client_mast table.

Q. alter table client_mast drop primary key;

6. Define the "deptno" as not null constraint from dept table.

Q. alter table depttable modify deptno not null;

7. Display empno, ename, and annual salary of each employee from employee table

   with a column name "Annual Salary" on the basis of basic_sal.

Q.  select empno as "Employee Number", ename as "Employee 

    Name", basic_sal*12 as "Annual salary"  from employee;

8. Add two columns"HOD" and "Strength" of datatype varchar2 and size=15 to the

   dept table.

Q. alter table depttable add (hod varchar2(15),strength varchar2(15));

9. Add a check constraint for deptno field to the dept table check deptno from 

   the 'D001', 'D002' and 'D003'. 

Q.

70. Examples - 3

Delete, Commit, Rollback and Renaming A Table

1. Delete all salesman from the salesman_mast whose salaries are equal to

   Rs.2000.

Q. delete from salesman_mast where sal_amount=2000;

2. Write a query to undo the above delete query.

Q. rollback;

3. Delete all products from product_mast where the Qty_Available is equal to

   100.

Q. delete from product_mast where qty_available=100 ;

4. Write a query such that the above query cannot be undone.

Q. commit;

5. Delete from client_mast where the state venue is "Illinois".

Q. delete from client_mast where state='Illinois';

6. Delete all employees from employee where basic_sal is less than 2000.

Q. delete from employee where basic_sal<2000 br="">

7. Write a query to mark the above two queries such that the first delete

   operation is undo

Q. savepoint d1;

   delete from client_mast where state='Illinois';

   rollback to d1;

8. Delete employee from the employee table where basic_sal is equal to

   Rs.3000 and job='clerk'.

Q. delete from employee where basic_sal=3000 and job='Clerk';

9. Delete employee from the employee table where job='clerk' or manager.

Q. delete from employee where job='Clerk' or job='Manager';

10. Delete all records from depttable table.

Q.  delete from depttable;

11. Delete all managers and salesman with salary over 1500 from employee

    table.

Q.  delete from employee where job='Manager' or

                          job='salesman' and basic_sal>1500;

12. Delete all employee name, job and department number of everyone whose

    name fall in the alphabetical range 'C' to 'L' from employee table.

Q.

13. Write a query so the the following statement can be delivered 'Kim is

    working as manager since 15-12-2002'  where employee number is E0001.

Q.  select ename || ' is working as ' || job ||'since'

    || hiredate from employee where ename='Kim';

14. Rename the table name from client_mast to T_client_mast.

Q.  rename client_mast to T_client_mast;

15. Write a query the column name 'name' should be displayed as employee name

    from the employee table.

Q.  select ename as "Name" from employee;