CREATE TABLE world_region ( PARENT_NAME VARCHAR2(30) NOT NULL, CHILD_NAME VARCHA

ID: 3826968 • Letter: C

Question

CREATE TABLE world_region

(

PARENT_NAME VARCHAR2(30) NOT NULL,

CHILD_NAME VARCHAR2(30) NOT NULL

);

INSERT INTO world_region VALUES('***','Asia');

INSERT INTO world_region VALUES('***','Australia');

INSERT INTO world_region VALUES('***','Europe');

INSERT INTO world_region VALUES('***','North America');

INSERT INTO world_region VALUES('Asia','China');

INSERT INTO world_region VALUES('Asia','Japan');

INSERT INTO world_region VALUES('Australia','New South Wales');

INSERT INTO world_region VALUES('New South Wales','Sydney');

INSERT INTO world_region VALUES('Canada','Ontario');

INSERT INTO world_region VALUES('China','Beijing');

INSERT INTO world_region VALUES('England','London');

INSERT INTO world_region VALUES('Europe','United Kingdom');

INSERT INTO world_region VALUES('Illinois','Aurora');

INSERT INTO world_region VALUES('Illinois','Cook County');

INSERT INTO world_region VALUES('Illinois','Rockford');

INSERT INTO world_region VALUES('Wisconsin','Madison');

INSERT INTO world_region VALUES('Japan','Osaka');

INSERT INTO world_region VALUES('Japan','Tokyo');

INSERT INTO world_region VALUES('North America','Canada');

INSERT INTO world_region VALUES('North America','United States');

INSERT INTO world_region VALUES('Ontario','Ottawa');

INSERT INTO world_region VALUES('Ontario','Toronto');

INSERT INTO world_region VALUES('United States','Colorado');

INSERT INTO world_region VALUES('United States','Illinois');

INSERT INTO world_region VALUES('United States','Texas');

INSERT INTO world_region VALUES('United Kingdom','England');

INSERT INTO world_region VALUES('Texas','Rockford');

INSERT INTO world_region VALUES('Colorado','Aurora');

INSERT INTO world_region VALUES('Cook County','Chicago');

COMMIT;

Based on the REGION table, write a PL/SQL anonymous block that accepts a region name (CHILD_NAME) from the user input and displays all its parent regions.

Using different table/column names will receive 0 points.

You cannot assume that the maximum number of region names is 5.

Make sure that the region names in your output are in the same line and separated by “==>”.

Creating cleanly formatted output is a common programming requirement. The format of your output must match mine EXACTLY. If your output does not match mine EXACTLY (e.g., missing “USER INPUT:”, “MY OUTPUT:”, “[”, “]”, “(1)”, or “==>” in your output), you will lose some points.

Explanation / Answer

Using WHILE LOOP and not the CURSOR

Code:

DECLARE
PARENT_NAME VARCHAR2(30);
CHILD_NAME VARCHAR2(30);
CHILD VARCHAR2(30) = 'Beijing';

SELECT PARENT_NAME,CHILD_NAME
       INTO PARENT_NAME, CHILD_NAME
   FROM world_region
   WHERE CHILD_NAME = @CHILD;

BEGIN
WHILE PARENT_NAME != '***' LOOP

dbms_output.put_line(PARENT_NAME || ' ==> ' || CHILD_NAME);
CHILD := PARENT_NAME;

   SELECT PARENT_NAME,CHILD_NAME
       INTO PARENT_NAME, CHILD_NAME
   FROM world_region
   WHERE CHILD_NAME = CHILD;
END LOOP;
END;

Sample output:

Asia==>China

China==>Beijing

Navigate

CREATE TABLE vendor ( vendor_id int NOT NULL, vendor_name char(50) NOT NULL, con

CREATE THE CODE ON MICROSOFT VISUAL STUDIO.\" C+\" /* Instructions: You are to c

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

CREATE TABLE world_region ( PARENT_NAME VARCHAR2(30) NOT NULL, CHILD_NAME VARCHA

Question

Explanation / Answer

Related Questions

Navigate