The TAB_BIRTHDAY table consists of every day of the year, from January 1 to Dece

Question

The TAB_BIRTHDAY table consists of every day of the year, from January 1 to December 31, along with a ranking based on how many babies were born in the United States on that date between 1973 and 1999. Rank 1 is the most popular, rank 2 is the next most popular, and so forth.

Create and populate the TAB_BIRTHDAY table by using the following SQL statements.

CREATE TABLE tab_birthday

(BIRTHDAY_MONTH     NUMBER,

BIRTHDAY_DAY       NUMBER,

BIRTHDAY_RANK      NUMBER);

/

INSERT INTO tab_birthday

SELECT * FROM hchen.tab_birthday;

/

COMMIT;

/

SELECT COUNT(*) FROM tab_birthday;

/

Please make sure that there are 366 rows in your TAB_BIRTHDAY table.

You cannot change the definition of the TAB_BIRTHDAY table. You will receive 0 points if you use different table names, column names, or data types.

In the TAB_BIRTHDAY table, you can find that September 16 is the most popular birthday (rank = 1) and February 29 is the least popular birthday (rank = 366). Excluding leap years, December 25 is the least popular birthday (rank = 365).

Write a PL/SQL anonymous block that accepts an integer n (n = 1 or n = 2) from the user input and 1) displays five (5) most popular birthdays along with the ranks for each month if the user input is 1 (n = 1), or 2) displays five (5) least popular birthdays along with the ranks for each month if the user input is 2 (n = 2). Sort your output in ascending order by months, and then most/least popular birthdays.

You will lose 10 points if the title lines are missing in your output.

You will lose 10 points if your output is not in the correct format. For example, you must display the birthdays and ranks for the same month in one line.

Each day-rank pair must be displayed in the DD/RRR format (2-digit day and 3-digit rank) (e.g., 03/088).

You may hard-corded values of months (e.g., FOR idx IN 1..12 LOOP).

If you have hard coded the birthdays or ranks (e.g., DBMS_OUPT.PUT_LINE('1 <20/240> <14/260>')) in your PL/SQL block, you will receive 0 points.

To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9 and Enter.

This question can be solved without using cursors.

You will receive 0 points if you submit more than one PL/SQL program.

Explanation / Answer

accept a NUMBER prompt 'Please enter 1 or 2';

DECLARE
z NUMBER := &a;
BEGIN
IF ( z = 1 )THEN
FOR x IN ( SELECT LPAD(B_DAY,2,'0') AS 'd_day',
LPAD(B_RANK,3,'0') AS 'd_rank' FROM birthday
GROUP BY CONCAT(B_DAY,"-",B_MONTH) ORDER BY
COUNT(B_DAY) DESC B_MONTH ASC,
CONCAT(B_DAY,"-",B_MONTH) ASC FETCH FIRST 5
ROWS ONLY )
LOOP
DBMS_OUTPUT.PUT_LINE(x.d_day||'/'||x.d_rank);
END LOOP;
ELSIF (z = 2) THEN
FOR x IN ( SELECT LPAD(B_DAY,2,'0') AS 'd_day',
LPAD(B_RANK,3,'0') AS 'd_rank' FROM birthday
GROUP BY CONCAT(B_DAY,"-",B_MONTH) ORDER BY
COUNT(B_DAY) ASC B_MONTH ASC,
CONCAT(B_DAY,"-",B_MONTH) ASC FETCH FIRST 5
ROWS ONLY )
LOOP
DBMS_OUTPUT.PUT_LINE(x.d_day||'/'||x.d_rank);
END LOOP;
ESLE
DBMS_OUTPUT.PUT_LINE('Please Enter Either 1 or 2');
END IF;
END;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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