以Oracle和MSSQL为标准
1) 找出佣金高于工资的员工
SELECT * FROM emp WHERE comm>sal ;
2) 工作满10年的员工
SELECT ename,hiredate FROM emp WHERE MONTHS_BETWEEN(sysdate,hiredate) / 12 > 10 ;
3)找出每个月最后第三天被雇佣的
SELECT * FROM emp WHERE LAST_DAY(hiredate)-2=hiredate ;
4)将所有员工的首字母大写
SELECT INITCAP(ename) FROM emp ;
5)打印出姓名、受雇年、受雇月并排序
SELECT ename,TO_CHAR(hiredate,'yyyy') year, TO_CHAR(hiredate,'mm') mon FROM emp ORDER BY mon,year ;
6)显示所有员工的服务年限
SELECT ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12)} year FROM emp ;
更进一步
SELECT ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year ,
TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) mon ,
TRUNC(MOD(sysdate-hiredate,30)) day FROM emp ;