PL/SQL Cursor Exercises: Show the uses of subquery in FROM clause of parent query in an explicit cursor
PL/SQL Cursor: Exercise-49 with Solution
Write a block in PL/SQL to show the uses of subquery in FROM clause of parent query in an explicit cursor.
Sample Solution:
PL/SQL Code:
DECLARE
CURSOR emp_cur IS
SELECT d1.department_id, department_name, emp_no
FROM departments d1,
( SELECT department_id, COUNT(*) AS emp_no
FROM employees
GROUP BY department_id
) d2
WHERE (d1.department_id = d2.department_id) AND emp_no >= 6
ORDER BY emp_no;
BEGIN
DBMS_OUTPUT.PUT_LINE (rpad('Department',25)||'No. of Employees');
DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
FOR dept IN emp_cur
LOOP
DBMS_OUTPUT.PUT_LINE (rpad(dept.department_name,25) || dept.emp_no);
END LOOP;
END;
/
Sample Output:
Department No. of Employees ---------------------------------------- Purchasing 6 Finance 6 Sales 34 Shipping 45 PL/SQL procedure successfully completed.
Flowchart:
Improve this sample solution and post your code through Disqus
Previous: Write a block in PL/SQL to shows how are records are declared and initialized.
Next: Write a block in PL/SQL to show the uses of corelated subquery in an explicit cursor.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework