oracle考試試題及答案

oracle考試試題及答案

Questionsonebyone,fillintheblanks(4pointsperquestion

exergyatotalof20points)

1,databasemanagementtechnologyhasgonethroughthreestages:

manualmanagement,filesystemanddatabasesystem

2,databasethreeleveldatastructureisexternalmode,mode,

internalmodel

3,theOracledatabaseSGAdatabasebufferbyexergyexergy

exergyredologbuffersharedpool.

4,intheOracledatabaseintegrityconstraintsexergytypes

arePrimaykeyconstraints.ForeignkeyUniqueconstraint

exergy

Checknotneedexergyexergyconstraintconstraintconstraint

Declarecursoropenthecursorcursorexergyexergyexergy

extractioninPL/SQL,including5closemyCursorcursor

operation

Two,thetrueorfalsequestionsineveryday2points20points

totalexergyrate

Thebasicobjectsstoredinthedatabaseis1,thedatarate

inT

2,thedatabasesystemisthecoreofDBMSintheTrate

Thecharacteristicsofrelationshipbetweenthe3andthe

operationissetintheoperatingrateofT

4,fivebasicoperationsinrelationalalgebra,andisthe

difference,selection,projection,connectionintheFrate

5,OracleprocessistheserverprocessintheFrate

6,theoracletsystemSGAprocessserverandallusersinthe

processofsharingrateT

7,theOracledatabasesysteminthedatablocksizeintheT

operationoftheratesystem

8,Oracledatabasesystemexergystartdatabaseandthefirst

stepistostartadatabaseinstanceintheTrate

Thecursor9,PL/SQLdatacanbechangedintheFrate

10,thedatabaseconceptmodelismainlyusedindatabase

conceptualstructuredesignintheFrate

Three,usethetitleinthematcheach7points35pointstotal

exergyrate

Logicalindependenceandphysicalindependencein1,whatis

thedatabasesystemdataintheprogram

DBMS

Providesatwolayermappingmechanisminexternalmodethree

modestructure

Schema,image,andmode

Internalschemaimage.Thetwo

Thelayermappingmechanismguaranteesthelogical

independenceandphysicalindependenceofdatainthedatabase

system.

Externalmode

Theschemaimagedefinesthecorrespondencebetweenthe

externalschemaofthedifferentusersinthedatabaseandthe

logicalschemaofthedatabase.

Whenthedatabaseschemachangessuchasarelationaldatabase

systemtoincreaseexergychangewiththenewrelationship,the

relationshipbetweenattributedatatypescanexergy

Externalmodeadjustment

Therelationshipbetweenimagemodeexergyguaranteeaconstant

userorientedmodeofeach.Theapplicationisbasedonthedata

ofthemodelpreparedbytheexergy

Whichapplicationisnotrequiredtoensuretheindependence

ofthelogicofexergyexergydataandapplicationoflogical

dataindependence.

Pattern

Theinternalschemaimagedefinesthegloballogicalstructure

ofthedatainthedatabaseandthephysicalstorage

organizationofthosedatainthesystem

Correspondence.

Whenchangingthephysicalstorageofdatastructuresinthe

databasewhentheinternalmodelchangessuchasthedefinition

andselectionofastoragestructurecanadjustthe

Theconstantsothattheexternalschemaofdatabasesystemand

individualapplicationsdonothavetochangethedatabase

schemaintheschemamappingrelationship/holdmode.

Thiswillensurethatthephysicaldataindependenceandthe

independenceofthephysicaldatabetweenapplicationsor

databases.

2,therelationalalgebraequijoinsdifferenceisnotanatural

connectionwithoutcontactyesterday

Answerwhentheoperatorconnectedconditionsincludedinthe

useofthisconnectioniscalledequivalentconnection.

Connectionoperation

Generalinthetwotablebetweenforcanalsobeinatabledoes

nothaveitsownconnectionbetweenconnectionoperationsuch

asfromthe.

Answer.Theequivalentconnectionsandselfconnectionsbelong

totheinternalconnectionquery

3,whatisthedatabasedatabasedesignisgenerallydivided

intowhatstagefrom

1)

Exergydatabase

Database

Inaccordingtothedatastructuretoorganize,storeandmanage

datawarehouse.

2)

Requirementdesign

conceptualdesign

logicdesign

physicaldesign

Implementation,operationandmaintenance

Four

Briefdescription

Oracle

Compositionofexergylogicaldatabase

Atablespace,segment,datablock

Five

Well,tryanyoneexampleofusingmethodfromcursor

Aexergy

Createtable

