SQL> --select into and cusrors continued SQL> --write an anonymous block that prompts the user to enter a department id and then SQL> --calculate and print the average salary of the employees in that department SQL> --the message should be in the following format SQL> -- The avergae salary in depaqrtment is >average salary you calculțated>. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is 3 select region_name, count(*) 4 from regions r, countries c 5 where r.region_id=c.region_id 6 group by region_name; 7 vn varchar2(30); 8 vc number; 9 begin 10 open c; 11 loop 12 fetch c into vn,vc; 13 exit when c%notfound; 14 dbms_output.put_line(vn||'--'||vc); 15 end loop; 16 close c; 17* end; SQL> select avg(Salary) 2 from employees 3 where department_id = &x; Enter value for x: 30 old 3: where department_id = &x new 3: where department_id = 30 AVG(SALARY) ----------- 4150 SQL> / Enter value for x: 10 old 3: where department_id = &x new 3: where department_id = 10 AVG(SALARY) ----------- 4400 SQL> ed Wrote file afiedt.buf 1 declare 2 y number; 3 begin 4 select avg(Salary) into y 5 from employees 6 where department_id = &x; 7 dbms_output.put_line('the avergae salary is '||y); 8* end; 9 / Enter value for x: 10 old 6: where department_id = &x; new 6: where department_id = 10; the avergae salary is 4400 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 begin 4 select avg(Salary) into v_avg 5 from employees 6 where department_id = &any_dept_id; 7 dbms_output.put_line('the averge salary is '||v_avg); 8* end; SQL> / Enter value for any_dept_id: / old 6: where department_id = &any_dept_id; new 6: where department_id = /; where department_id = /; * ERROR at line 6: ORA-06550: line 6, column 24: PL/SQL: ORA-00936: missing expression ORA-06550: line 4, column 2: PL/SQL: SQL Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 begin 4 select avg(Salary) into v_avg 5 from employees 6 where department_id = &any_dept_id; 7 dbms_output.put_line('the averge salary is '||v_avg); 8* end; SQL> / Enter value for any_dept_id: 10 old 6: where department_id = &any_dept_id; new 6: where department_id = 10; the averge salary is 4400 PL/SQL procedure successfully completed. SQL> / Enter value for any_dept_id: 30 old 6: where department_id = &any_dept_id; new 6: where department_id = 30; the averge salary is 4150 PL/SQL procedure successfully completed. SQL> SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 begin 4 select avg(Salary) into v_avg 5 from employees 6 where department_id = &any_dept_id; 7 dbms_output.put_line('the averge salary in department '||&any_dept_id||' is '||v_avg); 8* end; 9 / Enter value for any_dept_id: 30 old 6: where department_id = &any_dept_id; new 6: where department_id = 30; Enter value for any_dept_id: 30 old 7: dbms_output.put_line('the averge salary in department '||&any_dept_id||' is '||v_avg); new 7: dbms_output.put_line('the averge salary in department '||30||' is '||v_avg); the averge salary in department 30 is 4150 PL/SQL procedure successfully completed. SQL> / Enter value for any_dept_id: 30 old 6: where department_id = &any_dept_id; new 6: where department_id = 30; Enter value for any_dept_id: 10 old 7: dbms_output.put_line('the averge salary in department '||&any_dept_id||' is '||v_avg); new 7: dbms_output.put_line('the averge salary in department '||10||' is '||v_avg); the averge salary in department 10 is 4150 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 v_dept_id number ; 4 begin 5 v_dept_id := &any_dept_id; 6 select avg(Salary) into v_avg 7 from employees 8 where department_id = V_dept_id; 9 dbms_output.put_line('the averge salary in department '||V_dept_id||' is '||v_avg); 10* end; 11 / Enter value for any_dept_id: 10 old 5: v_dept_id := &any_dept_id; new 5: v_dept_id := 10; the averge salary in department 10 is 4400 PL/SQL procedure successfully completed. SQL> / Enter value for any_dept_id: 30 old 5: v_dept_id := &any_dept_id; new 5: v_dept_id := 30; the averge salary in department 30 is 4150 PL/SQL procedure successfully completed. SQL> / Enter value for any_dept_id: 8 old 5: v_dept_id := &any_dept_id; new 5: v_dept_id := 8; the averge salary in department 8 is PL/SQL procedure successfully completed. SQL> --correct the program so that if there are no employees in the department SQL> --it print 'no emps in dept SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 v_dept_id number ; 4 begin 5 v_dept_id := &any_dept_id; 6 select avg(Salary) into v_avg 7 from employees 8 where department_id = V_dept_id; 9 dbms_output.put_line('the averge salary in department '||V_dept_id||' is '||v_avg); 10* end; SQL> select count(*) 2 from employees 3 where department_id = &x; Enter value for x: 10 old 3: where department_id = &x new 3: where department_id = 10 COUNT(*) ---------- 1 SQL> / Enter value for x: 30 old 3: where department_id = &x new 3: where department_id = 30 COUNT(*) ---------- 6 SQL> / Enter value for x: 20 old 3: where department_id = &x new 3: where department_id = 20 COUNT(*) ---------- 2 SQL> / Enter value for x: 8 old 3: where department_id = &x new 3: where department_id = 8 COUNT(*) ---------- 0 SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 v_count number; 4 v_dept_id number ; 5 begin 6 v_dept_id := &any_dept_id; 7 select count(*) into v_count 8 from employees 9 where department_id = v_dept_id; 10 if v_count > 0 then 11 select avg(Salary) into v_avg 12 from employees 13 where department_id = V_dept_id; 14 dbms_output.put_line('the averge salary in department '||V_dept_id||' is '||v_avg); 15 else 16 dbms_output.put_line('there are no employees in department '||V_dept_id); 17 end if; 18* end; 19 / Enter value for any_dept_id: 20 old 6: v_dept_id := &any_dept_id; new 6: v_dept_id := 20; the averge salary in department 20 is 9500 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg number; 3 v_dept_id number ; 4 begin 5 v_dept_id := &any_dept_id; 6 select avg(Salary) into v_avg 7 from employees 8 where department_id = V_dept_id; 9 if v_avg is not null then 10 dbms_output.put_line('the averge salary in department '||V_dept_id||' is '||v_avg); 11 else 12 dbms_output.put_line('there are no employees in department '||V_dept_id); 13 end if; 14* end; 15 / Enter value for any_dept_id: 30 old 5: v_dept_id := &any_dept_id; new 5: v_dept_id := 30; the averge salary in department 30 is 4150 PL/SQL procedure successfully completed. SQL> --solve the same question without using a group function SQL> select salary 2 from employees 3 where department_id = 20; SALARY ---------- 13000 6000 SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is select salary from employees where department_id = &deptid; 3 v_salary number; 4 v_sum number; 5 v_count number; 6 v_avg number; 7 begin 8 open c; 9 loop 10 fetch c into v_salary; 11 exit when c%notfound; 12 v_count := v_count+1; 13 v_sum :=v_sum+v_salary; 14 end loop; 15 v_avg := v_sum/v_count; 16 dbms_poutput.put_line('v_count='||v_count); 17 dbms_poutput.put_line('v_sum='||v_sum); 18 dbms_poutput.put_line('v_avg='||v_avg); 19 close c; 20* end; 21 / Enter value for deptid: 20 old 2: cursor c is select salary from employees where department_id = &deptid; new 2: cursor c is select salary from employees where department_id = 20; dbms_poutput.put_line('v_count='||v_count); * ERROR at line 16: ORA-06550: line 16, column 2: PLS-00201: identifier 'DBMS_POUTPUT.PUT_LINE' must be declared ORA-06550: line 16, column 2: PL/SQL: Statement ignored ORA-06550: line 17, column 2: PLS-00201: identifier 'DBMS_POUTPUT.PUT_LINE' must be declared ORA-06550: line 17, column 2: PL/SQL: Statement ignored ORA-06550: line 18, column 2: PLS-00201: identifier 'DBMS_POUTPUT.PUT_LINE' must be declared ORA-06550: line 18, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is select salary from employees where department_id = &deptid; 3 v_salary number; 4 v_sum number; 5 v_count number; 6 v_avg number; 7 begin 8 open c; 9 loop 10 fetch c into v_salary; 11 exit when c%notfound; 12 v_count := v_count+1; 13 v_sum :=v_sum+v_salary; 14 end loop; 15 v_avg := v_sum/v_count; 16 dbms_output.put_line('v_count='||v_count); 17 dbms_output.put_line('v_sum='||v_sum); 18 dbms_output.put_line('v_avg='||v_avg); 19 close c; 20* end; SQL> / Enter value for deptid: 20 old 2: cursor c is select salary from employees where department_id = &deptid; new 2: cursor c is select salary from employees where department_id = 20; v_count= v_sum= v_avg= PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is select salary from employees where department_id = &deptid; 3 v_salary number := 0; 4 v_sum number:=0; 5 v_count number:=0; 6 v_avg number:=0; 7 begin 8 open c; 9 loop 10 fetch c into v_salary; 11 exit when c%notfound; 12 v_count := v_count+1; 13 v_sum :=v_sum+v_salary; 14 end loop; 15 v_avg := v_sum/v_count; 16 dbms_output.put_line('v_count='||v_count); 17 dbms_output.put_line('v_sum='||v_sum); 18 dbms_output.put_line('v_avg='||v_avg); 19 close c; 20* end; SQL> / Enter value for deptid: 30 old 2: cursor c is select salary from employees where department_id = &deptid; new 2: cursor c is select salary from employees where department_id = 30; v_count=6 v_sum=24900 v_avg=4150 PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 20 old 2: cursor c is select salary from employees where department_id = &deptid; new 2: cursor c is select salary from employees where department_id = 20; v_count=2 v_sum=19000 v_avg=9500 PL/SQL procedure successfully completed. SQL> --write anonymous block to print the name of region together with the number of countries in that region SQL> --version 1 : use joins SQL> --version 2: do not use joins SQL> spool off