Pdfcoffee.com Plsql56practice 3 PDF Free

www.oracle.com/academy

Database Programming with PL/SQL 5-6: Using Multiple Cursors Practice Activities Vocabulary No new vocabulary for this lesson Try It / Solve It 1. Write and run a PL/SQL block which produces a listing of departments and their employees. Use the DEPARTMENTS and EMPLOYEES tables. In a cursor FOR loop, retrieve and display the department_id and department_name for each department, and display a second line containing '----------' as a separator. In a nested cursor FOR loop, retrieve and display the first_name, last_name, and salary of each employee in that department, followed by a blank line at the end of each department. Order the departments by department_id, and the employees in each department by last_name. You will need to declare two cursors, one to fetch and display the departments, the second to fetch and display the employees in that department, passing the department_id as a parameter. Your output should look something like this (only the first few departments are shown): 10 Administration ----------------------------Jennifer Whalen 4400 20 Marketing ----------------------------Pat Fay 6000 Michael Hartstein 13000 50 Shipping ----------------------------Curtis Davies 3400 Randall Matos 2600 Kevin Mourgos 5800 Trenna Rajs 3500 Peter Vargas 2500

DECLARE CURSOR dept_cursor IS SELECT department_id,department_name FROM departments ORDER BY department_id; CURSOR emp_cursor(v_deptno departments.department_id%TYPE) IS SELECT first_name, last_name, salary FROM employees WHERE department_id = v_deptno ORDER BY last_name; BEGIN FOR dept_record IN dept_cursor LOOP DBMS_OUTPUT.PUT_LINE(dept_record.department_id ||' ' || dept_record.department_name); DBMS_OUTPUT.PUT_LINE('-----------------------------'); DBMS_OUTPUT.PUT_LINE(' '); END LOOP; END;

2. Write and run a PL/SQL block which produces a report listing world regions, countries in those regions, and the land area and population for each country. You will need two cursors: an outer loop cursor which fetches and displays rows from the REGIONS table, and an inner loop cursor which fetches and displays rows from the COUNTRIES table for countries in that region, passing the region_id as a parameter. Restrict your regions to those in the Americas (region_name like ‘%America%’). Order your output by region_name, and by country_name within each region. Your output should look something like this (only the first two regions are shown): 13 Central America ----------------------------Belize 22966 287730 Republic of Costa Rica 51100 4075261 Republic of El Salvador 21040 6822378 Republic of Guatemala 108890 12293545 Republic of Honduras 112090 7326496 Republic of Nicaragua 129494 5570129 Republic of Panama 78200 3191319 United Mexican States 1972550 107449525

21 North America ----------------------------Bermuda 53 65773 Canada 9984670 33098932 Greenland 2166086 56361 Territorial Collectivity of Saint Pierre and Miquelon 242 7026 United States of America 9631420 298444215

DECLARE CURSOR region_curs IS SELECT region_id, region_name FROM world_regions WHERE region_name like '%America% 'ORDER BY region_name; CURSOR country_curs(p_region_id world_regions.region_id%TYPE) IS SELECT country_name, area, population FROM countries WHERE region_id = p_region_id ORDER BY country_name; BEGIN FOR region_rec IN region_curs LOOP DBMS_OUTPUT.PUT_LINE(region_rec.region_id ||' ' || region_rec.region_name); DBMS_OUTPUT.PUT_LINE(FOR country_rec IN country_curs(region_rec.region_id) LOOP DBMS_OUTPUT.PUT_LINE(country_rec.country_name || ' ' ||country_rec.area || ' ' ||country_rec.population); END LOOP;DBMS_OUTPUT.PUT_LINE(' '); END LOOP; END

3. Modify your block from question 2 to display the names of official spoken languages in each country. You will need three cursors and three loops. The first two cursors should fetch and display regions and countries, as in question 2. The innermost loop should accept a country_id as a parameter, and fetch and display the name of each official language, using a join of the SPOKEN_LANGUAGES table and the LANGUAGES table. Within each country, the languages should be ordered by language_name. Test your block, restricting regions to those in the Americas.

Your output should look something like this (only the first two regions are shown): 13 Central America ----------------------------Belize 22966 287730 --- English Republic of Costa Rica 51100 4075261 --Spanish Republic of El Salvador 21040 6822378 Republic of Guatemala 108890 12293545 Republic of Honduras 112090 7326496 Republic of Nicaragua 129494 5570129 --- Spanish Republic of Panama 78200 3191319 --- Spanish United Mexican States 1972550 107449525 21 North America ----------------------------Bermuda 53 65773 --- English Canada 9984670 33098932 --- English --- French Greenland 2166086 56361 Territorial Collectivity of Saint Pierre and Miquelon 242 7026 --- French United States of America 9631420 298444215 --English

DECLARE CURSOR region_curs IS SELECT region_id, region_name FROM world_regions WHERE region_name LIKE '%America%' ORDER BY region_name; CURSOR country_curs(p_region_id world_regions.region_id%TYPE) IS SELECT country_id, country_name, area, population FROM countries WHERE region_id = p_region_id ORDER BY country_name; CURSOR language_curs(p_country_id countries.country_id%TYPE) IS SELECT language_name FROM spoken_languages sl, languages l WHERE sl.language_id = l.language_id AND official = 'Y' AND country_id = p_country_id ORDER BY language_name; BEGIN FOR region_rec IN region_curs LOOP DBMS_OUTPUT.PUT_LINE(region_rec.region_i d ||' ' || region_rec.region_name); DBMS_OUTPUT.PUT_LINE('----------------------------'); FOR country_rec IN country_curs(region_rec.region_id) LOOP DBMS_OUTPUT.PUT_LINE (country_rec.country_name || ' ' ||country_rec.area || ' ' ||country_rec.population); FOR language_rec IN language_curs(country_rec.country_id) LOOP DBMS_OUTPUT.PUT_LINE('--- ' ||language_rec.language_name); END LOOP; END LOOP; DBMS_OUTPUT.PUT_LINE(' '); END LOOP; END; ;