Createtabletest

(

Namechar(30),

Agechar(40),

Subjectchar(20),

IDnumeric(10)

)

insertdata

Insert,into,test,values('hehe','haha','hh',4)

Definevariables

Declare@namechar(30)

Declare@agechar(40)

Declare?subjectchar(20)

Declare@idnumeric

CreateaCursor

Declarehimmlcursor

For,select,[name],age,subject,ID,from,test

open

Openhimml

Usecursorstoscrollthrough

Fetch,himml,into,?name,@age,?subject,@id一

Becareful

@@FETCH_STATUS

yes

SQLSERVER

Insidevariablesand

ORACLE

The

@@sqlstatus

Differ.

While(@@FETCH_STATUS=0)

Begin

Print

Print@name

Print@age

Print?subject

Print@id

Fetch,himml,into,@name,?age,?subject,@id

End

Closethecursorrateclosethecursorresultsetinits

entiretyinsteadofexergy

Closehimml

Closethecursorcursorratereleasethememoryinandletthe

cursornamecanbeusedagainexergy

Deallocatehimmlfivewithemployeetables

EMP(empno,ename,age,Sal,Tel,deptno),inwhichempno-----

name------nameage一numberofexergyexergyexergyexergyof

tel-----electricsal------agewage

word

Deptno-----Departmentnumber.

PleaseprogramatSQL*PLUSinthemorningfollowingthe

followingrequirements.Ineveryday3pointstotalof15points

inthe1exergyrate,hometelephonestaffinformationquery.

IntheSQL>SELECTFROMEMPWHERETelNOT*NULL;inthe2,query

wagesin500to800yuanbetweentheemployeeinformationin

SQL>SELECT*FROMEMPWHEREBETWEEN500AND800;in3,according

totheageincreasingorderdisplayemployeenumber,name,age,

salaryintheSQL>SELECTempno,ename,age,SalFROMEMPORDER

BYageASC;SQL>SELEC,4

AstheaveragewageintheDepartmentofSQL>SELECTAVGDOI

(SAL)FROMEMPWHEREdeptno=*D_0f;inthe5,findthe

departmentnumberDOIover40yearsofageandwageof400yuan

inthelistofemployeesintheSQL>SELECTenameFROMEMPWHERE

deptno=D_orANDage>40AND.

Anexaminationquestiontwo

Two,fillintheblanks(each2points30pointstotalexergy)

pleasefillinthecorrectanswersintheblankseveryday.No

fillandnofill.

1.datamodelisusuallycomposedofthreeelementsofthedata

structure,dataoperationanddata___constraint_.

2.databasesystems,alltypesofuserrequestsfordatabase

operations(datadefinition,query,update,andvarious

controls)aremadeupof

Acomplexsoftwaretocompletetheexergythissoftwarecalled

DBMS

3.intheSQLSELECTstatementinthequerytoremoveduplicate

recordsofexergyexergyinthequeryresultsshouldbeused

__DISTINCT_.

Keyword.4.theuseofSQLlanguageSELECTstatementforthe

querypacketinthepacketifhehopedtogetridofnotmeet

theconditionsshouldbe

UsetheHAVINGclause.

5.relationaldatabasedatamanipulationlanguage(DML)

includestwotypesofoperationintheirsearchandupdate

6.inrelationaldatabasedesigninthedatabasedesignis

dividedintorequirementanalysis,conceptdesign,logic

design,physicaldesign,application

Programcoding,debuggingoperation,databaseoperationand

maintenanceinsixstages.

Whatstageofdatabasedesignisthedesignrelationalschema?

Exergytasklogicdesign___

7.operationscanbedividedinto_relationalalgebra_

relationalcalculusand_twocategories.

Therelationshipbetweenthe8.INF_nonmainfunctionto

eliminatethedependenceonthekeyattributeintheparadigm

aftergradeto2NF.2NF

Therelationshipbetween_eliminatenonmainattributeson

thekeysofthetransferfunctioncanbe_dependentuponhis

paradigmlevelincreasedto3NF.

Thethreelevelstructureofthe9.databasethroughtheconcept

ofthepattern/imagewithinthepatterntoensure____

independenceinthephysicalmodelofconceptmapping/by

Asinthe_logic_independenceguarantee.

10.themeaningofSQLis_structuredquerylanguage___.

11.DBMSusuallyprovidetheauthorizationfunctiontocontrol

permissionsinthedataofdifferentuserstoaccessthe

databaseinitspurposeistonumber

Accordingtothe____securitydatabase.

Three,shortanswerquestions(6pointseachiteminatotal

of24points)

