簡單sql部分強化練習題，簡單sql強化練習題

簡單sql部分強化練習題，簡單sql強化練習題

簡單查詢部分sql練習題

-- 選擇部門30中的所有職工select * from emp where deptno = 30;-- 列出所有業務員(CLERK)的姓名，編號，和部門編號select e.ename, e.empno, e.deptno from emp e where e.job = 'CLERK';-- 找出獎金高於薪金的員工select * from emp where comm > sal;-- 找出獎金高於薪金的60%的員工select * from emp where comm > sal * 0.6;-- 找出部門10中所有經理(MANAGER)和部門20中所有業務員(CLERK)的詳細資料select * from emp e where e.deptno = 10 and e.job = 'MANAGER'     or e.deptno = 20 and e.job = 'CLERK';select * from emp e where (e.deptno = 10 and e.job = 'MANAGER')     or (e.deptno = 20 and e.job = 'CLERK');select * from emp e where e.deptno = 10 and e.job = 'MANAGER'union select * from emp e where e.deptno = 20 and e.job = 'CLERK';-- 找出部門10中所有經理(MANAGER),部門20中所有業務員(CLERK),既不是經理又不是業務員但其薪水大於等於2000的所有員工的詳細資料select * from emp e where e.deptno = 10 and e.job = 'MANAGER' unionselect * from emp e where e.deptno = 20 and e.job = 'CLERK'union select * from emp e where e.sal > 2000 and e.job not in('MANAGER', 'CLERK');-- 找出收取獎金的員工的不同工作select distinct e.job from emp e;-- 找出不收取獎金或收取的獎金低於100的員工select * from emp e where e.comm is null or e.comm < 100;-- 找出各月倒數第3天受雇的所有員工select * from emp e where e.hiredate between last_day(hiredate)-3 and last_day(hiredate);-- 找出早於30年前受雇的員工select * from emp e where (sysdate - e.hiredate)/365 > 30;-- 以首字母大寫的方式顯示所有員工的姓名select initcap(ename) from emp;-- 顯示正好為5個字元的員工姓名select * from emp where length(ename) = 5;-- 顯示不帶有”R”的員工姓名select * from emp where ename not like '%K%';-- 顯示所有員工姓名的前三個字元select substr(ename, 0, 3) from emp;-- 顯示所有員工的姓名，並用’a’替換所有’A’select replace(ename, 'A', 'a') from emp;-- 顯示滿30年服務年限的員工姓名和受雇日期select * from emp where (sysdate - hiredate)/365 > 30;-- 顯示員工的詳細資料，按姓名由大到小排序select * from emp order by ename desc;-- 顯示員工的姓名和受雇日期，根據其服務年限，將最老的員工排在最前面select ename, hiredate from emp order by hiredate asc;-- 顯示所有員工的姓名，工作和薪金，按工作降序排列，若工作相同則按薪金升序排序select ename, job, sal from emp order by job desc, sal asc;select ename, job, sal from emp order by 2 desc, 3;-- 顯示所有員工的姓名，加入公司的年份和月份，按受雇日期所在的月排序，若月份相同，則將最早年份排在最前面select ename, to_number(to_char(hiredate, 'yyyy')) Year, to_number(to_char(hiredate, 'mm')) from emp order by 3 desc, 2 asc;-- 顯示一個月為30天的情況所員工的日薪金，忽略餘數select round(sal/30) 日薪 from emp;-- 找出在(任何年份)2月受聘的所有員工select * from emp where to_number(to_char(hiredate, 'mm')）= 2;-- 對每個員工，顯示其加入公司的天數select ename, round(sysdate - hiredate) Days from emp;-- 顯示姓名中任意位置包含“A”的所有員工姓名select * from emp where upper(ename) like '%A%';-- 以年月日方式顯示所有員工的服務年限select ename, hiredate, trunc(months_between(sysdate, hiredate) /12)  year  ,trunc(mod(months_between(sysdate, hiredate) ,  12 ) ) months  , trunc(sysdate - add_months(hiredate,months_between(sysdate, hiredate))) dayfrom emp ;