Write a PLSQL block that will use a variable to hold a depar

Write a PL/SQL block that will use a variable to hold a department ID (try assigning your variable the value of 10, 50, and 60 to test). Using that variable, select the department name (not ID) (from DEPARTMENTS table), and the minimum and maximum salary (use SQL functions) for employees with that department id (from EMPLOYEES table). Print the information in a message such as “For department Administration the minimum salary is 4200 and the maximum salary is 9000”.

Declare

lv_department_id departments.department_id%Type := 90;

lv_department_name departments.department_name%Type;

lv_min_salary employees.salary%Type;

lv_max_salary employees.salary%Type;

Begin

Select d.department_name, min(e.salary), max(e.salary)

Into lv_department_name, lv_min_salary, lv_max_salary

From d departments Left Outer Join e employees

ON departments.department_id = employees.department_id;

Where lv_department_id = department_id

Group by department_name;

End;

DBMS_OUTPUT.PUT_LINE (\'For department\'||lv_department_name||\'the minimum salary is\'||lv_min_salary||\'and the maximum salary is\'||lv_max_salary);

This is what I have so far I just cannot get it to run, can anyone help me.

Solution

if you are using oracle 10g or 11g, its very easy to run the program.

if so, type the program in either notepad or notepad++

after saving the program, open your command line, connect to the database.

make sure you have saved your program with .sql extension.

set the path of your pl/sql file location(use SET SERVER OUTPUT ON to view the output on the screen).

and just run your program.