MySQL数据库练习三

2024-07-07 16:59| 来源: 网络整理| 查看: 265

1.查询Score表中成绩在60到80之间的所有记录

SELECT * from SCORE where degree>60 and degree= all( select degree from score)

6.查询‘3-105’号课程的平均分。

select avg(degree) from SCORE where cno='3-105';

7.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select cno,avg(degree) from SCORE where cno like '3%' GROUP BY cno having count(sno)>=5;

8.查询最低分大于70，最高分小于90的Sno列。

select sno from SCORE GROUP BY sno having min(degree)>70 and max(degree)( select degree from score where sno = 109 and cno = '3-105');

15.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

-- 小于无论什么课程的最大值（最大值只有一个） select * from SCORE where sno in ( select sno from SCORE where degree != (select max(degree) from score) GROUP BY sno HAVING count(cno) >1)

–针对每个学生最大分数

select * from score a -- 将sno分组内连接求每组不等于最大值的，针对于学生 join (select sno,max(degree) max_degree from score GROUP BY sno) b on a.sno=b.sno and a.DEGREE!=b.max_degree;

– 针对于每科课程最大分数

SELECT * from score a join (select cno,max(degree) as max_degree from score GROUP BY cno) b on a.cno=b.cno and a.degree != b.max_degree;

16.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sno,sname,sbirthday from STUDENT where YEAR(sbirthday)=( select YEAR(sbirthday) from STUDENT where sno=108) and sno!=108;

17.查询“张旭“教师任课的学生成绩。

select * from SCORE where cno in ( select cno from course where tno in (select tno from teacher where tname='张旭'));

18.查询选修某课程的同学人数多于5人的教师姓名。

select tname from teacher where tno in(select tno from course where cno in ( select cno from score GROUP BY cno HAVING count(sno)>5));

19.查询95033班和95031班全体学生的记录。

select * from STUDENT where class in ('95033','95031');

20.查询存在有85分以上成绩的课程Cno。

select cno from SCORE where cno in (select cno from SCORE where degree>85) GROUP BY cno;

21.查询出“计算机系“教师所教课程的成绩表。

select * from SCORE where cno in( select cno from COURSE where tno in ( select tno from teacher where depart='计算机系'));

22.查询所有教师和同学的name、sex和birthday。

select tname name,tsex sex,tbirthday birthday from TEACHER UNION //集合函数，将教师和学生拼接起来 select sname name,ssex sex,sbirthday birthday from student

23.查询所有“女”教师和“女”同学的name、sex和birthday。

--只要11对应，无论什么属性 select tname name,tsex sex,tbirthday birthday from teacher where tsex = '女' UNION select sname name,ssex sex,sbirthday birthday from student where ssex = '女';

24.查询成绩比该课程平均成绩低的同学的成绩表。

SELECT * from SCORE where degree1;

27.查询Student表中不姓“王”的同学记录。

select * from STUDENT where sname not like '王%';

28.查询Student表中每个学生的姓名和年龄。

select sname,YEAR(NOW())-YEAR(sbirthday) age from STUDENT

【本文地址】

公司简介

联系我们