oracle-web考試題（oracle-web考試題）

oracle-web考試題(oracle-web考試題)

Oracle:

1.inquireaboutallemployeeswhosepayisabove7000but

withoutcommission

Select*from,employees,where,salary>7000,and,

commissionpet,is,null

2.queryalllast_nameemployeeswithmorethanfourletters

Select*fromemployeeswherelast_namelike'%°/o

3.inquireaboutthenameandsalaryofeachpersonin

department50aftertheincreaseof1000yuansalary

Select,first_name,last_name,(salary+1000)salary,from,,

employees,where,department_id=50

4.inquirestheemployee，semailfullname,thecompanyemail

unifiedwith”@zpark.cn"end

Select(emaiF?zpark.cn'emailfromemployees||)

5.enquiriesCommission(COMM)is0orNULLemployee

information

Select*from,employees,where,commission_pct=0,or,

commissionjpct,is,null

6.showsthesalarylevelofallemployeesinthecompanycase

when

<=5000A5000-8000,B8000-15000,C15000,Dabove

Select,first_name,case,when,salary<=5000,then,'A'

When,salary>5000,and,salary<=8000,then,'B'

When,salary>8000,and,salary<=15000,then,'C'

"Else'D'end"salaryscale”

Fromemployees

7.thinkofthehiredatecolumnasemployee，sbirthdayandask

foryourbirthdaynextmonth

Select,first_name,from,employees,where,tochar

(hire_date,'mm，),=to_char(addmonths(sysdate,

8.,seekingthesecondhalfof1981entrystaff(inspect

knowledgepoint:one-wayfunction)

Select*,from,employees,where,hiredate,between(01-7,

-1982,,T981',and,501-1,month

9.judgewhetherthisyearisaleapyearornot

Selectsysdate-217fromdual

10.printthenexthalfmonthentrystaffinformation(one-way

function)

Select*from,employees,where,to_char

(hire_date,'mm')>'06'

Thenumberofemployees11.foreachmonthin1981post

(knowledge:groupfunction)

Select,count(*)entrynumber,to_char(hire_date,'mm'),

month,from,employees

Where,tochar(hire_date,'yyyy'),=’1981',group,by,

hire_date

12.inquireabouttheaveragesalaryofdepartment50,

Department60,section70

SelectAVG(salary),“averagewage","department_id”,

,'departmentnumber","fromemployees'"

Where,department_id,in(50,60,70),group,by,department_id

13.inquireaboutthetotalnumberofemployeesineachposition

Selectcount(*)numberofemployees,job_id,post,from,

employees,group,by,jobid

14.inquirestheaveragewageofhigherthan8000yuanofthe

Department'smaximumwage

Selectmax(max)highestsalary”

From(select,max(salary),Max,departmentid,from,

employees,group,by,department_id,having,AVG

(salary),>8000)

15.statisticsthenumberofmanagersinthecompany

Selectcount(distinctmanagerid)fromemployees

16.printallemployee，sname,salary,entrytimeandthename

oftheDepartment

SelectfirstnamelastnameI|-；Iname,salary,hiredate,

departmentidfromemployees

17.printoutthecompany，shighestsalary,rankingfifthto

tenthemployeeinformation

Select*from

(select,e.*,rownum,R,from

(SELECT*FROM順序排序薪水的員工)在rownum<11)E2

在5和10之間

18o打印當(dāng)前系統(tǒng)時(shí)間的詳細(xì)信息，精確到秒

選擇to_char(SYSDATE,；yyyyMMDDDYhh24：MI：SS)從雙

190顯示工資比'alien”高的所有員工的姓名和工資

選擇first_name|-"I|last_name名字，從員工工資

在工資》（選擇薪酬從員工那里first_name='alien"）

20。查詢工資大于本部門平均工資的員工基本信息

SELECT*FROM員工在薪酬》（選擇AVG（工資）員工組

e.department_id）

21o查詢所有工資高于平均工資（平均工資包括所有員工）的銷售人

員Csal_repn）

選擇first_name員工

在job_id='sal_rep"工資+工資XNVL（commission_pct,0）>（選

擇AVG（工資）員工）

220查詢各個(gè)職位員工工資大于平均工資（平均工資包括所有員工）

的人數(shù)和員工職位

selectcount（*）,job_id員工

在工資》（選擇AVG（工資）員工）組job_id

23o查詢?cè)谘邪l(fā)部（“”）工作員工的編號(hào)，姓名，工作部門，工作

所在地

選擇Eemployee_id,e.first_name,e.department_id,員工離開

e2.streetaddress加入部門El

在department_id=e.departmentidEl0

左連接位置E2

在location_id=el.location_idE2O

在Eldepartmentname="”。

24o查詢各個(gè)部門的名稱和員工人數(shù)

選擇el.department_name,計(jì)數(shù)（*）從員工離開加入部門El

在e.department_id=el.department_id

集團(tuán)通過(guò)el.department_name

25o查詢工資相同的員工的工資和姓名

選擇e.first_name,El0firstname,員工參力口員工e.salaryElE

在e.salary=el.salary哪里e.employee_id>el.employee_id

26o查詢平均工資最高的部門信息

從部門選擇*

在department_id=（選擇department_id員工組department_id

平均（工資）=（選擇最大（平均（工資））從員工組department_id）））

270統(tǒng)計(jì)50部門'st_man”和'st_clerk”兩種職位的人數(shù)

department_idst_manstclerk

50520

選擇department_id,計(jì)數(shù)（在job_id='st_man然后1人零結(jié)

束）"st_man”，

計(jì)數(shù)（時(shí)job_id='st_clerk然后1人零結(jié)束）"st_clerkw

從員工那里department_id=50

集團(tuán)通過(guò)department_id；

28。將100員工的信息按照如下方式顯示

標(biāo)題的價(jià)值

employeeid100

first_name史提芬

工資24000

department^d90

選擇"employee_id"標(biāo)題”，

to_char（employee_id）的“價(jià)值"從員工那里employee_id=100

聯(lián)盟

選擇ufirst_name"標(biāo)題”,tochar（firstname）的"價(jià)值”從

員工那里employee_id=100

聯(lián)盟

選擇“工資”的“頭銜”，to_char（工資）的“價(jià)值”從員工工資=

24000

聯(lián)盟

選擇udepartment_id"標(biāo)題"，to_char（departmentid）的“價(jià)

值”從員工那里employee_id=100

29o建表語(yǔ)句中約束的有哪幾種類型

1）主鍵主鍵約束，非空并且唯一，一張表只能有一個(gè)主鍵

2）不為空非空約束，插入時(shí)這一列必須給值

3）獨(dú)特的：唯一性約束，要求這一列值必須唯一，對(duì)空沒有限制，

可以出現(xiàn)多個(gè)空值

4）check（）：自定義約束

5）外鍵：外鍵約束，使用關(guān)鍵字參考表名（PK）,外鍵必須指向另一

個(gè)表的主鍵或者唯一鍵，外鍵的值必須在父表的主鍵里取，外間的值

可以重復(fù)，可以為空

30o表一級(jí)約束和列一級(jí)約束的區(qū)別

約束分為列一級(jí)約束和表一級(jí)約束，通?？梢曰Q，但是在以下兩種

特殊情況下不能互換

1.not空只能定義在列一級(jí)

2。聯(lián)合鍵只能定義在表一級(jí)

310寫出DML命令

DML（數(shù)據(jù)操作語(yǔ)言）：插入、更新和刪除

320事務(wù)的四大特性

1,（原子）：原子性，事務(wù)包涵的命令不可再分

2oC（一致性）：一致性，事務(wù)開始和結(jié)束時(shí)數(shù)據(jù)狀態(tài)保持一致

3o我（隔離）：隔離性，并發(fā)運(yùn)行的多個(gè)事務(wù)之間，互不影響

4OD（耐久性）：持久性，事務(wù)結(jié)束時(shí)一定要做持久化操作（永久保

存犯，永久丟棄回滾）

33o關(guān)聯(lián)關(guān)系（一對(duì)一，一對(duì)多，多對(duì)多）在數(shù)據(jù)庫(kù)中的表示方法

1）一對(duì)一：學(xué)生電腦

FK+英國(guó)PK+FK（共享主鍵）：雙方聯(lián)系緊密時(shí)選擇

建立學(xué)生表

（編號(hào)（6）主鍵,

名字空不空(15),

CID編號(hào)(6)引用計(jì)算機(jī)(id)唯一

)

創(chuàng)建表的計(jì)算機(jī)

(編號(hào)(6)主鍵，

(15)非空型空

)

2)一對(duì)多：?jiǎn)T工一一部門

