(C) D.B.M.S. WITH ORACLE (SPECIALIZATION - I),University Of Pune Question Paper,2010 Question Paper,P. G. D. B. M. (Semester - IV)

University Of Pune Question Paper
P. G. D. B. M. (Semester - IV) Examination - 2010
(C) D.B.M.S. WITH ORACLE
(SPECIALIZATION - I)
(New 2008 Pattern)
Time : 3 Hours] [Max. Marks : 70
Instructions :
(1) Q. Nos. 1 and 5 are compulsory.
(2) Solve any two questions from the remaining.
(3) Figures to the right indicate full marks.
Q.1) Consider the following table structure to write SQL queries : [20]
Patient Table :
pno number(3), pname varchar2(30), billamt number(4), disease
Date, discharge_date Date.
Doctor Table :
doctorno number(2), dname varchar2(30), specialization varchar2(30)
PatientDoctor Table :
pno number(3), doctorno number(2)
(1) List all patients from Pune City.
(2) List all patients having ‘R’ as third character in their names.
(3) Show alphabetical list of patients admitted on 20 August, 2009.
(4) Show all doctors working for cancer patients.
(5) Display names of doctors attending maximum patients.
(6) Display how many days patients were admitted in the hospital ?
(7) Show which doctor has not attended any patient ?
(8) Create patient table with proper constraints.
(9) Insert a record in patient table.
(10) Show patient name who has paid maximum bill amount.
Q.2) (A) Write PL/SQL block which will accept a number and print table
of that number. [10]
(B) Explain Alter Statement. [10]
Q.3) (A) Explain E.F. Codd’s Rules. [10]
(B) Write a user defined function which will accept patient name
and will return admit date of that patient. [10]
Q.4) (A) Explain Oracle Architecture. [10]
(B) Explain characteristics of DBMS. [10]
Q.5) Write short notes : (Any Two) [10]
(a) Select Statement
(b) Data Types
(c) Aggregate Functions
(d) Constraints