Urgent Help Needed > what's wrong with these codes

[OilSheikh]

Help would be much appreciated.

 

1. User prompted for values... values checked ... values entered into table.

 

ACCEPT eno NUMBER -
PROMPT "Enter Employee no :"
ACCEPT enam VARCHAR2 -
PROMPT "Enter Employee name :"
ACCEPT jo VARCHAR2 -
PROMPT "Enter Job Title :"
ACCEPT man NUMBER -
PROMPT "Enter Manager no :"
ACCEPT hd DATE -
PROMPT "Enter Starting Date :"
ACCEPT sala NUMBER -
PROMPT "Enter Employee's salary :"
ACCEPT com NUMBER -
PROMPT "Enter Employee commission :"
ACCEPT dno NUMBER -
PROMPT "Enter Employee's Department number :"

EXECUTE add_any_emp ( 'eno','enam','jo','man','hd','sala','com','dno');


CREATE OR REPLACE PROCEDURE add_any_emp 
  (aEMPNO IN NUMBER, aENAME IN VARCHAR2, aJOB IN VARCHAR2, aMGR IN NUMBER, aHIREDATE IN DATE, aSAL IN NUMBER, aCOMM IN NUMBER, aDEPTNO IN NUMBER )

AS
BEGIN
SELECT EMPNO FROM EMP2;
IF EMP2.EMPNO = aEMPNO THEN
      raise_application_error(-20101, 'Employee exists');
  ELSE 
        IF aSAL > 20000 THEN
           aSAL = 20000
              ELSE
                    INSERT INTO EMP2
                        VALUES ( aEMPNO, aENAME, aJOB, aMGR, aHIREDATE, aSAL, aCOMM, aDEPTNO );
        END IF;
END IF;
end;

[OilSheikh]

2. Calculate average salary of all employees excl. the highest paid fat cat 

 

CREATE OR REPLACE FUNCTION average_emp
RETURN AVGSAL
IS
BEGIN
SELECT SAL
FROM EMP2
WHERE EMPNO != NULL
AND AVGSAL (NUMBER ) = AVG(SUM(SAL) - MAX(SAL)) ;

DBMS_OUTPUT.PUT_LINE('Average Salary is : ' || AVGSAL);

 

[btherl]

In the second one, avg(Sum(sal) - max(sal)) looks suspicious.  sum() and max() are already aggregates that will give a single result, and you cannot take the average of a single result.  Instead you should either use a where clause to exclude the highest salary and then use avg(), or do the average calculation explicitly using sum() and max() and count().