oracle試驗(yàn)內(nèi)容1（共40題）

本文格式為Word版，下載可任意編輯——oracle試驗(yàn)內(nèi)容1（共40題）12000

8.Createareportthatdisplaysthelastnameanddepartmentnumberforemployeenumber176.

selectlast_name,department_id

fromemployeeswhereemployee_id=176

9.TheHRdepartmentneedstofindhigh-salaryandlow-salaryemployees.Modifylab_02_01.sqltodisplaythelastnameandsalaryforanyemployeewhosesalaryisnotintherangeof$5,000to$12,000.

selectlast_name,salary

fromemployees

wheresalary>12000orsalary0

orderbysalarydesc,commission_pctdesc

15.MembersoftheHRdepartmentwanttohavemoreflexibilitywiththequeriesthatyouarewriting.Theywouldlikeareportthatdisplaysthelastnameandsalaryofemployeeswhoearnmorethananamountthattheuserspecifiesafteraprompt.(Youcanusethequerythatyoucreatedinpracticeexercise1andmodifyit.)

selectlast_name,salaryfromemployeeswheresalary>=&sal

16..TheHRdepartmentwantstorunreportsbasedonamanager.CreateaquerythatpromptstheuserforamanagerIDandgeneratestheemployeeID,lastname,salary,anddepartmentforthatmanager’semployees.TheHRdepartmentwantstheabilitytosortthereportonaselectedcolumn.Youcantestthedatawiththefollowingvalues:

managerID=103,sortedbyemployeelastname:

selectemployee_id,last_name,salary,department_idfromemployees

wheremanager_id=&m_idorderbylast_name

managerID=201,sortedbysalary:

managerID=124,sortedbyemployeeID:

17.Displayallemployeelastnamesinwhichthethirdletterofthenameisa.

selectlast_namefromemployees

wherelast_namelike'__a%'

18.Displaythelastnamesofallemployeeswhohavebothanaandaneintheirlastname.

selectlast_namefromemployees

wherelast_namelike'%a%'andlast_namelike'%e%'

19.Displaythelastname,job,andsalaryforallemployeeswhosejobsareeithersales

representativeorstockclerkandwhosesalariesarenotequalto$2,500,$3,500,or$7,000.

selectlast_name,job_id,salaryfromemployees