Database Management System Lab Question Bank

MCA II/I D.B.M.S. Lab

1. Create a table to represent sb-account of a bank consisting of account-no,
customer-name, balance-amount.
Write a PL/SQL block to implement deposit and withdraw. Withdraws should not be allowed if the balance goes below Rs.1000.

2. Create The following two tables :

College-info
Faculty-info
College-info consists of fields : college-code, college-name, address
Faculty-info consists of fields : college-code, faculty-code, faculty-name,
qualification, experience-in-no-of-years, address.
The field college-code is foreign key.

(a) Design a form to accept the data from the user.
(b) Generate queries to do the following :
(i) List all those faculty members whose experience is greater than or equal to 10 years and have M. Tech degree.
(ii) List all those faculty members, who have at least 10 years of experience but do not have M. Tech degree.

3. Create the following tables for Library Information System :
Book : (accession-no, title, publisher, author, status)
Status could be issued, present in the library, sent for binding, and cannot be
issued.
Write a trigger which sets the status of a book to "cannot be issued", if it is
published 20 years back.

4. Create the following tables for Library Information System :
Book(accession-no, title, publisher, author, status, date-of-purchase)
Status could be issued, present in the library, sent for binding, and account be
issued.
(a) Create a form to accept the data from the user Create a form to accept the
data from the user with appropriate validation checks.
(b) Generate queries to do the following :
(i) List all those books which are new arrivals. The books which are acquired during the last 6 months are categorized as new arrivals.
(ii) List all those books that cannot be issued and purchased 20 years ago.

5. Create the following tables :
Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those students who are greater than 18 years of age and have opted for
MCA course.
(ii) List all those courses whose fee is greater than that of MCA course.

6. Create the following table :
Student (roll-no, name, subject-name, subject-opted)
Subject(faculty-code, faculty-name, specialization)

(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have enrolled for the subject "DBMS".
(ii) Find all those faculty members who have not offered any subject.

7. Create the following table :
Item (item-code, item-name, qty-in-stock, reorder-level)
Supplier (supplier-code, supplier-name, address)
Can-supply(supplier-code, item-code)
(a) Create a form to accept the data from the user with appropriate validation checks.
(b) Generate queries to do the following :
(i) List all those suppliers who can supply the given item.
(ii) List all those items which cannot be supplied by given company.

8. Create the following tables:
Student (roll-no, marks, category, district, state)
Student-rank(roll-no, marks, rank)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those students who have come from Tamilnadu state and secured a rank
above 100.
(ii) List all those students who come from Andhra Pradesh state and belong to
given category who have secured a rank above 100.

9. Create the following tables :
Branch (branch-id, branch-name, customer-city, branch-id)
Customer (customer-id, customer-name, customer-city, branch-id)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those customers who live in the same city as the branch in which they
have account.
(ii) List all those customers who have an account in a given branch city.

10. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
Member(member-id, name, number-of-books-issued, max-limit)
Book-issue(accession-no, member-id, date-of-issue)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those books which are due from the students to be returned. A book is considered to be due if it has been issued 15 days back and yet not returned.
(ii) List all those members who cannot be issued any more books.

11. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
Member(member-id, name, number-of-books-issued, max-limit)
Book-issue(accession-no, member-id, date-of-issue)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write a PL/SQL procedure to issue the book.
Write a trigger to set the status of students to "back listed" if they have taken
book but not returned even after one year.

12. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
Book-Place(accession-no, rack-id, rack-position)
Member(member-id, name, number-of-books-issued, max-limit, status)
Book-issue(accession-no, member-id, date-of-issue)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write a PL/SQL procedure to issue the book.
Write a trigger to set the status of a book neither to "lost" which is neither
issued nor in the library.

13. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
Member(member-id, name, number-of-books-issued, max-limit, status)
Book-issue(accession-no, member-id, date-of-issue, due-date)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write a PL/SQL to list all those students who are defaulters. A student is
considerer to be a defaulter if he has not returned a book even after due-date.
Write a trigger to set the status of students to "back listed" if they have taken
book but not returned even after one year.

14. Create the following tables :
Branch (branch-id, branch-name, branch-city)
Customer (customer-id, customer-name, customer-city, branch-id)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those customers who live in the same city as the branch in which they
have account.
(ii) List all those customers who have an account in more than one branch.

15. Create the following tables :
Branch (branch-id, branch-name, customer-city)
Customer (customer-id, customer-name, customer-city, branch-id)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those customers who have more than 100 customer.
(ii) List all those customers who have an account in more than one branch.

16. Create the following table :
Student (roll-no, name, category, district, state)
Student –rank (roll-no, marks, rank)

(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List names of the students who are having same rank but they should reside in
different districts.
(ii) List details of students they belongs to same category with same rank.

17. Create the following tables :
Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) List all those students who are between 18-19 years of age and have opted for
MCA course.
(ii) List all those courses in which number of students are less than 10.

18. Create the following tables :
Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration, status)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to do the following :
Set the status of course to "not offered" in which the number of candidates is
less than 5.

19. Create the following tables :
Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration, status)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to do the following :
Set the status of course to "not offered" in which the number of candidates is
less than 5.

20. Create the following tables :
Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration, status)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to do the following :
Set the status of course to "offered" in which the number of candidates is at
least 10 otherwise set it to "not offered".

21. Create the following table :
Item (item-code, item-name, qty-in-stock, reorder-level)
Supplier (supplier-code, supplier-name, address)
Can-supply(supplier-code, item-code)
(a) Create a form to accept the data from the user with appropriate validation checks.
(b) Write PL/SQL procedure to do the following :
Generate a report to list the items whose qty-in-stock is less than or equal to their reorder-levels.

22. Create the following table :
Item (item-code, item-name, qty-in-stock, reorder-level)
Supplier (supplier-code, supplier-name, address, status)
Can-supply(supplier-code, item-code)
(a) Create a form to accept the data from the user with appropriate validation checks.
(b) Write PL/SQL procedure to do the following :
Set the status of the supplier to "important" if the supplier can supply more
than five items.

23. Create the following tables :
Item (item-code, item-name, qty-in-stock, reorder-level)
Supplier (supplier-code, supplier-name, address, status)
Can-supply(supplier-code, item-code)
(a) Create a form to accept the data from the user with appropriate validation checks.
(b) Write PL/SQL procedure to do the following :
Generate a report of those items that are supplied by those suppliers whose
status is "important".

24. Create the following tables :
Student (roll-no, name, category, district, state)
Student –rank (roll-no, marks, rank)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to the following :
Generate a report to list of those districts from which the first hundred rankers
come from.

25. Create the following tables :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code, specialization)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to the following :
Set the status of the subject to "not offered" if the subject is not opted by at
least 5 students.

26. Create the following tables :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code, specialization)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to the following :
Set the status of the subject to "not offered" if the subject is not offered by any
of the faculty members.

27. Create the following tables :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have enrolled for the subject "DBMS"
(ii) Find all those subjects which are not offered by any faculty members.

28. Create the following tables :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have enrolled for the subject "DBMS"
(ii) Find all those subjects which are offered by more than one faculty member.

29. Create the following tables :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have enrolled for the subject "OS"
(ii) Find all those students who opted for more than 5 subjects.

30. Create the following tables :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have not enrolled for the subject "DBMS"
(ii) Find all those subjects which are offered by more than one faculty member.

*****