大连理工大学软件学院数据库上机题目+答案(最新)

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
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='内功学院'
the current student.
4. Find students who have registered for some but not all courses(PART COURSE, for short)
taught by instructors of department '拳脚学院'. Furthermore, the registration of these
the result, information displayed should involve:
* Identifier of student(i.e. the primary key for student) * Name of student
* Identifier of section(i.e. the primary key for section) * Title of course.
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
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)
students for such courses (i.e. PART COURSE above) should have grade, even the grade is
never registered for any section should also be considered. For each student, information
displayed should involve:
* Identifier of student(i.e. the primary key for student) * Name of student
2. USE outer join to construct the following query
Find all information for student registration and course offered.
The students who have
never registered for any courses and the courses has never been offered. For each record in
????select id,name
from student
where dept_name like '%功%'
select id,name,tot_cred
from student
where dept_name='兵器学院' or dept_name='邪门学院'
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
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.
might need to insert extra data to show the effect of a particular feature.
Recommendation: With clause is strongly recommended for simplifying the query.
The result should be like the following
3. USE scalar subquery to construct the following query
For all students, grade information of each student is needed. Those students who have
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.
1. 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:
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
* Total salary all over the instructors who in charge of teaching the course in
spring ,2010
* Total credit hours performed per week( Note: 1 credit hour equals to 50 minutes).
大连理工大学软件学院——数据库上机题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
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
and teaches.id=T.id
and teaches.course_id=course.course_id
group by course.course_id,course.title
order by avg_sal desc
where S.course_id=prereq.course_id and prereq.prereq_id=T.course_id
大连理工大学软件学院——数据库上机题2
Intermediate SQL-2
Using the university schema that you have write the following queries. In some cases you