SQL> desc student Name Null? Type ----------------------------------------- -------- ---------------------------- STUDENTID NOT NULL CHAR(5) LAST NOT NULL VARCHAR2(15) FIRST NOT NULL VARCHAR2(15) STREET VARCHAR2(25) CITY VARCHAR2(15) STATE CHAR(2) ZIP CHAR(5) STARTTERM CHAR(4) BIRTHDATE DATE FACULTYID NUMBER(3) MAJORID NUMBER(3) PHONE CHAR(10) SQL> desc faculty Name Null? Type ----------------------------------------- -------- ---------------------------- FACULTYID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(15) ROOMID NUMBER(2) PHONE CHAR(3) DEPTID NUMBER(1) SQL> desc department Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTID NOT NULL NUMBER(1) DEPTNAME VARCHAR2(20) FACULTYID NUMBER(3) SQL> desc course Name Null? Type ----------------------------------------- -------- ---------------------------- COURSEID NOT NULL VARCHAR2(6) TITLE VARCHAR2(20) CREDITS NUMBER(1) PREREQ VARCHAR2(6) SQL> desc crssection Name Null? Type ----------------------------------------- -------- ---------------------------- CSID NOT NULL NUMBER(4) COURSEID NOT NULL VARCHAR2(6) SECTION NOT NULL CHAR(2) TERMID NOT NULL CHAR(4) FACULTYID NUMBER(3) DAY VARCHAR2(2) STARTTIME VARCHAR2(5) ENDTIME VARCHAR2(5) ROOMID NUMBER(2) MAXCOUNT NUMBER(2) SQL> select * from faculty 2 ; FACULTYID NAME ROOMID PHO DEPTID ---------- --------------- ---------- --- ---------- 111 Jones 11 525 1 222 Williams 20 533 2 123 Mobley 11 529 1 235 Vajpayee 12 577 2 345 Sen 12 579 3 444 Rivera 21 544 4 555 Chang 17 587 5 333 Collins 17 599 3 8 rows selected. SQL> --write an anonymous block that will accept a deptıd from keyboard and dısplay name of the facu SQL> --faculty member in that department SQL> select name 2 from faculty 3 where deptid=&a_department_id; Enter value for a_department_id: 5 old 3: where deptid=&a_department_id new 3: where deptid=5 NAME --------------- Chang SQL> ed Wrote file afiedt.buf 1 select name 2 from faculty 3* where deptid=&a_department_id SQL> ed Wrote file afiedt.buf 1 declare 2 v_name faculty.name%type; 3 begin 4 select name into v_name 5 from faculty 6 where deptid=&a_department_id 7 dbms_output.put_line(v_name); 8* end; 9 / Enter value for a_department_id: 5 old 6: where deptid=&a_department_id new 6: where deptid=5 dbms_output.put_line(v_name); * ERROR at line 7: ORA-06550: line 7, column 9: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 4, column 2: PL/SQL: SQL Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_name faculty.name%type; 3 begin 4 select name into v_name 5 from faculty 6 where deptid=&a_department_id; 7 dbms_output.put_line(v_name); 8* end; SQL> / Enter value for a_department_id: 5 old 6: where deptid=&a_department_id; new 6: where deptid=5; PL/SQL procedure successfully completed. SQL> set verify off SQL> / Enter value for a_department_id: 5 PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 3 declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 SQL> / Enter value for a_department_id: 9 declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 SQL> ed Wrote file afiedt.buf 1 declare 2 v_name faculty.name%type; 3 v_deptid faculty.deptid%type; 4 begin 5 v_deptid := &a_departmentid; 6 select name into v_name 7 from faculty 8 where deptid=v_deptid; 9 dbms_output.put_line(v_name); 10 exception 11 when no_data_found then 12 dbms_output.put_line('so such department'); 13 when too_many_rows then 14 dbms_output.put_line('too many employees'); 15* end; 16 / Enter value for a_departmentid: 5 PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> clear screen SQL> / Enter value for a_departmentid: 5 Chang PL/SQL procedure successfully completed. SQL> / Enter value for a_departmentid: 3 too many employees PL/SQL procedure successfully completed. SQL> / Enter value for a_departmentid: 9 so such department PL/SQL procedure successfully completed. SQL> select * from faculty 2 ; FACULTYID NAME ROOMID PHO DEPTID ---------- --------------- ---------- --- ---------- 111 Jones 11 525 1 222 Williams 20 533 2 123 Mobley 11 529 1 235 Vajpayee 12 577 2 345 Sen 12 579 3 444 Rivera 21 544 4 555 Chang 17 587 5 333 Collins 17 599 3 8 rows selected. SQL> ed Wrote file afiedt.buf 1 declare 2 v_name faculty.name%type; 3 v_deptid faculty.deptid%type; 4 begin 5 v_deptid := &deptid; 6 select name into v_name 7 from faculty 8 where deptid = v_deptid; 9 dbms_output.put_line(v_nAME); 10 EXCEPTION 11 when no_data_found then 12 dbms_output.put_line('no one'); 13 when too_many_rows then 14 dbms_output.put_line('too many'); 15* end; 16 / Enter value for deptid: 5 Chang PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 2 too many PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 9 no one PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 99 declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 5 SQL> ed Wrote file afiedt.buf 1 declare 2 v_name faculty.name%type; 3 v_deptid faculty.deptid%type; 4 begin 5 v_deptid := &deptid; 6 select name into v_name 7 from faculty 8 where deptid = v_deptid; 9 dbms_output.put_line(v_nAME); 10 EXCEPTION 11 when no_data_found then 12 dbms_output.put_line('no one'); 13 when too_many_rows then 14 dbms_output.put_line('too many'); 15 when others then 16 dbms_output.put_line('somethin is wronggg'); 17* end; 18 / Enter value for deptid: 5 Chang PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 3 too many PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 9 no one PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 99 somethin is wronggg PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_name faculty.name%type; 3 v_deptid faculty.deptid%type; 4 begin 5 v_deptid := &deptid; 6 select name into v_name 7 from faculty 8 where deptid = v_deptid; 9 dbms_output.put_line(v_nAME); 10 EXCEPTION 11 when no_data_found then 12 dbms_output.put_line('no one'); 13 when too_many_rows then 14 dbms_output.put_line('too many'); 15 when others then 16 dbms_output.put_line('somethin is wronggg'); 17* end; SQL> ed Wrote file afiedt.buf 1 declare 2 cursor faculty_list is 3 select name 4 from faculty; 5 v_name faculty.name%type; 6 begin 7 open faculty_list; 8 loop 9 fetch faculty_list into v_name; 10 exit when faculty_list%notfound; 11 dbms_output.put_line(v_nAME); 12 end loop; 13 close faculty_list; 14* end; 15 / Jones Williams Mobley Vajpayee Sen Rivera Chang Collins PL/SQL procedure successfully completed. SQL> spool off