mysql oracle大数据库_【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,'ganzexin@163.com');

INSERT INTO T_EMAIL VALUES(2,'ganzexin@163.com');

INSERT INTO T_EMAIL VALUES(3,'1395520340@qq.com');

解题答案：

-- 删除重复项

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);

创建两张表格：

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;