大連理工大學(xué)軟件學(xué)院數(shù)據(jù)庫上機(jī)題目答案修訂稿

1、Mhn1.3- rL集團(tuán)標(biāo)準(zhǔn)化工作小組Q8QX9QT-X8QQB8Q8-NQ8QJ8-M8QMN大連理工大學(xué)軟件學(xué)院一一數(shù)據(jù)庫上機(jī)題1Find the ID, names of all the students from departments whose name contain character 功.Find the ID, names and total credits of students in 邪門學(xué)院 department or in 兵器學(xué)院 department whose total credits are higher than 50 creditsFor the in

2、structor 83821, show course_id and title of all courses taught by the instructorAs above, but show the total number of credits for such courses (taught by that instructor). You should use SQL aggregation on courses taught by that instructor.As above, but display the total credits for each of the ins

3、tructors, along with the ID of the instructor; dont bother about the name of the instructors.(Dont bother about instructors who have not taught any course, they can be omitted)Find average instructors salaries for each of courses, along with the course_id and title of the course, taught by instructo

4、rs of 內(nèi)功學(xué)院, theresult should be sorted from the lowest to the highest according to the average salaries.Find the names of all courses which have been taught in 南疆雨林 ever (there should be no duplicate names)Display the IDs and names of all students who have never registered for a courseFind the id an

5、d names of the courses which have been registered by some students without evaluated grade.Find the courses which are the Subsequence courses of othercourses. The result should involve the ids and titles of the Subsequencecourses and the ids and titles of its prerequisites (note: the names ofcolumns

6、 in result should show the roles of the courses clearly)select id,namefrom studentwhere dept_name like %功select id,name,tot_credfrom studentwhere dept_name=兵器學(xué)院or dept_name=邪門學(xué)院and tot_cred50group by id,name,tot_credselect distinct course.course_id,titlefrom course join teaches on course.course_id=t

7、eaches.course_id where teaches.id=83821select distinct course.course_id,title,sum(credits) as cre_sum from course join teaches on course.course_id=teaches.course_id where teaches.id=83821group by course.course_id,titleselect S.id,sum(credits) as cre_sumfrom instructor as S,teaches as T,coursewhere S

8、.id=T.id and T.course_id=course.course_idgroup by S.idselect course.course_id,title,AVG(salary) as avg_salfrom course,instructor as T,teacheswhere course.dept_name=內(nèi)功學(xué)院and T.dept_name=內(nèi)功學(xué)院and teaches.id=T.idand teaches.course_id=course.course_idgroup by course.course_id,course.titleorder by avg_sal

9、descselect distinct T.titlefrom section as S,course as Twhere T.course_id=S.course_idand S.building=南疆雨林select id,namefrom studentwhere id not in(select id from takes)select S.course_id,S.title,SubCourse as Role,T.course_id,T.title,PreCourse as Role from course as S,course as T,prereqwhere S.course_

10、id=prereq.course_id and prereq.prereq_id=T.course_id大連理工大學(xué)軟件學(xué)院一一數(shù)據(jù)庫上機(jī)題2Intermediate SQL-2Using the university schema that you have write the following queries. In some cases youmight need to insert extra data to show the effect of a particular feature.Recommendation: With clause is strongly recommen

11、ded for simplifying the query.Find the courses which have been offered for 2 years at least and have sections in spring,2010. For each course as such, information displayed should involve:Identifier of course(i.e. the primary key for section) * Title of the courseNumber of instructors who in charge

12、of teaching the course in spring ,2010Total salary all over the instructors who in charge of teaching the course inspring ,2010Total credit hours performed per week( Note: 1 credit hour equals to 50 minutes).USE outer join to construct the following queryFind all information for student registration

13、 and course offered.The students who have never registered for any courses and the courses has never been offered. For each record inthe result, information displayed should involve:Identifier of student(i.e. the primary key for student) * Name of studentIdentifier of section(i.e. the primary key fo

14、r section) * Title of course.The result should be like the followingUSE scalar subquery to construct the following queryFor all students, grade information of each student is needed. Those students who havenever registered for any section should also be considered. For each student, informationdispl

