1. select * from emp;

2. select empno, ename, job from emp;

3. select empno 編號(hào), ename 姓名, job 工作 from emp;

4. select job from emp;

5. select distinct job from emp;

6. select distinct empno, job from emp;
說(shuō)明:因?yàn)楣蛦T編號(hào)不重復(fù), 所以此時(shí)證明所有的列沒(méi)有重復(fù),所以不能消除掉重復(fù)的列.

7. 查詢(xún)出雇員的編號(hào), 姓名, 工作, 但是顯示的格式:編號(hào)是: 7369 的雇員, 姓名是: smith, 工作是: clear
select '編號(hào)是: ' || empno || '的雇員, 姓名是: ' || ename || ', 工作是: ' || job from emp;

8. 求出每個(gè)雇員的姓名及年薪
select ename, sal * 12 income from emp;

9. 求出工資大于 1500 的所有雇員信息
select * from emp where sal > 1500;

10. 查詢(xún)每月可以得到獎(jiǎng)金的雇員信息
select * from emp where comm is not null;

11. 查詢(xún)沒(méi)有獎(jiǎng)金的雇員信息
select * from emp where comm is null;

12. 查詢(xún)出基本工資大于 1500 同時(shí)可以領(lǐng)取獎(jiǎng)金的雇員信息
select * from emp where sal > 1500 and comm is not null;

13. 查詢(xún)出基本工資大于 1500 或者可以領(lǐng)取獎(jiǎng)金的雇員信息
select * from emp where sal > 1500 or comm is not null;

14. 查詢(xún)出基本工資不大于 1500 或者不可以領(lǐng)取獎(jiǎng)金的雇員信息
select * from emp where not(sal > 1500 and comm is not null);

15. 查詢(xún)基本工資大于 1500, 但是小于 3000 的全部雇員信息
select * from emp where sal > 1500 and sal < 3000;

16. 查詢(xún)基本工資大于等于 1500, 但是小于等于 3000 的全部雇員信息
select * from emp where sal >= 1500 and sal <= 3000;
select * from emp where sal between 1500 and 3000;

17. 查詢(xún)出在 1981 年雇傭的全部雇員信息(1981 年 1 月 1 日 到 1981 年 12 月 31 日之間的雇傭的雇員)
select * from emp where hiredate between '1-1月-81' and '31-12月-81';

18. 要求查詢(xún)出姓名是 smith 的雇員信息
select * from emp where ename = 'SMITH';

19. 要求查詢(xún)出雇員是 7369, 7499, 7521 的雇員的具體信息
select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
select * from emp where empno in(7369, 7499, 7521);

20. 要求查詢(xún)出雇員不是 7369, 7499, 7521 的雇員的具體信息
select * from emp where empno not in(7369, 7499, 7521);

21. 要求查詢(xún)出姓名是 smith, allen, king 的雇員信息
select * from emp where ename in('SMITH', 'ALLEN', 'KING');

22. 查詢(xún)出所有雇員姓名中第二個(gè)字母包含 "M" 的雇員信息
        select * from emp where ename like '_M%';

23. 查詢(xún)出雇員姓名中包含字母 M 的雇員信息
select * from emp where ename like '%M%';

24. 要求查詢(xún)出在 1981 年雇傭的雇員信息
select * from emp where hiredate like '%81%';

25. 查詢(xún)工資中包含 5 的雇員信息
select * from emp where sal like '%5%';

26. 查詢(xún)雇員編號(hào)不是 7369 的雇員信息
select * from emp where empno != 7369;
select * from emp where empno <> 7369;

27. 要求按照工資由低到高排序
select * frm emp order by sal;
select * from emp order by sal asc;

28. 要求按照工資由高到低排序
select * from emp order by sal desc;

29. 要求查詢(xún)出 20 部門(mén)的所有雇員信息, 查詢(xún)的信息按照工資由高到低排序,如果工資相等,則按照雇傭日期由早到晚排序.
select * from emp where deptno = 20 order by sal desc, hiredate asc;

30. 將小寫(xiě)字母變?yōu)榇髮?xiě)字母
select upper('hello') from dual;

31. 將大寫(xiě)字母變?yōu)樾?xiě)字母
select lower('HELLO WORLD') from dual;

32. 要求查詢(xún)出姓名是 smith 的雇員信息
select * from emp where ename = upper('smith');

