create view myplayerlist(firstname, lastname, feet, weight, pts, reb) as 
(select P.firstname, P.lastname, P.h_feet, P.weight, avg(pts), avg(reb)
from system.players P, system.playerregularseason PR
where P.ilkid = PR.ilkid
group by P.ilkid, P.firstname, P.lastname, P.h_feet, P.weight)


REM Problem 2 - function to print good coaches whose performance is above average
-- Write a function named print_good_coaches to print out the coaches who had more net wins in a specified year than the average net wins of all coaches in that year. The procedure takes the year as the only parameter and returns the number of coaches found. 

create or replace function print_good_coaches (season IN INT)
return INT
IS
 --Define variables, cursors here
counter INT :=0;
averageNetWins NUMBER := 0;
CURSOR csCursor IS
	SELECT FIRSTNAME, LASTNAME, (SEASON_WIN + PLAYOFF_WIN - SEASON_LOSS - PLAYOFF_LOSS) coachNetWins
    FROM system.coachesseason
    WHERE YEAR = season AND (SEASON_WIN + PLAYOFF_WIN - SEASON_LOSS - PLAYOFF_LOSS) > averageNetWins;

BEGIN
   -- Implement function body here
   
   -- Compute the average net wins for this year
   SELECT AVG(coachNetWins) INTO averageNetWins
   FROM (SELECT (SEASON_WIN + PLAYOFF_WIN - SEASON_LOSS - PLAYOFF_LOSS) coachNetWins
         FROM system.coachesseason
         WHERE YEAR = season);
   
   -- Display the given year
   DBMS_OUTPUT.PUT_LINE('Year ' || season);
   DBMS_OUTPUT.NEW_LINE();
   
   -- Display the average net wins for this year
   DBMS_OUTPUT.PUT_LINE('Average net wins of all coaches: ' || averageNetWins);
   DBMS_OUTPUT.NEW_LINE();
   
   -- Display the coaches' first name, last name, and net wins for the given season
   FOR coach IN csCursor LOOP
       DBMS_OUTPUT.PUT_LINE('Coach ' || coach.FIRSTNAME || ' ' || coach.LASTNAME || ' has ' || coach.coachNetWins || ' net wins');
       DBMS_OUTPUT.NEW_LINE();
       counter:= counter + 1;
   END LOOP;  
   
   return counter;
END;
/

REM Problem 3 - A small anonymous block that calls function print_good_coaches

-- Variables defined here
DECLARE
    numberOfGoodCoaches INT;

BEGIN
-- implement the body

    numberOfGoodCoaches := print_good_coaches(2000);

    IF numberOfGoodCoaches = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No results found!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('There are ' || numberOfGoodCoaches ||' good coaches');
    END IF;
END;
/

REM Problem 4 - procedure height_histograms, print height statistics

CREATE OR REPLACE PROCEDURE height_histograms AS
    --define variables and cursors here
    countRangeBelow180 INT := 0;
    countRange180To190 INT := 0;
    countRange190To200 INT := 0;
    countRange200To210 INT := 0;
    countRange210To220 INT := 0;
    countRangeBeyond220 INT := 0;
    CURSOR pCursor IS
		SELECT (((H_FEET * 12) + H_INCHES) * 2.54) height -- in centimeters
		FROM system.players;

BEGIN
    --Implement procedure here
  
    --Display Header
    DBMS_OUTPUT.PUT_LINE('---- Height Distribution of NBA Players ----');
    DBMS_OUTPUT.NEW_LINE();
    DBMS_OUTPUT.PUT_LINE('Range    <180    (180,190]   (190,200]   (200,210]   (210,220]   >220');
    DBMS_OUTPUT.NEW_LINE();
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------');
    DBMS_OUTPUT.NEW_LINE();
  
    --Tally up the heights of the players in each range
    FOR player IN pCursor LOOP
        IF player.height < 180 THEN
            countRangeBelow180 := countRangeBelow180 + 1;
        ELSIF player.height <= 190 THEN
            countRange180To190 := countRange180To190 + 1;
        ELSIF player.height <= 200 THEN
            countRange190To200 := countRange190To200 + 1;
	ELSIF player.height <= 210 THEN
	    countRange200To210 := countRange200To210 + 1;
	ELSIF player.height <= 220 THEN
	    countRange210To220 := countRange210To220 + 1;
	ELSIF player.height > 220 THEN
	    countRangeBeyond220 := countRangeBeyond220 + 1;
	END IF;
    END LOOP;
    
    --Display the results of the tallies for the user
    DBMS_OUTPUT.PUT_LINE('Count    ' || RPAD(countRangeBelow180, 8, ' ')  ||  
                                        RPAD(countRange180To190, 12, ' ') ||  
                                        RPAD(countRange190To200, 12, ' ') || 
                                        RPAD(countRange200To210, 12, ' ') || 
                                        RPAD(countRange210To220, 12, ' ') || 
                                        countRangeBeyond220);
    DBMS_OUTPUT.NEW_LINE();
END;
/

REM Problem 5 - execute the above procedure height_histograms

BEGIN
height_histograms;
END;