15、ayed should involve:Identifier of student(i.e. the primary key for student) * Name of studentDepartment name of studentNumber of failure for the student to pass some section. (That is the number of grade F)Total number of failure of passing sections for the students in the same department asthe curr

16、ent student.Find students who have registered for some but not all courses(PART COURSE, for short)taught by instructors of department 拳腳學(xué)院.Furthermore, the registration of thesestudents for such courses (i.e. PART COURSE above) should have grade, even the grade isF. Do this using the not exists . ex

17、cept . structure. For each student as such,information displayed should involve:Identifier of student(i.e. the primary key for student) * Name of the studentNumber of courses, taught by instructors of department 拳腳學(xué)院， registered by thestudentUse EXISTS or NOT EXISTS clause in WHERE clause to constru

18、ct following query.Find those sections which have no instructor as the teacher. Moreover, these sectionsshould have never been registered by any student. For each section as such, informationdisplayed should involve:Identifier of student(i.e. the primary key for student) * Name of the corresponding

19、course.Credits of the course-1with course_twoYear(course_id) as(select course_id from teaches wherecourse_id in(select course_idintersect (select course_idfrom teaches where year=2010 and semester=Spring), number(course_id,ID,num) as (selectcourse_id,ID,COUNT(sec_id)from teacheswhere year=2010 and s

20、emester=Spring group by course_id,ID),tot_time(course_id,times) as (selectcourse_id,SUM(credits*50) from course group by course_id)select course.course_id ,title ,num,SUM(salary)as tot_sal,timesfrom course_twoYear ,course ,number,instructor,tot_time where course.course_id=course_twoYear.course_id an

21、d number.course_id=course.course_id andinstructor.ID=number.ID andcourse.course_id=tot_time.course_idgroup by course.course_id ,title ,num,times;-2with stu_take(course_id,name,sec_id,year,semester) as (select course_id,name,sec_id,year,semester from student left join takes on student.ID=takes.ID)sel

22、ect name,sec_id,year,semester,title from stu_take full join course on stu_take.course_id=course.course_id;-3 select student.ID,,student.dept_name, (selectCOUNT(takes.grade)from takes wheretakes.ID二student.id and takes.grade=F), (select COUNT(takes.ID)from student,takeswhere student.ID二ta

23、kes.ID and takes.grade=F) from student;-4with T(student_name,total_course)as(select ,COUNT(course.course_id)from student join takeson student.ID二takes.IDjoin course on takes.course_id=course.course_id where course.dept_name= 拳腳學(xué)院group by )select ID,name,total_coursefrom stude

24、nt join T on 二T.student_name where not exists(select student_name from T where T.total_course=(selectCOUNT(course_id)from course where course.dept_name =拳腳學(xué)院)except(select student_name from T)-5select course.course_id,title,creditsfrom course Full outer join teaches on course.course_id =

25、 teaches.course_idFull outer join takes on course.course_id = takes.course_idFull outer join section on course.course_id = section.course_idwherecourse.course_idnot； fromteaches,course,takes；whereteaches.course_id=s； andsection.course_id=tak；where course.course_id not in(select course.course_idfrom

26、teaches ,course ,takes ,sectionwhere teaches.course_id = section.course_id and takes.course_id = course.course_idand section.course_id = takes.course_id )大連理工大學(xué)軟件學(xué)院一一數(shù)據(jù)庫上機(jī)題3Using the university schema that you have write the following queries. In some cases you might need to insert extra data to sho

27、w the effect of a particular feature.Insert each student as an instructor of department 拳腳學(xué)院，with salary=40000Now delete all the newly added instructors above (note: already existing instructors who happened to have salary=40000 should not get deleted)Update the salary of each instructor to 10000 ti

28、mes the number of course sections they have taught.The university rules allow an F grade to be overridden by any pass grade (for example, A).Now, lists students who have fail grades that have not been overridden.For each student as such, information displayed (in one row) should involve:l Identifier

29、 of student l Name of studentl Count of F grades that have not been overridden.In one result, list the instructors who have never taught any courses and the students who have never registered for any courses. For each person, information displayed (in one row) should involve:l Id of the personl Name of the personl Role of the person. The value of role should be student or instructor.?1insert i