University of Pune 2007 B.C.A Computer Application ORACLE - Question Paper

[3196]-405
B.C.A. (Semester-IV) Examination-2007
ORACLE
Time: three Hours] [Max. marks: 80


Q1) For the subsequent tables, write SOL commands: (Any ten) [20]

Table : COURSE (course_code, course_name, duration)
Table: learner (roll_no, name, age,admin_date, fees_paid, category, course_code)
COURSE TABLE: course_code is primary key.
learner TABLE: roll_no is primary key and course_code is foreign key.

(1) List the name of students having age more than 22.
(2) Display the names of students whose name begins with ‘P’ and 4 characters long.
(3) Display the month in which ‘Anand’ took the admission.
(4) Display the total number of students per course.
(5) Change the category of ‘Anuja’ from ‘OPEN’ to ‘OBC’.
(6) List out the students who had not paid the fees.
(7) Display the students who had joined either ‘MBA’ and ‘MCA’ courses.
(8) Display the courses available in the institute.
(9) Arrange the records of students in ascending order of name.
(10) Display total no. of students having category ‘OBC’
(11) Display the course name with maximum duration.
(12) Display the course details for which ‘Mangesh’ has taken admission.


Q2) Write short notes on: (Any Three) [15]

(a) Locks in Oracle
(b) Data kinds in Oracle
(c) Object Oriented Concepts: Inheritance, Polymorphism
(d) TCL (Commit, Rollback, Savepoint)

Q3) (A) Differentiate ranging from SOL and PL/SQL. [05]
(B) Create a report to display the departmentwise employee details. [05]

Q4) discuss the following: (Any Three) [15]

(a) Joins in oracle: Equi Join, Non-equi Join, Self Join, Outer Join
(b) Set Operators (Union, Intersect, Minus)
(c) Views (Create, Alter, drop)
(d) Sequence

Q5) Attempt the following: (Any Four) [20]

(a) Write a PL/SQL block to obtain maximum of 3 numbers.
(b) Write a trigger which will not allow to delete the record from emp table.
(c) Write a cursor block to display the 1st 5 least paid employees from emp table.
(d) Write a cursor and use it to raise the employee salary as follows:
(1) All employees of department no. 10, get 10% raise.
(2) All employees of department no. 20, get 20% raise.
(e) Write a procedure on EMP Table. It should increase commission of an employee. Employee number and commission are passed as parameters to the called procedure.

Earning:   Approval pending.