33. 使用 initcap() 函數(shù)將單詞的第一個(gè)字母大寫(xiě)
select initcap('hello world') from dual;

34. 將雇員表中的雇員姓名變?yōu)殚_(kāi)頭字母大寫(xiě)
select initcap(ename) from emp;

35. 將字符串 "hello" 和 "world" 進(jìn)行串聯(lián)
select concat('hello ', 'world') from dual;

36. 對(duì)字符串進(jìn)行操作的常用字符處理函數(shù)
select substr('hello', 1, 3) 截取字符串, length('hello') 字符串的長(zhǎng)度, replace('hello', 'l', 'x') 字符串替換 from dual;
select substr('hello', 0, 3) 截取字符串, length('hello') 字符串的長(zhǎng)度, replace('hello', 'l', 'x') 字符串替換 from dual;

37. 顯示所有雇員的姓名及姓名的后三個(gè)字符
select ename, substr(ename, length(ename) -2) from emp;
select ename, substr(ename, -3, 3) from emp;

38. 使用數(shù)值函數(shù)執(zhí)行四舍五入操作
select round(789.536) from dual;

39. 要求將 789.536 數(shù)值保留兩位小數(shù)
select round(789.536, 2) from dual;

40. 要求將 789.536 數(shù)值中的整數(shù)的十位進(jìn)行四舍五入進(jìn)位
select round(789.536, -2) from dual;

41. 采用 trunc() 函數(shù)不會(huì)保留任何小數(shù),而且小數(shù)點(diǎn)也不會(huì)執(zhí)行四舍五入的操作
select trunc(789.536) from dual;

42. 通過(guò) trunc() 也可以指定小數(shù)點(diǎn)的保留位數(shù)
select trunc(789.536, 2) from dual;

43. 作用負(fù)數(shù)表示位數(shù)
select trunc(789.536, -2) from dual;

44. 使用 mod() 函數(shù)可以進(jìn)行取余的操作
select mod(10, 3) from dual;

45. 顯示 10 部門(mén)雇員進(jìn)入公司的星期數(shù)(當(dāng)前日期 - 雇傭日期 = 天數(shù) / 7 = 星期數(shù))
select empno, ename, round((sysdate - hiredate) / 7) from emp where deptno = 10;

46. 日期函數(shù)
months_between(): 求出給定日期范圍的月數(shù)
add_months(): 在指定的日期上加上指定的月數(shù), 求出之后的日期
next_day(): 指定日期的下一個(gè)日期
last_day(): 求出給定日期當(dāng)月的最后一天日期

47.
select empno, ename, months_between(sysdate, hiredate) from emp;
select empno, ename, round(months_between(sysdate, hiredate)) from emp;

48. select sysdate, add_months(sysdate, 4) from dual;

49. select next_day(sysdate, '星期一') from dual;

50. select last_day(sysdate) from dual;

51. 轉(zhuǎn)換函數(shù)
to_char(): 轉(zhuǎn)換成字符串
to_number(): 轉(zhuǎn)換成數(shù)字
to_date(): 轉(zhuǎn)換成日期

52. 查詢(xún)所有雇員的雇員編號(hào), 姓名, 雇傭日期
select empno,
ename,
to_char(hiredate, 'yyyy') year,
to_char(hiredate, 'mm') months,
to_char(hiredate, 'dd') day
from emp;

select empno, ename, to_char(hiredate, 'yyyy-mm-dd') from emp;

select empno, ename, to_char(hiredate, 'fmyyyy-mm-dd') from emp;

53. 查詢(xún)所有雇員的編號(hào), 姓名和工資
select empno, ename, sal from emp;
select empno, ename, to_char(sal, '99,999') from emp;
select empno, ename, to_char(sal, 'L99,999') from emp;
select empno, ename, to_char(sal, '$99,999') from emp;

54. select to_number('123') + to_number('123') from dual;

55. 將一個(gè)字符串轉(zhuǎn)換成日期類(lèi)型
select to_date('2009-01-01', 'yyyy-mm-dd') from dual;

56. 求出每個(gè)雇員的年薪(要求加上獎(jiǎng)金)
select empno, ename, sal, comm, (sal + comm) * 12 from emp;
select empno, ename, sal, comm, nvl(comm, 0), (sal + nvl(comm, 0)) * 12 income from emp;

