(Oracle)SQL知識與40個例項

SQL語句

用具體的練習題來學習吧!(本文使用oracle自帶的表：emp表、dept表)

1.  查詢部門30中的僱員資訊。

  select * from emp where deptno=30;

2.  查詢佣金（comm）高於薪金(sal)的僱員資訊。

select * from emp where comm>sal;

3.  查詢佣金高於薪金60%的僱員資訊。

  select * from emp where comm>sal*0.6;

4.  查詢部門10中所有經理（MANAGER）和部門20中所有辦事員(CLERK)的資訊。

  select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 andjob='CLERK');

5.  查詢部門10中所有經理、部門20中所有辦事員和既不是經理又不是辦事員但薪金大於2000的所有僱員資訊。

select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or(job<>upper('manager') and job<>upper('clerk') and sal>2000);

注:本例中用到字元型函式upper把小寫轉化為大寫 ，還要注意不等於<>的用法，不等於還有另外兩種（!=和^=）。

6.  列出所有辦事員的姓名、編號、部門。

  select ename,empno,dname from emp e,dept d where e.deptno=d.deptno andjob=upper('clerk');

  注：本例中用到了多表連線查詢。

7.  查詢收取佣金的僱員的不同工作。

  select distinct job from emp where comm>0;

  注：distinct用來把重複的排除掉。

8.  查詢佣金低於100的僱員。

  select ename from emp where nvl(comm,0)<100;

  注：用nvl函式來對為空值的comm進行處理，若comm為空，則把空值轉化為0來與100比較。

9.  查詢各月最後一天受僱的僱員資訊。

  select * from emp  wherehiredate=last_day(hiredate);

注：這個例子比較有技巧，大家要好好琢磨。

10.查詢工作年限大於25年的僱員資訊。

select * from emp where months_between(sysdate,hiredate)/12>25;

注：這裡用months_between函式計算僱員工作的總月份。

select * from emp where hiredate<add_months(sysdate,-12*25);

注：日期是能夠比較大小的，本例中用add_months函式把當前日期計算到25年前。

11.查詢只有首字母大寫的僱員姓名。

   select ename from emp where ename=initcap(ename);

   注：本例中用initcap函式把ename轉化為首字母大寫的形式來與ename比較。

12.查詢正好為6個字元的僱員姓名。

   select ename from emp where length(ename)=6;

   注：本例中用length函式求ename的長度。

13.查詢不帶‘R’的僱員姓名。

   select ename from emp where ename not like '%R%';

   注：注意like的用法，%代表0個或多個任意字元，_代表一個任意字元。

14.查詢所有僱員的姓名的前3個字元。

   select substr(ename,1,3) from emp;

   注：本例中用substr函式提取ename的前三個字元。

15.查詢所有僱員姓名並用‘a’替換‘A’。

   select replace(ename,'A','a')from emp;

   注：本例中用replace函式來把A替換為a。

16.查詢所有僱員的姓名和工齡滿10年時的日期。

   select ename as 姓名,add_months(hiredate,12*10)as 工齡滿10年時的日期 from emp;

   注：本例中要注意列名的重新命名，期中as可以省略。

17.查詢僱員的資訊，按姓名排序。

   select * from emp order by ename;

注：排序就用order by,預設為升序，若要降序，要加desc關鍵字。

18.根據工作年限查詢僱員姓名，並將最老的僱員排在前面。

   select ename from emp order by hiredate;

19.查詢僱員的姓名、工作、薪金，按工作的降序排序，工作相同時按工資升序排序。

   select ename,job,sal from emp order by job desc , sal;

   注：本例中的排序用法大家要記住嗷！

20.查詢所有僱員的姓名和入職的年份和月份，按僱員受僱日所在月排序，將最早年份的排在前面。

   select ename as 姓名,to_char(hiredate,'yyyy')as 年份,to_char(hiredate,'mm') as 月份 from emp order by hiredate;

   注：注意本例中對日期hiredate的處理。

