Other Master Degree-Master of Computer Management (M.C.M) 2nd Sem ORACLE(University of Pune, Pune-2013)

                                             [4377]-203
               M. C. M. (Semester - II) Examination - 2013
                                                ORACLE
                                    (New 2008 Pattern)

Time : 3 Hours]                                                                [Max. Marks : 70

Instructions :

(1) Question Nos. 1 and 6 are compulsory.

(2) Solve any three from the remaining.

(3) Figures to the right indicate full marks.

(4) State your assumptions clearly.

 

Q.1) (A) Create the tables mentioned below with required constraints and insert atleast 2 records. [06]

Book_master (Book_no, Book_title, Book_price, Book_author, Book_status)

Member_master (Mem_no, Mem_name, Mem_type, Book_no)

(B) Solve the following queries : (Any Seven) [14]

(1) Display number of books in the library.

(2) Display Mem_type wise no. of members.

(3) Display the book having highest price.

(4) List of books issued to the members.

(5) List of books not issued.

[4377]-203 1 P.T.O.

(6) List book–title without duplication.

(7) Display total books’ cost.

(8) List of books having book_status null.

(9) List of books having title first character ‘z’

(10) Display the book having largest title.

 

Q.2) Write a function display_title which accept Book_no and returns the book title. [10]

 

Q.3) Write a function No-of-books which accept Book_Author and returns the no. of books of that Author. [10]

 

Q.4) Write a cursor to display all books having price more than Rs. 1,000 in ascending order of price. [10]

 

Q.5) Write a trigger which will not allow transaction on book_master on 15th August and 26th January every year. [10]

 

Q.6) Write short notes : (Any Four) [20]

(a) SQL

(b) Aggregate Functions

(c) Joins

(d) Rollback

(e) String Functions

(f) Date Functions

[4377]-203/2

Earning:  ₹ 7.05/-