57. decode() 函數(shù)類(lèi)似于 if....elsif...else 語(yǔ)句
select decode(1, 1, '內(nèi)容是 1', 2, '內(nèi)容是 2', 3, '內(nèi)容是 3') from dual;

58. 查詢(xún)出雇員的編號(hào), 姓名, 雇傭日期及工作, 要求將雇員的工作替換成以下信息:
select empno 雇員編號(hào),
ename 雇員姓名,
hiredate 雇傭日期,
decode(job,
'CLERK', '業(yè)務(wù)員',
'SALESMAN', '銷(xiāo)售人員',
'MANAGER', '經(jīng)理',
'ANALYST', '分析員',
'PRESIDENT', '總裁'
) 職位
from emp;

59. 笛卡爾積(交差連接)
select * from emp, dept;
select * from emp cross join dept;

60. 內(nèi)連接
select * from emp e, dept d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;

61. 自然連接
select * from emp natural join dept;
select * from emp e join dept d using(deptno);

62. 要求查詢(xún)出雇員的編號(hào), 姓名, 部門(mén)的編號(hào), 名稱(chēng), 地址
select e.empno, e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;

63. 要求查詢(xún)出雇員的姓名, 工作, 雇員的直接上級(jí)領(lǐng)導(dǎo)姓名
select e.ename, e.job, m.ename from emp e, emp m where e.mgr = m.empno;

64. 要求查詢(xún)出雇員的姓名, 工作, 雇員的直接上級(jí)領(lǐng)導(dǎo)姓名以及部門(mén)名稱(chēng)
select e.ename, e.job, m.ename, d.dname from emp e, emp m, dept d where e.mgr = m.empno and e.deptno = d.deptno;

65. 要求查詢(xún)出每個(gè)雇員的姓名, 工資, 部門(mén)名稱(chēng), 工資在公司的等級(jí)(salgrade), 及其領(lǐng)導(dǎo)的姓名及工資所在公司的等級(jí)
select e.ename, e.sal, d.dname, s.grade, m.ename, m.sal, ms.grade
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e.mgr = m.empno
and m.sal between ms.losal and ms.hisal;

select e.ename,
e.sal,
d.dname,
decode(s.grade, 1, '第五等級(jí)', 2, '第四等級(jí)', 3, '第三等級(jí)', 4, '第二等級(jí)', 5, '第一等級(jí)'),
m.ename,
m.sal,
decode(ms.grade, 1, '第五等級(jí)', 2, '第四等級(jí)', 3, '第三等級(jí)', 4, '第二等級(jí)', 5, '第一等級(jí)')
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empno
and m.sal between ms.losal and ms.hisal;

66. select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno;
    select empno, ename, d.deptno, dname, loc from emp e inner join dept d on e.deptno = d.deptno;

67. 左外連接
    select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno(+);
    select empno, ename, d.deptno, dname, loc from emp e left outer join dept d on e.deptno = d.deptno;
    select empno, ename, d.deptno, dname, loc from emp e left join dept d on e.deptno = d.deptno(+);

68. 右外連接
    select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno(+) = d.deptno;
    select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;
    select empno, ename, d.deptno, dname, loc from emp e right join dept d on e.deptno = d.deptno;

69. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno;

70. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno(+);

71.
select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30;
select * from emp e inner join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e natural join dept d where deptno = 30;
select * from emp e join dept d using(deptno) where deptno = 30;

72.
select e.ename, d.deptno, d.dname, d.loc from emp e right outer join dept d on e.deptno = d.deptno;
select e.ename, d.deptno, d.dname, d.loc from emp e right join dept d on e.deptno = d.deptno;
select e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno(+) = d.deptno;

73. select count(ename) from emp;

74. select min(sal) from emp;

75. select max(sal) from emp;

76. select sum(sal) from emp;

77. select avg(sal) from emp;

78. select sum(sal) from emp where deptno = 20;

79. select avg(sal) from emp where deptno = 20;

80. 求出每個(gè)部門(mén)的雇員數(shù)量
select deptno, count(deptno) from emp group by deptno;
select deptno, count(empno) from emp group by deptno;

81. 求出每個(gè)部門(mén)的平均工資
select deptno, avg(sal) from emp group by deptno;

82. 按部門(mén)分組, 并顯示部門(mén)的名稱(chēng), 及每個(gè)部門(mén)的員工數(shù)
select d.dname, count(e.empno) from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

