EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 NULL 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7565 EMILY SALESMAN 7698 20-FEB-81 1250 800 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7655 JONES MANAGER 7839 2-APR-81 2975 NULL 20 7698 BLAKE MANAGER 7839 1-MAY-91 2850 NULL 30 7782 CLARK MANAGER 7839 9-JUN-81 2450 NULL 10 7788 SCOTT ANALYST 7655 21-MAR-87 3000 NULL 20 7844 TURNER SALESMAN 7698 18-SEP-81 1500 NULL 30 7876 ADAMS CLERK 7788 24-APR-87 1100 NULL 20 7900 JAMES CLERK 7698 3-DEC-81 950 NULL 30 7902 FORD ANALYST 7655 3-DEC-81 3000 NULL 20 7934 MILLER CLERK 7782 3-JAN-81 1300 NULL 10 Given This Table Called 'EMP', please define a function that, given a department number, returns the average salary of that department.
时间: 2024-02-10 12:07:06 浏览: 62
Sure, here's the function in SQL:
```
CREATE FUNCTION avg_salary_dept (dept_no INT) RETURNS DECIMAL(10, 2)
BEGIN
DECLARE total_salary DECIMAL(10, 2);
DECLARE num_employees INT;
SELECT SUM(sal) INTO total_salary, COUNT(*) INTO num_employees FROM EMP WHERE DEPTNO = dept_no;
RETURN total_salary / num_employees;
END;
```
This function takes in a department number and returns the average salary of employees in that department. It uses the SQL `SUM` function to add up all the salaries of employees in the department, and the `COUNT` function to get the number of employees in the department. Then it calculates the average salary by dividing the total salary by the number of employees, and returns the result.
阅读全文