在多的一方建外鍵

建立學(xué)生表

(編號(hào)(6)主鍵，

名字空不空(15),

CID編號(hào)(6)參考計(jì)算機(jī)(ID)

)

創(chuàng)建表的類

(編號(hào)(6)主鍵,

名字空不空(15)

)

3)多對(duì)多：學(xué)生課程

無(wú)法直接表示，通過(guò)增加一個(gè)關(guān)系實(shí)體的方式，將多對(duì)多拆成兩個(gè)一

對(duì)多處理

建立學(xué)生表

(編號(hào)(6)主鍵，

名字空不空(15),

)

創(chuàng)建表relationtable

(SID編號(hào)(6)引用學(xué)生(ID)),

CID編號(hào)(6)參考課程(ID),

主鍵(SID,CID)

)

課程表

（編號(hào)（6）主鍵,

名字空不空(15)

)

34o事務(wù)的隔離級(jí)別有幾種，

Whatarethey?

1.readbeforesubmissionofread-uncommited:

2.readaftersubmissionofread-commited:

3.repeatable-read:repeat(additionallock)

4.serializable:serializationaccess(plustaglock)

Jdbc:

1.writeaJDBCbasiccode,printoutthedatainthedatabase

shop_usertable

Packagedayl;

Importjava.sql.

