oracle練習(xí)題（oracle練習(xí)題）

oracle練習(xí)題(oracle練習(xí)題)

ApplicationofROWNUM:

(1)querythefirst5dataoftheEMPtable

SELECT*FROM,EMP,WHERE,R0WUM<=5;

(2)querythesixthtotenthrecordsoftheEMPtable

SELECT*FROM(SELECT,ROWNUM,RN,FROM,EMP,WHERE,ROWNUM,

<=10)TEST,WHERE,test.rn>5;

ApplicationofROWID:deleteduplicaterecordsintable

Whichrecordshaveduplicaterecords?

SELECT,dName,LOC,MIN(ROWID),FROM,copy_dept

GROUP,BY,dName,LOC,HAVING,COUNT(deptno)>1;

一datathatcannotbedeleted

SELECT,dName,LOC,MIN(ROWID),FROM,copy_dept

GROUP,BY,dName,loc

DELETE,FROM,copy_dept,WHERE,ROWID,NOT,IN

(SELECT,MIN(ROWID),FROM,copy_dept

GROUP,BY,dName,LOC)

1,listatleastoneemployee'sdepartmentnumber,name,and

statisticsofthesedepartmentsaveragewage,minimumwage,

maximumwage

SELECT,d.deptno,d.dname,COUNT(empno),AVG(SAL),MIN(SAL),

MAX(SAL)

FROM,EMP,e,Dept,D

WHEREe.deptno=d.deptno

GROUP,BY,d.deptno,d.dname

HAVINGCOUNT(empno)>1

ORDERBYdeptno;

2.Listthenumbers,names,Departmentnames,andnamesofall

employeeswhosesalariesaregreaterthanthoseof“SMITH"or

"ALLEN".

SELECT,e.empno,e.ename,d.dname

FROM,EMP,e,Dept,D,EMP,M

