Oracle練習(1)

練習一

1、查詢dept表的結構
   在命令視窗輸入：
   desc dept;

2、檢索dept表中的所有列資訊
   select * from dept

3、檢索emp表中的員工姓名、月收入及部門編號
   select ename "員工姓名",sal "月收入",empno "部門編號" from emp
   注意查詢欄位用分號隔開。

4、檢索emp表中員工姓名、及僱傭時間
   日期資料的預設顯示格式為“DD-MM-YY",如果希望使用其他顯示格式(YYYY-MM-DD),那麼必須使用TO_CHAR函數進行轉換。
   select ename "員工姓名", hiredate "僱用時間1",to_char(hiredate,'YYYY-MM-DD') "僱用時間2" from emp
   注意：第一個時間是日期類型的，在Oracle的查詢介面它的旁邊帶有一個日曆。
         第二個時間是字元型的。
   易錯點：不要將YYYY-MM-DD使用雙引號

5、使用distinct去掉重複行。
   檢索emp表中的部門編號及工種，並去掉重複行。
   select  distinct deptno "部門編號",job "工種"  from emp order by deptno
   注意distinct放的位置
   為什麼不放在from的前面？
   翻譯成漢語就明白了
   應該是：選擇不重複的部門編號和工種從emp表。而不是：選擇部門編號和工種不重複地從emp表。這還是人話嗎？？？O(∩_∩)O哈哈~

6、使用運算式來顯示列
   檢索emp表中的員工姓名及全年的月收入
   select ename "員工姓名", (sal+nvl(comm,0))*12 "全年度營收" from emp
   注意：防止提成comm為空白的操作，使用nvl函數

7、使用列別名
   用姓名顯示員工姓名，用年度營收顯示全年月收入。
   select ename "員工姓名",sal*12 "全年度營收" from emp

8、連接字串
　 在oracle中連接字串用“||”操作符來完成的，當連接字串時，如果在字串要加入數字值
   那麼在“||”後可以直接指定數字，如果在字串加入字元和日期值，則必須要用單引號。
   檢索emp表，用is a 這個字串來串連員工姓名和工種兩個欄位

   select ename||' is a '||job "他們各自的職位"from emp
   注意：用的是單引號！！！

9、使用WHERE子句
   檢索月收入大於2000的員工姓名及月收入。
   select ename "姓名" ,sal "月薪"from emp where sal>2000
   檢索月收入在1000元到2000元的員工姓名、月收入及僱傭時間。
   select ename "姓名" ,sal "月薪",hiredate "僱傭時間" from emp where sal between 1000 and 2000

10、like的用法：
    檢索以S開頭的員工姓名及月收入。
    select ename "員工姓名",sal "月收入" from emp where ename like 'S%'
    檢索員工姓名中的第三個字元是A的員工姓名及月收入。
    select ename "員工姓名",sal "月收入" from emp where ename like '__A%'
    注意這裡A前面有兩個萬用字元。
    且注意like後面使用的是單引號

11、在WHERE條件中使用IN操作符
    檢索emp表中月收入是800的或是1250的員工姓名及部門編號
    select ename "姓名",deptno "部門編號",sal "工資" from emp where sal in(800,1250)
    注意:IN的意思是或者。是800或者1250而不是表示範圍

12、在WHERE條件中使用邏輯操作符(AND、OR、NOT)
    顯示在部門20中崗位CLERK的所有僱員資訊
    select * from emp where deptno='20' and job='CLERK'
    顯示工資高於2500或崗位為MANAGER的所有僱員資訊
    select * from emp where sal>'2500' or job='MANAGER'
    注意：在where裡面的條件都是使用的單引號

13、查詢表中是空值的資料
    檢索emp表中有提成的員工姓名、月收入及提成。
    select ename "姓名",comm "提成",sal "工資" from emp where comm is not null

14、使用ORDER BY子句，進行排序。
    檢索emp表中部門編號是30的員工姓名、月收入及提成，並要求其結果按月收入升序、然後按提成降序顯示。
    select ename "姓名",comm "提成",sal "工資" from emp where deptno='30' order by sal asc,comm desc

練習二

1. 查詢工資大於1200的員工姓名和工資
   select ename "姓名",sal "工資" from emp where sal>1200

2. 查詢員工號為7934的員工的姓名和部門號
   select ename "姓名",deptno "部門編號" from emp where empno='7934'

3. 選擇工資不在5000到12000的員工的姓名和工資
   select ename "姓名",sal "工資" from emp where sal not between 5000 and 12000
   注意:不是 is not

4. 選擇僱用時間在1981-02-01到1981-05-01之間的員工姓名，職位(job)和僱用時間,按從早到晚排序.
   select ename "姓名",job "職位",hiredate "僱傭時間"
   from emp where hiredate between to_date('1981-02-01','YYYY-MM-DD') and to_date('1981-05-01','YYYY-MM-DD')
   order by hiredate asc
   總結：
   1，要轉換為字元類型轉換為日期類型！！！否則無法比較
   2，1981-05-01要用單引號引起來！！！否則報錯——日期格式不夠長
   這個例子很重要！！！！！！！！！！！！！！！！！！！！！！！！

