大连理工大学软件学院 数据库 Intermediate SQL-2 上机答案

大连理工大学软件学院 数据库 Intermediate SQL-2 上机答案

你的下载是我上传的动力,请不要吝啬一个财富值

Intermediate SQL-2

Using the university schema that you have write the following queries. In some cases you

might need to insert extra data to show the effect of a particular feature.

Recommendation: With clause is strongly recommended for simplifying the query.

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:

* Identifier of course(i.e. the primary key for section) * Title of the course

* Number of instructors who in charge of teaching the course in spring ,2010

* 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).

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

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.

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

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

* Department name of student

* Number 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 as 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

students for such courses (i.e. PART COURSE above) should have grade, even the grade is

‘F’. Do this using the \exists ... except ...\structure. For each student as such,

information displayed should involve:

* Identifier of student(i.e. the primary key for student) * Name of the student

* Number of courses, taught by instructors of department '拳脚学院', registered by the student

5. Use EXISTS or NOT EXISTS clause in WHERE clause to construct following query.

Find those sections which have no instructor as the teacher. Moreover, these sections should have never been registered by any student. For each section as such, information

displayed should involve:

* Identifier of student(i.e. the primary key for student) * Name of the corresponding course. * Credits of the course

--1

with course_twoYear(course_id) as

(select course_id from teaches where course_id in

(select course_id

from teaches where year=2009) intersect

(select course_id

from teaches where year=2010 and semester='Spring')), number(course_id,ID,num) as (select course_id,ID,COUNT(sec_id)from teaches

where year=2010 and semester='Spring' group by course_id,ID),

tot_time(course_id,times) as (select

course_id,SUM(credits*50) from course group by course_id)

select course.course_id ,title ,num,SUM(salary)as tot_sal,times

from course_twoYear ,course ,number,instructor,tot_time where course.course_id=course_twoYear.course_id and number.course_id=course.course_id and instructor.ID=number.ID and

course.course_id=tot_time.course_id

group by course.course_id ,title ,num,times; --2

with 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)

select 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.name,student.dept_name, (select COUNT(takes.grade)from takes where takes.ID=student.id and takes.grade='F'),

(select COUNT(takes.ID) from student,takes

where student.ID=takes.ID and takes.grade='F') from student; --4

with T(student_name,total_course)as

(select student.name,COUNT(course.course_id) from student join takes on student.ID=takes.ID

join course on takes.course_id=course.course_id where course.dept_name='拳脚学院' group by student.name)

select ID,name,total_course

from student join T on student.name=T.student_name where not exists( (select student_name from T where T.total_course=(select COUNT(course_id)from course where course.dept_name='拳脚学院')) except

(select student_name from T) ) --5

select course.course_id,title,credits

from course Full outer join teaches on course.course_id = teaches.course_id

Full outer join takes on course.course_id = takes.course_id

Full outer join section on course.course_id = section.course_id

where course.course_id not in((select course.course_id

from teaches ,course ,takes ,section

where teaches.course_id = section.course_id and takes.course_id = course.course_id

and section.course_id = takes.course_id ) )

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库大连理工大学软件学院 数据库 Intermediate SQL-2 上机答案在线全文阅读。