WHERE,e.deptno=d.deptno,AND,e.sal,>ALL(SELECT,Sal,FROM,

EMP,WHERE,ename,IN,('SMITH'ALLEN'))

ANDe.mgr=m.empno(+);

3,listallemployeesnumber,nameandtheirdirectsuperior

number,name,displaytheresultsaccordingtotheleadership

oftheannualsalarydescendingorder;

SELECT,e.empno,e.ename,m.empno,superiornumber,m.ename,

superiorname,(m.sal+NVL(m.comm,0)),*12,income

FROM,EMP,e,EMP,M

WHEREe.mgr=m.empno(+)

ORDERBYincomeDESC;

4.Listthenumber,name,departmentname,Departmentlocation

andnumberofallemployeeswhoseemploymentdateisearlier

thantheirdirectsuperior

SELECT,e.empno,e.ename,e.deptno,m.ename,superior

leadership,d.dname,d.loc

FROM,EMP,e,EMP,m,Dept,D,(SELECT,deptno,dno,COUNT

(empno),cou,FROM,temp,EMP,GROUP,BY,deptno)

WHERE,e.mgr=m.empno(+),AND,e.hiredate,<m.hiredate

ANDe.deptno=d.deptnoANDe.deptno=temp.dno;

Statisticsareneededwhenqueriesaredisplayed,butthey

cannotbedirectlyqueriedbystatisticalfunctions,and

statisticsaremadeafterthefromclausethroughthesubquery.

5,listthenameoftheDepartmentandthestaffinformation

(quantity,averagesalary)ofthesedepartments,andlistthe

departmentswithoutemployees.

SELECT,d.deptno,d.dname,COUNT(e.empno),AVG(e.sal)

FROM,EMP,e,Dept,D

WHEREe.deptno(+)=d.deptno

GROUP,BY,d.deptno,d.dname,d.loc

ORDERBYd.deptno

6,listallthe“CLERK“nameanddepartmentname,department

number,salarylevel

SELECT,e.ename,d.dname,temp,income,s.grade

FROM,EMP,e,Dept,D,(SELECT,deptno,dno,COUNT(empno),

income,FROM,EMP,GROUP,BY,deptno)temp,salgrade,s

WHERE,job='CLERK',AND,e.deptno=d.deptno,AND,

e.deptno=temp.dno,AND,e.sal,BETWEEN,,s.losal,AND,

s.hisal;

7,liststheminimumwageofmorethan1500ofallkindsofwork

andthenumberofemployeesengagedinthisworkandthename

oftheDepartment,location,averagewage

SELECT,temp,job,temp,count,d.dname,d.loc,e.ename,res.AVG,

FROM,Dept,D,(

SELECTjobjob,

計(jì)數(shù)(EMPNO)計(jì)數(shù)

從EMP

通過工作組

具有最小(SAL)>1500)溫度，EMPE(SELECTDEPTNODNO,AVG

(SAL)的工資從EMP組AVG)研究

在e.deptno=d.deptno和e.job=temp,job和e.deptno=res.dno

8、列出在部門“銷售”工作的員工姓名、基本工資、雇傭日期、部

門名稱，假定不知道銷售部的部門編號(hào)

查詢每個(gè)部門,e.sal,e.hiredate,及其所在

從E,D部

在e.deptno=='salesd.deptno和及其所在的；

9、列出薪金高于公司平均薪金的所有員工，所在部門，上級(jí)領(lǐng)導(dǎo)，

公司的工資等級(jí)

查詢每個(gè)部門及其所在”，部門名稱”，m.ename"上級(jí)領(lǐng)導(dǎo)”，

s.grade”工資等級(jí)”

從EMPE系D,EMPm,salgradeS

在e.deptno=d.deptno和e.mgr=m.empno(+)

和e.sal>(選擇AVG(SAL)從EMP)和e.sals.losal和s.hisal

之間

10、列出與“史葛”從事相同工作的所有員工及部門名稱，部門人數(shù)

選擇e.empno,e.ename,e.job,及其所在，temp,cou

從EMPE系D(SELECTDEPTNODNO,計(jì)數(shù)(EMPNO)由部門的雇員臨

時(shí)湊)

在e.deptno=d.deptno和e.job=(選擇工作在ename="斯科特

的EMP)

和e.ename?史葛和溫度e.deptnoDNO=；

11、列出公司各個(gè)工資等級(jí)的數(shù)量、平均工資

選擇s.grade,s.losal,s.hisal,計(jì)數(shù)(e.empno),AVG(e.sal)

從EMP的E,salgrade

在薩爾s.losal和s.hisal之間E.

通過s.grade,s.losal組，s.hisal

12、列出薪金高于在部門30工作的所有員工的薪金的員工姓名、薪

金、部I1名稱

查詢每個(gè)部門，e.saL及其所在

從E,D部

在e.deptno=d.deptno和e.sal＞所有(選擇薩爾從EMP,表30)；

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

選擇d.deptno,及其所在，d.loc,AVG(SAL),AVG(monthsbetween

(SYSDATE,e.hiredate)/12)年

從E,D部

在e.deptno(+)=d.deptno

通過d.deptno,及其所在集團(tuán),d.loc；

選擇多種計(jì)數(shù)(EMPNO),AVG(SAL),AVG(months_between(系統(tǒng)日

期，姓名和雇傭)/12)由部門的雇員年；

14、列出所有員工的姓名、部門名稱和工資

查詢每個(gè)部門，及其所在，e.sal

從E,D部

在e.deptno=d.deptno

15、列出所有部門的詳細(xì)信息和部門人數(shù)

選擇d.deptno,及其所在，d.loc,計(jì)數(shù)(e.empno)

從E,D部

在e.deptno（+）=d.deptno

通過d.deptno,及其所在集團(tuán),d.loc；

16、列出各種工作的最低工資及從事此工作的雇員姓名

查詢每個(gè)部門，e.job,e.sal

從EMPE（從作業(yè)組中選擇作業(yè)，min（薩爾）分鐘）溫度

在e.job=temp,job和e.sal=temp,min

17、列出各個(gè)部門經(jīng)理的最低薪金、姓名、部門名稱、部門人數(shù)

查詢每個(gè)部門，e.sal,及其所在，res.count

從EMPE系D（SELECTDEPTNODNO,min（SAL）的工資從EMP工作=

'manager組SAL）溫度，

（選擇DEPTNODNO,

計(jì)數(shù)（EMPNO）的工資從EMP組數(shù)）研究

在e.deptno=d.deptno和e.deptno=temp,dno和e.sal=temp,sal

和e.job='manager和res.dno=d.deptno；

18、列出所有員工的年工資、所在部門名稱，按年薪從低到高排序

查詢每個(gè)部門，e.salX12ngz,及其所在

從E,D部

在e.deptno=d.deptno

為了ngz；

19、查出某個(gè)員工的上級(jí)主管及所在部門名稱，并要求出這些主管中

的薪水超過3000

選擇m.ename,及其所在，m.sal

從EMPE,D部，EMPM

在m.deptno=d.deptno和e.mgr=m.empno(+)和m.sal>3000；

20、求出部門名稱中帶的字符的部門員工的工資合計(jì)、部門人數(shù)。

選擇及其所在，和(e.sal),計(jì)數(shù)(e.empno)

從E,D部

在e.deptno(+)=d.deptno和及其所在在s”

組及其所在

21、給任職日期超過30年或者在87年雇傭的雇員加薪，加薪原則：

10部門增長(zhǎng)10%、20部門增長(zhǎng)20%、30部門增長(zhǎng)30%,以此類推

更新EMP組

薩爾=（1+上/100）*薩爾

在months_between（系統(tǒng)日期，姓名和雇傭）/12>30或to_char

（姓名和雇傭，'yyyy"）=1987；

有某個(gè)學(xué)生運(yùn)動(dòng)會(huì)比賽信息的數(shù)據(jù)庫，保存了如下的表：

1）運(yùn)動(dòng)員運(yùn)動(dòng)員（運(yùn)動(dòng)員編號(hào)sporterid,運(yùn)動(dòng)員姓名名字，運(yùn)動(dòng)

員性別性別，所屬系號(hào)部）

2）項(xiàng)目項(xiàng)目（項(xiàng)目編號(hào)$Itemid,項(xiàng)目名稱項(xiàng)目名稱，項(xiàng)目比賽地點(diǎn)

位置）

3）成績(jī)級(jí)（運(yùn)動(dòng)員編號(hào)sporterid,項(xiàng)目編號(hào)$Itemid,積分標(biāo)記）

請(qǐng)用SQL語句完成以下功能：

1、建表，并在相應(yīng)字段上添加約束

I-定義各個(gè)表的主鍵和外鍵約束

I-運(yùn)動(dòng)員的姓名和所屬性別不能為空

I-積分要么為空值，要么為6,4,2,0,分別代表第一、二、三名和

其他名次的積分,注意名次可以有并列名次，后面的排名不往前提升,

例如：如果有兩個(gè)并列第一，則沒有第二名。

2、向表中插入指定的數(shù)據(jù)：

運(yùn)動(dòng)員（

1001、李明，男，計(jì)算機(jī)系

1002、張三，男，數(shù)學(xué)系

1003、李四，男，計(jì)算機(jī)系

1004、王二，男，物理系

1005、李娜，女，心理系

1006、孫儷，女，數(shù)學(xué)系）

項(xiàng)目（

X001,男子五千米，一操場(chǎng)

x002,男子標(biāo)槍，一操場(chǎng)

x003,男子跳遠(yuǎn)，二操場(chǎng)

x004,女子跳高，二操場(chǎng)

x005,女子三千米，三操場(chǎng)）

積分（

1001、x001,6

1002、x001,4

1003、xOOl,2

)

降表級(jí)清除；

表運(yùn)動(dòng)員清除；

刪除表項(xiàng)清除；

創(chuàng)建表的運(yùn)動(dòng)員

(

sporterid號(hào)碼(4)主鍵，

名字空不空(20),

性別VARCHAR2(4)檢查(性(“男‘，'女')),

(20)非空系空的

);

項(xiàng)目表

(

$ItemidVARCHAR2(4)主鍵，

項(xiàng)目名稱VARCHAR2(20)不為空,

VARCHAR2（20）沒有空位置

）;

創(chuàng)建表的等級(jí)

sporterid號(hào)（4）參考運(yùn)動(dòng)員（sporterid）級(jí)聯(lián)刪除,

(4)引用項(xiàng)$ItemidVARCHAR2(ITEMID)級(jí)聯(lián)刪除,

標(biāo)號(hào)（1）檢查（標(biāo)記（0、2、4、6））

）;

插入運(yùn)動(dòng)員（sporterid,姓名,性別，系）值(1001,“李明‘，’

男‘，‘計(jì)算機(jī)系”）;

插入運(yùn)動(dòng)員（sporterid,姓名,性別,系）值(1002,“張三'，'

男‘，‘?dāng)?shù)學(xué)系”）;

插入運(yùn)動(dòng)員（sporterid,姓名,性別,系）值(1003,“李四‘，’

男‘，‘計(jì)算機(jī)系”）;

，，不一，，

插入運(yùn)動(dòng)員（sporterid,姓名,性別,系）值(1004,

男‘，‘物理系”）；

插入運(yùn)動(dòng)員（sporterid,姓名,性別,系）值(1005,“李娜‘，’

女‘，‘心理系”）；

插入運(yùn)動(dòng)員（sporterid,姓名，性別，系）值（1006,"孫儷’，

女‘，‘?dāng)?shù)學(xué)系”）；

插入項(xiàng)目（$Itemid,項(xiàng)目名稱，位置）的值（'xOOl'男子五千

米‘，’一操場(chǎng)”）；

插入項(xiàng)目（$Itemid,項(xiàng)目名稱，位置）的值Cx002','男子標(biāo)槍’，

'一操場(chǎng)”）;

插入項(xiàng)目（$Itemid,項(xiàng)目名稱，位置）的值（‘x003','男子跳遠(yuǎn)’，

'二操場(chǎng)”）;

插入項(xiàng)目（Sltemid,項(xiàng)目名稱，位置）的值（'x004',‘女子跳高

'二操場(chǎng)”）;

插入項(xiàng)目（$Itemid,項(xiàng)目名稱，位置）的值（'x005','女子三千

米‘，’三操場(chǎng)”）;

插入級(jí)(sporterid,$Itemid,馬克)值(1001,*x001,,6)；

插入級(jí)(sporterid,$Itemid,馬克)值(1002,'xOOl',4)；

插入級(jí)(sporterid,$Itemid,馬克)值(1003,'xOOl',2)；

插入級(jí)(sporterid,$Itemid,馬克)值(1004,'xOOl',0)；

插入級(jí)(sporterid,$Itemid,馬克)值(1001,^003',4)；

插入級(jí)(sporterid,$Itemid,馬克)值(1002,'x0036)；

插入級(jí)（sporterid,$Itemid,馬克）值（1004,'x0032）；

插入級(jí)（sporterid,$Itemid,馬克）值（1005,*x0046）；

插入級(jí)（sporterid,$Itemid,馬克）值（1004）!x0032）；

插入級(jí)（sporterid,$Itemid,馬克）值（1006,^0044）；

插入級(jí)（sporterid,$Itemid,馬克）值（1003,'x002',6）；

插入級(jí)（sporterid,$Itemid,馬克）值（1005,'x002',4）；

插入級(jí)（sporterid,$Itemid,馬克）值（1006,'x002',2）；

插入級(jí)（sporterid,$Itemid,馬克）值（1001,'x0020）；

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

(1)不考慮重復(fù)積分

SELECT*FROM（

選擇百貨，和（g.mark）summark

從運(yùn)動(dòng)員S,G級(jí)

在s.sporterid=g.sporterid

組部

通過summark倒序）在rownum=1；

(2)考慮有重復(fù)積分

選擇百貨，和(g.mark)summark

從運(yùn)動(dòng)員S,G級(jí)

在s.sporterid=g.sporterid

組部

具有總和(g.mark)=

(選擇最大(sum(g.mark))summark

從運(yùn)動(dòng)員S,G級(jí)

在s.sporterid=g.sporterid

集團(tuán)以百貨)

2、找出在一操場(chǎng)進(jìn)行比賽的各項(xiàng)目名稱及其冠軍的姓名

Step1:findoutabouttheeventsinaplayground

SELECT,itemid,itemname,FROM,item,WHERE,location13',

playground,'GROUP,BY,itemid,itemname

Thesecondstep:togetthechampionshipresults

SELECT,i.itemid,MAX(g.mark)

FROM,item,I,grade,G

z，WHEREi.itemid=g.itemidANDi.location」playground”

GROUPBYi.itemid

Thethirdstep:accordingtotheresults,tofindthenameof

theathlete

SELECT,s.name,g.itemid,temp,max

FROM,sporter,s,grade,G,(

SELECT,i.itemid,IID,MAX(g.mark)max

FROM,item,I,grade,G

“WHEREi.itemid=g.itemidANDi.location='playground”

GROUP,BY,i.itemid)temp

WHERE,g.itemid=temp.iid,AND,s.sporterid=g.sporterid,AND,

g.mark=temp.max

Thefourthstep:findthenameoftheproject

SELECT,s.name,g.itemid,temp,max,i.itemname

FROM,sporter,s,grade,G,(

SELECT,i.itemid,IID,MAX(g.mark)max

FROM,item,I,grade,G

“WHEREi.itemid=g.itemidANDi.location：'playground”

GROUP,BY,i.itemid)temp,item,I

WHEREg.itemid=temp.iidANDs.sporterid=g.sporterid

ANDg.mark=temp.maxANDi.itemid=g.itemid

3.Findoutthenamesoftheotherstudentswhoparticipated

inalloftheeventsattendedbyZhangthree

Step1:findtheIDofalltheentriesinwhichZhangisthree

SELECTi.itemid

FROM,sporter,s,item,I,grade,G

WHERE,s.sporterid=g.sporterid,AND,i.itemid=g.itemid,AND,

=',Zhangthree

Thesecondstepistofindthestudentswhoparticipatedinthe

program

SELECT

FROM,sporter,s,item,I,grade,G

WHERE,s.sporterid=g.sporterid,AND,i.itemid=g.itemid,AND

I.itemidIN(...

SELECTi.itemid

FROM,sporter,s,item,I,grade,G

WHERE,s.sporterid=g.sporterid,AND,i.itemid=g.itemid,AND,

s.name=',Zhangthree,')

ANDs.nameNOTIN(SELECTNAMEFROMsporterWHERENAME=，Zhang

three')

GROUPBYs.name

4,afterinvestigation,ZhangSanbecauseoftheuseofbanned

drugs,theresultsarerecorded0points,pleasemakethe

correspondingchangesinthedatabase

Step1:firstcheckoutZhangSan'sID

SELECT,sporterid,FROM,sporter,WHERE,NAME=，,Zhangthree

Thesecondstep:changeZhangSan'sgradeto0

UPDATE,grade,SET,mark=0,WHERE,sporterid,IN

(SELECT,sporterid,FROM,sporter,WHERE,NAME=，,Zhang,,...)

5.Thewomen'shighjumpeventsneedtobedeletedafter

consultationwiththeOrganizingCommittee

DELETE,FROM,item,WHERE,itemname=，,women'shighjump;

Buysite:

1,thefrontdeskusercanregisterandsystemloginoperation

2,anyusercanbuygroupinformationbrowsing,inthegroup

informationlist,youcanwriteclearthebasicinformationof

eachgrouppurchaseinformation,

Suchas:title,currentprice,originalprice,discount,amount

ofmoneysavedandthenumberofbuyers;

3,userscanclickonaspecificgrouptobrowseinformation,

youcandisplaythepublicitypicture,title,buybasic

information,buy

Asofthetime(countdown)anddetails,suchasbusiness

information,address,thecontentsofthepurchase,the

validityperiod

4,iftheuserdeterminestoparticipateinthisgrouppurchase,

clickthe"buy"buttontoentertheordersubmissionpage,you

can