大连理工大学软件学院数据库第二次上机答案

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

1.

select course_id

from section

group by course_id

having count (distinct year)>1

select course.course_id , title

from section join course on section.course_id = course.course_id where year = '2010' and semester = 'spring'

and section.course_id in (select course_id

from section

group by course_id

having count (distinct year)>1)

with c_2010 as (

select course_id

from section

where year = '2010' and semester = 'spring'

and course_id in (select course_id

from section

group by course_id

having count (distinct year)>1))

,

ins_sla as

(select course_id ,sum(salary)as sum_sal , count (teaches.id) as ins_cnt

from teaches join instructor on teaches.id = instructor.id

where semester = 'spring' and year = '2010'

and course_id in (select course_id from c_2010)

group by course_id

)

,

sec_credit_hr as (

select time_slot_id ,sum (((end_hr - start_hr)*60+ end_min-start_min)/50)as cred_hr

from time_slot

group by time_slot_id )

,

course_hr as (

select course_id , cred_hr

from section join sec_credit_hr on section.time_slot_id = sec_credit_hr.time_slot_id

where semester = 'spring' and year = '2010'

and course_id in (select course_id from c_2010)

)

select course.course_id, title , cred_hr,sum_sal

from ins_sla join course_hr on ins_sla.course_id = course_hr.course_id

join course on ins_sla.course_id= course.course_id

2,

course takes student section

select * from section

select name ,takes.course_id , takes.semester ,takes.year , takes.sec_id ,title

from student left join takes on student.id = takes.id

left join section on takes.course_id = section.course_id

and takes.sec_id= section.sec_id

and takes.semester= section.semester

and takes.year= section.year

full join course on section.course_id = course.course_id

3.

select * from takes

select count(*)

from takes

where grade = 'F' and id = '45678'

select count (*)

from takes

where grade = 'F' and id in (select id

from student

where dept_name in (select dept_name from student where

id = '45678' )

)

select count (*)

from takes

where exists (select *

from student as s join student as t

on s.dept_name= t.dept_name

where s.id = takes. id and t.id = '45678') and grade = 'F'

select id ,name ,dept_name ,

(select count(*)

from takes

where grade = 'F' and id = student.id)as fail ,

(

select count (*)

from takes

where grade = 'F' and id in (select id

from student as s1

where dept_name in (select dept_name from student as s2 where id = student.id )

)) as all_fail

from student

4.

²î¼¯¿ÉÒÔÓÃnot in ±íʾ

select distinct takes.id

from instructor join teaches on instructor.id = teaches.id

join takes on takes.course_id = teaches.course_id

and takes.sec_id=teaches.sec_id

and takes.semester = teaches.semester

and takes.year = teaches.year

where instructor.dept_name= 'ÄÚ¹¦Ñ§Ôº'

µÃµ½Ñ¡ÐÞÄÚ¹¦Ñ§ÔºÀÏʦËù½ÌÈ«²¿¿Î³ÌµÄѧÉú

with stu_all_id as (

select id

from student

where not exists (

(select course_id

from teaches join instructor on instructor.id = teaches.id where dept_name= 'ÄÚ¹¦Ñ§Ôº')

except

(select course_id

from takes

where takes.id = student.id and grade is not null )

相关文档
最新文档