SQL> --cuersors and select into continued SQL> --write an anonymous block to print the average salary of a gıven department. SQL> --read the department id from keyboard SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> set serveroutput on SQL> select avg(salary= 2 ed 3 ed 4 . SQL> ed Wrote file afiedt.buf 1 select avg(salar) 2 from employees 3* where department_id = &x SQL> / Enter value for x: 4 old 3: where department_id = &x new 3: where department_id = 4 select avg(salar) * ERROR at line 1: ORA-00904: "SALAR": invalid identifier SQL> a y 1* select avg(salar)y SQL> ed Wrote file afiedt.buf 1 select avg(salar) 2 from employees 3* where department_id = &x SQL> 1 1* select avg(salar) SQL> c/)/y) 1* select avg(salary) SQL> / Enter value for x: 10 old 3: where department_id = &x new 3: where department_id = 10 AVG(SALARY) ----------- 4400 SQL> lıst SP2-0042: unknown command "lıst" - rest of line ignored. SQL> list 1 select avg(salary) 2 from employees 3* where department_id = &x SQL> run 1 select avg(salary) 2 from employees 3* where department_id = &x Enter value for x: 20 old 3: where department_id = &x new 3: where department_id = 20 AVG(SALARY) ----------- 9500 SQL> 1 1* select avg(salary) SQL> change/avg(salary)/last_name 1* select last_name SQL> l 1 select last_name 2 from employees 3* where department_id = &x SQL> del 2 SQL> lıst SP2-0042: unknown command "lıst" - rest of line ignored. SQL> list 1 select last_name 2* where department_id = &x SQL> --change del list run spool are all sql plus commands used SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id = &a_dept_id; 7 dbms_output.put_line('The average salary is '||v'.'); 8* end; SQL> / Enter value for a_dept_id: 10 old 6: where department_id = &a_dept_id; new 6: where department_id = 10; dbms_output.put_line('The average salary is '||v'.'); * ERROR at line 7: ORA-06550: line 7, column 51: PLS-00103: Encountered the symbol "." when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem => <> or != or ~= >= <= <> and or like like2 like4 likec as between from using || member submultiset The symbol "," was substituted for "." to continue. SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id = &a_dept_id; 7 dbms_output.put_line('The average salary is '||v||'.'); 8* end; SQL> / Enter value for a_dept_id: 10 old 6: where department_id = &a_dept_id; new 6: where department_id = 10; The average salary is 4400. PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id = &a_dept_id; 7 dbms_output.put_line('The average salary is '||v||' in department '|| &a_dept_id ||'.'); 8* end; 9 / Enter value for a_dept_id: 10 old 6: where department_id = &a_dept_id; new 6: where department_id = 10; Enter value for a_dept_id: 10 old 7: dbms_output.put_line('The average salary is '||v||' in department '|| &a_dept_id ||'.'); new 7: dbms_output.put_line('The average salary is '||v||' in department '|| 10 ||'.'); The average salary is 4400 in department 10. PL/SQL procedure successfully completed. SQL> / Enter value for a_dept_id: 10 old 6: where department_id = &a_dept_id; new 6: where department_id = 10; Enter value for a_dept_id: 50 old 7: dbms_output.put_line('The average salary is '||v||' in department '|| &a_dept_id ||'.'); new 7: dbms_output.put_line('The average salary is '||v||' in department '|| 50 ||'.'); The average salary is 4400 in department 50. PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id = &&a_dept_id; 7 dbms_output.put_line('The average salary is '||v||' in department '|| &a_dept_id ||'.'); 8* end; SQL> / Enter value for a_dept_id: 10 old 6: where department_id = &&a_dept_id; new 6: where department_id = 10; old 7: dbms_output.put_line('The average salary is '||v||' in department '|| &a_dept_id ||'.'); new 7: dbms_output.put_line('The average salary is '||v||' in department '|| 10 ||'.'); The average salary is 4400 in department 10. PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 x number; 4 begin 5 x := &a_dept_id; 6 select avg(salary) into v 7 from employees 8 where department_id = x; 9 dbms_output.put_line('The average salary is '||v||' in department '|| x ||'.'); 10* end; 11 / old 5: x := &a_dept_id; new 5: x := 10; The average salary is 4400 in department 10. PL/SQL procedure successfully completed. SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 x number; 4 begin 5 x := &a_dept_id; 6 select avg(salary) into v 7 from employees 8 where department_id = x; 9 dbms_output.put_line('The average salary is '||v||' in department '|| x ||'.'); 10* end; SQL> SQL> run 1 declare 2 v number;--used to store value returned by the select into statement 3 x number; 4 begin 5 x := &a_dept_id; 6 select avg(salary) into v 7 from employees 8 where department_id = x; 9 dbms_output.put_line('The average salary is '||v||' in department '|| x ||'.'); 10* end; old 5: x := &a_dept_id; new 5: x := 10; The average salary is 4400 in department 10. PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number;--used to store value returned by the select into statement 3 x number; 4 begin 5 x := &dept_id; 6 select avg(salary) into v 7 from employees 8 where department_id = x; 9 dbms_output.put_line('The average salary is '||v||' in department '|| x ||'.'); 10* end; SQL> / Enter value for dept_id: 20 old 5: x := &dept_id; new 5: x := 20; The average salary is 9500 in department 20. PL/SQL procedure successfully completed. SQL> run 1 declare 2 v number;--used to store value returned by the select into statement 3 x number; 4 begin 5 x := &dept_id; 6 select avg(salary) into v 7 from employees 8 where department_id = x; 9 dbms_output.put_line('The average salary is '||v||' in department '|| x ||'.'); 10* end; Enter value for dept_id: 30 old 5: x := &dept_id; new 5: x := 30; The average salary is 4150 in department 30. PL/SQL procedure successfully completed. SQL> run 1 declare 2 v number;--used to store value returned by the select into statement 3 x number; 4 begin 5 x := &dept_id; 6 select avg(salary) into v 7 from employees 8 where department_id = x; 9 dbms_output.put_line('The average salary is '||v||' in department '|| x ||'.'); 10* end; Enter value for dept_id: 8 old 5: x := &dept_id; new 5: x := 8; The average salary is in department 8. PL/SQL procedure successfully completed. SQL> --write an anonymous block that prompts the user to enter a department id from keyboard and SQL> --calculate and print the average salary in that department. SQL> --if there are no employees in that department print "no such department" SQL> --otherwise print the avergae salary in the following format SQL> --the average salary is in department . SQL> ed Wrote file afiedt.buf 1 declare 2 v_avg_sal number; 3 v_dept_id number; 4 v_count number; 5 begin 6 v_dept_id := &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_sal 12 from employees 13 where department_id = v_dept_id; 14 dbms_output.put_line('The average salary is '||v_avg_sal||' in department '|| v_dept_id ||'.'); 15 else 16 dbms_output.put_line('no employees in department '||v_dept_id||'.'); 17 end if; 18* end; 19 / Enter value for dept_id: 10 old 6: v_dept_id := &dept_id; new 6: v_dept_id := 10; The average salary is 4400 in department 10. PL/SQL procedure successfully completed. SQL> / Enter value for dept_id: 8 old 6: v_dept_id := &dept_id; new 6: v_dept_id := 8; no employees in department 8. PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_deptid number;--used to store value returned by the select into statement 3 v_count number; 4 begin 5 v_deptid := &dept_id; 6 select avg(salary) into v_count 7 from employees 8 where department_id = v_dept_id; 9 if v_count IS NULL then 10 dbms_output.put_line('no employees in department '||v_dept_id); 11 else 12 dbms_output.put_line('The average salary is '||v_count||' in department '|| v_dept_id ||'.'); 13 end if; 14* end; 15 / Enter value for dept_id: 10 old 5: v_deptid := &dept_id; new 5: v_deptid := 10; where department_id = v_dept_id; * ERROR at line 8: ORA-06550: line 8, column 26: PL/SQL: ORA-00904: "V_DEPT_ID": invalid identifier ORA-06550: line 6, column 4: PL/SQL: SQL Statement ignored ORA-06550: line 10, column 60: PLS-00201: identifier 'V_DEPT_ID' must be declared ORA-06550: line 10, column 8: PL/SQL: Statement ignored ORA-06550: line 12, column 80: PLS-00201: identifier 'V_DEPT_ID' must be declared ORA-06550: line 12, column 4: PL/SQL: Statement ignored SQL> eded SP2-0042: unknown command "eded" - rest of line ignored. SQL> ed Wrote file afiedt.buf 1 declare 2 v_dept_id number;--used to store value returned by the select into statement 3 v_count number; 4 begin 5 v_dept_id := &dept_id; 6 select avg(salary) into v_count 7 from employees 8 where department_id = v_dept_id; 9 if v_count IS NULL then 10 dbms_output.put_line('no employees in department '||v_dept_id); 11 else 12 dbms_output.put_line('The average salary is '||v_count||' in department '|| v_dept_id ||'.'); 13 end if; 14* end; SQL> / Enter value for dept_id: 10 old 5: v_dept_id := &dept_id; new 5: v_dept_id := 10; The average salary is 4400 in department 10. PL/SQL procedure successfully completed. SQL> / Enter value for dept_id: 8 old 5: v_dept_id := &dept_id; new 5: v_dept_id := 8; no employees in department 8 PL/SQL procedure successfully completed. SQL> -- find the average salary of a gıven dept without using group fuunctions SQL> select sal 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 select sal * ERROR at line 1: ORA-00904: "SAL": invalid identifier SQL> a ary 1* select salary SQL> / Enter value for x: 10 old 3: where department_id = &x new 3: where department_id = 10 SALARY ---------- 4400 SQL> / Enter value for x: 30 old 3: where department_id = &x new 3: where department_id = 30 SALARY ---------- 11000 3100 2900 2800 2600 2500 6 rows selected. SQL> l 1 select salary 2 from employees 3* where department_id = &x SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_salary is 3 select salary 4 from employees 5 where department_id = &x; 6 v_sal number; 7 v_sum number; 8 v_count number; 9 v_avg number; 10 begin 11 open cur_salary; 12 loop 13 fetch cur_salary into v_sal; 14 exit when cur_salary%notfound; 15 v_sum:=v_sum + v_sal; 16 v_count := v_count+1; 17 end loop; 18 v_avg :=v_sum/v_count; 19 dbms_output.put_line('Did we calculate correctly? is the answer '|| v_avg); 20 close cur_salary; 21* end; 22 / Enter value for x: 30 old 5: where department_id = &x; new 5: where department_id = 30; Did we calculate correctly? is the answer PL/SQL procedure successfully completed. SQL> select avg(Salary) 2 from depatrments 3 where department_id=30; from depatrments * ERROR at line 2: ORA-00942: table or view does not exist SQL> select avg(Salary) 2 from employees 3 where department_id=30; AVG(SALARY) ----------- 4150 SQL> ed Wrote file afiedt.buf 1 select avg(Salary) 2 from employees 3* where department_id=30 SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_salary is 3 select salary 4 from employees 5 where department_id = &x; 6 v_sal number; 7 v_sum number; 8 v_count number; 9 v_avg number; 10 begin 11 open cur_salary; 12 loop 13 fetch cur_salary into v_sal; 14 exit when cur_salary%notfound; 15 v_sum:=v_sum + v_sal; 16 v_count := v_count+1; 17 end loop; 18 v_avg :=v_sum/v_count; 19 dbms_output.put_line('v_count='||v_count); 20 dbms_output.put_line('v_sum='||v_sum); 21 dbms_output.put_line('Did we calculate correctly? is the answer '|| v_avg); 22 close cur_salary; 23* end; 24 / Enter value for x: 30 old 5: where department_id = &x; new 5: where department_id = 30; v_count= v_sum= Did we calculate correctly? is the answer PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_salary is 3 select salary 4 from employees 5 where department_id = &x; 6 v_sal number; 7 v_sum number; 8 v_count number; 9 v_avg number; 10 begin 11 v_count := 0; 12 v_sum :=0; 13 open cur_salary; 14 loop 15 fetch cur_salary into v_sal; 16 exit when cur_salary%notfound; 17 v_sum:=v_sum + v_sal; 18 v_count := v_count+1; 19 end loop; 20 v_avg :=v_sum/v_count; 21 dbms_output.put_line('v_count='||v_count); 22 dbms_output.put_line('v_sum='||v_sum); 23 dbms_output.put_line('Did we calculate correctly? is the answer '|| v_avg ||'?'); 24 close cur_salary; 25* end; SQL> / Enter value for x: 30 old 5: where department_id = &x; new 5: where department_id = 30; v_count=6 v_sum=24900 Did we calculate correctly? is the answer 4150? PL/SQL procedure successfully completed. SQL> --write an anonymolus block to print region name and number of coıuntries in that region SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> desc countries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> select region_name, count(*) 2 from regions r, countries c 3 where r.region_id=c.region_id 4 group by region_name; REGION_NAME COUNT(*) ------------------------- ---------- Middle East and Africa 6 Europe 8 Asia 6 Americas 5 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; 18 / Middle East and Africa--6 Europe--8 Asia--6 Americas--5 PL/SQL procedure successfully completed. SQL> spool off