1: Select * from S where SD = '计算机系' and XB = '男' 2: Select SC.SNO,S.SN,C.CN from SC JOIN S ON SC.SNO = S.SNO join C on SC.CNO = C.CNO where SC.G<60 3: Select SC.SNO,S.SN,avg(G) from SC JOIN S ON SC.SNO = S.SNO group by SC.SNO,S.SN where avg(G) >= 80 4: Select SC.SNO,S.SN from SC JOIN S ON SC.SNO = S.SNO join C on SC.CNO = C.CNO where C.CN = '数学'
3、select a.sno,a.SN from 学生表S a,选课表SC b where a.SNO=b.SNO group by a.sno,a.SN having avg(b.g)>=80 4、select a.* from 学生表S a,选课表SC b,课程表C c where a.sno=b.sno and b.CN0=c.CN0 and c.cn='数据库' and b.g<60
麻烦用标准sql语言编写,不用transact-sql语言编写。是考试题目。
1、查询计算机系的所有男生名单。 select * from c where SD='计算机系' and xb='男'2、查询所有不及格成绩的学生名单。 select * from s where SNO in (select SN0 from SC where G<60)3、查询所有平均成绩不低于 80 分的学生名单。 select * from s where SNO in (select SN0 from SC group by SNO having avg(G)>=80)4、查询数据库课程不及格的学生名单。 select * from s where SNO in (select SN0 from SC where CN0 in (select CNO from C where CN='数据库') and G<60)
Select * from S where SD = '计算机系' and XB = '男'
2:
Select SC.SNO,S.SN,C.CN from SC JOIN S ON SC.SNO = S.SNO join C on SC.CNO = C.CNO where SC.G<60
3:
Select SC.SNO,S.SN,avg(G) from SC JOIN S ON SC.SNO = S.SNO
group by SC.SNO,S.SN where avg(G) >= 80
4:
Select SC.SNO,S.SN from SC JOIN S ON SC.SNO = S.SNO join C on SC.CNO = C.CNO
where C.CN = '数学'
4、select a.* from 学生表S a,选课表SC b,课程表C c where a.sno=b.sno and b.CN0=c.CN0 and c.cn='数据库' and b.g<60
select * from c where SD='计算机系' and xb='男'2、查询所有不及格成绩的学生名单。
select * from s where SNO in (select SN0 from SC where G<60)3、查询所有平均成绩不低于 80 分的学生名单。
select * from s where SNO in (select SN0 from SC group by SNO having avg(G)>=80)4、查询数据库课程不及格的学生名单。
select * from s where SNO in (select SN0 from SC where CN0 in (select CNO from C where CN='数据库') and G<60)