Importutil.JdbcUtill;

Public,class,SelectSample{

Public,static,void,main(String口，args){

Connectionconn=nul1;

Statementstm=nul1;

ResultSetrs=null;

Try(

Conn=JdbcUtill.getConnection();

Stm=conn.createStatement();

String,sql=,“select,username,password,name,zip,address,

from,shop_user,where,username」luxw';

Rs=stm.executeQuery(SQL);

If(rs.next()){

//while(rs.next()){

Stringusername=rs.getString(1);

Stringpassword=rs.getString(2);

Stringname=rs.getString(3);

Intzip=rs.getlnt(4);

Stringaddress=rs.getString(5);

System,out.printin(username+,-),+password+,—,+name+,-

+zip+,，，+address，/;

)

}catch(Exception,e){

E.printStackTrace();

}finally{

JdbcUtill.close(RS,STM,Conn);

}

)

)

2.brieflydescribetheworkingprincipleofPreparedStatement

PreparedStatement:isthechildinterfaceofStatement

Difference:

1.whencreated

Stm=conn.createStatement();SQLstatementsarenotrequired

forcreation

Pstm=conn.prepareStatment(SQL);whenyoucreate,youneeda

givenSQLstatement(youcanuseSQLtocompleteit)

2.executiontime

Stm.execute(SQL);whenexecuted,giventheSQL,sendthefull

SQLcommandtotheserver

Pstm.execute(SQL);executesdirectlywhenexecuted,creates

agivenSQL,andsendsasetofvalueseachtimetheexecution

isexecuted

3.useenvironment:

TheimplementationisisomorphictoSQL,andthechoiceof

PreparedStatementisefficient

HeterogeneousSQLisexecuted,andtheefficiencyofselecting

Statementishigh

WhentheSQLcommandisconfiguredtorequireexternally

importedarguments,usePSTM

Whataretheimplementationmethods(4)in3.Statementandthe

returnvalueofthemethod?

1.Booleanexecute(SQL)selectInsertupdatedeleteI

TheBooleanvalueindicateswhethertheexecutedSQLcommand

hasaresultset,returnstrue:,andthereisnofalse:

Ifthereturnvalueistrue,Resultrs=stm.getResultSet();

2.ResultSetexecuteQuery(SQL)/select

3.intexecuteUpdateinsertupdatedeleteI(SQL)

Theintvalueindicatestheexecutionofthecommand,affecting

severalrowsofdatainsideandoutsidethetable

4.whatarethepositionsofthepointeroftheresultsetat

thebeginningandattheend?

Thepointerstartsattheupperlineofthefirstrowandends

atthenextlineofthelastline.Nomatterhowmanyrowsin

theresultset,thedatamustbecalledbeforetraversing

Onenext()

5.jdbcdefaulttransactionsubmissionpolicy?Whatarethe3

methodsrequiredtomanuallycontroltransactions?

InJDBC,thedefaulttransactionishandledbyacommand,a

transaction,andacommit

Manuallycontrolledtransactionapi:

Conn.setAutoCommit(false);Iwillautomaticallysubmit

strategiesclosetransactions,insteadofmanualsubmission

Conn,commit(I);commit

Conn.rollback(rollback);bal1;

6.brieflydescribetheroleofThreadLocal

Tiethreadsandconnectionstogether

7.writetogetascroll,

Thecodeforthenon-renewableResultSet

Stm=conn.createStatement(int,VI,int,V2);

Pstm=conn.prepareStatement(SQL,VI,V2);

VI:scrollResultSet.TYPE_SCROLL_INSENSITIVE

V2:updateResultSet.CONCUR_UPDATABLE

ResultSet.CONCUR_READ_ONLY

8.,usingJDBCtoachievelargedatacopiesofthetable,what

skillscanbeadjustedtoimprovetheefficiencyoftheprogram,

atleastcited4

1.controlthesizeofthetransaction

2.usingprepareStatement

3.usebatchupdateBatch

4.isolationlevelsareappropriatelylowered