Safetyprotectionfunction1.databaseprovidesfouraspects

whichtrytoexplaintheirmeaningofexergy

Exergysecuritydatabaseisaratecausedbyuseofdatabase

protectionpreventillegaldataleakage,changeordamage.SQL

Server2000

Thesecuritymechanismconsistsoffourlayers

Thefirstlayerincludedoperatingsystemlogin

Secondlayerserversecuritymanagementexergyexergy

SQLServer

Login

Specialaccount

SA

Thirdlayerdatabasesecuritymanagementdatabaseexergy

exergyaccess

Databaseuser

Fourthlayerdatabaseobjectsinsafetymanagementofexergy

exergyexergydatabaseobjecttablesandviewsinaccess

Databaseusergetsroles

2.thereferentialintegrityrulesinthepurposeofitinthe

testexampledonburi.3.toOracleDBMSfortheSQLrelational

databaselanguagesupportisgiveninthecaseofgradethree

logicschematic1)SQL

Languagesupportrelationaldatabasethreelevellogic

structureconsistsoftheouterlayerandamemory,theconcept

ofasshowninfig..

2)

Theconceptofrecordinglayercorrespondstotheconceptual

modelisthebasictable.Thebasictableisatablethatitself

actuallyexists.

Abasictableisaitnotbyotherformsofexporttable.The

basictableisused

CREATETABLE

Statementbuilt.

3)

Intheouterasseenbytheusercanbethebasictablecanalso

beviewcanalsobethebasictableview.Aviewisavirtual

table

Itiscomposedofoneorseveralbasicformsofexporttable

itdoesnotexistinthephysicalmemorydirectlyonthetable.

Viewisused

CREATESQLVIEW

language

Sentenceestablished.

4)

Intheinnereachbasictablewithafilestorageissaidby

agroupofthesametypeofstoredrecordstoindicatethevalue.

DBA

Youcanmanipulatephysicalstoragefiles.

4.,brieflydescribetheDBMSdatabasesecuritycontrol

function,includingwhatarethecommonlyusedmeans?

Adatabasemanagementsystemfordatacontrolfunctiondata

securitycontrolfunctioninordertoensurethesafetyand

reliabilityofthedatawithinthedatabasetoprevent

Theuseofillegalcausedataleakageanddamagethedatathat

avoidbeingpeeped,tamperingorruiningexergydataintegrity

controlreferstothefunctionofinsurance

Thedatacardinthedatabasecorrectlyandeffectivelyandto

preventthecompatibilityerrordataisnotthesemanticinput

oroutput.

Four,databasedesign(15points)

Supposethereisarelationshipbetweenthe1.torecordeach

person'sidentitycardnumber,nameandworkunit.Also

containseveryoneofhis/herchildren

Theidentitycardnumber,nameandplaceofbirthandthehe

/shehaseverycarbrandsandmodels.

Therealworldfromknownfactsthat

Somepeoplemayhaveseveralcarsbutthesecarsmaybethesame

typebutmayalsoisnotthesametypeofexergy

Somepeopledonothavethecarifsomeonehasthecarincluded

hiseverycarhasacarincluded

Somepeoplemayhaveseveralchildrenbuttherearesomepeople

withoutchildren.Therelationshipmodelofthepreliminary

designoftheareasfollows

R(identitycards,thenameoftheworkunitoftheCidentity

cards,thenameoftheCCwasborninthecarthemodel)

The"CC"identitycards,thenameCwasborn"arethechild's

identitycardnumber,nameandplaceofbirth.

Pleasesendthispatternintothepatternoftherelationship

betweenBCNFtodeterminetherelationshipbetweenthemainkey.

7pointsinaexergyexergy,thecitizenidentitycardsinthe

nameoftheworkunit

Thetypeofcarcarexergytheidentitycardnumberin

Thechildidentitycards,theexergyofCCCwasborninthe

nameoftheidentitycardnumberinacertainschoollibrary

2.assumptionstoestablishadatabasetosavethereaders,

booksandreadersofrecord.Inordertobuildthe

Weneedtodesignagooddatabasedesignfromtheconceptual

modelisshownandthenthefigure-theconceptualmodelinto

arelationalmodel,please

DesignfromLa-map.

Thereaderhasreadersattributenumber,name,age,addressand

unit.

AttributesofeachbookareISBN,title,authorandpublisher.

Eachbookforeachreaderborroweddateandshouldalsohave

outofdate.8pointsinexergy

Areaderreaderthenameaddresstheexergynumberintheunit

