Untitled

--4
create or replace procedure newprice (categoryID in number, amount in number)
as
    average NUMBER(9,2);
begin
    SELECT AVG(list_price) INTO average 
        FROM new_products 
        WHERE category_id = categoryID;
    if (average < amount) then
        UPDATE new_products
            SET list_price = list_price * 1.02
            WHERE category_id = categoryID;
        DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' rows are updated.');
    else
        DBMS_OUTPUT.PUT_LINE ('Average of 1 is ' || average || ', which is no lower than ' || amount || '. We won''t update the price!');
    end if;
end;
/
BEGIN
  newprice(2, 10000);
  newprice(1, 500);
END;
/
-- 5
create or replace procedure creditreport
as
    avgCredit NUMBER(8,2); 
    maxCredit NUMBER(8,2);
    minCredit NUMBER(8,2);
    
    avg_count NUMBER(6,0); 
    high_count NUMBER(6,0);
    low_count NUMBER(6,0);
begin
    SELECT AVG(credit_limit) INTO avgCredit FROM customers;
    SELECT MAX(credit_limit) INTO maxCredit FROM customers;
    SELECT MIN(credit_limit) INTO minCredit FROM customers;
    
    SELECT COUNT(*) INTO avg_count 
        FROM customers 
        WHERE credit_limit >= (avgCredit + minCredit) / 2 AND credit_limit <= (avgCredit + maxCredit) / 2;
    SELECT COUNT(*) INTO high_count FROM customers WHERE credit_limit < (avgCredit + maxCredit) / 2;
    SELECT COUNT(*) INTO low_count FROM customers WHERE credit_limit < (avgCredit + minCredit) / 2;
    
    
    DBMS_OUTPUT.PUT_LINE ('The number of customers with average credit limit: ' || avg_count);
    DBMS_OUTPUT.PUT_LINE ('The number of customers with high credit limit: ' || high_count);
    DBMS_OUTPUT.PUT_LINE ('The number of customers with low credit limit: ' || low_count);
end;
/
BEGIN
  creditreport ();
END;
/