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 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 22966287730 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. Copyright 202020 , Onade and/or its affliates. All rights reserved. Oracie and lava are registered trademarks of Oracie and/or its affesates. Other names may be trademarks of their respective awners. Your output should look something like this (only the first two regions are shown): 13 Central America Belize 22966287730 -. English Republic of Costa Rica 511004075261 .. Spanish Republic of El Salvador 210406822378 Republic of Guatemala 10889012293545 Republic of Honduras 1120907326496 Republic of Nicaragua 1294945570129 ... Spanish Republic of Panama 782003191319 - Spanish United Mexican States 1972550107449525 21 North America Bermuda 5365773 -. English Canada 998467033098932 -. English ... French Greenland 216608656361 Territorial Collectivity of Saint Pierre and Miquelon 2427026 .. French United States of America 9631420298444215 -. English
