Ques-2

1. Write a PL/SQL block to produce a list of available vehicle license plate numbers. These numbers must be in the following format: NN-MMM, where NN is between 60 and 65 , and MMM is between 100 and 110. Use nested FOR loops. The outer loop should choose numbers between 60 and 65. The inner loop should choose numbers between 100 and 110, and concatenate the two numbers together. 2. Modify your block from question 1 to calculate the sum of the two numbers on each iteration of the inner loop (for example, 62-107 sums to 169), and exit from the OUTER loop if the sum of the two numbers is greater than 172 . Use loop labels. Test your modified code. Vocabulary Identify the vocabulary word for each definition below: Try It / Solve It 1. In your own words, explain the difference between implicit and explicit cursors. 2. Which SQL statement can use either an explicit or an implicit cursor, as needed? 3. List two circumstances in which you would use an explicit cursor. 4. Exercise using CURRENCIES tables: A. Write a PL/SQL block to declare a cursor called currencies_cur. The cursor will be used to read and display all rows from the CURRENCIES table. You will need to retrieve currency_code and currency_name, ordered by ascending currency_name. B. Add a statement to open the currencies_cur cursor. C. Add variable declarations and an executable statement to read ONE row through the currencies_cur cursor into local variables. D. Add a statement to display the fetched row, and a statement to close the currencies_cur cursor. E. Run your block to confirm that it works. It should display: AFA Afghani. F. Your code so far displays only one row. Modify your code so that it fetches and displays all the rows, using a LOOP and EXIT statement. Test your modified block. It should fetch and display each row in the CURRENCIES table. If it doesn't, check that your EXIT statement is in the correct place in the code. G. Write and test a PL/SQL block to read and display all the rows in the COUNTRIES table for all countries in region 5 (South America region). For each selected country, display the country_name, national_holiday_date, and national_holiday_name. Display only those countries having a national holiday date that is not null. Save your code (you will need it in the next practice). 5. Identify three guidelines for declaring and using explicit cursors. 6. Write a PL/SQL block to read and display the names of world regions, with a count of the number of countries in each region. Include only those regions having at least 10 countries. Order your output by ascending region name