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

大连理工大学软件学院——数据库上机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)

 

 

[sql] 
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.building='南疆雨林'  
  
select id,name  
from student  
where 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,prereq  
where S.course_id=prereq.course_id and prereq.prereq_id=T.course_id