TheauthorofthebookISBNTitleExergyinthepress.

ThenumberofreaderstoborrowthebooksISBNexergydatethe

dateshouldbeinfive,calculation(thetitle3itemswithin

atotalof16points)

Clientswithacommercialrelationaldatabasethethreebasic

tablethetablestructureisasfollows

TableArticle(commoditygoods,thepriceofthestockinthe

commodityname)

TableCustomer(customerclients,theclientsnamethesexthe

agethephone)

Orderltemorderform(thenumberoftheclients,thepurchase

priceofgoodsnumberthedate)

Notethattheanswertowillgivetheanswerswrittenprovisions

ofthelocalexergyanswerrequirementsmustbeclearlynot

allowedtochangetheincludedwritingprogram

Andoptionallyaddsubqueries.

1.pleasecreateaGM_VIEWviewoftheretrievalclientsusing

SQLlanguageclients,clientsandorderinggoods

Name,amountanddate,(thenumberisequaltothepurchase

price*amount)6pointsinexergy

GREATVIEWGM_VIEW(clients,theclientsnamethecommodity

nametheamountofthenumberofdate)*ASSELECT_clients,

clientsinabrandnameinthepurchasepriceintheamountof

asinthedateofFROMArtcle,Customer,

Orderitem

WHERECustomer,clients,=OrderItem.clients,andArticle,

commodityNo.=OrderItem.ArticleNo.2.pleaseusetheSQL

languageoffemaleclientsbuygoodsnumber,commoditynameand

thetotalnumberofout.6exergyrate

SELECT_Orderitem.,commoditynumberAS,commoditynumber,

Orderitem,commodityname,AScommodityname,SUM(Orderitem,

quantity)

ThetotalquantityofASisFROM,Orderltem,Artcle,Customer,

WHERE_Artcle.,commoditynumber=Orderitem.,commodity

numberAND,Orderitem.,commoditynumber=Customer.,

commoditynumberAND

Customer.=，GROUPBYOrderitem,femalegenderincommodity

tradenameNo.

3.pleaseusetheSQLlanguageALTERTABELcommandofafield

inafieldcalledtheoriginwillincreasethenumberofgoods

totableArticle

AccordingtothetypeoftheCHARinthelengthof30inthe

rateof4pointsexergycommandisasfollows

CharALTERTABEL_ArticleADD(30)—origin

Item31,fillintheblanks(each2pointsinatotalof20

points)

TheSELECTstatementforgroupingquery1,usingtheSQL

languageinthepacketwillnotmeetifyouwanttoremovethe

conditionsshouldbe

Using_HAVING..._clause.

In2,inthedesignofrelationaldatabaseindatabasedesign

isdividedintorequirementanalysis,conceptdesign,logic

design,physicaldesign,shouldbe

Programcoding,debuggingrun,databaseoperationand

maintenanceinsixstages.Whatstageofdatabasedesignisthe

designrelationalschema?

Thetaskofexergy_logicdesign

Relationaloperationsinrelationalalgebraand3,including

theselection,projection,____connectionanddivision.

4,therelationshipmodelofentityintegrityinreferential

integrityinuser-definedintegrityofthreetypesof

integrity.

5,twoyuanforentitysetbetweenAandBbetweenthesetin

mappingbasesetmustbeoneofthefollowingfour

1.,onetoone,contact2.,onetomanycontacts,morethan3.

toone,contactmorethan4.,manypairsofcontacts

6,PL/SQLcursorthetwotypesofexplicitandimplicitcursor

cursor.

Two,singlechoice(3pointsperiteminatotalof15points)

1,inarelationaldatabasemanagementsystemwillcreatethe

viewinthedatabasethreelayerstructurebelongsto(A)

A.externalmodeB.storagemode

C.intraschemaD.conceptualschema

Thegeneralcharacteristicsofthe2,intheworldofthings

inrealityintheinformationworldiscalled(A)

A.entityB.entitykey

TheC.propertyD.key3RelationshipModelSJPSJPinthe

SinisstudentsJcurriculumPisranking.Eachstudenttakes

ineachcourse

Theperformancehasacertainrankeachcourserankingonlyone

studentinthecolumnandNo.Therelationshipmodelbelongs

toexergy(C)

A,2NF,B,3NF,C,BCNF,D,4NF

4,thecompanyhasadepartmentofanumberofdepartmentsand

employeeseachstaffonlybelongstoadepartmentcanhavea

numberofstaff

Thetypeofcontactfromstafftodepartmentis(C)

A.manytomany,B.,onetoone,C.,onetomany,oneto