PL/SQL Control Statement Exercises: Count the number of employees in a specific department and check whether this department have any vacancies or not. If any vacancies, how many vacancies are in that department
PL/SQL Control Statement: Exercise-9 with Solution
Write a PL/SQL program to count the number of employees in a specific department and check whether this department have any vacancies or not. If any vacancies, how many vacancies are in that department.
SET SERVEROUTPUT ON DECLARE tot_emp NUMBER; get_dep_id NUMBER; BEGIN get_dep_id := '&new_dep_id'; SELECT Count(*) INTO tot_emp FROM employees e join departments d ON e.department_id = d.department_id WHERE e.department_id = get_dep_id; dbms_output.Put_line ('The employees are in the department '||get_dep_id||' is: ' ||To_char(tot_emp)); IF tot_emp >= 45 THEN dbms_output.Put_line ('There are no vacancies in the department '||get_dep_id); ELSE dbms_output.Put_line ('There are '||to_char(45-tot_emp)||' vacancies in department '|| get_dep_id ); END IF; END; /
Enter value for new_dep_id: 20 old 6: get_dep_id := '&new_dep_id'; new 6: get_dep_id := '20'; The employees are in the department 20 is: 2 There are 43 vacancies in department 20 PL/SQL procedure successfully completed.
Improve this sample solution and post your code through Disqus
What is the difficulty level of this exercise?
- New Content published on w3resource :
- 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