大连理工大学数据库上机-大学模式数据库

1.1select student.NAME,course.TITLE,count*from studentjoin takeson student.ID=takes.ID joincourse ontakes.COURSE_ID=course.COURSE_ID group by student,name,course.TITLE havingcount*=

21.2select ID,NAME from student where not exists select ID from takes where takes.lD=student.ID

1.3select c.TITLE,s.SEC_ID,s.year,s.SEMESTERfrom section s jointakes ton s.COURSE_ID=t.COURSE ID and s.SEC_ID=t.SEC_IDand s.SEMESTER=t.SEMESTER ands.year=t.yearjoin coursec on s.COURSE_ID=c.COURSE_ID where t.GRADE isnull

1.4withn as select DEPT_NAME,count*as numfrom instructorgroup byDEPTNAMEselect s.id,s.name,s.DEPT_NAMEfrom student s joinn on s.DEPT_NAME=n.DEPT NAMEwheren.num=

41.5withn as select DEPT_NAME,count*as numfrom instructorgroup byDEPT_NAMEselect d.DEPT_NAME,d.BUILDING,n.numfrom departmentd joinn ond.DEPT_NAME=n.DEPT NAMEwhere existsselect namefrominstructorwhere instructor,name like%世%andinstructor.DEPT_NAME=d.DEPT_NAME

2.1with easselectCOURSE_1D,SEC_ID,SEMESTER,YEARfrom sectiongroupby COURSE_ID,SEC_ID,SEMESTER,YEAR,t asselectCOURSE_IDfrom takeswhereGRADE like%F%groupbyCOURSE_IDselect course.COURSE_ID,TITLE,countSEC_IDfrom courseleft joine oncourse.COURSE_ID=e.COURSE IDwherenot existsselect*from twheret.COURSE_ID=course.COURSE_IDgroup bycourse.COURSE_ID,TITLE

2.2select s.COURSE_1D,s.YEAR,s.SEMESTER,s.SEC_ID,sumcase when t.GRADE inA,A+,A，then1else0end,sum case when t.GRADEinB,B+,B,then1else0end,sumcase when t.GRADEinC,C+,Cthen1else0end,sum casewhent.GRADEin D,D+,D，then1else0end,sum casewhent.GRADEinF,F+,F，then1else0end,sumcase whent.GRADEis nullthen1else0endfrom section s jointakes tons.COURSE_ID=t.COURSE ID ands.SEC_ID=t.SEC_ID ands.SEMESTER=t.SEMESTER ands.YEAR=t.YEARgroup by s.COURSE_ID,s.SEC_ID,s.SEMESTER,s.YEAR

2.3with timeslot_credithour asselect time_slot_id,count**end_hr-start_hr*60+end min-startjnin/50credit_hrfrom time_slotgroup bytime_slot_id,start_hr,endhr,start_min,endminselect course_id,year,semester,count*sec_cnt,sumcredit_hr credhrfromsection,timeslot_credithourwhere section,time slotid=timeslot credithour,time slotidgroup bycourse_id,year,semester

2.4select s.NAME,i.NAMEfrom students joinadvisor aons.ID=a.S IDjoin instructori ona.I_ID=i.IDwhere i.ID inselectt.ID fromteaches tjoin sectionsec ont.COURSE_ID=sec.COURSE_ID and t.SEC_ID=sec.SEC_IDandt.SEMESTER=sec.SEMESTER andt,YEAR=sec.YEAR jointakes ontakes.COURSE ID=sec.COURSE IDand takes.SEC_ID=sec.SEC IDand takes.SEMESTER=sec.SEMESTER andtakes.YEAR=sec.YEARwhere takes.ID=s.ID

3.2with sasselect*from studentselect s.NAME,s.DEPT_NAMEfrom swherenot existsselectdistinct cl.COURSEIDfrom studentsi joindepartment dlon si.DEPT_NAME=dl.DEPT_NAME joincoursecl oncl.DEPT_NAME=dl.DEPT_NAMEwhere si.DEPT_NAME=s.DEPT_NAMEexceptselect distinctt

2.COURSE ID from takest2wheret

2.grade Fandt

2.ID=s.ID

3.3select ID,NAMEfrom selectB.ROWNUM RNfromselect*from select ID,NAMEfrom studentorderby idasc abwhereROWNUM〈二10where RN〉=

63.4withstd asselectt.ID,t.COURSE ID from takestWHERE t.GRADE=F,a asselectID,COURSEID fromstd whereexistsselectID,COURSE_IDfromstd stwhere st.ID=std.ID exceptselect ID,COURSE_IDfromtakeswheretakes.ID=std.IDandtakes.GRADEF,b asselectID,COURSE IDfrom aexceptselectID,COURSE_IDfromtakeswhere takes.GRADE Fselects.ID,s.NAME,count*fromstudents joinbons.ID=b.ID groupbys.ID,s.NAME

3.5withf_exam asselectID,COURSE_ID,casewhenGRADE=F then1else0end numfromtakesselect s.ID,s.NAME,COURSE_ID,count*,sum numfromf_exam joinstudentsons.ID=f_exam.IDgroup bys.ID,f_exam.COURSE_ID,NAMEhaving count*1。