KARPAGAM ACADEMY OF HIGHER EDUCATION

(Deemed to be University)
(Established Under Section 3 of UGC Act, 1956)
Eachanari Post, Coimbatore- 641 021
Department of Computer Applications

Sub Name & Code : Relational DataBase Management Systems - Practical & 21CAU312

List of Programs

1. To implement Data Definition language

2. To implement Data Manipulation language

3. To implementation on DCL and TCL

4. To implement the following Constraints

(a). Primary key (b). Foreign Key (c). Check (d). Unique (e). Null (f) Not null

5. Create a table with following

fields: Employee table:
Field name Constraint Type Size
Employee_no Primary key Character 6
Employee_name Character 30
Address Character 25
Designation Character 15
Dob Date
Gender Check Character 1
Doj Date
Salary Number 10,2
Queries:
a. Display name of the employees whose salary is greater than “10,000”.
b. Display the details of employees in ascending order according to Employee Code
c. Display the details of employees earning the highest salary
d. Display the names of employees who earn more than “Ravi”.

6. Create table named Student with following fields and insert the values:
Field name field type field size
Student Name Character 15
Gender Character 6
Roll No. Character 10
Department Name Character 15
Address Character 25
Percentage of marks Number 4,2
Queries:
a. Calculate the average mark percentage of the students
b. Display the names of the students whose percentage marks are greater than 80%
c. Display the details of the students who got the highest percentage of marks
d. Display the details of the students whose mark percentage between 50 and 70
e. Display the details of the students whose mark percentage is greater the mark percentage of Roll
No=12CA01
7. Create a table with following fields:
Staff table:
Field name Constraint Type Size
Staff_no Primary key Character 6
Staff_name Character 30
Dob Date
Dept_code Foreign key Character 4
Designation Character 15
Basic Number 7,2
Department table:
Field name constraint Type Size
Dept_code Primary key Character 4
Dept_name Character 30
Execute the following queries:
a.To list the staff who joined 2 years back.
b.To list the staff in computer science dept.
c.To list the staff_name and the dept_name in which he/she works.
d. To list the maximum and minimum salary in each dept.
e. To list the dept along with the total amount spent on salary
f. To list the name of the employees who draw the salary more than the average salary.

8. Create a table with the following fields:

Book table:
Field name Constraint Type Size
Access_no Primary key Character 6
Title Character 30
Author Character 30
Publisher Character 30
Subject Character 10
Price Number 6,2
Execute the following queries:
a. The title of C and C++ books.
b. The books written by a particular author.
c. The books which costs between Rs.300/- and Rs.500/-
d. The number of books available in each subject.
e. The books in the decreasing order of the cost.

9. Create a table with the following

fields: Account table:
Field name Constraint Type Size
Acc_no Primary key Number 4
Cust_name Varchar2 30
Branch_name Varchar2 30
Cust_city Varchar2 30
Borrower table:
Field name Constraint Type Size
Acc_no Foreign key Number 30
Branch_name Varchar2 30
Amount Number 8,2
Write queries to perform different types of Join.

10. Write a PL/SQL block to create and handle User Defined Exception
clientmaster
 Field name Constraint Type Size
Client_id Number 6
Client_name Varchar2 30
Address Varchar2 50
Phone Number 10
Balance Number 10,2

11. Create table with following

fields: Product table:
Field name Constraint Type Size
Product_code Primary key Varchar2 7
Product_name Varchar2 30
Price Number 6,2
Quantity Number 4

Vendor table:
Field name Constraint Type Size
Vendor_name Varchar2 30
Vendor address Varchar2 30
Product_code Foreign Key Varchar2 7
Create a Trigger to fire when the Record is deleted and inserted.

12. Write a PL/SQL trigger to update the records while deleting the one record in another table.
Voters_master:
Field name Constraint Type Size
Voterid Primary key Number 5
Name Varchar2 30
Ward_no Primary Key Number 4
Dob Date
Address Varchar2 150
New_list
Field name Constraint Type Size
Voterid Number 5
Ward_no Number 4
Name Varchar2 30
Description Character 50

13. Create a table to store the salary details of the employees in a company. Declare the Cursor id to
contain empno, employee name and net salary. Use cursor to update the employee details.
Salary:

Field name Constraint Type Size

Emp_no Primary key Number 4
Emp_name Varchar2 30
Designation Varchar2 25
Dept Varchar2 30
Basic Number 5

14. Create a table stock contains the itemcode varchar2(10), itemname varchar2(50),
current_stocknumber(5), date_of_last_purchase date. Write a stored procedure to seek for an item
using itemcode and delete it, if the date of last purchase is before 1 year from the current date. If
not, update the curent stock.

15. Create a Package in PL/SQL

Internal Examiner External Examiner