INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);Oracle的sql纠错

你的 SQL 语句中存在一些错误。以下是你的 SQL 语句的修正版本：

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

修正后的 SQL 语句如下：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

在这个修正后的 SQL 语句中，我为 EMP 表中的每个列都提供了明确的列名，确保插入的数据与表结构一致。请根据你的实际需求修改列名和值。

相关问题

use mydb2; CREATE TABLE emp( empno INT, ename VARCHAR(50)， job VARCHAR(50), mgr INT, hiredate DATE, sal INT, comm INT, deptno INT ); INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902, '1980-12-17',800,NULL,20); INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,' 1981-02-20',1600,300,30); INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000, NULL,20); INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20); INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); -- 6、求平均薪水大于2000的部门]编号 -- 7、将部门平均薪水大于1500的部门]编号列出来，按部门平均薪水降序排列I -- 8、选择公司中有奖金的员工姓名，工资 -- 9、查询员工最高工资和最低工资的差距

我理解了你的问题，以下是答案：

6、求平均薪水大于2000的部门编号：

SELECT deptno FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

7、将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列：

SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING AVG(sal) > 1500 ORDER BY avg_sal DESC;

8、选择公司中有奖金的员工姓名、工资：

SELECT ename, sal FROM emp WHERE comm IS NOT NULL;

9、查询员工最高工资和最低工资的差距：

SELECT MAX(sal) - MIN(sal) AS diff_sal FROM emp;

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.

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.