SQL> rem write an anonymous block that will read a department number from keyboard and prưnt the last name of all employees in that department sorted alphabetically 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> select LAST_NAME 2 from employees 3 where department_id =&a ed Wrote file afiedt.buf 1 select LAST_NAME 2 from employees 3 where department_id =&any_department_id 4* order by last_name SQL> / Enter value for any_department_id: 10 old 3: where department_id =&any_department_id new 3: where department_id =10 LAST_NAME ------------------------- Whalen SQL> / Enter value for any_department_id: 20 old 3: where department_id =&any_department_id new 3: where department_id =20 LAST_NAME ------------------------- Fay Hartstein SQL> ed Wrote file afiedt.buf 1 declare 2 cursor tomiwa is 3 select salary,LAST_NAME 4 from employees 5 where department_id =&any_department_id 6 order by last_name; 7 v_last_name employees.last_name%type; 8 v_salary employees.salary%type; 9 begin 10 open tomiwa; 11 loop 12 fetch tomiwa into v_salary,v_last_name; 13 exit when tomiwa%notfound; 14 dbms_output.put_line(v_last_name||' '||v_salary); 15 end loop; 16 close tomiwa; 17* end; 18 / Enter value for any_department_id: 10 old 5: where department_id =&any_department_id new 5: where department_id =10 PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Enter value for any_department_id: 10 old 5: where department_id =&any_department_id new 5: where department_id =10 Whalen 4400 PL/SQL procedure successfully completed. SQL> / Enter value for any_department_id: 20 old 5: where department_id =&any_department_id new 5: where department_id =20 Fay 6000 Hartstein 13000 PL/SQL procedure successfully completed. SQL> --2-list name of 10 highest paid employees SQL> select first_name,last_name ,salary 2 from employees 3 concat "." (hex 2e) pagesize 14 pagesize 14 linesize 80 order by salary; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- TJ Olson 2100 Steven Markle 2200 Hazel Philtanker 2200 James Landry 2400 Ki Gee 2400 Karen Colmenares 2500 James Marlow 2500 Joshua Patel 2500 Peter Vargas 2500 Martha Sullivan 2500 Randall Perkins 2500 Guy Himuro 2600 Randall Matos 2600 Donald OConnell 2600 Douglas Grant 2600 Irene Mikkilineni 2700 John Seo 2700 Sigal Tobias 2800 Mozhe Atkinson 2800 Girard Geoni 2800 Vance Jones 2800 Shelli Baida 2900 Michael Rogers 2900 Timothy Gates 2900 Anthony Cabrio 3000 Kevin Feeney 3000 Alexander Khoo 3100 Curtis Davies 3100 Jean Fleaur 3100 Alana Walsh 3100 Julia Nayer 3200 Stephen Stiles 3200 Winston Taylor 3200 Samuel McCain 3200 Laura Bissot 3300 Jason Mallin 3300 Julia Dellinger 3400 Trenna Rajs 3500 Renske Ladwig 3600 Jennifer Dilly 3600 Kelly Chung 3800 Britney Everett 3900 Sarah Bell 4000 Alexis Bull 4100 Diana Lorentz 4200 Nandita Sarchand 4200 Jennifer Whalen 4400 David Austin 4800 Valli Pataballa 4800 Kevin Mourgos 5800 Bruce Ernst 6000 Pat Fay 6000 Sundita Kumar 6100 Amit Banda 6200 Charles Johnson 6200 Sundar Ande 6400 Shanta Vollman 6500 Susan Mavris 6500 David Lee 6800 Luis Popp 6900 Oliver Tuvault 7000 Sarath Sewall 7000 Kimberely Grant 7000 Mattea Marvins 7200 Elizabeth Bates 7300 William Smith 7400 Nanette Cambrault 7500 Louise Doran 7500 Ismael Sciarra 7700 Jose Manuel Urman 7800 Payam Kaufling 7900 Matthew Weiss 8000 Christopher Olsen 8000 Lindsey Smith 8000 John Chen 8200 Adam Fripp 8200 William Gietz 8300 Jack Livingston 8400 Jonathon Taylor 8600 Alyssa Hutton 8800 Alexander Hunold 9000 Daniel Faviet 9000 Peter Hall 9000 Allan McEwen 9000 Patrick Sully 9500 Danielle Greene 9500 David Bernstein 9500 Tayler Fox 9600 Hermann Baer 10000 Harrison Bloom 10000 Janette King 10000 Peter Tucker 10000 Clara Vishney 10500 Eleni Zlotkey 10500 Gerald Cambrault 11000 Den Raphaely 11000 Ellen Abel 11000 Lisa Ozer 11500 Alberto Errazuriz 12000 Shelley Higgins 12008 Nancy Greenberg 12008 Michael Hartstein 13000 Karen Partners 13500 John Russell 14000 Lex De Haan 17000 Neena Kochhar 17000 Steven King 24000 107 rows selected. SQL> l 1 select first_name,last_name ,salary 2 from employees 3* order by salary SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select first_name,last_name ,salary 4 from employees 5 order by salary desc; 6 v_firSt_nAME EMPLOYEES.FIRST_NAME%TYPE; 7 v_LAST_nAME EMPLOYEES.LAST_NAME%TYPE; 8 v_SALARY EMPLOYEES.SALARY%TYPE; 9 begin 10 OPEN cur_elist; 11 LOOP 12 FETCH CUR_ELIST INTO V_FIRST_NAME,V_LAST_NAME,V_SALARY; 13 EXIT WHEN CUR_ELIST%ROWCOUNT>10 ; 14 DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME||V_LAST_NAME||V_SALARY); 15 END LOOP; 16 CLOSE CUR_ELIST; 17* end; 18 / StevenKing24000 NeenaKochhar17000 LexDe Haan17000 JohnRussell14000 KarenPartners13500 MichaelHartstein13000 NancyGreenberg12008 ShelleyHiggins12008 AlbertoErrazuriz12000 LisaOzer11500 PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select first_name,last_name ,salary 4 from employees 5 WHERE DEPARTMENT_ID=20 6 order by salary desc; 7 v_firSt_nAME EMPLOYEES.FIRST_NAME%TYPE; 8 v_LAST_nAME EMPLOYEES.LAST_NAME%TYPE; 9 v_SALARY EMPLOYEES.SALARY%TYPE; 10 begin 11 OPEN cur_elist; 12 LOOP 13 FETCH CUR_ELIST INTO V_FIRST_NAME,V_LAST_NAME,V_SALARY; 14 EXIT WHEN CUR_ELIST%ROWCOUNT>10 ; 15 DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME||V_LAST_NAME||V_SALARY); 16 END LOOP; 17 CLOSE CUR_ELIST; 18* end; 19 / MichaelHartstein13000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 PatFay6000 declare * ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ORA-06512: at "SYS.DBMS_OUTPUT", line 97 ORA-06512: at "SYS.DBMS_OUTPUT", line 112 ORA-06512: at line 15 SQL> ED Wrote file afiedt.buf 1 declare 2 cursor cur_elist is 3 select first_name,last_name ,salary 4 from employees 5 WHERE DEPARTMENT_ID=20 6 order by salary desc; 7 v_firSt_nAME EMPLOYEES.FIRST_NAME%TYPE; 8 v_LAST_nAME EMPLOYEES.LAST_NAME%TYPE; 9 v_SALARY EMPLOYEES.SALARY%TYPE; 10 begin 11 OPEN cur_elist; 12 LOOP 13 FETCH CUR_ELIST INTO V_FIRST_NAME,V_LAST_NAME,V_SALARY; 14 EXIT WHEN CUR_ELIST%ROWCOUNT>10 OR CUR_ELIST%NOTFOUND; 15 DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME||V_LAST_NAME||V_SALARY); 16 END LOOP; 17 CLOSE CUR_ELIST; 18* end; SQL> / MichaelHartstein13000 PatFay6000 PL/SQL procedure successfully completed. SQL> SPOOL OFF