查询计算机基础课不及格的学生名单,SQL-select语句练习

1、查询各系教师和学生人数

2、查询学生的选课情况(学生只能选修所在系的教师课程)

3、统计各系各职称教师的最高最低和平均工资

4、查询学生中年龄最小的前三名学生

5、查询各系姓“王”学生的分布情况

6、查询学生中重名的名字，并统计同名的学生人数

7、查询教师中总收入排行前3的教师信息

8、统计每门课程的学生选修人数。要求输出人数超过10人的课程号和选修人数， 查询结果按人数降序排列，若人数相同，按课程号升序排列。

9、查询选修了“计算机基础”而没选修“微机原理”的学生名单

10、查询没有选全部课程的学生名单

11、查询选修了“张雪”教师课程中分数最高的学生选课情况

12、查询每门课程考试成绩最好的同学学号和姓名

13、统计有两门课程不及格的学生学号及其平均成绩

14、把成绩低于该课程平均成绩的女生分数提高5分

15、查询未选修任何课程的学生名单

解析：

use jiaoxuedb

/*1*/select teacher.dept,count(distinct sno) 学生,count(distinct tno) 教师 from teacher,student

where teacher.dept=student.dept group by teacher.dept

/*2*/select sn,cn,score from student,sc,course,tc,teacher where student.sno=sc.sno and http://doc.xuehai.neto=http://doc.xuehai.neto and http://doc.xuehai.neto=http://doc.xuehai.neto and tc.tno=teacher.tno

/*3*/select dept,prof,max(sal) 最高,min(sal) 最低,avg(sal) 平均 from teacher group by dept,prof

/*4*/select top 3 * from student order by age desc

/*5*/select dept,count(sno) from student where sn like '王%'group by dept

/*6*/select sn,count(sno) from student group by sn having count(sno)>1 /*7*/select * from teacher where tno in(select top 3 tno from teacher order by sal+comm desc)

/*8*/select cno,count(sno) from sc group by cno having count(sno)>=10 order by count(sno) desc,cno asc

/*9*/select sno from sc where sno in (select sno from sc,course where http://doc.xuehai.neto=http://doc.xuehai.neto and cn='计算机基础')and sno not in

(select sno from sc,course where http://doc.xuehai.neto=http://doc.xuehai.neto and cn='微机原理') /*10*/select sn from student where sno in(select sno from sc group by sno having

count(cno)<>(select count(cno) from course ))

/*11*/select top 1 sc.sno from sc,teacher,tc,course,student where teacher.tno=tc.tno and http://doc.xuehai.neto=http://doc.xuehai.neto and http://doc.xuehai.neto=http://doc.xuehai.neto and