SQL> --run the script file college.sql SQL> @d:\college.sql Table dropped. DROP TABLE FACULTY CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE COURSE CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE CRSSECTION CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE REGISTRATION CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE ROOM CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE DEPARTMENT CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE LOCATION CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE TERM CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist DROP TABLE MAJOR CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist Table created. Table created. Table created. Table created. Table created. Table created. Table created. Table created. Table created. Table created. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. Table altered. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. Table altered. Table altered. 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> select * from faculty 2 : 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> desc registration Name Null? Type ----------------------------------------- -------- ---------------------------- STUDENTID NOT NULL CHAR(5) CSID NOT NULL NUMBER(4) MIDTERM CHAR(1) FINAL CHAR(1) REGSTATUS CHAR(1) SQL> desc department Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTID NOT NULL NUMBER(1) DEPTNAME VARCHAR2(20) FACULTYID NUMBER(3) SQL> desc location Name Null? Type ----------------------------------------- -------- ---------------------------- ROOMID NOT NULL NUMBER(2) BUILDING NOT NULL VARCHAR2(7) ROOMNO NOT NULL CHAR(3) CAPACITY NUMBER(2) ROOMTYPE CHAR(1) SQL> desc term Name Null? Type ----------------------------------------- -------- ---------------------------- TERMID NOT NULL CHAR(4) TERMDESC VARCHAR2(11) STARTDATE DATE ENDDATE DATE SQL> desc major Name Null? Type ----------------------------------------- -------- ---------------------------- MAJORID NOT NULL NUMBER(3) MAJORDESC VARCHAR2(25) SQL> --lets check the contents of the faculty table SQL> select * from faculty; 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 anonympous block that accepts a deptid from keyboard and lists the name of the SQL> --faculty member who works in that department SQL> sho ti time OFF SQL> sho ti on time OFF SP2-0158: unknown SHOW option "on" SQL> show time time OFF SQL> set time on 09:20:06 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('Name is '||v_name) 10* end; 09:30:49 11 / Enter value for deptid: 5 old 5: v_deptid := &deptid; new 5: v_deptid := 5; where deptid := v_deptid; * ERROR at line 8: ORA-06550: line 8, column 15: PL/SQL: ORA-00920: invalid relational operator ORA-06550: line 6, column 2: PL/SQL: SQL Statement ignored ORA-06550: line 10, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "END" to continue. 09:30:56 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('Name is '||v_name) 10* end; 09:31:47 SQL> / Enter value for deptid: 5 old 5: v_deptid := &deptid; new 5: v_deptid := 5; end; * ERROR at line 10: ORA-06550: line 10, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "END" to continue. 09:31:50 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('Name is '||v_name); 10* end; 09:32:13 11 09:32:22 12 / Enter value for deptid: 5 old 5: v_deptid := &deptid; new 5: v_deptid := 5; PL/SQL procedure successfully completed. 09:32:37 SQL> set serveroutput on 09:32:52 SQL> set time off SQL> / Enter value for deptid: 5 old 5: v_deptid := &deptid; new 5: v_deptid := 5; Name is Chang PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 3 old 5: v_deptid := &deptid; new 5: v_deptid := 3; declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6 SQL> set veri off SQL> / Enter value for deptid: 3 declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6 SQL> / Enter value for deptid: 77 declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 5 SQL> / Enter value for deptid: 9 declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6 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('Name is '||v_name); 10 exception 11 when no_data_found then 12 dbms_output.put_line('no such deprtment'); 13 when too_many_rows then 14 dbms_output.put_line('Saleh'); 15* end; 16 / Enter value for deptid: 5 Name is Chang PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 3 Saleh PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 9 no such deprtment 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('Name is '||v_name); 10 exception 11 when no_data_found then 12 dbms_output.put_line('no such deprtment'); 13 when too_many_rows then 14 dbms_output.put_line('Saleh'); 15 when others then 16 dbms_output.put_line('some error occured'); 17* end; SQL> / Enter value for deptid: 5 Name is Chang PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 3 Saleh PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 9 no such deprtment PL/SQL procedure successfully completed. SQL> / Enter value for deptid: 99 some error occured PL/SQL procedure successfully completed. SQL> set veri on SQL> / Enter value for deptid: 5 old 5: v_deptid := &deptid; new 5: v_deptid := 5; Name is Chang PL/SQL procedure successfully completed. SQL> set verify off SQL> / Enter value for deptid: 5 Name is Chang PL/SQL procedure successfully completed. SQL> spool off