【SQL面试题1】 Mysql或者Oracle数据库

如下有三道面试题：

创建两张表格：

CREATE TABLE CUSTOMERS
(
ID NUMBER(2),
NAME VARCHAR2(10)
);


CREATE TABLE ORDERS
(
ID NUMBER(2),
CUSTOMERID NUMBER(2)
);


SELECT * FROM CUSTOMERS;

INSERT INTO CUSTOMERS VALUES(1,'JOE');
INSERT INTO CUSTOMERS VALUES(2,'HENRY');
INSERT INTO CUSTOMERS VALUES(3,'SAM');
INSERT INTO CUSTOMERS VALUES(4,'MAX');

SELECT * FROM ORDERS;
INSERT INTO ORDERS VALUES(1,3);
INSERT INTO ORDERS VALUES(2,1);

解题答案：

/* 
某网站包含两张表，Customer和Orders表。
编写一个SQL查询。找出所有从不订购任何东西的用户。
*/
SELECT * FROM CUSTOMERS WHERE ID NOT IN
(
SELECT CUSTOMERID FROM ORDERS
)；  

---

创建两张表格：

CREATE TABLE T_EMAIL
(
ID NUMBER(2) PRIMARY KEY,
EMAIL VARCHAR2(20)
);

SELECT * FROM T_EMAIL;

INSERT INTO T_EMAIL VALUES(1,'[e-mail]');
INSERT INTO T_EMAIL VALUES(2,'[e-mail]');
INSERT INTO T_EMAIL VALUES(3,'[e-mail]');

解题答案：

-- 删除重复项
DELETE FROM T_EMAIL WHERE ROWID NOT IN
(
SELECT MIN(ROWID) FROM T_EMAIL  GROUP BY EMAIL -- 记得要写Group by 要不然就只会
) 


DELETE FROM T_EMAIL WHERE EMAIL
IN (SELECT EMAIL FROM T_EMAIL GROUP BY EMAIL HAVING COUNT(*)>1) 
And ROWID Not In (Select Min(ROWID) From T_EMAIL Group By EMAIL Having Count(*) > 1);

删除重复值：ROWID
注意事项：
1.ROWID的使用——快速删除重复的记录
2.rowid去重（删除表的重复记录）

---

创建两张表格：

CREATE TABLE T_EMPLOYEE
(
ID NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(10),
SALARY NUMBER(7,2),
DEPTNO NUMBER(2)
);

CREATE TABLE T_DEPTARTMENT
(
ID NUMBER(2) PRIMARY KEY,
NAME VARCHAR2(10)
);

INSERT INTO T_EMPLOYEE VALUES(1,'JOE',7000,1);
INSERT INTO T_EMPLOYEE VALUES(2,'HENRY',8000,2);
INSERT INTO T_EMPLOYEE VALUES(3,'SAM',7000,2);
INSERT INTO T_EMPLOYEE VALUES(4,'MAX',10000,1);


INSERT INTO T_DEPTARTMENT VALUES(1,'IT');
INSERT INTO T_DEPTARTMENT VALUES(2,'SALES');

SELECT * FROM T_EMPLOYEE;
SELECT * FROM T_DEPTARTMENT;

解题答案：

--找出每个部门工资最高的员工

SELECT * FROM (

SELECT TDEPT.NAME AS DEPTNAME,TEMP.NAME,TEMP.SALARY,ROW_NUMBER()OVER(PARTITION BY TDEPT.NAME ORDER BY TEMP.SALARY DESC)AS RK  FROM T_EMPLOYEE TEMP
JOIN T_DEPTARTMENT TDEPT
ON TEMP.DEPTNO =TDEPT.ID
)
WHERE RK =1;