大连理工大学软件学院——数据库上机题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,name
from student
where dept_name like '%功%'

select id,name,tot_cred
from student
where dept_name='兵器学院' or dept_name='邪门学院'
		and tot_cred>50
group by id,name,tot_cred

select distinct course.course_id,title
from course join teaches on course.course_id=teaches.course_id
where teaches.id=83821

select distinct course.course_id,title,sum(credits) as cre_sum
from course join teaches on course.course_id=teaches.course_id
where teaches.id=83821
group by course.course_id,title

select S.id,sum(credits) as cre_sum
from instructor as S,teaches as T,course
where S.id=T.id and T.course_id=course.course_id
group by S.id


select course.course_id,title,AVG(salary) as avg_sal
from course,instructor as T,teaches
where course.dept_name='内功学院'
		 and T.dept_name='内功学院'
		 and teaches.id=T.id
		 and teaches.course_id=course.course_id
group by course.course_id,course.title
order by avg_sal desc


select distinct T.title
from section as S,course as T
where T.course_id=S.course_id
	and S.bu