The TAB_BIRTHDAY table consists of every day of the year, from January 1 to Dece
ID: 3867028 • Letter: T
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; / 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.
Explanation / Answer
accept n NUMBER prompt 'please enter number 1 or 2';
DECLARE
n number=&n;
BEGIN
if(n=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(n=2) then
for x in(select lpad(b_day,2,'0'),lpad(b_rank,3,'0');
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;
else
dbms_output.put_line('please enter either 1 or 2');
end if;
end;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.