MYSQL之SQL语句练习及思路_1

2018/03/06 18:42
阅读数 7

关系模式

表格需求:

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表中出现的次数,该数字大于等于程军老师教授课程总数量。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部