5. 選擇在20或10號部門工作的員工姓名和部門號
   select ename "姓名",deptno "部門號" from emp where deptno in ('20','10')
   select ename "姓名",deptno "部門號" from emp where deptno in (20,10)
   加不加單引號都可以。那麼有什麼區別呢？？？？？？？？？？？？？？？？？？？

6. 選擇在1987年僱用的員工的姓名和僱用時間
   select ename "姓名",hiredate "僱傭時間" from emp where to_char(hiredate,'YYYY')='1987'
   取出僱用時間的年份且轉換為字元形式；然後與'1987'比較
   select ename "姓名",hiredate "僱傭時間" from emp where to_char(hiredate,'MM')='04'
   select ename "姓名",hiredate "僱傭時間" from emp where to_char(hiredate,'MM')='4'
   前者是可以的，後者不可以？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？
   這個例子也很重要！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

7. 選擇公司中沒有管理者的員工姓名及job
   select ename "姓名",job "工作" from emp where mgr is null

8. 選擇公司中有獎金 (COMM不為空白,且不為0) 的員工姓名，工資和獎金比例,按工資逆排序,獎金比例逆排序.     
   select ename "姓名",sal "工資",comm "獎金比例"from emp where comm is not null and comm !=0 order by sal desc,comm desc

9. 選擇員工姓名的第三個字母是a的員工姓名
   select ename "姓名"from emp where ename='__A'
   select ename "姓名"from emp where ename like '__A%'
   總結：
   1，前者是錯的，這樣定死了：一共只有三個字母，且最後一個是A
   2，後者正確。表示A後的不限定
   這個例子也很重要，可以用於模糊查詢！！！！！！！！！！！！！！

練習三

1. 顯示系統時間(取別名為“DATE”).
   select sysdate "Date" from dual
   注意：dual是Oracle系統的一個虛擬表
  
2. 查詢員工號，姓名，工資(若為NULL則作為0處理)，補助(若為NULL則作為0處理)
   以及工資提高百分之20%後四捨五入到整數的結果（取別名為new salary）
   select ename "姓名",empno "員工號",nvl(sal,0) "原來的sal",round(sal*(1+0.2),2) "new salary",nvl(comm,0) "補助"from emp
   總結：
   這裡用了兩個很重要的函數：nvl和round()用於四捨五入——比如將原工資除以7，只保留三位小數
   select round(sal/7,3) "new sal" from emp

3. 將員工的姓名（取別名為"Name"）按字母表先後順序排序，並寫出姓名的長度（取別名為"length"）
   select ename "Name",length(ename) "length" from emp order by ename asc
   select ename "名字",length(ename) "名字的長度" from emp order by ename asc

4. 查詢各員工的姓名，並顯示出各員工在公司工作了多少個月份（起別名為"worked_month"）四捨五入到整數.
   select ename "名字",round(months_between(sysdate,hiredate),0) "worked_month" from emp
   計算指定日期到僱傭日期有多少個月？
   select ename "名字",round(months_between(to_date('1990-01-01','YYYY-MM-DD'),hiredate),0) "worked_month" from emp
   計算兩個日期之間有多少個月？
   select ename "名字",round(months_between(to_date('1990-01-01','YYYY-MM-DD'),to_date('1980-01-01','YYYY-MM-DD')),0) "worked_month" from emp
   注意:months_between()這個函數裡的兩個參數是日期類型，所以需要轉型！！！！！！！！！！！！！！！！

5. 查詢員工的姓名和工資，按下面的形式顯示結果(工資欄位必須為15位,空位用$填充)
   姓名   工資
   KING   $$$$$$$$$$24000
   MIKE   $$$$$$$$$$$4800

   select ename "名字",lpad(sal,15,'$') "工資" from emp
   注意lpad的最後一個參數，因為它是字元型的，所以用單引號

6. 查詢員工的姓名，以及在公司工作滿了多少個月（worked_month）,並按月份數降序排列
   select ename "姓名",months_between(sysdate,hiredate) "worked_month" from emp order by  having worked_month desc(錯誤的)
   select ename "姓名",months_between(sysdate,hiredate) "worked_month" from emp order by "worked_month" desc(不太正確的)
   注意order by後面的雙引號
   select ename "姓名",trunc(months_between(sysdate,hiredate)) "worked_month" from emp order by "worked_month" desc
   trunc()函數表示只取整數！！！！！！！！！！題目要求的是"滿了多少個月"，所以應該取整
   為什麼可以使用worked_month？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？

