A. oracle sql的左外連接怎麼寫
select * from tb1 left join tb2 on tb1.id=tb2.id where ........
B. 在Oracle中查詢出來後有一列中的首位數字為0，我把數據提取出來後，那個為0的數字就沒有了。
你是怎麼提取出來的。
你句子這也寫試試： to_char(chr(9)||c.dist_nbr )
C. oracle資料庫的sql語句問題：用substr()方法截取字元串，字元串的第一位是0還是1，網上說0，我試一下是1
SUBSTR(string,start,count)取子字元串，從start開始（如果start是負數，從尾部開始），取count個
上述就是PL/SQL函數的解釋，從中可以看出，是1開始從左開始取數；如果是負值，那麼就從右開始取數。
D. oracle資料庫如何修改欄位的數據類型
1、首先打開plsql，連接上oracle資料庫，假設有一張資料庫表叫做 tblTest，通過sql查詢也可以看出，這裡面是有測試數據的。
E. oracle sqlplus 命令都有那些 show命令除了 parameter外，還有什麼可以show。
一般在SQL>下進行SQLPLUS操作，都需要進行必要的環境設置才能完成我們所需要的輸出。所有環境的設置由SET命令加相應的環境變數來完成。下面是常用的環境設置：
§2.5.1 ARRAYSIZE（取回的行數）
SET ARRAY[SIZE]{integer}
一次可以提取（Fetch）的行的數目，1->5000，當有較長欄位時應設小些。
§2.5.2 AUTOCOMMIT(自動提交)
SET AUTO [COMMIT] { [ OFF | ON | IMM | n] }
用於在操作中是自動提交或是部分提交或是不自動提交。
1） 或IMM使得在完成每條SQL語句時將未提交的改變立刻提交給資料庫系統。ON
2） 允許在用戶發出COMMIT後，可以執行命令的數量（將n條SQL語句所做的改變進行提交）。N
3） 停止自動提交，用戶必須用COMMIT命令才能被提交。OFF
§2.5.3 LINESIZE(行顯示寬度)
可以設置LINESIZE環境變數來控制行的顯示寬度，預設是80個字元。
SET Lin[esize]{80|integer}
Integer =設置行寬度(字元個數)，最大值999，如：
SQL>set linesize 160
§2.5.4 LONG(長類型顯示位元組數)
在預設的SQL> 狀態下，SQL>緩沖區用於顯示LONG 的位元組數只有80個字元。如果我們需要查詢的列中含有 LONG類型的欄位的話，就需要將LONG緩沖區設置大些。
SET LONG{80|integer}
Integer是 顯示或拷貝long值的最大寬度， n=1->32767（但必須小於Maxdata值）
SQL>show Maxdata （最大行寬）
SQL>set long 2000
§2.5.5 PAGESIZE(頁行數)
在預設的SQL> 狀態下，SQL>緩沖區顯示頁的行數是24行，其中22行顯示數據，2行顯示標題和橫線。我們將pagesize 設置大些以減少提示標題和橫線。
SET pag[esize] {24|integer}
SQL>SET pagesize 66
§2.5.6 PAUSE(暫停)
可以設置 PAUSE 為ON 或OFF來控制屏幕顯示。當設置為ON 時，在select 語句發出後需要按Enter鍵才能顯示一屏。
SET PAUSE [ ON | OFF ]
SQL> setpause on
提示：在發出select 語句並按Enter鍵後還要再按Enter鍵才能顯示結果.
§2.5.7 SPACE(列間空格)
可用 set space 來設置各列間的空格數，語法為：
SET SPA[CE] {1|n}
N為設置輸出行列間的空格數，最大為10。
SQL>set space 2
建議：在一般情況下，不用設置space參數。
§2.5.8 Termout (啟/停屏幕顯示)
TERMOUT用於設置在屏幕上顯示或不顯示所輸出的信息。
SET TERMOUT { ON | OFF }
set termout off
set termout on
set termout off 常用SPOOL XXX時，即關閉報表在屏幕上的顯示（節省時間）
set termout on 常用SPOOL off之後，即恢復報表在屏幕上的顯示
§2.5.9 ECHO (啟/停命令顯示)
可以用ECHO命令來顯示或不顯示所執行的SQL命令。語法如：
SET ECHO{OFF|ON}
顯示執行當中的各命令（ 即用start 時）
set echo 受到 set termout 的影響
set pagesize 100
set echo on
select table_name from dict where rownum<20;
select * from cat where rownum<30;
set echo off
--下面只顯示結果不顯示命令：
select table_name from dict where rownum<20;
select * from cat where rownum<30;
§2.5.10 TRANSACTION (啟動事務)
一個很重要的事務環境設置是TRANSACTION。它包括兩個部分的內容：
SET TRANSACTION { READ ONLY | USE ROLLBACK SEGMENT segment_name }
READ ONLY是用於保證讀的一致性。即其他用戶的修改不影響當前查詢結果。
USE ROLLBACK SEGMENT segment_name是為當前所處理的事務指定專門的回滾段。這主要是在進行大量的Insert或Delete或Update時，需要一個大的回滾段以保證事務正常完成。詳細見資料庫管理員。
§2.5.11 SHOW ALL(列出所有參數)
可以用SHOW ALL來顯示當前的所有參數情況。它的用法很簡單。比如：
SQL> show all
appinfo為ON並且已設置為"SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and為下一條SELECT語句的前幾個字元
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
commit 0
COPYTYPECHECK為ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
markup HTML OFF SPOOL OFF ENTMAP ON PREFORMAT OFF
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE為OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 801070000
repfooter OFF and為NULL
repheader OFF and為NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and為下一條SELECT語句的前幾個字元
underline "-" (hex 2d)
USER為"SYS"
verify ON
wrap :行將為已換行
SQL>
你可以從上面的參數中看到其當前值，也可以修改某些參數的值。
格式化輸出
§2.6.1 一般數據的格式化輸出
在Oracle的SQL>下，經常用COLUMN命令來對所輸出的列進行格式化，即按照一定的格式進行顯示。COLMUN命令語法如下：
COL[UMN] [{ column | expr } [ option_1 ... option_n ] ]
column：列名
expr：有效的SQL表達式
option_1...option_n：可以是下列之一：
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
下面給出常用的關鍵字的解釋：
Alias 給出列的別名，BREAK和COUMN可以引用所定義的別名。
CLEAR 取消列的定義。
FORMAT列顯示格式，format為：
9999990 9或0的個數決定最多顯示多少位
9,999,999.99按照逗號和小數點來顯示數據，若是0以空格顯示
099999 顯示前面補0
$999,999.99 數字前加美圓號
B99999 若為0，則結果為空白
99999Mi 若數字為負，則負號放在數字後（右邊），預設放在左邊
99999PR 負號將以括弧括起
9.999EEEE 以科學記數法表示（必須有4個E）
999V99 數字乘以10n ,如1234變為123400
DATE 採用日期數字格式（MM/DD/YY）
Heading重新標記列的顯示標題，如:
SQL> col ename heading 姓名format a10
SQL> select ename,sal from emp;
例子：
SQL
COLUMN SALARY FOR $9,999,999.99
COLUMN LAST_NAME FOR A35.
§2.6.2 日期的格式化輸出
Oracle系統提供了一個NLS_DATE_FORMAT的環境變數來設置日期的顯示格式。用它可以完成按照不同格式要求的顯示，比如按照中國的習慣為yyyy年mm月dd日等。
1.系統日期 sysdate 的顯示
用sysdate 可以顯示ORACLE RDBMS 所在機器的日期及時間，如：
SQL> alter session set nls_date_format ='"公元"yyyy"年"mm"月"dd"日"';
會話已更改。
SQL> select sysdate from al;
SYSDATE
------------------
公元2001年05月30日
2.日期類型的顯示
select sysdate,to_char(sysdate,』yyyy.mm.dd hh24:mi;ss』) from al;
SQL> connect scott/tiger
已連接。
SQL> alter session set nls_date_format ='yyyy"年"mm"月"dd"日生"';
會話已更改。
SQL> col HIREDATE heading生日
SQL> col sal heading工資
SQL> col sal ename姓名
SQL> select ename,sal,hiredate from emp;
姓名 工資 生日
---------- ---------- -----------------
SMITH 800 1980年12月17日生
ALLEN 1600 1981年02月20日生
WARD 1250 1981年02月22日生
JONES 2975 1981年04月02日生
MARTIN 1250 1981年09月28日生
BLAKE 2850 1981年05月01日生
CLARK 2450 1981年06月09日生
SCOTT 3000 1987年04月19日生
KING 5000 1981年11月17日生
TURNER 1500 1981年09月08日生
ADAMS 1100 1987年05月23日生
JAMES 950 1981年12月03日生
FORD 3000 1981年12月03日生
MILLER 1300 1982年01月23日生
已選擇14行。
§2.7 加標題
有時在輸出一些結果時，可能需要加一些標題，如表上面的頂標題，落款等。這樣的要求可由Ttitle和Btitle來完成。
ttitle和btitle
ttitle [center|left|right]string 頂標題
btitle [center|left|right]string 底標題
ttitle center 'XX公司人員情況表'
btitle left '製表人：xxxx' right '日期：xxxx年xx月'
Clear ttitle
§2.8 建立簡單報告
我們可以用TTITLE、BTITLE、COLUMN、BREAK ON、COMPUTE SUM及SET LINESIZE、SET PAGESIZE、SET NEWPAGE來設置查詢結果的顯示格式；在用SPOOL命令將顯示結果輸出到一個操作系統文件中去，一般輸出文件的類型為.LST。
建立簡單報告主要使用下面命令來實現：
1． SPOOL命令
SPOOL filename 將緩沖區的內容寫到文件中
SPOOL off 終止寫命令
2． COLUMN 命令
column col_name[，heading] format format_spe
把欄位的結果指定為一種輸出格式
COL name heading '姓名' for a10
COL sal heading '工資' for 9,999.99
3.ttitle、btitle
ttitle [center|left|right]string 頂標題
btitle [center|left|right]string 底標題
ttitle center 'XX公司人員情況表'
btitle left '製表人：趙元傑' right '日期：1998.11月'
Clear ttitle
4.break、compute
clear breaks,clear computes
break on column 在該列上中斷
break on row 在每一行上中斷
break on Page
break on report
skip n 跳過n行
skip page 跳過未用完的頁
compute avg
compute count
compute max
compute min
compute std
compute sum
compute var
compute num 計算所有行
compute sum of sal on deptno
5.set 在報表中的設置
l set termout off、set termout on命令
l set termout off 常用SPOOL XXX前，即關閉報表在屏幕上的顯示（節省時間）
l set termout on 常用SPOOL off之後，即恢復報表在屏幕上的顯示
l set ECHO{OFF|ON} 顯示執行當中的各命令（即用start 時），set echo 受到 set termout 的影響
l set Lin[esize]{80|integer} 設置行寬度，最大值999
l set pag[esize] {24|integer} 設置頁的大小
例子：
SQL>COL ename heading 『姓名』 for a12
SQL>COL sal heading 『工資』 for a999,999.99
SQL>COL hiredate heading 『出生』
SQL>SET LINESIZE 200
SQL>SET PAGESIZE 60
SQL>SPOOL c:\all_emp
SQL>select ename,sal,deptno,hiredate from emp order by deptno;
SQL>SPOOL OFF
§2.9 輸入變數
Oracle提供一種在處理SQL語句時可以將參數作為變數來對待的技術，即在條件句中可以是變數而不是具體的值，這樣的處理就是輸入變數。這樣做的目的就是可以重復使用同樣的語句，每次只要輸入相應的值即可。要實現將參數寫成為變數，只要在變數前加一個&號即可。看下面語句：
Select sid, serial#,username, command from v$session
Where USERNAME = upper(『&usr』);
這樣的語句在運行中，系統會自動提示你回答變數的具體值，上面語句運行時提示和回答時顯示的信息如下：
SQL> Select sid, serial#,username, command from v$session
2 Where USERNAME = upper('&usr');
輸入 usr 的值: sys
原值 2: Where USERNAME = upper('&usr')
新值 2: Where USERNAME = upper('sys')
SID SERIAL# USERNAME COMMAND
---------- ---------- ------------------------------ ----------
7 26 SYS 3
在變數說明中，可以使用多個變數，比如：
Alter system kill session 『&sid,&ser』;
或
Alter system kill session 『&會話號,&序列號』;
它的運行情況如下：
SQL> Select sid, serial#,username, command from v$session;
SID SERIAL# USERNAME COMMAND
---------- ---------- ------------------------------ ----------
1 1 0
2 1 0
3 1 0
4 1 0
5 1 0
6 1 0
7 26 SYS 3
8 16 ZHAO 0
已選擇8行。
SQL> Alter system kill session '&sid,&ser';
輸入sid的值: 8
輸入ser的值: 16
原值 1: Alter system kill session '&sid,&ser'
新值 1: Alter system kill session '8,16'
系統已更改。
一般系統預設下是使用 「&」 符號來定義變數，你也可以使用另外的符號來代替，比如不喜歡用 & 而要用 ？，則有：
SQL> set define ?
SQL> select sid,serial#,username from v$session where username='?usr';
輸入 usr 的值: SYS
原值 1: select sid,serial#,username from v$session where username='?usr'
新值 1: select sid,serial#,username from v$session where username='SYS'
SID SERIAL# USERNAME
---------- ---------- ------------------------------
7 26 SYS
次時由於我們修改了會話環境的參數值，可以用下面命令查看：
SQL> show define
define "?" (hex 3f)
F. Oracle SQL查詢和SQL函數 問題
誒亞
看來我的貼幾章書才行
1.請總結為日期欄位賦值的幾種方式，並舉例說明
oracle中有個到當前系統時間--sysdate，如：
select sysdate from al
可對日期進行自述運算：
select (sysdate-mybirthday)/7 from person
months_between('01-sep-95','11-jan-94')---取得二個日期之間的間隔月數(19.6774194)
add_months('11-jan-94',6)---給指定日期加上指定的月份後得到一個新的日期(11-jul-94)
next_day('01-sep-85','friday')---取得當前日期中下個周五的日期(01-jul-95)
last_day('01-feb-95')---取得當前日期中月份的最後一天(28-feb-95)
round進行四捨五入，trunc則否,以下是我的操作結果：
sysdate為：
SYSDATE
----------
28-7月 -06
select
round(sysdate,'month') RM,
round(sysdate,'year') RY,
trunc(sysdate,'month') TM,
trunc(sysdate,'year') TY
from al;
RM RY TM TY
---------- ---------- ---------- ----------
01-8月 -06 01-1月 -07 01-7月 -06 01-1月 -06
2.請總結select語句的幾種用法，並舉例說明
inner join
left join
right join
cross join (一般不允許用）
3.請總結SQL操作符，每種操作符各舉一例說明
||字元串相加
>
<
=
Like
Between and
4.請總結SQL單行函數，每個函數各舉一例說明
越全面越好,
>字元處理
upper(str)---將字元串str全部轉換成大寫
lower(str)---將字元串str全部轉換成小寫
initcap(str)---將字元串中每個單詞的首字母大寫
concat(str1,str2)---將字元串str1與str2連接起來(也可以通過'||'號直接相連)
substr(str,a,b)---取字元串str中的指定字元，從位置a開始取長度為b的字元串，假如a為正則從左邊開始，否則從右邊開始
instr(str,'z')---取得str字元串中從左邊開始每一次出現z字元的下標位置(下標從1開始)
lpad(str,12,'*')---左填充，即將字元串str長度填充到12，假如其不足12位則在左邊以*號填充
rpad(str,12,'*')---右填充，同上
length(str)---計算字元串str的長度
2>數字函數
round(45.926,2)---將前一數保留指定的小數位，並四捨五入(45.93)，假如指定位是負數則意為在小數點左邊保留指定位,如round(45.923,-1)=50,rount(45.923,0)=46,round(45.93,-2)=0,round(55.93,-2)=100
trunc(45.926,2)---同上，得不四捨五入(45.92)
mod(1600,300)---求余(100)
G. oracle資料庫的sql語句問題：用substr()方法截取字元串，字元串的第一位是0還是1，網上說0，我試一下是1
select
substr('a123456',instr('a123456'，'a')+1,len('a123456')-instr('a123456','a'))
from
al
instr
返回1，
所以給它+1
，
從第2位開始截取到總長度-『a』這個字元串的位置，
就是
6
，
所以最終會是
select
substr('a123456',2,6)
from
al
這么寫能明白嗎？
H. 請教在MSSQL中有和ORACLE里LPAD一樣的補齊字元函數嗎
Oracle lpad函數將左邊的字元串填充一些特定的字元，其語法格式如下： lpad( string1, padded_length, [ pad_string ] )
其中string1是需要粘貼字元的字元串 padded_length是返回的字元串的數量，如果這個數量比原字元串的長度要短，
lpad函數將會把字元串截取成padded_length; pad_string是個可選參數，這個字元串是要粘貼到string1的左邊，
如果這個參數未寫，lpad函數將會在string1的左邊粘貼空格。
例如： lpad('tech', 7); 將返回' tech'
lpad('tech', 2); 將返回'te'
lpad('tech', 8, '0'); 將返回'0000tech'
lpad('tech on the net', 15, 'z'); 將返回 'tech on the net'
lpad('tech on the net', 16, 'z'); 將返回 'ztech on the net'
Lpad（str1,number,str2），這個函數的意思是，如果str1不足number那麼多位，則使用str2去補齊左邊的空位
I. oracle sql 如何選取數據集中四分位數(min,25%,MED,75%max)
-- 首先，以超級管理員的身份登錄oracle
sqlplus sys/bjsxt as sysdba
--然後，解除對scott用戶的鎖
alter user scott account unlock;
--那麼這個用戶名就能使用了。
--(默認全局資料庫名orcl)
1、select ename, sal * 12 from emp; --計算年薪
2、select 2*3 from al; --計算一個比較純的數據用al表
3、select sysdate from al; --查看當前的系統時間
4、select ename, sal*12 anuual_sal from emp; --給搜索欄位更改名稱（雙引號 keepFormat 別名有特殊字元，要加雙引號）。
5、--任何含有空值的數學表達式，最後的計算結果都是空值。
6、select ename||sal from emp; --(將sal的查詢結果轉化為字元串，與ename連接到一起，相當於Java中的字元串連接)
7、select ename||'afasjkj' from emp; --字元串的連接
8、select distinct deptno from emp; --消除deptno欄位重復的值
9、select distinct deptno , job from emp; --將與這兩個欄位都重復的值去掉
10、select * from emp where deptno=10; --(條件過濾查詢)
11、select * from emp where empno > 10; --大於 過濾判斷
12、select * from emp where empno <> 10 --不等於 過濾判斷
13、select * from emp where ename > 'cba'; --字元串比較，實際上比較的是每個字元的AscII值，與在Java中字元串的比較是一樣的
14、select ename, sal from emp where sal between 800 and 1500; --(between and過濾，包含800 1500)
15、select ename, sal, comm from emp where comm is null; --(選擇comm欄位為null的數據)
16、select ename, sal, comm from emp where comm is not null; --(選擇comm欄位不為null的數據)
17、select ename, sal, comm from emp where sal in (800, 1500,2000); --(in 表范圍)
18、select ename, sal, hiredate from emp where hiredate > '02-2月-1981'; --(只能按照規定的格式寫)
19、select ename, sal from emp where deptno =10 or sal >1000;
20、select ename, sal from emp where deptno =10 and sal >1000;
21、select ename, sal, comm from emp where sal not in (800, 1500,2000); --(可以對in指定的條件進行取反)
22、select ename from emp where ename like '%ALL%'; --(模糊查詢)
23、select ename from emp where ename like '_A%'; --(取第二個字母是A的所有欄位)
24、select ename from emp where ename like '%/%%'; --(用轉義字元/查詢欄位中本身就帶%欄位的)
25、select ename from emp where ename like '%$%%' escape '$'; --(用轉義字元/查詢欄位中本身就帶%欄位的)
26、select * from dept order by deptno desc; (使用order by desc欄位 對數據進行降序排列 默認為升序asc);
27、select * from dept where deptno <>10 order by deptno asc; --(我們可以將過濾以後的數據再進行排序)
28、select ename, sal, deptno from emp order by deptno asc, ename desc; --(按照多個欄位排序 首先按照deptno升序排列,當detpno相同時，內部再按照ename的降序排列)
29、select lower(ename) from emp; --(函數lower() 將ename搜索出來後全部轉化為小寫)；
30、select ename from emp where lower(ename) like '_a%'; --(首先將所搜索欄位轉化為小寫，然後判斷第二個字母是不是a)
31、select substr(ename, 2, 3) from emp; --(使用函數substr() 將搜素出來的ename欄位從第二個字母開始截，一共截3個字元)
32、select chr(65) from al; --(函數chr() 將數字轉化為AscII中相對應的字元)
33、select ascii('A') from al; --(函數ascii()與32中的chr()函數是相反的 將相應的字元轉化為相應的Ascii編碼） )
34、select round(23.232) from al; --(函數round() 進行四捨五入操作)
35、select round(23.232, 2) from al; --(四捨五入後保留的小數位數 0 個位 -1 十位)
36、select to_char(sal, '$99,999.9999')from emp; --(加$符號加入千位分隔符，保留四位小數，沒有的補零)
37、select to_char(sal, 'L99,999.9999')from emp; --(L 將貨幣轉化為本地幣種此處將顯示￥人民幣)
38、select to_char(sal, 'L00,000.0000')from emp; --(補零位數不一樣，可到資料庫執行查看)
39、select to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp; --(改變日期默認的顯示格式)
40、select to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from al; --(用12小時制顯示當前的系統時間)
41、select to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from al; --(用24小時制顯示當前的系統時間)
42、select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS'); --(函數to-date 查詢公司在所給時間以後入職的人員)
43、select sal from emp where sal > to_number('$1,250.00', '$9,999.99'); --(函數to_number()求出這種薪水裡帶有特殊符號的)
44、select ename, sal*12 + nvl(comm,0) from emp; --(函數nvl() 求出員工的"年薪 + 提成(或獎金)問題")
45、select max(sal) from emp; -- (函數max() 求出emp表中sal欄位的最大值)
46、select min(sal) from emp; -- (函數max() 求出emp表中sal欄位的最小值)
47、select avg(sal) from emp; --(avg()求平均薪水);
48、select to_char(avg(sal), '999999.99') from emp; --(將求出來的平均薪水只保留2位小數)
49、select round(avg(sal), 2) from emp; --(將平均薪水四捨五入到小數點後2位)
50、select sum(sal) from emp; --(求出每個月要支付的總薪水)
------------------------/組函數(共5個)：將多個條件組合到一起最後只產生一個數據------min() max() avg() sum() count()----------------------------/
51、select count(*) from emp; --求出表中一共有多少條記錄
52、select count(*) from emp where deptno=10; --再要求一共有多少條記錄的時候，還可以在後面跟上限定條件
53、select count(distinct deptno) from emp; --統計部門編號前提是去掉重復的值
------------------------聚組函數group by() --------------------------------------
54、select deptno, avg(sal) from emp group by deptno; --按照deptno分組，查看每個部門的平均工資
55、select max(sal) from emp group by deptno, job; --分組的時候，還可以按照多個欄位進行分組，兩個欄位不相同的為一組
56、select ename from emp where sal = (select max(sal) from emp); --求出
57、select deptno, max(sal) from emp group by deptno; --搜素這個部門中薪水最高的的值
--------------------------------------------------having函數對於group by函數的過濾 不能用where--------------------------------------
58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; （order by ）--求出每個部門的平均值，並且要 > 2000
59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;--求出sal>1200的平均值按照deptno分組，平均值要>1500最後按照sal的倒序排列
60、select ename,sal from emp where sal > (select avg(sal) from emp); --求那些人的薪水是在平均薪水之上的。
61、select ename, sal from emp join (select max(sal) max_sal ,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno=t.deptno); --查詢每個部門中工資最高的那個人
------------------------------/等值連接--------------------------------------
62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; --自連接，把一張表當成兩張表來用
63、select ename, dname from emp, dept; --92年語法 兩張表的連接 笛卡爾積。
64、select ename, dname from emp cross join dept; --99年語法 兩張表的連接用cross join
65、select ename, dname from emp, dept where emp.deptno = dept.deptno; -- 92年語法 表連接 + 條件連接
66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); -- 新語法
67、select ename,dname from emp join dept using(deptno); --與66題的寫法是一樣的，但是不推薦使用using ： 假設條件太多
--------------------------------------/非等值連接------------------------------------------/
68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); --兩張表的連接 此種寫法比用where更清晰
69、select ename, dname, grade from emp e
join dept d on(e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%'; --三張表的連接
70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); --自連接第二種寫法，同62
71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); --左外連接 把左邊沒有滿足條件的數據也取出來
72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); --右外連接
73、select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);--求每個部門平均薪水的等級
74、select ename from emp where empno in (select mgr from emp); -- 在表中搜索那些人是經理
75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); -- 面試題 不用組函數max()求薪水的最大值
76、select deptno, max_sal from
(select avg(sal) max_sal,deptno from emp group by deptno)
where max_sal =
(select max(max_sal) from
(select avg(sal) max_sal,deptno from emp group by deptno)
);--求平均薪水最高的部門名稱和編號。
77、select t1.deptno, grade, avg_sal from
(select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
)--求平均薪水等級最低的部門的名稱 哈哈 確實比較麻煩
78、create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
--視圖的創建，一般以v$開頭，但不是固定的
79、select t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
v$_dept_avg_sal_info t1
)
)--求平均薪水等級最低的部門的名稱 用視圖，能簡單一些，相當於Java中方法的封裝
80、---創建視圖出現許可權不足時候的解決辦法：
conn sys/admin as sysdba;
--顯示：連接成功 Connected
grant create table, create view to scott;
-- 顯示: 授權成功 Grant succeeded
81、-------求比普通員工最高薪水還要高的經理人的名稱 -------
select ename, sal from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
)
82、---面試題：比較效率
select * from emp where deptno = 10 and ename like '%A%';--好，將過濾力度大的放在前面
select * from emp where ename like '%A%' and deptno = 10;
83、-----表的備份
create table dept2 as select * from dept;
84、-----插入數據
insert into dept2 values(50,'game','beijing');
----只對某個欄位插入數據
insert into dept2(deptno,dname) values(60,'game2');
85、-----將一個表中的數據完全插入另一個表中（表結構必須一樣）
insert into dept2 select * from dept;
86、-----求前五名員工的編號和名稱（使用虛欄位rownum 只能使用 < 或 = 要使用 > 必須使用子查詢）
select empno,ename from emp where rownum <= 5;
86、----求10名雇員以後的雇員名稱--------
select ename from (select rownum r,ename from emp) where r > 10;
87、----求薪水最高的前5個人的薪水和名字---------
select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5;