ORACLE百例試煉五

Oracle系列《五》：SQL綜合練習(xí)

網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、小程序設(shè)計(jì)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了白城免費(fèi)建站歡迎大家使用！

【1】列出最低薪金大于1500的各種工作及從事此工作的全部雇員人數(shù)

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

【2】列出在部門(mén)'SALES'工作的員工姓名

<1>先查詢SALES的部門(mén)編號(hào)

SQL> SELECT deptno FROM dept WHERE dname='SALES';

<2>SELECT ename FROM emp

WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');

【3】列出薪金高于公司平均薪金的所有員工，所在部門(mén)，上級(jí)領(lǐng)導(dǎo)，公司的工資等級(jí)

<1>求出公司平均薪金

SQL> SELECT AVG(sal) FROM emp;

<2>列出薪金高于平均薪金的所有員工

SQL> SELECT * FROM emp

WHERE sal>(SELECT AVG(sal) FROM emp);

<3>查詢所在部門(mén)信息

SQL> SELECT e.*,d.dname,d.loc FROM emp e,dept d

WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno;

<4>查詢上級(jí)領(lǐng)導(dǎo)

SQL>

SQL>SELECT e.empno,e.ename,m.empno,m.ename,d.deptno,d.dname,d.loc

FROM emp e,dept d,emp m

WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr = m.empno(+);

<5>求出工資的工資等級(jí)

SQL> SELECT

e.empno,e.ename,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc

FROM emp e,dept d,emp m,salgrade s

WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+)

AND e.sal BETWEEN s.losal AND s.hisal;

【4】列出與"SCOTT"從事相同工作的所有員工及部門(mén)名稱

<1>找出與SCOTT相同工作的雇員，但不能包括自己

SQL> SELECT empno,ename,job FROM emp

WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';

<2>與部門(mén)表關(guān)聯(lián)，查詢部門(mén)名稱

SQL> SELECT e.empno,e.ename,e.job,d.dname FROM emp e,dept d

WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';

AND e.deptno=d.deptno;

【5】列出薪金等于部門(mén)30中員工的薪金的所有員工的姓名和薪金（本題無(wú)結(jié)果，薪金都和30號(hào)部門(mén)不一樣）

<1>列出部門(mén)30員工的薪金

SQL> SELECT sal FROM emp WHERE deptno=30;

<2>上述條件作為子查詢，這里注意上述結(jié)果是返回多條記錄的，所以要用IN

SQL> SELECT ename,sal FROM emp

WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;

【6】列出在每個(gè)部門(mén)工作的員工數(shù)量、平均工資和平均服務(wù)期限

<1>每個(gè)部門(mén)的員工數(shù)量：可求出部門(mén)名稱

SQL> SELECT d.dname,COUNT(e.empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno GROUP BY d.dname;

<2>求平均工資和服務(wù)年限

SQL> SELECT d.dname,COUNT(e.empno),AVG(e.sal),AVG(MONTH_BETWEENS(sysdate,hiredate)/12) year

FROM emp e,dept d

WHERE e.deptno=d.deptno GROUP BY d.dname;

【7】列出所有部門(mén)的詳細(xì)信息和部門(mén)人數(shù)

<1>列出所有部門(mén)人數(shù)

SQL> SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;

<2>將以上查詢當(dāng)做一張臨時(shí)表

SQL> SELECT d.*,ed.cou

FROM dept d,(SELECT deptno,COUNT(empno) cou from emp GROUP BY deptno) ed

WHERE d.deptno = ed.deptno;

但是以上沒(méi)有40部門(mén)的信息，則應(yīng)該使用NVL和左連接操作

SQL> SELECT d.*,NVL(ed.cou,0)

FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed

WHERE d.deptno = ed.deptno(+);

【8】列出各種工作的最低工資及從事該工作的雇員姓名

<1>按照工作分組，使用MIN函數(shù)求出最低工資

SQL> SELECT job,MIN(sal) FROM emp GROUP BY job;

<2>按照工資查詢雇員信息

SQL> SELECT * FROM emp

WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY job);

【9】列出各個(gè)部門(mén) MANAGER的最低薪金

SQL> SELECT deptno,MIN(sal) FROM emp

WHERE job='MANAGER' GROUP BY deptno;

【10】列出所有員工的年工資，按年薪從低到高排序

SQL> SELECT ename,(sal+NVL(comm,0))*12 income

FROM emp ORDERY BY income;

【11】求出部門(mén)名稱中，帶'S'字符的部門(mén)員工，工資合計(jì)，部門(mén)人數(shù)

<1>使用模糊查詢，獲得部門(mén)編號(hào)

SQL> SELECT deptno FROM dept WHERE dname LIKE '%S%';

<2>上面作為子查詢

