An expression that selects a result and returns it into a variable. Shows the re
ID: 3747043 • Letter: A
Question
An expression that selects a result and returns it into a variable.
Shows the results of all possible combinations of two conditions.
A block of code that performs actions based on conditional tests.
Write a PL/SQL block:
A. To find the number of airports from the countries table for a supplied country_name. Based on this number, display a customized message as follows:
# Airports
Message
0–100
There are 100 or fewer airports.
101–1,000
There are between 101 and 1,000 airports.
1001–1,0000
There are between 1,001 and 10,000 airports.
> 10,000
There are more than 10,000 airports.
No value in database
The number of airports is not available for this country.
Use a CASE statement to process your comparisons.
You can use the following code to get started:
DECLARE
v_country_name countries.country_name%TYPE := '<country_name>'; v_airports countries.airports%TYPE;
BEGIN
SELECT airports INTO v_airports
FROM wf_countries
WHERE country_name = v_country_name;
CASE
WHEN ...
…
END CASE;
END;
Write a PL/SQL block:
A. To find the number of airports from the countries table for a supplied country_name. Based on this number, display a customized message as follows:
# Airports
Message
0–100
There are 100 or fewer airports.
101–1,000
There are between 101 and 1,000 airports.
1001–1,0000
There are between 1,001 and 10,000 airports.
> 10,000
There are more than 10,000 airports.
No value in database
The number of airports is not available for this country.
Use a CASE statement to process your comparisons.
You can use the following code to get started:
DECLARE
v_country_name countries.country_name%TYPE := '<country_name>'; v_airports countries.airports%TYPE;
BEGIN
SELECT airports INTO v_airports
FROM wf_countries
WHERE country_name = v_country_name;
CASE
WHEN ...
…
END CASE;
END;
Test your code for the following countries and confirm the results.
No coastline
Small coastline
Mid-range coastline
Large coastline
Canada
X
Grenada
X
Jamaica
X
Japan
X
Mongolia
X
Ukraine
X
3.Use a CASE statement:
Write a PL/SQL block to select the number of countries using a supplied currency name. If the number of countries is greater than 20, display “More than 20 countries”. If the number of countries is between 10 and 20, display “Between 10 and 20 countries”. If the number of countries is less than 10, display “Fewer than 10 countries”. Use a CASE statement.
Test your code using the following data:
Fewer than 10 countries
Between 10 and 20 countries
More than 20 countries
US Dollar
X
Swiss franc
X
Euro
X
4.Examine the following code.
What do you think the output will be? Test your prediction by running the code.
DECLARE x BOOLEAN := FALSE; y BOOLEAN;
v_color VARCHAR(20) := 'Red';
BEGIN
IF (x OR y)
THEN v_color := 'White'; ELSE
v_color := 'Black';
END IF;
DBMS_OUTPUT.PUT_LINE(v_color);
END;
Change the declarations to x and y as follows. What do you think the output will be? Test your prediction by running the code again.
xBOOLEAN ; y BOOLEAN ;
Change the declarations to x and y as follows. What do you think the output will be? Test your prediction by running the code again.
xBOOLEAN := TRUE; y BOOLEAN := TRUE;
Experiment with changing the OR condition to AND.
# Airports
Message
0–100
There are 100 or fewer airports.
101–1,000
There are between 101 and 1,000 airports.
1001–1,0000
There are between 1,001 and 10,000 airports.
> 10,000
There are more than 10,000 airports.
No value in database
The number of airports is not available for this country.
Explanation / Answer
----------------------A--------------------
DECLARE
v_country_name countries.country_name%TYPE := 'Canada';
v_airports countries.airports%TYPE;
BEGIN
SELECT airports INTO v_airports FROM countries
WHERE country_name = v_country_name;
CASE
WHEN v_airports>=0 and v_airports<=100 then DBMS_OUTPUT.PUT_LINE('There are 100 or fewer airports.');
WHEN v_airports>=101 and v_airports<=1000 then DBMS_OUTPUT.PUT_LINE('There are between 101 and 1,000 airports.');
WHEN v_airports>=1001 and v_airports<=10000 then DBMS_OUTPUT.PUT_LINE('There are between 1,001 and 10,000 airports.');
WHEN v_airports>10000 then DBMS_OUTPUT.PUT_LINE('There are more than 10,000 airports.');
ELSE DBMS_OUTPUT.PUT_LINE('The number of airports is not available for this country.');
END CASE;
END;
----------------------3--------------------
DECLARE
v_currency_name currencies.currency_name%TYPE :='Euro';
v_contar number;
v_currency_description varchar2(50);
BEGIN
select count(countries.country_id) into v_contar from
currencies inner join countries
on currencies.currency_code=countries.currency_code
where currencies.currency_name=v_currency_name;
v_currency_description :=
CASE
WHEN v_contar<10 then 'Fewer than 10 countries'
WHEN v_contar>=10 and v_contar<=20 then 'Between 10 and 20 countries'
WHEN v_contar>20 then 'More than 20 countries'
END CASE;
DBMS_OUTPUT.PUT_LINE('Country: '||v_currency_name||' Number of countries: '||v_contar||' and Currency: '||v_currency_description);
END;
----------------------4--------------------
What do you think the output will be? Test your prediction by running the code.
DECLARE
x BOOLEAN := FALSE;
y BOOLEAN;
v_color VARCHAR(20) := 'Red';
BEGIN
IF (x OR y)
THEN v_color := 'White';
ELSE
v_color := 'Black';
END IF;
DBMS_OUTPUT.PUT_LINE(v_color);
END;
----Result: Black
Change the declarations to x and y as follows. What do you think the output will be? Test your prediction by running the code again.
xBOOLEAN ;
y BOOLEAN ;
----Result: Black
Change the declarations to x and y as follows. What do you think the output will be? Test your prediction by running the code again.
xBOOLEAN := TRUE;
y BOOLEAN := TRUE;
----Result: White
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.