DISTANCE EDUCATION
M.C.A DEGREE EXAMINATION, MAY 2017.
First Semester
RELATIONAL DATABASE MANAGEMENT SYSTEMS LAB
(2010 Academic Year onwards)
Time Three hours Maximum 100 marks
Examiner should select any give one question to each
candidate from 1st system.
1. A Hospital Information System Maintains Blood Donors
Record information in a file. The File contains the
following Fields:
Donor number, Donor name, Donor age, Donor address,
Pin code, Place of birth, Blood group AB and O).
Create the Table and Insert 10 Records based on
following Queries.
Write a program in SQL to Display the Donor number,
Donor name and address of the donors for the following
categories
Blood donors having AB blood group.
Blood donors in the age group between 16 to 25.
Female donors with blood group in the age
between 18 to 25.
Blood donors who are living in Chennai only.
Blood donors whose name begins with S only.
Sub. Code
106
DE-9547
2
wk7
2. A Salary Statement contains the following Fields.
Employee No, Employee Name, Basic Pay, HRA, DA, PF,
IT, Gross Pay, Net Pay. Create the Table and Insert 10
Records.
Calculate
HRA of Basic Pay
DA 75% of Basic Pay
PF 10% of Basic Pay
Gross pay Basic Pay DA HRA
IT 10% of Gross Pay
Net Pay Gross Pay — (PF IT)
Write a program in SQL to prepare Salary Report for 10
Employees.
3. Write a program in SQL for Hospital Management
System with the following fields
Patient no, Patient name, Age, Attending Doctor,
Patient Type
Consulting Fee, Blood Test Fee, X-Ray Fee, Other Fee
and Total Fee.
Create the Table and Insert 10 Records based on
following Queries.
Write the Query in SQL to Display the following
conditions
The patients, who take Blood test and X-Ray
The patients, whose Name ends with R.
The patients, who belong to In-patient category.
Calculate the total fee collected for the entire Day.
The Patients who take the treatment for the age
more than 60.
Cut here
DE-9547
3
wk7
4. 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
Create Both the Table and Insert 10 Records based on
following Queries.
Write a program in ORACLE to Display the following
Conditions
List the Passenger Name, seat number, class and
Flight number.
Total number of Married Female Passengers in a
particular Flight.
List out the male passengers in the age between
18 to 25 in a Flight.
Display how many Indians are in the Flight?
List the Passenger Name starts with S.
DE-9547
4
wk7
5. Tamil Nadu Electricity Board Billing System contains the
following Fields.
Consumer No, Consumer Name, Address, Current Value,
Previous Value, Chargeable Unit, Code stands for
Domestic, C stands for Commercial), Pay Mode
(Cheque/Cash)
Calculate the Bill based on following Criteria.
For Code D Rs. 2 Per Unit. (If Chargeable Unit 200)
Rs. 3 Per Unit (If Chargeable Unit 200)
For Code C Rs. 5 Per Unit. (If Chargeable Unit
Rs. 7 Per Unit (If Chargeable Unit 200)
Create the Table and Insert 10 Records based on
following Queries. Write the Query in SQL to Display the
following conditions
Display the Consumer Name who paid the bill in
Pay Mode Cheque
List the Consumer Number who paid the bill More
than 2000 Rs.
Find the Consumer Name who paid the Lowest
Amount on that Day.
Display the Consumer Number who paid the Bill
Code C.
Display the Consumer Name who are Living in
Chennai.
DE-9547
5
wk7
6. Create a Table Employee with the following Fields.
Emp no, Emp name, Dept no, Doj, Designation
Insert 10 Records based on following Queries.
Alter the table employee, make Emp no as Primary
key and Dept no as Not Null.
Increase the column width by 3 for Deptno field.
Add the new field as Address.
List all the employees whose name begin with
Display all the employees who have joined before
31-DEC-2013.
7. Library Information System maintains the following
Fields.
Roll no, Name, Book No, Book Name, Author Name,
Publisher Name, Issue Date, Return Date, Fine students
should Return the Book within 10 Days. Otherwise Fine
should be collected for each Day Rs. 5
Insert 10 Records based on following Queries.
Display the Book Number, Publisher Name only.
Display Total Amount of Fine Collected.
Display BPB Publisher Books Only.
List the students Name those who are Return the
Book Within 10 Days.
List all the Author Name whose name ends with
Cut here
DE-9547
6
wk7
8. Write a PL/SQL Program to Prepare a Mark Statement of
a Student using Student Table and Mark Table Using
Cursor.
9. Write a PL/SQL Block to insert Records into Student
Table using Stored Procedure.
10. Write a PL/SQL Block to find the Reverse of given String
using stored function.