SQL> SELECT deptno,SUM(sal),COUNT(empno) FROM emp

WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE '%S%') GROUP BY deptno;

【12】給任職10年以上的人加薪10%

SQL> UPDATE emp SET sal=sal+(sal*0.1)

WHERE MONTH_BETWEENS(sysdate,hiredate)/12>10;

【綜合題】有個(gè)學(xué)生運(yùn)動(dòng)會(huì)比賽信息的數(shù)據(jù)庫(kù)，需要建立如下的表，結(jié)構(gòu)如下運(yùn)動(dòng)員sporter: (運(yùn)動(dòng)員編號(hào) sporterid,運(yùn)動(dòng)員姓名 name,運(yùn)動(dòng)員性別 sex,所屬系號(hào) department)

項(xiàng)目item: (項(xiàng)目編號(hào)itemid,項(xiàng)目名稱itemname,項(xiàng)目比賽地點(diǎn) location)

成績(jī)grade: (運(yùn)動(dòng)員編號(hào) sporterid,項(xiàng)目編號(hào)itemid,積分mark)

1、建表要求

<1>定義各個(gè)表的主外鍵約束

<2>運(yùn)動(dòng)員姓名和所屬系別不能為空

<3>積分要么控制，要么為6,4,2,0,

CREATE TABLE sporter(

sporterid NUMBER(4) PRIMARY KEY,

name VARCHAR2(20) NOT NULL,

sex VARCHAR2(2) NOT NULL,

department VARCHAR2(20) NOT NULL,

CONSTRAINT sporter_sex_CK CHECK(sex IN('M','F'))

);

CREATE TABLE item(

itemid VARCHAR2(4)PRIMARY KEY,

itemname VARCHAR2(20) NOT NULL,

location VARCHAR2(20) NOT NULL );

CREATE TABLE grade(

sporterid NUMBER(4),

itemid VARCHAR2(20),

mark NUMBER(2),

CONSTRAINT sporter_grade_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,

CONSTRAINT item_grade_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,

CONSTRAINT grade_mark_CK CHECK(mark IN(6,4,2,0))

);

記錄可自己視情況插入，完成以上的查詢語(yǔ)句

1、求出目前總積分最高的系名，及其積分

SQL> SELECT s.department,SUM(g.mark) sum FROM sporter s,grade g

WHERE s.sporterid=g.sporterid GROUP BY s.department ORDER BY sum DESC;

當(dāng)然上述查出來(lái)的結(jié)果是排序的多條記錄，使用ROWNUM最為簡(jiǎn)便

SQL> SELECT * FROM (

SELECT s.department,SUM(g.mark) sum FROM sporter s,grade g

where s.sporterid = g.sporterid GROUP BY s.department ORDER BY sum DESC)

WHERE ROWNUM=1;

2、找出場(chǎng)地為'S1'，進(jìn)行比賽的各項(xiàng)目名稱及其冠軍的姓名

<1>首先確定一操場(chǎng)中的全部項(xiàng)目和每個(gè)項(xiàng)目的最高成績(jī)

SQL> SELECT i.itemname,s.name,g.mark FROM item i,grade g,sporter s

WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid = g.sporterid;

<2>根據(jù)上述結(jié)果求出最高分

SQL> SELECT i.item,s.name,g.mark FROM item i,grade g,sporter s

WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid AND g.mark=6;

3、找出參加了wilson所參加過(guò)的項(xiàng)目的其他同學(xué)的姓名

<1>找到wilson參加過(guò)的項(xiàng)目編號(hào)

SQL> SELECT g.itemid FROM sporter s,grade g

WHERE s.sporterid=g.sporterid AND s.name='wislon';

<2>SELECT DISTINCT s.name FROM sporter s, grade g

WHERE s.sporterid=g.sporterid AND s.name<>'wilson';

AND g.itemid IN

(SELECT g.itemid FROM sporter s,grade g WHERE s.sporterid=g.sporterid AND s.name='wilson');

4、wilson使用了違禁藥物，成績(jī)記為0

SQL> UPDATE grade SET makr=0

WHERE sporterid=(SELECT sporterid FROM sporter WHERE name='wilson');

5、刪除S2項(xiàng)目

SQL> DELETE FROM item WHERE itemid='S2'

到此ORACLE基礎(chǔ)語(yǔ)法等訓(xùn)練結(jié)束了，后續(xù)我們還將進(jìn)行ORACLE體系結(jié)構(gòu)的學(xué)習(xí)

當(dāng)前名稱：ORACLE百例試煉五
鏈接地址：http://muchs.cn/article46/jdoshg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營(yíng)銷、用戶體驗(yàn)、外貿(mào)建站、網(wǎng)站設(shè)計(jì)公司、網(wǎng)站制作、靜態(tài)網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容