MySQL数据库练习_查询题目答案

/*1.查询Student表中的所有记录的Sname、Ssex和Class列*/
select SNAME,SSEX,CLASS from student;
/*2.查询教师所有的单位即不重复的Depart列*/
select DISTINCT depart from teacher;
/*3.查询Student表的所有记录*/
select * from student;
/*4.查询Score表中成绩在60到80之间的所有记录*/
select * from score where degree between 60 and 80;
/*5.查询Score表中成绩为85，86或88的记录*/
select * from score where degree in(85,86,88);
/*6.查询Student表中“95031”班或性别为“女”的同学记录*/
select * from student where ssex = '女' or class = '95031';
/*7.以Class降序查询Student表的所有记录*/
select * from student ORDER BY class desc;
/*8.以Cno升序、Degree降序查询Score表的所有记录*/
select * from score ORDER BY cno,degree desc;
/*9.查询“95031”班的学生人数*/
select COUNT(DISTINCT sno) from student where class = 95031;
/*10.查询Score表中的最高分的学生学号和课程号*/
select sno,cno,DEGREE from score where DEGREE = (select max(degree) from score);
/*11.查询‘3-105’号课程的平均分*/
select avg(degree) as '平均分' from score where cno = '3-105';
/*12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数*/
select CNO,avg(DEGREE) from score
where cno like '3%' group by cno having count(*) >=5;
/*13.查询最低分大于70，最高分小于90的Sno列*/
select sno,cno,degree from score
where degree in (select degree from score
group by degree having min(degree)>70 and MAX(degree) < 90);
/*14.查询所有学生的Sname、Cno和Degree列*/
select sname,cno,degree from student inner join score ON student.sno = score.sno;
/*15.查询所有学生的Sno、Cname和Degree列*/
select sno,cname,degree from score inner join course on https://www.wendangku.net/doc/cb6119283.html,o = https://www.wendangku.net/doc/cb6119283.html,o;
/*16.查询所有学生的Sname、Cname和Degree列*/
select sname,cname,degree from student
inner join score on student.sno = score.sno inner join course on https://www.wendangku.net/doc/cb6119283.html,o = https://www.wendangku.net/doc/cb6119283.html,o;
/*17.查询“95033”班所选课程的平均分*/
select class,AVG(degree) from student inner join score where student.sno = score.sno and CLASS = '95033';
/*18.假设使用如下命令建立了一个grade表*/
create table grade(low NUMERIC(3,0),upp NUMERIC(3),rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
/*现查询所有同学的Sno、Cno和rank列*/
select sno,cno,degree,rank from score,grade
where degree between low and upp;
/*19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录*/
select * from score
where degree > (select degree from score where sno = 109 and cno = '3-105')
and cno = '3-105';
/*20.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录*/
select * from score where degree not in
(s

elect MAX(degree) from score group by sno having count(*) > 1);
/*21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录*/
select * from score where degree >
(select degree from score where sno = 109 and cno = '3-105');
/*22.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列*/
select sno,sname,sbirthday from student where
year(sbirthday) = (select year(sbirthday) from student where sno = 108)
and sno != 108;
/*23.查询“张旭“教师任课的学生成绩*/
select sno,cno,degree from score where cno = (select cno from course inner join
teacher on course.tno = teacher.tno and teacher.tname = '张旭');
/*24.查询选修某课程的同学人数多于5人的教师姓名*/
select tname from teacher where
tno = (select tno from course where cno = (select cno from score group by cno having count(*) >5));
/*25.查询95033班和95031班全体学生的记录*/
select * from student where class = '95033' or class = '95031';
/*26.查询存在有85分以上成绩的课程Cno*/
select cno,degree from score where
degree in (select degree from score where degree >85);
/*27.查询出“计算机系“教师所教课程的成绩表*/
select cno,degree from score where cno in
(select cno from course where tno in
(select tno from teacher where depart = '计算机系'));
/*28.查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof*/
select tname,prof,depart from teacher where depart = '计算机系'
and prof not in (select prof from teacher where DEPART = '电子工程系');
/*29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序*/
select * from score where
degree > (select max(degree) from score where cno = '3-245')
order by degree desc;
/*30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree*/
select * from score where cno = '3-105' and
degree > (select max(degree) from score where cno = '3-245');
/*31.查询所有教师和同学的name、sex和birthday*/
select sname as name,ssex as sex,sbirthday from student union
select tname,tsex,tbirthday from teacher;
/*32.查询所有“女”教师和“女”同学的name、sex和birthday*/
select sname as name,ssex as sex,sbirthday as birthday from student where ssex = '女'
union select tname,tsex,tbirthday from teacher where tsex = '女';
/*33.查询成绩比该课程平均成绩低的同学的成绩表*/
select * from score s1 where
degree < (select avg(degree) from score s2 where https://www.wendangku.net/doc/cb6119283.html,o = https://www.wendangku.net/doc/cb6119283.html,o);
/*34.查询所有任课教师的Tname和Depart*/
select tname,depart from teacher inner join course on teacher.tno = course.tno;
/*35.查询所有未讲课的教师的Tname和Depart*/
select tname,depart from teacher t where
not exists (select * from course c where t.tno = c.tno);
/*36.查询至少有2

名男生的班号*/
select class from student where ssex = '男' group by class having count(*) >= 2;
/*37.查询Student表中不姓“王”的同学记录*/
select * from student where sname not like '王%';
/*38.查询Student表中每个学生的姓名和年龄*/
select sname as '姓名',floor((to_days(now()) - to_days(sbirthday))/365) as '年龄' from student;
/*39.查询Student表中最大和最小的Sbirthday日期值*/
select max(YEAR(sbirthday)) as '最大日期',MIN(year(sbirthday)) as '最小日期' from student;
/*40.以班号和年龄从大到小的顺序查询Student表中的全部记录*/
select * from student order by class desc,sbirthday desc;
/*41.查询“男”教师及其所上的课程*/
select tname,cname from teacher inner join
course on teacher.tno = course.tno where tsex = '男';
/*42.查询最高分同学的Sno、Cno和Degree列*/
select * from score where degree = (select MAX(degree) from score);
/*43.查询和“李军”同性别的所有同学的Sname*/
select sname from student where
ssex = (select ssex from student where sname = '李军') and sname != '李军';
/*44.查询和“李军”同性别并同班的同学Sname*/
select sname from student where
ssex = (select ssex from student where sname = '李军')
and class = (select class from student where sname = '李军') and sname != '李军';
/*45.查询所有选修“计算机导论”课程的“男”同学的成绩表*/
select sname,ssex,cname,degree from course
inner join score on https://www.wendangku.net/doc/cb6119283.html,o = https://www.wendangku.net/doc/cb6119283.html,o
inner join student on score.sno = student.sno
where cname = '计算机导论' and ssex = '男';