Max Data Code: 75. Examples

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

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

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;

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;

Pages