University Of Pune Question Paper
M. C. M. (Semester - II) Examination - 2010
ORACLE
(New 2008 Pattern)
Time : 3 Hours] [Max. Marks : 70
Instruction :
All questions are compulsory.
Q.1) Consider the following tables :
(1) Purchase-Order-Header : (PONo, PODate, SupplierCode,
TotalAmount)
(2) Purchase-Order-Detail : (PONo, ItemNo, ItemName, Qty, Rate)
Write SQL queries : (Any Ten) [20]
(a) Create Purchase-Order-Header table with proper constraints.
(b) Add column expected - delivery - date to the table PurchaseOrder-Header.
(c) Display total cost (qty * rate) of every Purchase Order.
(d) Display PODate as ‘5th April 2010, Monday’.
(e) Write a query to count total no. of Suppliers.
(f) Display item names having ‘a’ in them.
(g) Display all PO’s having total amount > 10,000.
(h) Display all Purchase Orders whose Expected Delivey Date is the
same month.
(i) Display PODatewise POs.
(j) Count number of items in every PO.
(k) Display PONo, PODate, SupplierCode and ItemName for all POs.
(l) Display PO details of items with 8 no. of characters in item name.
Q.2) Write PL/SQL block for the following : (Attempt Any Five) [25]
(a) Display top 5 PO records with highest TotalAmount.
(b) Accept PONo and display PODate and SupplierCode.
(c) Write a procedure that accepts PONo as parameter. Search this
PONo in Purchase-Order-Header. If found, set expected-deliverydate
to current date. If not found, raise an exception which
displays error message ‘PONo NOT FOUND’.
(d) Write a function that accepts PONo as a parameter and returns
true if TotalAmount is greater than 6,000; otherwise returns false.
(e) While inserting a record in Purchase-Order-Detail table, if qty is
less than or equal to φ, trigger should be fired and message
“qty < = φ not allowed” should be displayed.
(f) Accept PONo from user and delete that record from PurchaseOrder-Header.
Q.3) Write short notes : (Any Five) [25]
(a) Group Functions in SQL
(b) Views
(c) Set Operators
(d) Data types of PL/SQL
(e) Grant and Revoke
(f) Any five character functions with examples
M. C. M. (Semester - II) Examination - 2010
ORACLE
(New 2008 Pattern)
Time : 3 Hours] [Max. Marks : 70
Instruction :
All questions are compulsory.
Q.1) Consider the following tables :
(1) Purchase-Order-Header : (PONo, PODate, SupplierCode,
TotalAmount)
(2) Purchase-Order-Detail : (PONo, ItemNo, ItemName, Qty, Rate)
Write SQL queries : (Any Ten) [20]
(a) Create Purchase-Order-Header table with proper constraints.
(b) Add column expected - delivery - date to the table PurchaseOrder-Header.
(c) Display total cost (qty * rate) of every Purchase Order.
(d) Display PODate as ‘5th April 2010, Monday’.
(e) Write a query to count total no. of Suppliers.
(f) Display item names having ‘a’ in them.
(g) Display all PO’s having total amount > 10,000.
(h) Display all Purchase Orders whose Expected Delivey Date is the
same month.
(i) Display PODatewise POs.
(j) Count number of items in every PO.
(k) Display PONo, PODate, SupplierCode and ItemName for all POs.
(l) Display PO details of items with 8 no. of characters in item name.
Q.2) Write PL/SQL block for the following : (Attempt Any Five) [25]
(a) Display top 5 PO records with highest TotalAmount.
(b) Accept PONo and display PODate and SupplierCode.
(c) Write a procedure that accepts PONo as parameter. Search this
PONo in Purchase-Order-Header. If found, set expected-deliverydate
to current date. If not found, raise an exception which
displays error message ‘PONo NOT FOUND’.
(d) Write a function that accepts PONo as a parameter and returns
true if TotalAmount is greater than 6,000; otherwise returns false.
(e) While inserting a record in Purchase-Order-Detail table, if qty is
less than or equal to φ, trigger should be fired and message
“qty < = φ not allowed” should be displayed.
(f) Accept PONo from user and delete that record from PurchaseOrder-Header.
Q.3) Write short notes : (Any Five) [25]
(a) Group Functions in SQL
(b) Views
(c) Set Operators
(d) Data types of PL/SQL
(e) Grant and Revoke
(f) Any five character functions with examples
0 comments:
Pen down your valuable important comments below