Modify the table COUNTRIES by adding a new column called FLAG of fixed character length (10 characters).

Write the PL/SQL block so that will as input accept Region Id (check table REGIONS for valid values) and then it will find all Countries with empty Location information (without City listed in that country). Then it will modify blank value in the column Flag for all such countries to become EMPTY_n (where n is their Region Id) .

Then, it will display message about all countries with NO city listed for the provided Region Id in the alphabetical order including a small comment (figure out by checking the testing outputs). Finally it will show their total count.

Show also all modified rows from Countries table sorted by Region Id and then by country name and finally Undo your change.

You need also to code for the case: the provided Region does NOT exist.

Hint: Use Cursor For Loop and do NOT use Exception handler.

 

Here are the possible scenarios:

SQL> DESC countries

Name Null? Type

----------------------------------------- -------- -------------------------

COUNTRY_ID NOT NULL CHAR(2)

COUNTRY_NAME NOT NULL VARCHAR2(40)

REGION_ID NUMBER

FLAG VARCHAR2(10)

 

SQL> @asgn2;

Enter value for region: 5

This region ID does NOT exist: 5

 

PL/SQL procedure successfully completed.

 

no rows selected

Rollback complete.

 

SQL> @asgn2;

Enter value for region: 1

In the region 1 there is country Belgium with NO city.

Cool!

In the region 1 there is country Denmark with NO city.

Cool!

In the region 1 there is country France with NO city.

This country name ends on E!

=============================================================================

Total Number of countries with NO cities listed in the Region 1 is: 3

 

PL/SQL procedure successfully completed.

 

CO COUNTRY_NAME REGION_ID FLAG

-- ---------------------------------------- ---------- ----------

BE Belgium 1 Empty_1

DK Denmark 1 Empty_1

FR France 1 Empty_1

AR Argentina 2 Empty_2

ML Malaysia 3 Empty_3

EG Egypt 4 Empty_4

IL Israel 4 Empty_4

KW Kuwait 4 Empty_4

NG Nigeria 4 Empty_4

ZM Zambia 4 Empty_4

ZW Zimbabwe 4 Empty_4

 

11 rows selected.

 

Rollback complete.

 

SQL> @asgn2;

Enter value for region: 2

In the region 2 there is country Argentina with NO city.

This country name ends on A!

=============================================================================

Total Number of countries with NO cities listed in the Region 2 is: 1

 

PL/SQL procedure successfully completed.

 

CO COUNTRY_NAME REGION_ID FLAG

-- ---------------------------------------- ---------- ----------

BE Belgium 1 Empty_1

DK Denmark 1 Empty_1

FR France 1 Empty_1

AR Argentina 2 Empty_2

ML Malaysia 3 Empty_3

EG Egypt 4 Empty_4

IL Israel 4 Empty_4

KW Kuwait 4 Empty_4

NG Nigeria 4 Empty_4

ZM Zambia 4 Empty_4

ZW Zimbabwe 4 Empty_4

 

11 rows selected.

 

Rollback complete.

 

SQL> @asgn2;

Enter value for region: 4

In the region 4 there is country Egypt with NO city.

Cool!

In the region 4 there is country Israel with NO city.

Cool!

In the region 4 there is country Kuwait with NO city.

Cool!

In the region 4 there is country Nigeria with NO city.

This country name ends on A!

In the region 4 there is country Zambia with NO city.

This country name ends on A!

In the region 4 there is country Zimbabwe with NO city.

This country name ends on E!

=============================================================================

Total Number of countries with NO cities listed in the Region 4 is: 6

 

PL/SQL procedure successfully completed.

 

CO COUNTRY_NAME REGION_ID FLAG

-- ---------------------------------------- ---------- ----------

BE Belgium 1 Empty_1

DK Denmark 1 Empty_1

FR France 1 Empty_1

AR Argentina 2 Empty_2

ML Malaysia 3 Empty_3

EG Egypt 4 Empty_4

IL Israel 4 Empty_4

KW Kuwait 4 Empty_4

NG Nigeria 4 Empty_4

ZM Zambia 4 Empty_4

ZW Zimbabwe 4 Empty_4

 

11 rows selected.

 

Rollback complete.