select d.deptno, d.dname, temp.c
from (select deptno, count(e.empno) c from emp e group by e.deptno) temp, dept d
where temp.deptno = d.deptno;

83. 要求顯示出平均工資大于 2000 的部門(mén)編號(hào)和平均工資
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;

84. 顯示非銷(xiāo)售人員工作名稱(chēng)以及從事同一工作雇員的月工資的總和,并且要滿(mǎn)足從事同一工作的雇員的月工資合計(jì)大于 5000, 輸出結(jié)果按月工資的合計(jì)升序排序.
select job, sum(sal) su from emp where job <> 'SALESMAN' group by job having sum(sal) > 5000 order by su;

select temp.job, sum(temp.sal) s
from (select job, sal from emp e where job <> 'SALESMAN') temp
group by temp.job
having sum(temp.sal) > 5000
order by s;

85. 求出平均工資最高的部門(mén)工資
select max(avg(sal)) from emp group by deptno;

86. 要求查詢(xún)出比雇員編號(hào)為 7654 工資高的所有雇員信息
select * from emp where sal >(select sal from emp where empno = 7654);

87. 要求查詢(xún)出工資比 7654 高, 同時(shí)與 7788 從事相同工作的全部雇員信息
select * from emp
where sal >(select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);

88. 要求查詢(xún)出工資最低的雇員姓名, 工作, 工資
select ename, job, sal from emp where sal = (select min(sal) from emp);

89. 要求查詢(xún)出:　部門(mén)名稱(chēng)，部門(mén)的員工數(shù)，部門(mén)的平均工資，部門(mén)的最低收入雇員的姓名
select d.dname, temp.c, temp.a, e.ename
from dept d,
(select deptno, count(empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp,
emp e
where d.deptno = temp.deptno and e.sal = temp.m;

select d.deptno, temp.dname, temp.c, temp.a, e.ename, e.sal
from
(select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal) m
from emp e, dept d
where e.deptno = d.deptno
group by d.dname) temp,
emp e,
dept d
where temp.m = e.sal
and temp.dname = d.dname;

90. 求出每個(gè)部門(mén)的最低工資的雇員的信息
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any(select min(sal) from emp group by deptno);
select * from
(select min(sal) m from emp group by deptno) temp,
emp e
where e.sal = temp.m;

91. 范例 90 中, 比子查詢(xún)條件中最低(小)的工資要大的雇員信息
select * from emp where sal >any(select min(sal) from emp group by deptno);
select * from emp where sal > (select min(min(sal)) from emp group by deptno);

92. 范例 90 中, 比子查詢(xún)條件中最高(大)的工資要小的雇員信息
select * from emp where sal <any(select min(sal) from emp group by deptno);
select * from emp where sal < (select max(min(sal)) from emp group by deptno);

93. 范例 90 中, 比子查詢(xún)條件中最高(大)的工資要大的雇員信息
select * from emp where sal >all(select min(sal) from emp group by deptno);
select * from emp where sal > (select max(min(sal)) from emp group by deptno);

94. 范例 90 中, 比子查詢(xún)條件中最低(小)的工資要小的雇員信息
select * from emp where sal <all(select min(sal) from emp group by deptno);
select * from emp where sal < (select min(min(sal)) from emp group by deptno);

95. 查找出 20 部門(mén)中沒(méi)有獎(jiǎng)金的雇員信息
select * from emp where (sal, nvl(comm, -1)) in (select sal, nvl(comm, -1) from emp where deptno = 20);
select * from emp where deptno = 20 and comm is null;

96. union 操作符返回兩個(gè)查詢(xún)選定的所有不重復(fù)的行
select deptno from emp union select deptno from dept;

97. union all 操作符合并兩個(gè)查詢(xún)選定的所有行，包括重復(fù)的行
select deptno from emp union all select deptno from dept;

98. intersect 操作符只返回兩個(gè)查詢(xún)都有的行
select deptno from emp intersect select deptno from dept;

99. minus 操作符只返回由第一個(gè)查詢(xún)選定但是沒(méi)有被第二個(gè)查詢(xún)選定的行, 也就是在第一個(gè)查詢(xún)結(jié)果中排除在第二個(gè)查詢(xún)結(jié)果中出現(xiàn)的行
select deptno from dept minus select deptno from emp;