经典SQL面试上机题目

现EMP表中含有重复数据，请根据题目完成下列问题

（1）建表语句：

CREATE TABLE EMP (
    empno INT PRIMARY KEY,
    ename VARCHAR(50),
    job VARCHAR(50),
    sal DECIMAL(10, 2),
    deptno INT
);

（2）插入数据：

INSERT INTO EMP (empno, ename, job, sal, deptno)
VALUES
(7839, 'KING', 'PRESIDENT', 5000.00, 10),
(7566, 'JONES', 'MANAGER', 2975.00, 20),
(7698, 'BLAKE', 'MANAGER', 2850.00, 30),
(7830, 'KING', 'PRESIDENT', 5000.00, 10),
(7782, 'CLARK', 'MANAGER', 2450.00, 10);

（3）查询出所有的重复数据：

SELECT empno, COUNT(*) AS count FROM emp GROUP BY empno HAVING COUNT(*) > 1 ;

（4）查询出多余的数据：

SELECT empno, ename, job, sal, deptno
FROM emp
WHERE empno IN (
    SELECT empno
    FROM emp
    GROUP BY empno
    HAVING COUNT(*) > 1
);

（5）删除多余的数据：

DELETE FROM emp
WHERE empno IN (
    SELECT empno
    FROM emp
    GROUP BY empno
    HAVING COUNT(*) > 1
);

（6）查询去重：

SELECT DISTINCT empno, ename, job, sal, deptno
FROM emp;