第一题、 请按照以下题目的意思,写出相应的SQL语句 (20分)
为管理岗位业务培训信息,建立3个表:
S (SNO,SN,SD,SA) SNO,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (CNO,CN,CT ) CNO,CN,CT 分别代表课程编号、课程名称、任课教师
SC ( SNO,CNO,G ) SNO,CNO,G 分别代表学号、所选修的课程编号、学习成绩
1. 使用标准SQL语句查询选修课程名称为’RUBY’的学员学号和姓名.
2. 使用标准SQL语句查询课程分数大于80分以上的学员的学号,姓名以及课程.
3. 使用标准SQL语句查询选修课程在两门及两门以上学员的学号,姓名以及课程,并按照选修课程的数量降序排序.
4. 使用标准SQL语句查询没有选修”张老师”课程的所有学员的学号及姓名.
5. 使用标准SQL语句查询列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
1.
select * from s a where sno in (select sno from sc inner join c on sc.cno=c.cno and a.cn='RUBY' )
2.
select a.SNO,a.SN,b.CN from S a inner join
(select sc.sno,c.cn from sc inner join c on sc.cno=c.cno and sc.G>80)b on a.sno=b.sno
3.
select a.SNO,a.SN,b.CN from S a inner join
(select aa.sno,c.cno from (select sno ,count(cno) as CNONUM from sc group by sno having CNONUM>=2)aa
inner join c on sc.cno=c.cno and sc.G>80)b on a.sno=b.sno order by aa.CNONUM desc
4.
select a.SNO,a.SN from S a left join
(select sc.sno,c.CT from sc inner join c on sc.cno=c.cno AND c.CT='张老师' )b
on a.sno=b.sno where b.sno is null
5.
select a.SN,b.chengji from S a inner join
(select aa.chengji,c.cno from ( select c.cno,aa.avg(G) AS chengji from sc where G<60 group by sno )aa
inner join c on sc.cno=c.cno )b on a.sno=b.sno
为管理岗位业务培训信息,建立3个表:
S (SNO,SN,SD,SA) SNO,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (CNO,CN,CT ) CNO,CN,CT 分别代表课程编号、课程名称、任课教师
SC ( SNO,CNO,G ) SNO,CNO,G 分别代表学号、所选修的课程编号、学习成绩
1. 使用标准SQL语句查询选修课程名称为’RUBY’的学员学号和姓名.
2. 使用标准SQL语句查询课程分数大于80分以上的学员的学号,姓名以及课程.
3. 使用标准SQL语句查询选修课程在两门及两门以上学员的学号,姓名以及课程,并按照选修课程的数量降序排序.
4. 使用标准SQL语句查询没有选修”张老师”课程的所有学员的学号及姓名.
5. 使用标准SQL语句查询列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
1.
select * from s a where sno in (select sno from sc inner join c on sc.cno=c.cno and a.cn='RUBY' )
2.
select a.SNO,a.SN,b.CN from S a inner join
(select sc.sno,c.cn from sc inner join c on sc.cno=c.cno and sc.G>80)b on a.sno=b.sno
3.
select a.SNO,a.SN,b.CN from S a inner join
(select aa.sno,c.cno from (select sno ,count(cno) as CNONUM from sc group by sno having CNONUM>=2)aa
inner join c on sc.cno=c.cno and sc.G>80)b on a.sno=b.sno order by aa.CNONUM desc
4.
select a.SNO,a.SN from S a left join
(select sc.sno,c.CT from sc inner join c on sc.cno=c.cno AND c.CT='张老师' )b
on a.sno=b.sno where b.sno is null
5.
select a.SN,b.chengji from S a inner join
(select aa.chengji,c.cno from ( select c.cno,aa.avg(G) AS chengji from sc where G<60 group by sno )aa
inner join c on sc.cno=c.cno )b on a.sno=b.sno
5.
select a.学员姓名 ,b.平均成绩 from s a join a
(select sno,AVG(G) 平均成绩 from sc where g<60 group by sno having COUNT(1)>=2) b
on a.sno=b.sno
from s , c , sc
where s.sno = sc.sno and sc.cno = c.cno and c.cn = 'RUBY'2. 使用标准SQL语句查询课程分数大于80分以上的学员的学号,姓名以及课程.
select s.SNO,s.SN,c.cn
from s , c , sc
where s.sno = sc.sno and sc.cno = c.cno and sc.g > 803. 使用标准SQL语句查询选修课程在两门及两门以上学员的学号,姓名以及课程,并按照选修课程的数量降序排序. select s.sno , s.sn , sc.cn , t.cnt
from s , c , sc ,
(select sno , count(*) cnt from sc group by sno having count(*) > 1)
wehre s.sno = c.cno and s.sno = t.sno and sc.cno = s.cno
order by t.cnt desc4. 使用标准SQL语句查询没有选修”张老师”课程的所有学员的学号及姓名. select s.sno , s.sn from s where sno in
(select distinct sc.sno from sc , c where sc.cno = c.cno and c.ct = '张老师')5. 使用标准SQL语句查询列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
select s.sno , avg(sc.g) from s , sc where s.sno = sc.sno and s.sno in
(select sno from sc where g < 60 group by sno having (count(*)) > 1) group by s.sno
from s , c , sc
where s.sno = sc.sno and sc.cno = c.cno and c.cn = 'RUBY'2. 使用标准SQL语句查询课程分数大于80分以上的学员的学号,姓名以及课程.
select s.SNO,s.SN,c.cn
from s , c , sc
where s.sno = sc.sno and sc.cno = c.cno and sc.g > 803. 使用标准SQL语句查询选修课程在两门及两门以上学员的学号,姓名以及课程,并按照选修课程的数量降序排序. select s.sno , s.sn , sc.cn , t.cnt
from s , c , sc ,
(select sno , count(*) cnt from sc group by sno having count(*) > 1)
wehre s.sno = c.cno and s.sno = t.sno and sc.cno = s.cno
order by t.cnt desc4. 使用标准SQL语句查询没有选修”张老师”课程的所有学员的学号及姓名. select s.sno , s.sn from s where sno not in
(select distinct sc.sno from sc , c where sc.cno = c.cno and c.ct = '张老师')5. 使用标准SQL语句查询列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
select s.sno , avg(sc.g) from s , sc where s.sno = sc.sno and s.sno in
(select sno from sc where g < 60 group by sno having (count(*)) > 1) group by s.sno
看看当代卖国贼长什么模样
p m51.com/p/mgza.asp
(select * from sc , c where sc.cno = c.cno and sc.sno=sno and c.ct = '张老师')