Create and populate the BIRTHDAY table by using the following SQL statements. CR

ID: 3833006 • Letter: C

Question

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

CREATE TABLE birthday

(B_MONTH NUMBER,

B_DAY NUMBER,

B_RANK NUMBER);

INSERT INTO birthday

SELECT * FROM hchen.birthday;

COMMIT;

SELECT COUNT(*) FROM 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.

Submitting more than one PL/SQL program will receive 0 points

Explanation / Answer

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

DECLARE
n NUMBER := &nn;
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;

Navigate

Create and populate a small normalized database in MS-Access that will schedule

Create and run JunitTest cases for the following Java classes class Person { pro

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

Create and populate the BIRTHDAY table by using the following SQL statements. CR

Question

Explanation / Answer

Related Questions

Navigate