DISTANCE EDUCATION
MASTER OF COMPUTER APPLICATION DEGREE
EXAMINATION, DECEMBER 2017.
First Semester
RELATIONAL DATABASE MANAGEMENT SYSTEMS
(2010 Academic Year Onwards)
Time Three hours Maximum 100 marks
Examiner has to select ONE question and give it to the
students by the lot system.
1. A Newspaper vendor buys newspapers at wholesale from
a distributor for Rs. 4 and sell them at retail for Rs. 4.50.
At the end of the day the unsold papers are returned to
the distributor for Rs. 1 rebate per paper. Write a
program using oracle to prepare a report for the
newspaper vendor in the following format with 9 weeks
data:
Week Bought Sold Return Profit Loss
1 100 50 XX XXX XX
2 100 50 XX XXX XX
. ...... ...... ...... ...... ......
. ...... ...... ...... ...... ......
Total
Sub. Code
106
DE-9845
2
wk12
2. An airline reservation database contains the reservation
table and personal table.
Reservation table contains following fields
Flight_no, class, passenger_name, seat_number.
Personal table contains following fields
Passenger_name, sex, age, marital_status, nationality.
Write a program in oracle to prepare the following
reports:
List the passenger names with seat number
according to class and flight number.
The total number of married female passengers on a
particular flight.
List out the male passengers in the age between
18 to 25 for all flights.
3. A hospital maintains blood donor record information in a
file. The file contains the following fields
Donor_number, donor_name, donor_age, donor_address,
pincode, place_of_birth, sex, blood_group AB, O).
Write SQL statements to print Donor's name, number,
and address for the following categories.
Blood donors having blood group.
Donors in the age between 16 to 25.
Female donors with blood group in the age
between 18 to 25.
Cut here
DE-9845
3
wk12
4. Create a table MARKS contains:
Roll no, name, mark mark mark 3
Using the MARKS table do the following:
Search a Roll no and display total marks and grade.
For a pass subject minimum is 40. For the failed
students grade is "F". For the passed student grade
is set as
if total 240
if total between 180 and 240
if total between 150 and 180
otherwise exception must be raised if Roll no
could not find.
5. Create a table to represent a theatre ticket reservation
consisting of the following fields.
Customer_name, sex, date, seat_number, show_time,
class.
Create the above table by properly specifying
primary keys and foreign keys.
Find the customers who have more than 3 tickets.
Display all the female customers in a show.
Display the shows which are houseful in a day.
Cut here
DE-9845
4
wk12
6. A daily sales file contains records with the following
fields.
Department_number, date, item_description, quantity,
cost_of_each_item, sale_price_for_each_item.
Write a program using Oracle to list all the input data
and compute the total amount of the sales and profit for
each sale. Accumulate and print total sales and profit.
Show the output report in the following format:
Department number:
Item Description:
Sales Price:
Quantity:
Cost:
Profit:
7. Write a PL/SQL program to prepare a mark statement of
a student using Student table and Mark table. Assume
your own data.
8. Write a program in SQL for hospital billing system with
the following fields:
patient_no, patient_name, age, attending_doctor,
patient_type consulting_charge,
blood_test_charges, Xray_charges, other_test_charges,
and total_fee.
Prepare reports with the following conditions:
The patients, undergone blood test.
The patients, who taken X-ray.
The patients, who belong to In-patient category.
The common fields to be included in the above mentioned
reports:
patient_no, patient_name, age, charges, total_fee.
————————