21.查詢僱員的日薪金（假定每月30天）。

   select ename as 姓名,sal/30 as日薪 from emp;

22.查詢在2月份入職的僱員資訊。

   select * from emp where to_char(hiredate,'mm')=02;

23.查詢每個僱員加入公司的天數。

   select ename 姓名,sysdate-hiredate as工作天數 from emp;

   注：兩個日期相減得出它們之間的天數。

24.查詢姓名中包含‘A’的所有僱員姓名。

   select ename from emp where ename like '%A%';

25.以年、月、日查詢每位僱員的工作年限。

   select ename as 姓名,months_between(sysdate,hiredate)/12as 工作年數,months_between(sysdate,hiredate) as 工作月數,sysdate-hiredate as 工作天數 from emp;

26.查詢至少有一個僱員的部門。

   select dname from dept where deptno in(select distinct deptno from emp);

   注：本例很有技巧，大家多看一分鐘，哈哈！

27.查詢薪金比‘SMITH’多的僱員資訊。

   select * from emp where sal>(select sal from emp whereename='SMITH');

28.查詢僱員姓名以及其直接上級的姓名。

   select a.ename as 姓名,b.ename as 上級姓名 from emp a,emp b where a.mgr=b.empno;

   注：本例可以類似的看做多表連線查詢，只不過是兩個相同的表。

29.查詢入職日期早於其直接上級的僱員資訊。

   select a.* from emp a,emp b where a.hiredate<b.hiredate anda.mgr=b.empno;

   select * from emp e where hiredate<(select hiredate from emp whereempno=e.mgr);

   注：提供兩種做法供大家參考。

30.查詢部門名稱和這些部門的僱員以及沒有僱員的部門。

   select dname,ename from dept d left join emp e on e.deptno=d.deptnoorder by dname;

   注：本例中要注意left join 的用法，就是首先提取dept表中所有的dname，再加上與dapt和emp相匹配的資料項。

31.查詢各種工作類別的最低薪金，顯示最低薪金大於1500的記錄。

   select job,min(sal) from emp group by job having min(sal)>1500;

   注：按組查詢就用group by，having條件在分組後執行。

32.查詢薪金高於公司平均工資的僱員資訊。

   select * from emp where sal>(select avg(sal) from emp);

33.查詢與‘SCOTT’工作相同的僱員資訊。

   select * from emp where job=(select job from emp where ename='SCOTT');

34.查詢某些僱員的姓名和薪金，條件是他們的薪金高於部門30中所有僱員的薪金。

   select ename,sal from emp where sal>(select max(sal) from emp wheredeptno=30);

35.查詢某些僱員的姓名和薪金，條件是他們的薪金高於部門30中任一僱員的薪金。

   select ename,sal from emp where sal>(select min(sal) from emp wheredeptno=30);

36.查詢每個部門的資訊以及該部門中僱員的數量。

 select d.deptno,dname,count(ename) from dept dleft join emp e on(e.deptno=d.deptno) group by dname,d.deptno order byd.deptno;

37.查詢從事同一種工作但屬於不同部門的僱員資訊。

   select a.ename,a.job,a.deptno,b.deptno,b.job,b.ename from emp a,emp bwhere a.job=b.job and a.deptno!=b.deptno;

38.查詢各個部門的經理的最低薪金。

   select deptno,min(sal) from emp where job='MANAGER' group by deptno;

39.查詢所有僱員的年薪並按年薪排序；

   select ename,(sal+nvl(comm,0))*12 as 年薪 fromemp order by 年薪;

40.查詢薪金水平為4的僱員資訊。

   select * from (select ename,sal,rank() over(order by sal desc) as gradefrom emp) where grade=4;

   注：本例中值得學習的是rank() over()的用法，rank為跳躍排序，若1,1 則接下來就是3.本例就是用rank多資料按sal排序。