要求:
在学生成绩表中,显示存在有85分以上成绩的课程号,并且统计各门课程不及格人数在10人以上的课程数量
苦求 想了一会了 发现自己太笨了 谢谢大家
在学生成绩表中,显示存在有85分以上成绩的课程号,并且统计各门课程不及格人数在10人以上的课程数量
苦求 想了一会了 发现自己太笨了 谢谢大家
解决方案 »
- 求助,sqlserver写一个止盈止损的存储定时存储过程!
- 求一个查询语句~~~~~~~~~~~~~~~~我也不知道复杂不复杂!
- 数据库更新,怎么以更新就是整个数据库所有记录?
- 如何用一句sql实现!!!
- 通过为一个数据库的全部对象生成SQL脚本,再在目标机器上运行的方法,能否替代复制数据库向导的功能?
- 大侠,我请教
- 用存储过程,使其的OUTPUT参数为表的记录数,这个参数怎么设置哪?
- 怎么样获得局域网中SQL SERVER服务器名
- 求教一个sql写法
- sql批量修改数据
- 这个合并查询要怎么写?
- 挑战SQL大师级人物极限问题,望各位高手指点一下 怎么样写出这样的汇总语句出来 小弟感激不尽!!!!在线等!
1、 select Sname,Ssex,Class from Student;
2、 select distinct depart from teacher;
3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'from student;
或
select Sno as 学号,Sname as 姓名,Ssex as 性别,Sbirthday as 出生日期,Class as 班号 from student;
4、 select * from score where degree between 60 and 80;
或select * from score where degree>=60 and degree<=80;
5、 select * from score where degree in (85,86,88);
6、 select * from student where class='95031'or Ssex='女';
7、 select * from student order by class desc;
8、 select * from score order by cno asc ,degree desc;
或select * from score order by cno ,degree desc;
9、 select count(*) as CNT from student where class='95031';
10、select Sno as '学号',cno as '课程号', degree as '最高分' from score
where degree=(select max(degree) from score)
11、select avg(degree)as 课程平均分 from score where cno='3-105';
12、select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >5;
13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90;
14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno;
15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno;
16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno;
17、select y.Cno,avg(y.degree) from student x,score y where x.Sno=y.Sno and x.class='95033'group by y.cno;
18、select Sno,Cno,rank from score,grade where degree between low and upp order by rank;
19、select x.Cno,x.Sno,x.degree from score x,score y
where x.cno='3-105' and x.degree>y.degree and y.sno='109'and y.cno='3-105';
20、
1,查询成绩非本科最高 select * from score b where degree <(select max(degree) from score a where a.cno=b.cno);
2,查询成绩非本科最高并且选2门以上的学生的成绩:
21、select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and y.sno='109'and y.cno='3-105';
select cno,sno,degree from score where degree >(select degree from score where sno='109' and cno='3-105')
22、select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') from student where sno='108');
23、select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭');
24、select tname from teacher where tno in(select x.tno from course x,score y where x.cno=y.cno group by x.tno having count(x.tno)>5);
25、select * from student where class in('95033','95031');
26、select distinct cno from score where degree in (select degree from score where degree>85);
27、select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算机系');
28、select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
29、select * from score where cno='3-105' and degree>any (select degree from score where cno='3-245')order by degree desc;
30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
31、select tname,tsex,tbirthday from teacher
union select sname,ssex,sbirthday from student;
32、select tname,tsex,tbirthday from teacher where tsex='女'
union select sname,ssex,sbirthday from student where ssex='女';
33、select * from score a where degree<(select avg(degree)
from score b where a.cno=b.cno);
34、select tname,depart from teacher a where exists
(select * from course b where a.tno=b.tno);
35、select tname,depart from teacher a where not exists
(select * from course b where a.tno=b.tno);
36、select class from student where ssex='男'group by class having count(*)>=2;
37、select * from student where sname not like'王_';
38、select sname as 姓名,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) as 年龄 from student
39、select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) from student)
union select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student)
40、select class,sname,sbirthday from student order by class desc,sbirthday;
41、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';
42、select * from score where degree=(select max(degree)from score);
43、select sname from student where ssex=(select ssex from student where sname='李军');
44、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
45、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course
where cname='计算机导论');
表结构 可以是
如下
stud_id,course_id,grade
001 ,1 ,45
等等 谢谢大家
select 课程号
from 成绩表
where 成绩>=852.
SELECT 数量=count(1)
FROM
(
SELECT 课程号,cnt=count(1)
FROM 成绩表
WHERE 成绩<60
) A
WHERE cnt>=10
select distinct course_id
from 学生成绩表
where grade>=85
select count(*) from (
select course_id
from 学生成绩表
where grade<60
group by course_id
having count(*)>=10
) t
if object_id('tb') is not null drop table tb
go
create table tb([stud_id] varchar(50),[course_id] INT,[grade] INT)
insert into tb
select '001',1,45 union all
select '002',1,88 union all
select '003',1,90 union all
select '001',2,30 union all
select '002',2,50 union all
select '003',2,30 union all
select '004',2,30 union all
select '005',2,50 union all
select '006',2,55 union all
select '007',2,30 union all
select '008',2,95 union all
select '009',2,30 union all
select '010',2,44 union all
select '011',2,33 union all
select '012',2,30
go
--select * from tb
select course_id from tb group by course_id having max(grade) > 85
/*
course_id
-----------
1
2
*/
intersect
select course_id from tb group by course_id
having sum(case when grade < 60 then 1 else 0 end) > 10
/*
course_id 不及格人数
----------- -----------
2 11(1 行受影响)
*/--同时满足
select course_id from tb group by course_id having max(grade) > 85
intersect
select course_id from tb group by course_id
having sum(case when grade < 60 then 1 else 0 end) > 10 /*
course_id
-----------
2
*/
use tempdb
if object_id('tb') is not null drop table tb
go
create table tb([stud_id] varchar(50),[course_id] INT,[grade] INT)
insert into tb
select '001',1,45 union all
select '002',1,88 union all
select '003',1,90 union all
select '001',2,30 union all
select '002',2,50 union all
select '003',2,30 union all
select '004',2,30 union all
select '005',2,50 union all
select '006',2,55 union all
select '007',2,30 union all
select '008',2,95 union all
select '009',2,30 union all
select '010',2,44 union all
select '011',2,33 union all
select '012',2,30
go
--select * from tb
select course_id from tb group by course_id having max(grade) > 85
/*
course_id
-----------
1
2
*/
intersect
select course_id from tb group by course_id
having sum(case when grade < 60 then 1 else 0 end) > 10
/*
course_id 不及格人数
----------- -----------
2 11(1 行受影响)
*/--同时满足
select course_id from tb group by course_id having max(grade) > 85
intersect
select course_id from tb group by course_id
having sum(case when grade < 60 then 1 else 0 end) > 10 /*
course_id
-----------
2
*/