PL/SQL Cursor Exercises with Solution
PL/SQL Cursor [ 50 exercises with solution ]
1. Write a program in PL/SQL to show the uses of static PL/SQL statement.
2. Write a program in PL/SQL to show the uses of CURVAL and NEXTVAL with a sequence name.
3. Write a program in PL/SQL to find the number of rows effected by the use of SQL%ROWCOUNT attributes of an implicit cursor
4. Write a program in PL/SQL to show the uses of implicit cursor without using any attribute.
5. Write a program in PL/SQL to show the uses of SQL%FOUND to determine if a DELETE statement affected any rows.
6. Write a program in PL/SQL to show the uses of SQL%NOTFOUND to determine if a UPDATE statement affected any rows.
7. Write a program in PL/SQL to create a table-based record using the %ROWTYPE attribute.
8. Write a program in PL/SQL to display a table based detail information for the employee of ID 149 from the employees table.
9. Write a program in PL/SQL to display a cursor based detail information of employees from employees table.
10. Write a program in PL/SQL to retrieve the records from the employees table and display them using cursors.
11. Write a program in PL/SQL to declare a record datatype with same datatype of tables using %TYPE attribute.
12. Write a program in PL/SQL to create an implicit cursor with for loop.
13. Write a program in PL/SQL to create an explicit cursor with for loop.
14. Create a PL/SQL block to increase salary of employees in the department 50 using WHERE CURRENT OF clause.
15. Write a program in PL/SQL to FETCH single record and single column from a table.
16. Write a program in PL/SQL to FETCH more than one record and single column from a table.
17. Write a program in PL/SQL to FETCH multiple records and more than one columns from the same table.
18. Write a program in PL/SQL to FETCH multiple records and more than one columns from different tables.
19. Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor.
20. Write a program in PL/SQL to FETCH records with nested Cursors using Cursor FOR Loops.
21. Write a program in PL/SQL to print a list of managers and the name of the departments.
22. Write a program in PL/SQL to insert data into two tables from one table using cursor.
23. Write a program in PL/SQL to insert data into two tables from one table using an implicit cursor.
24. Write a program in PL/SQL to create a cursor displays the name and salary of each employee in the EMPLOYEES table whose salary is less than that specified by a passed-in parameter value.
25. Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop.
26. Write a program in PL/SQL to fetch the first three rows of a result set into three records using Same explicit cursor into different variables.
27. Write a PL/SQL block to show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch.
28. Write a PL/SQL block to display department name, head of the department,city, and employee with highest salary.
29. Write a PL/SQL block to display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history.
30. Write a block in PL/SQL to displays employee name and number of jobs he or she done at past. Displays employee name and job count by job.
31. Write a block in PL/SQL to display the name of department and their head.
32. Write a PL/SQL program to display the name of the employee and increment percentage of salary according to their working experiences.
33. Write a PL/SQL block to show the uses of a variable in explicit cursor query, and the result set is affected with the value of the variable is incremented after every fetch.
34. Write a PL/SQL block to show the uses of a virtual column in an explicit cursor query.
35. Write a PL/SQL block to display the employee ID, first name, job title and the start date of present job.
36. Write a PL/SQL block to display the name of department the their costliest employee.
37. Write a PL/SQL block to display the last name, first name and overpaid amount by using parameters.
38. Write a PL/SQL block to display the number of employees by month. Print number of employees by month
39. Write a PL/SQL block to display the last name of manager, and their departments for a particular city, using parameters with a default value in explicit cursor.
40. Write a PL/SQL block to find out the start date for current job of a specific employee.
41. Write a PL/SQL block to display the last name, first name and overpaid amount by adding formal parameters and specify a default values for the added parameters.
42. Write a block in PL/SQL to display the first name, job title and start date of employees.
43. Write a block in PL/SQL to print the department name, head of the department, city and number of employees are working in that department.
44. Write a block in PL/SQL to print the specific number of rows from a table.
45. Write a block in PL/SQL to print a dotted line in every 6th line.
46. Write a block in PL/SQL to display the first department with more than five employees.
47. Write a block in PL/SQL to print a report which shows that, the employee id, name, hire date, and the incentive amount they achieved according to their working experiences, who joined in the month of current date.
48. Write a block in PL/SQL to shows how are records are declared and initialized.
49. Write a block in PL/SQL to show the uses of subquery in FROM clause of parent query in an explicit cursor.
50. Write a block in PL/SQL to show the uses of correlated subquery in an explicit cursor.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/plsql-exercises/cursor/index.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics