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打印当前系统时间的详细信息，精确到秒

选择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查询各个职位员工工资大于平均工资（平均工资包括所有员工）

的人数和员工职位

selectcount（*）,job_id员工

在工资》（选择AVG（工资）员工）组job_id

23o查询在研发部（“”）工作员工的编号，姓名，工作部门，工作

所在地

选择Eemployee_id,e.first_name,e.department_id,员工离开

e2.streetaddress加入部门El

在department_id=e.departmentidEl0

左连接位置E2

在location_id=el.location_idE2O

在Eldepartmentname="”。

24o查询各个部门的名称和员工人数

选择el.department_name,计数（*）从员工离开加入部门El

在e.department_id=el.department_id

集团通过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统计50部门'st_man”和'st_clerk”两种职位的人数

department_idst_manstclerk

50520

选择department_id,计数（在job_id='st_man然后1人零结

束）"st_man”，

计数（时job_id='st_clerk然后1人零结束）"st_clerkw

从员工那里department_id=50

集团通过department_id；

28。将100员工的信息按照如下方式显示

标题的价值

employeeid100

first_name史提芬

工资24000

department^d90

选择"employee_id"标题”，

to_char（employee_id）的“价值"从员工那里employee_id=100

联盟

选择ufirst_name"标题”,tochar（firstname）的"价值”从

员工那里employee_id=100

联盟

选择“工资”的“头衔”，to_char（工资）的“价值”从员工工资=

24000

联盟

选择udepartment_id"标题"，to_char（departmentid）的“价

值”从员工那里employee_id=100

29o建表语句中约束的有哪几种类型

1）主键主键约束，非空并且唯一，一张表只能有一个主键

2）不为空非空约束，插入时这一列必须给值

3）独特的：唯一性约束，要求这一列值必须唯一，对空没有限制，

可以出现多个空值

4）check（）：自定义约束

5）外键：外键约束，使用关键字参考表名（PK）,外键必须指向另一

个表的主键或者唯一键，外键的值必须在父表的主键里取，外间的值

可以重复，可以为空

30o表一级约束和列一级约束的区别

约束分为列一级约束和表一级约束，通常可以互换，但是在以下两种

特殊情况下不能互换

1.not空只能定义在列一级

2。联合键只能定义在表一级

310写出DML命令

DML（数据操作语言）：插入、更新和删除

320事务的四大特性

1,（原子）：原子性，事务包涵的命令不可再分

2oC（一致性）：一致性，事务开始和结束时数据状态保持一致

3o我（隔离）：隔离性，并发运行的多个事务之间，互不影响

4OD（耐久性）：持久性，事务结束时一定要做持久化操作（永久保

存犯，永久丢弃回滚）

33o关联关系（一对一，一对多，多对多）在数据库中的表示方法

1）一对一：学生电脑

FK+英国PK+FK（共享主键）：双方联系紧密时选择

建立学生表

（编号（6）主键,

名字空不空(15),

CID编号(6)引用计算机(id)唯一

)

创建表的计算机

(编号(6)主键，

(15)非空型空

)

2)一对多：员工一一部门

在多的一方建外键

建立学生表

(编号(6)主键，

名字空不空(15),

CID编号(6)参考计算机(ID)

)

创建表的类

(编号(6)主键,

名字空不空(15)

)

3)多对多：学生课程

无法直接表示，通过增加一个关系实体的方式，将多对多拆成两个一

对多处理

建立学生表

(编号(6)主键，

名字空不空(15),

)

创建表relationtable

(SID编号(6)引用学生(ID)),

CID编号(6)参考课程(ID),

主键(SID,CID)

)

课程表

（编号（6）主键,

名字空不空(15)

)

34o事务的隔离级别有几种，

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