2) CSC 352 and CSC 452-
The BIRTHDAY_DISTRIBUTION 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 BIRTHDAY_DISTRIBUTION table by using the following SQL statements.
CREATE TABLE birthday_distribution
(MONTH NUMBER,
DAY NUMBER,
RANK NUMBER);
INSERT INTO birthday_distribution SELECT * FROM hchen.birthday_distribution;
COMMIT;
SELECT COUNT(*) FROM birthday_distribution;
Please make sure that there are 366 rows in your BIRTHDAY_DISTRIBUTION table.
In the BIRTHDAY_DISTRIBUTION 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).
Begin(2a) CSC 352 only
2a) CSC 352 only
Write a PL/SQL anonymous block that accepts an integer n (1 ≤ n ≤ 5) from the user input and displays n most popular birthdays along with the ranks for each month. Sort your output in ascending order by months, and then most popular birthdays.
• You will lose 10 points if the title lines are missing in your output.
• You will lose 10 points if your output has an incorrect format. For example, you must display the birthdays and ranks for the same month in one line.
• 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.
• Submitting more than one PL/SQL program will receive 0 points.
Test your program. You must ensure that the output of your program matches the following output (one month per line):
End(2a) CSC 352 only
Begin (2b) CSC 452 only
2b) CSC 452 only
Write a PL/SQL anonymous block that accepts an integer n (1 ≤ n ≤ 6) from the user input and displays n most and n least popular birthdays along with the ranks for each month. Sort your output in ascending order by months, most popular birthdays, and then 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 has an incorrect format.For example, you must display the birthdays and ranks for the same month in one line.
• 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.
• Submitting more than one PL/SQL program will receive 0 points.
Test your program. You must ensure that the output of your program matches the following output (one month per line):
Case 1)
Case 2)
Case 3)
Case 4)
......
End(2b) CSC 452 only
3) CSC 352 and CSC 452-
Begin(3a) CSC 352 only
3a) CSC 352 only
Based on the tables created in Assignment #1, write a PL/SQL program that accepts an employee ID from the user input and displays1) employee name, job, hire date, and his/her department name (If the given employee does not belong to any department, the department name is shown as "------" in your output.), and 2) all employees (alone with their jobs and hire dates) who work in the same department as the given employee andwere hired beforethe given employee (or "NO OUTPUT"). Sort your output by the employee name.
• Hard coding(e.g., IF v_emp_id = 7596 THEN v_1 := ...) will receive 0 points.
• You will lose 10 points if the title lines are missing in your output.
• You will lose 10 points if your output has an incorrect format.
• Submitting more than one PL/SQL program 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.
Test your program. You must ensure that the output of your program matches the following sample output:
Case 1)
Output:
Case 2)
Output:
Case 3)
Output:
Case 4)
Output:
End(3a) CSC 352 only
Begin(3b) CSC 452 only
3b) (CSC 452 only)
Based on the tables created in Assignment #1, write a PL/SQL anonymous block that displays all employees who were hired on the days of the week on which the highest number of employees were hired. The output of the program must contain all the hire dates, employee names, job, their corresponding department names (If an employee does not belong to any department, the department name is shown as "------" in your output.), and the names and salaries of their corresponding managers (If an employee does not have a manager, the manager name and salary are shown as "------" in your output.). Sort your output by days of the week (Monday, Tuesday, ..., Friday) and the hire date.
• You will lose 10 points if the title lines are missing in your output.
• You will lose 10 points if your output has an incorrect format.
• Hard coding (e.g., IF v_day = 'Thursday' OR v_day = 'Friday' OR v_max_num = 4 THEN ...) will receive 0 points.
• Submitting more than one PL/SQL program will receive 0 points.
Hints:
(1) TO_CHAR(hire_date, 'Day')
(2) TRIM(TO_CHAR(hire_date, 'Day'))
(3) TRIM(TO_CHAR(hire_date, 'D')
(4) GROUP BY TO_CHAR(hire_date, 'Day')
The output of your program must match the following:
End(3b) CSC 452 only
Attachment:- SQL_Assignment.rar