RDBMS Practical Slips – Complete Slip Wise Questions (PL/SQL & Database Design)

If you are preparing for your RDBMS Practical Exam, here is the complete list of RDBMS slip-wise practical questions arranged clearly for easy revision.

These slips include:

• Relational Database Design in 3NF
• PL/SQL Functions
• Procedures
• Triggers
• Cursors
• Constraint Implementation

Slip 1

1. Consider the following entities and their relationships: Client (client_no, client_name, address, birthdate) and Policy_info (policy_no, description, maturity_amt, prem_amt, date). The relationship between them is Many-to-Many. Create a relational database in 3NF and write PL/SQL blocks in Oracle to write a function which returns total maturity amount of policies of a particular client and write a cursor which displays policy date-wise client details.

Slip 2

1. Consider Item (itemno, itemname) and Supplier (supplier_no, supplier_name, address, city) with a Many-to-Many relationship having descriptive attributes rate and quantity. Create a relational database in 3NF and write PL/SQL blocks to write a function to return total number of suppliers for a particular item and write a trigger that fires before insert or update when rate or quantity is less than or equal to zero and raise an appropriate error.

Slip 3

1. Consider Newspaper (name, language, publisher, cost) and Cities (pincode, city, state) with a Many-to-Many relationship and daily_required as descriptive attribute. Create a relational database in 3NF and write PL/SQL blocks to write a trigger that fires before insert on Cities to ensure pincode is six digits and write a procedure to calculate city-wise total cost of each newspaper.

Slip 4

1. Consider Client (cno, cname, addr, bdate) and Policy (pno, disc, mamt, pamt, pdate) with Many-to-Many relationship. Create a relational database in 3NF and write PL/SQL blocks to write a procedure that displays all policy details having premium amount less than 5000 and write a trigger that fires before insert or update on policy where maturity amount is less than premium amount and raise an error.

Slip 5

1. Consider Library (lno, lname, location, librarian, no_of_books) and Book (bid, bname, author_name, price, publication) with One-to-Many relationship. Create a relational database in 3NF and write PL/SQL blocks to write a function which accepts publication name and returns total price of books of that publication and write a parameterized cursor to display library-wise book details.

Slip 6

1. Consider Employee (emp_id, emp_name, address) and Investment (inv_no, inv_name, inv_date, inv_amount) with One-to-Many relationship. Create a relational database in 3NF and write PL/SQL blocks to write a procedure that displays details of employees invested in Mutual Fund and write a cursor which displays investment details ordered by date.

Slip 7

1. Consider Bill (billno, day, tableno, total) and Menu (dish_no, dish_desc, price) with Many-to-Many relationship having quantity as descriptive attribute. Create a relational database in 3NF and write PL/SQL blocks to write a procedure to display menu items with price between 200 and 500 ordered on Saturday and write a trigger that fires before insert or update on Menu where price is less than or equal to zero and raise an appropriate error.

Slip 8

1. Consider Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt) and Customer (cust_no, cust_name, mobile_no) with One-to-Many relationship. Create a relational database in 3NF and write PL/SQL blocks to write a function which accepts plan number and displays all details of that plan and write a parameterized cursor which displays customer-wise plan details.

Slip 9

1. Consider Project (pno, pname, start_date, budget, status) and Department (dno, dname, HOD, loc) with Many-to-One relationship where project status must be C, P, or I. Create a relational database in 3NF and write PL/SQL blocks to write a function which accepts department name and displays total number of progressive projects and write a cursor which displays status-wise project details of each department.

Slip 10

1. Consider Gym (name, city, charges, scheme) and Member (id, name, phoneNo, address) with One-to-Many relationship. Create a relational database in 3NF and write PL/SQL blocks to write a function which accepts member id and scheme and displays charges paid by that member and write a trigger that fires before insert or update on Gym where charges are less than 1000 and raise an appropriate error.

Slip 11

1. Consider Student (rollno, sname, class, timetable) and Lab (labNo, labName, capacity, equipment) with Many-to-One relationship. Create a relational database in 3NF and write PL/SQL blocks to write a function which accepts lab number and displays total number of students allocated to that lab and write a cursor which displays lab-wise student details.

Slip 12

1. Consider Wholesaler (w_no, w_name, address, city) and Product (product_no, product_name, rate) with Many-to-Many relationship having quantity as descriptive attribute. Create a relational database in 3NF and write PL/SQL blocks to write a function which accepts wholesaler name and displays total number of items supplied by that wholesaler and write additional SQL or PL/SQL blocks based on wholesaler and product relationship.