大连理工大学软件学院——数据库上机1

大连理工大学软件学院——数据库上机题1

1. Find the ID, names of all the students from departments whose name contain character '功'.

2. Find the ID, names and total credits of students in 邪门学院 department or in 兵器学院 department whose total credits are higher than 50 credits

3. For the instructor 83821, show course_id and title of all courses taught by the instructor

4. As 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.

5. As above, but display the total credits for each of the instructors, along with the ID of the instructor; don't bother about the name of the instructors.

(Don't bother about instructors who have not taught any course, they can be omitted)

6. Find average instructors' salaries for each of courses, along with the course_id and title of the course, taught by instructors of 内功学院, theresult should be sorted from the lowest to the highest according to the average salaries.

7. Find the names of all courses which have been taught in 南疆雨林 ever (there should be no duplicate names)

8. Display the IDs and names of all students who have never registered for a course

9. Find the id and names of the courses which have been registered by some students without evaluated grade.

10. 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 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='兵器学院' or dept_name='邪门学院'and tot_cred>50group by id,name,tot_credselect distinct course.course_id,titlefrom course join teaches on course.course_id=teaches.course_idwhere teaches.id=83821select distinct course.course_id,title,sum(credits) as cre_sumfrom course join teaches on course.course_id=teaches.course_idwhere teaches.id=83821group by course.course_id,titleselect S.id,sum(credits) as cre_sumfrom instructor as S,teaches as T,coursewhere S.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='内功学院' and T.dept_name='内功学院' and teaches.id=T.id and teaches.course_id=course.course_idgroup by course.course_id,course.titleorder by avg_sal 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 Rolefrom course as S,course as T,prereqwhere S.course_id=prereq.course_id and prereq.prereq_id=T.course_id