SQL语句如何写？ (30分)

W

webease

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-26

• #1

已知
student表（sn,sname,ssex,grade,sdept） sn 学号为主键
course表（cn，cname，credit） cn 课程编码为主键
sc表（sn，cn，score） sn＋cn为主键
求计算机系选修了三门课程以上的学生学号，
1. 用嵌套写
2. 用连接写
并且采用那种写法效率高呢?

 

J

jsxjd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-26

• #2

select sn from (select sn,sum(1) as kcs from sc group by sn) aa
where aa.kcs>=3
and exists(select sn from student bb where bb.cn=aa.cn and bb.sdept='计算机系')

 

X

xj_liyq

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-26

• #3

sum(1) 什么意思
仅供参考：
select a.sn,count(cn),b.sdept from sc a left join student b on b.sn=b.sn
group by a.sn,b.sdept
having count (cn)>=3
and b.sdept='计算机系'

 

A

Admy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-26

• #4

1. 用嵌套
select * from student
where sn in
(select sn from sc
group by sn
having count(*)>3)
and sdept='计算机系'

2. 用连接写
select t.sn
from student t,sc s
where t.sn=s.sn
and sdept='计算机系'
group by t.sn
having count(*)>3

 

P

picmud

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-26

• #5

这个题目好像是大学教材的课后习题…………

 

J

jsxjd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-26

• #6

Sum(1) 也可以是 count(*) ,计算课程数

 

C

chinaluo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-27

• #7

select * from student
where
sdept='计算机系'
and sn in
(select sn from sc
group by sn
having count(*)>3)

 

W

webease

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-27

• #8

多人接受答案了。