5.controlnewtableconstraints

9.,EmployeeandDepartment,forexample,definetwoJava

classes,whichdefinehowtodefineatwo-way,onetomany

relationshipbetweenthetwo

PublicclassEmployee{

Private

PrivateDepartmentdep;

)

PublicclassDepartment(

Private

Private,List<Employee>,list=new,ArrayList<Employee>();

10.writethecode,acompletedescription,fromthedefinition

ofdatatothestorageofdata(Java,SQL,Dao),assumingthat

betweenthetwois

Oneway,onetomanyrelationshipsfromordertouser

Onetomany:ShopUserShopOrder

Onesideofthe1.java:retainsthesetofmorethanone,and

theothersideretainsonereference

ClassShopUser{

PrivateStringusername;privateStringpassword;private

Stringname;

Privateintzip;privateStringaddress;

//private,List<ShopOrder>,orders=new,ArrayList<ShopOrder>

0;

ClassShopOrder{

PrivateIntegerid;

PrivateDateorderTime;

PrivatedoubletotalPrice;

PrivateShopUseruser;

)

2.dbtable:createsforeignkeysontheotherside

Createtableshopuser

(usernameVARCHAR2(15)primarykey,

)；

Createtableshoporder

(IDnumber(7)primarykey,

Ordertimedate,

Totalpricenumber(7,2),

UsernameVARCHAR2(15)referencesshop_user(username)

)；

3.dao(ShopUserDao,ShopOrderDao)

ClassShopUserDao{insertudpatequery}

ClassShopOrderDao{

Publicvoidinsert(ShopOrderorder)throwsException;

PublicShopOrderqueryByld(IntegerID)throwsException;

PublicList<ShopOrder>queryByUsername(Stringusername)

throwsException;

HTML,CSS,JavaScript

1.embeddedCSScodeinHTML3methods,writespecificcode

1).TheintroductionofCSSfile<linkrel="stylesheet"type=

“text/css"href="/.Css"/>

2)inhead,write<style,type=,text/css,>,</style>

3)embeddedinthelabel,style=,color:red,..z，

2.definesahyperlinklabelthatrequirestheLoginView.html

filetoopenunderthemyservletapplicationinthenewwindow

afterclicking

<atarget=,"blank","href=","/myservlet/LoginView.html”,

and

3.defineaHTMLfile,

Inthemiddleofthewindow,displayapictureof200*200,the

picturehasaredborder

<img"src="align="center"valign="middle"width="200px”

height="200px"bordercolor="red”/>

4.javascriptdefinesasummingfunctionthatcalculatesthesum

oftwoparameters,a,B,andreturns,andgives3definitions

1).Functionadd(a,b){returna+b;}

2).Varadd=function(a,b){a+b;}

3).Var,add=new,Function("a","B","returna+b");

5.,thefollowingfourmethodstoclientoutput,whatisthe

difference?

Document,write("HelloWorld");alert("helloWorld");

Prompt("HelloWorld");confirm("HelloWorld");

6.defineauserregistrationpage,andcompletetheclient

correspondingformdatavalidation.

Username:lengthmustbe>=7bits

Password:thepasswordenteredtwotimesmustbeconsistent,

otherwiseitwillnotbesubmitted

Email:avalidemailmustcontain@

Address:includingprovinces,cities,streets.Provincesand

citiestomakeassociateddrop-downlist,aftertheselected

province,thecitydrop-downtablewillappear

Thecorrespondingcityoftheprovince.Thestreetisageneral

textbox

7.definitionofaclassinformationpage,throughtheformof

studentstoshowthespecificinformation,webpagesallow

dynamicadditionofnewstudents,

Allowsdeletionofatraineeandallowsbatchdeletionof

trainees.Studentinformationcontainsthefollowing:

StudentID,studentName,phone,email,QQ

Web:

1.whenyoucustomizeservlet,whatisthedifferencebetween

implementingtheServletinterfaceandinheritingthe

HttpServletclass?

2.writethe3methodsoftheservletlifecycle

3.whichscopespreservenamedattributesacrossmultiple

request.?

4.distinguishbetweenfourscopesandwritetheirown

characteristics

5.assumethatthemyservletapplicationhasaprogramwith

url-pattern/abc,writerequestforwarding(forward),and

Thecodethatredirectstotheprogram

6.whatisaServletContextobjectandwriteseveralwaysto

getthatobject?

7.assumethatthetotalnumberofticketsforatrainis200,

writeaservletprogram,provideticketingservices,andto

client

Printtheseatnumber,askingtheprogramtobethreadsafe.

Whatarethetwomethodsof8.httprequestsending?

9.brieflydescribetheimplementationprincipleof