MYSQL之SQL语句练习及思路_1

关系模式

表格需求：

1.student表

sno	sname	age	gender
1	liqiang	23	male
2	liuli	22	female
3	zhangyou	22	male

2.course表

cno	cname	teacher
k1	c	wanghua
k5	database	chengjun
k8	complie	chengjun

3.score表

sno	cno	score
1	k1	83
2	k1	85
5	k1	92
2	k5	90
5	k5	84
5	k8	80

问题：

1.用SQL语句实现如下检索：

（1）查询‘程军’老师所教授的所有课程

select cname from course where teacher like 'chengjun';

（2）查询‘李强’同学所有课程的成绩

select course.cname,score from score,student,course wre here student.sno=score.sno and score.cno=course.cno and sname='liqiang';

（3)查询课程名为‘C’的平均成绩

select avg(score) from score where cno=(select cno from course where cname='c');

（4）查询选修了所有课程的同学的信息

select sno from score group by sno  having count(sno) >= (select  count(cno) from course);

分析:该问题中首先是要对score表进行统计，统计出sno出现的次数，理论上sno的为学生所报的课程数，若该值等于course表中课程统计数量，即为结果。

（5）检索王老师所授课程的课程号和课程名

select cno,cname from course where teacher like 'wang%';

（6）检索年龄大于23岁的男学生的学号和姓名

select sno,sname from student where age > 23;

（7）检索至少选修王老师所授课程中一门课程的女学生姓名

select  distinct sname from student,score where score.cno  in (select cno from course where teacher like 'wang%') and gender='female';

select sname from student,course,score where student.sno=score.sno and course.cno=score.cno and course.teacher like 'wang%' and student.gender='female';

(8)检索李同学不学的课程的课程号

select cno from course where cno not in (select cno from rom score,student  where student.sno=score.sno and  sname like 'li');

（9）检索至少选修两门课程的学生学号

select sno from score group by sno having count(sno) > 2;

（10）检索全部学生都选修的课程的课程号和课程名

select cno,cname from course where cno in (select cno from score group by cno having count(*) = (select count(*) from student));

思路解析：学生数量=score表中单科目总数

（11）检索选修课程包含王老师所授课程的学生学号

select sno from score where cno in (select cno from course where teacher like 'wang%');

select distinct sno from score,course where score.cno=course.cno and teacher like 'wang%';

（12）统计有学生选修的课程门数

select count(distinct cno) from score;

（13）统计选修k1课程的学生的平均成绩

select avg(score) from score where cno='k1';

（14）求王老师所授课程的每门课程的学生的平均成绩

select avg(score),score.cno from score,course where course.cno=score.cno and teacher like 'wang%' group by score.cno;

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

select cno,count(sno) from score group by cno having count(sno) > 2 order by count(sno) desc,cno asc;

（16）检索学号比李同学打，而年龄比她小的学生姓名

select sname from student where sno > (select sno from student where sname like 'liq%' ) and age < (select age from student where sname likke 'liq%');

（17）检索姓名以li开头的所有学生的姓名和年龄

select sname,age from student where sname like 'li%';

（18）在score中检索成绩为空值的学生学号和课程号

select sno,cno from score where score is null;

（19）检索年龄大于女同学平均年龄的男学生姓名和年龄

select sname,age from student where age > (select avg(age) from student where gender='female') and gender='male';

（20）检索年龄大于所有女同学年龄的男学生姓名和年龄

select sname,age from student where age > any (select age from student where gender='female');

（21）检索至少选修程军老师所授全部课程的学生姓名

select sname from student,course,score  where student.sno=score.sno and course.cno=score.cno and teacher='chengjun' group by sname having count(*) >= (select count(*) from course where teacher='chengjun');

思路分析，首先找出选修程军老师全部课程的学生，并统计该值在score表中出现的次数，该数字大于等于程军老师教授课程总数量。