7. 做一個查詢，按下面的形式顯示結果
   <ename> earns <sal> monthly but wants <sal*3>
   Dream Salary
   KING earns $24000 monthly but wants $72000
   JONE earns $18000 monthly but wants $54000
   select upper(ename)||' earns '||to_char(sal,'$99999.99')|| ' per month but wants '||to_char(sal*3,'99999.999') "Dream Salary" from emp
   總結(這個也很常用):
   9，代表一位元字，如果當前位有數字，顯示數字，否則不顯示(小數部分仍然會強制顯示）
   0，強制顯示該位，如果當前位有數字，顯示數字，否則顯示0

8. 做一個查詢，按下面的形式顯示結果(注意兩個日期之間的關係)需要設定語言環境:  ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
   ENAME HIREDATE 6個月零4天后
   KING         17-JUN-87 MONDAY,the TWENTY-FIRST of DECEMBER , 1987

   alter session set nls_language='SIMPLIFIED CHINESE'

   Select upper(ename),to_char(hiredate,'dd-MON-yy'),
   to_char(add_months(hiredate,6)+4,'DAY ",the" DDSPTH " of" MONTH "," yyyy') from emp

   ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
   感覺這道題沒有意思，這是老師的答案。

9. 做一個查詢，按下面的形式顯示結果
   Employees_and_their_salarys
   King********
   Zhang*****
   Wang*****
   其中每一個*代表一千元(四捨五入)。按工資從多到少排序.
   select rpad(initcap(ename),length(ename)+round(sal/1000),'*') "一個*代表1000塊錢" from emp order by sal desc
   注意rpad()函數的參數。第一個是要填充的對象，第二個知道了填充後的長度，第三個指明用什麼填充
   此例子中第二個參數很好，有點意思！！！！！！！！！！！！！！！！！！！！！！！

10. 使用decode函數，按照下面的條件：(根據job類別來確定層級GRADE)
    job                                         grade

    AD_PRES     A
    ST_MAN     B
    IT_PROG     C
    SA_REP      D
    ST_CLERK                E
      OTHER
    產生下面的結果:
    ENAME Job_id Grade

    king AD_PRES A
    kate IT_PROG C

  
select ename "姓名",job "工作" ,sal "薪水",decode(job,
                                                  'CLERK','A',
                                                  'SALESMAN','B',
                                                  'PRESIDENT','C',
                                                  'MANAGER','D',
                                                            'E'
                                                  )等級
                                               from emp

    注意比較和下面的異同

11. 將上一題的查詢用case函數再寫一遍。

    select ename "姓名",job "工作" ,sal "薪水",case job
                                               when 'CLERK' then 'A'
                                               when 'SALESMAN' then 'B'
                                               when 'PRESIDENT' then 'C'
                                               when 'MANAGER' then 'D'
                                               else 'E'
                                               end "等級"
                                               from emp

  注意它的骨幹是select……case(要判斷的列)……when then(寫在同一行，可讀性好)……else end(用於起別名) from……

  //這個是可以的
   select ename "姓名",job "工作", sal "薪水",case sal
                                           when to_number(800.00) then 'A'
                                           when to_number(1600.00) then 'B'
                                           else 'C'
                                           end "工資等級"
                                           from emp

 

//怎麼樣按照工資排序，這是錯誤的？
參見day3232筆記

select ename "姓名",job "工作", sal "薪水",case sal
                                           when to_number('3000')<sal<to_number('6000') then 'A'
                                           when to_number('1000')<sal<to_number('3000') then 'B'
                                           else 'D'
                                           end "工資等級"
                                           from emp

練習四

1.查詢公司員工工資的最大值，最小值，平均值，總和
  select max(sal) "最高工資",min(sal) "最低工資",round(avg(sal),2) "平均工資",sum(sal) "工資總和" from emp

2.查詢各job的員工工資的最大值，最小值，平均值，總和
  select job "工種",max(sal) "最高工資",min(sal) "最低工資",round(avg(sal),2) "平均工資",sum(sal) "工資總和" from emp group by job

3.選擇具有各個job 的員工人數
  select job "工種",count(*) "此工種的員工人數" from emp group by job

4.查詢員工最高工資和最低工資的差距（DIFFERENCE）
  select max(sal)-min(sal) "DIFFERENCE" from emp

5.查詢公司的人數，以及在80,81,82,87年，每年僱用的人數，結果類似下面的格式
  total 1980 1981 1982 1987
  14  1  10  1  2
  select count(*) "total",
  sum(decode(to_char(hiredate,'YYYY'),1980,1,0)) "1980",
  sum(decode(to_char(hiredate,'YYYY'),1981,1,0)) "1981",
  sum(decode(to_char(hiredate,'YYYY'),1982,1,0)) "1982",
  sum(decode(to_char(hiredate,'YYYY'),1987,1,0)) "1987"
  from emp

 這個例子相當相當重要！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
 其中select decode(to_char(hiredate,'YYYY'),1980,1,0) from emp這個語句是核心
 如果年份是1980的將其置為1，否則為0，這樣操作了所有的年份(相當於形成了一張暫存資料表)
 然後再利用sum計算其和(即計算這個暫存資料表的總和就知道有多少個1980了)！！！
 
 同理可以判斷每個月份有多少人入職
 select
 sum(decode(to_char(hiredate,'MM'),02,1,0)) "2月入職",
 sum(decode(to_char(hiredate,'MM'),03,1,0)) "3月入職",
 sum(decode(to_char(hiredate,'MM'),04,1,0)) "4月入職",
 sum(decode(to_char(hiredate,'MM'),05,1,0)) "5月入職"
 from emp