PL/SQL Lab Diploma in Information Science & Engineering6th sem

Govt. of Karnataka, Department of Technical Education

Diploma in Information Science & Engineering

 

Sixth Semester

 

Subject: PL/SQL Lab

 

Contact Hrs / week: 6                                         Total hrs: 96

1

Write a PL/SQL code to create an employee database with the tables and fields specified as below.

a)      Employee

Emp_no

Employee_name

Street

City

 

 

 

b)      Works

Emp_no

Company_name

Joining_date

Designation

Salary

 

 

c)      Company

Emp_no

City

 

 

 

d)     Manages

 

Emp_no

Manager_name

Mang_no

 

 

 

Note: Primary keys are underlined.

 

2

Write a PL/SQL code to create an student database with the tables and fields specified as below.

a)      Student

Roll_no

Student_name

Course

Gender

 

b)      Student_personal

Roll_no

DOB

Father_name

Address

Place

 

 

 

 

c)      Student_enrollment

Roll_no

Course

Course_code

Sem

Total_marks

Percentage

 

 

3

Write a PL/SQL code to retrieve the employee name, join_date, and designation from employee database of an employee whose number is input by the user.

 

4

Write a PL/SQL  code to show TABLE type of data(Array)

5

Write a PL/SQL  code to calculate tax for an employee of an organization –XYZ and to display his/her name & tax, by creating a table under employee database as below.

a)      Employee_salary

Emp_no

Basic

HRA

DA

Total_deduction

Net_salary

Gross_salary

.

6

Write a PL/SQL code to calculate total and percentage of marks of the students in four subjects.

7

Write a PL/SQL code to calculate the total and the percentage of marks of the students in four subjects from the table- Student with the schema given below.

STUDENT ( RNO , S1 , S2, S3, S4, total, percentage)

8

Write a PL/SQL code to display employee number, name and basic of 5 highest paid employees.

9

Write a PL/SQL code to calculate the total salary of first n records of emp table. The value of n is passed to cursor as parameter.

10

Write a PL/SQL code to update the salary of employees who earn less than the average salary.

11

Write a row trigger to insert the existing values of the salary table in to a new table when the salary table is updated.

12

Write a trigger on the employee table which shows the old values and new values of Ename after any updations on ename on Employee table.

 

13

Writ a PL/SQL procedure to find the number of students ranging from 100-70%, 69-60%, 59-50% & below 49% in each course from the student_course table given by the procedure as parameter.

14

Create a store function that accepts 2 numbers and returns the addition of passed values. Also write the code to call your function.

15

Write a PL/SQL function that accepts department number and returns the total salary of the department. Also write a function to call the function.

16

Write a PL/SQL code to create,

a)      Package specification

b)      Package body.

For the insert, retrieve, update and delete operations on a student table.

 

 

 

 

Text:

Database Management Systems, A Practical approach By- Er. Rajiv Chopra, S.Chand Publications, ISBN : 8121932459.

Reference:

            Oracle PL/SQL Programming, In Simple Steps, By- Kogent Solutions INc., Wiley Dreamtech Publications, ISBN: 8177228552

 

Scheme of Valuation

1

Record

05

2

Writing two programs

2x15

30

4

Execution of one program

40

5

Result

05

6

Viva Voce

20

 

Total

100

 

Related Posts Plugin for WordPress, Blogger...