mysql面试题

1.产生一个结果集，如果一个员工工资小于等于2000，就返回消息“NO”,如果大于等于
4000，就返回消息“YES”。

如果在这两者之间，就返回“OK”.结果集如下：
EMP表原始数据
Name sal
------------------------------
Wsp 2300
Hjj 6700
king 6000
ford 1800
james 1200
按要求，显示效果图
Name sal status
------------------------------------------
Wsp 2300 ok
Hjj 6700 yes
king 6000 yes
ford 1800 no
james 1200 no
select Name, sal,
case WHEN sal <= 2000 THEN "NO"
WHEN sal >=4000 THEN "YES"
ELSE "OK"
end
as status
FROM EMP;
2.从表中随机返回5 条记录！可以修改下面的语句，要求下次执行时产生不同的结果集
Select ename,job from emp
Select ename, job from emp order by rand() limit 5;
3.如：emp表
Deptno cnt
------------------------------
10 3
20 5
30 6
效果图：
Deptno_10 Deptno_20 Deptno_30
---------------------------------------------------
3 5 6
SELECT
SUM(CASE WHEN Deptno = 10 THEN 3 ELSE 0 END) as Deptno_10,
SUM(CASE WHEN Deptno = 20 THEN 5 ELSE 0 END) as Deptno_20,
SUM(CASE WHEN Deptno = 30 THEN 6 ELSE 0 END) as Deptno_30 FROM emp;
4.如表emp原台数据如
empno job name
-------------------------------------------------------------
1 analyst scott
2 analyst ford
3 cleark smith
4 cleark miller
5 cleark james
6 manager jones
7 manager clark
8 manager blank
效果图:
Cleark manager analyst
----------------------------------------------
Smith jones scott
Miller clark ford
James blank
SELECT
MAX(IF(job='cleark',name,'')) AS cleark,
MAX(IF(job='manager',name,'')) AS manager,
MAX(IF(job='analyst',name,'')) AS analyst
FROM emp
GROUP BY name
ORDER BY name DESC。