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