5.15

Consider an employee database with two relations:

employee(employee_name, street,city)
works(employee_name,company_name,salary)

where the primary keys are underlined. Write a function avg_salary that takes a company name as an argument and finds the average salary of employees at that company. Then, write an SQL statement, using that function, to find companies whose employees earn a higher salary, on average, than the average salary at “First Bank.”

---

-- The following defines the sql function avg_salary.
-- Takes a company name as an argument and finds the average salary of
-- employees at that company.
CREATE FUNCTION avg_salary(company_name VARCHAR(20))
    RETURNS REAL
    BEGIN
    DECLARE retval REAL;
        SELECT AVG(salary)
        FROM works
        WHERE works.company_name = company_name;
    RETURN retval;
    END;

SELECT DISTINCT company_name
FROM works
WHERE avg_salary(company_name) > avg_salary('First Bank');