学习有一段时间了，再次练习题目；
#创建表 student
create table student
(sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5));
insert into student (sno,sname,ssex,sbirthday,class)
values(108,"曾华","男","1997-09-01",95033);
insert into student (sno,sname,ssex,sbirthday,class)
values(104,"匡明","男","1975-10-02",95031);
insert into student (sno,sname,ssex,sbirthday,class)
values(107,"王丽","女","1976-01-23",95033);
insert into student (sno,sname,ssex,sbirthday,class)
values(101,"李军","男","1976-02-10",95033);
insert into student (sno,sname,ssex,sbirthday,class)
values(109,"王芳","女","1975-02-01",95031);
insert into student (sno,sname,ssex,sbirthday,class)
values(103,"陆君","男","1974-06-03",95031);
select * from student;
#创建表 course
create table course
(cno VARCHAR(5) not NULL,
cname VARCHAR(10) not NULL,
tno VARCHAR(10) NOT NULL);
INSERT INTO course
values("3-105","计算机导论",825);
INSERT INTO course
VALUES("3-245","操作系统",804);
INSERT INTO course
VALUES("6-166","数据电路",856);
INSERT INTO course
VALUES("9-888","高等数学",100);
#创建表 score
drop table score;
CREATE TABLE score
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree numeric(10,1) NOT NULL);
INSERT INTO score
VALUES(103,"3-245",86);
INSERT INTO score
VALUES(105,"3-245",75);
INSERT INTO score
VALUES(109,"3-245",82);
INSERT INTO score
VALUES(103,"3-105",92);
INSERT INTO score
VALUES(105,"3-105",88);
INSERT INTO score
VALUES(109,"3-105",76);
INSERT INTO score
VALUES(101,"3-105",64);
INSERT INTO score
VALUES(107,"3-105",91);
INSERT INTO score
VALUES(108,"3-105",78);
INSERT INTO score
VALUES(101,"6-166",85);
INSERT INTO score
VALUES(107,"6-106",79);
INSERT INTO score
VALUES(108,"6-166",81);
select * from score;
#建表teacher
CREATE TABLE teacher
(tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL,
tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL,
prof VARCHAR(6),
depart VARCHAR(10) NOT NULL);
INSERT INTO teacher
VALUES(856,"李诚","男","1958-12-02","副教授","计算机系");
INSERT INTO teacher
VALUES(856,"张旭","男","1969-03-12","讲师","电子工程系");
INSERT INTO teacher
VALUES(825,"王萍","女","1972-05-05","助教","计算机系");
INSERT INTO teacher
VALUES(831,"刘冰","女","1977-08-14","助教","电子工程系");
select * from teacher;
#题目
#1 查询student中的所有记录的sname,ssex,class的列
select sname,ssex,class from student;
#2查询教师所有的单位即不重复的列；
select distinct * from teacher;
#3 查询student 表的所有记录
select * from student;
#4 查询score中成绩在60到80之间的所有记录
select * from score where degree between 60 and 80;
#5 查询score中成绩为85,86，或80之间的记录
select * from score where degree in (85,86,80);
#6 查询student 表中“95031”班或性别为女的所有记录；
select * from student where class = "95031" or ssex ="女";
#7 以class降序查询studnt表中的所有记录；
select * from student order by class desc;
#8 以cno升序、degree降序查询score的所有记录；
select * from score order by cno asc, degree desc;
#9 查询"95031"班的学生人数；
select count(*) from student group by class having class = "95031";
#10 查询score表中的最高分的学生学号和课程号；
select sno,cno from score order by degree desc limit 1;
#11 查询“3-105”课程的平均分；
select avg(degree) from score group by cno having cno ="3-105";
#12 查询score中至少有5学生选修的并以3开头的课程的平均分数
select avg(degree) from score where cno like "3%"group by cno having count(sno)>=5 ;
#13 最低分>70，最高分<90的sno列；
select sno,degree from score where degree between 70 and 90;
#14 查询所有学生的sname,cno 和degree列；
select b.sname, a.cno,a.degree from
(select sno,cno,degree from score) as a
inner join
(select sno,sname from student) as b
on a.sno = b.sno;
#15 查询所有学生的Sno、cname和Degree列
select c.cname, a.sno,a.degree from
(select sno,cno,degree from score) as a
inner join
(select cno,cname from course) as c
on a.cno = c.cno;
#16、查询所有学生的Sname-studet-c、Cname-course-a和Degree-score-b列
select b.sname, c.cname, a.degree from
(select cname,cno from course) as c
inner join
(select cno,sno,degree from score) as a
on c.cno = a.cno
inner join
(select sname,sno from student) as b
on b.sno=a.sno;
#17、查询“95033”班所选课程的平均分: 95033班-学生- 选的课程-得分
select avg(a.degree) from
(select sno,degree from score) as a
inner join
(select sno,class from student where class ="95033") as d
on a.sno=d.sno;
#18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
select * from score
where cno="3-105" and degree>(select degree from score where sno = "109" and cno="3-105");
#19 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select distinct a1.sno,a1.cno,a1.degree from
(select sno,cno,degree from score where degree< (select max(degree) from score)) as a1
inner join
(select sno from score group by cno having count(cno)>1) as b1
on a1.sno = b1.sno;
#20 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列
select b.sno,b.sname,b.sbirthday from student as b
where year(b.sbirthday) = (select year(c.sbirthday) from student as c where sno=108);
select * from student;
#21 查询“张旭“教师任课的学生成绩: teacher-tname,tno,course-tno,cno,score-cno,degree
#表连接法
select b.sno,b.degree from
(select tno,cno from course) as a
inner join (select tno from teacher where tname="张旭") as c
on a.tno = c.tno
inner join (select cno,sno,degree from score) as b
on a.cno= b.cno;
#子查询法
select sno,cno,degree from score c
where c.cno in (select cno from course b
where b.tno = (select tno from teacher a where tname="张旭"));
#22、查询选修某课程的同学人数多于5人的教师姓名
select tname from teacher c
where c.tno = (select tno from course b
where b.cno = (select cno from score a group by cno having count(cno)>5));
#23、查询95033班和95031班全体学生的记录
select * from student where class="95033" or class="95031";
#24、查询存在有85分以上成绩的课程Cno.
select cno from score where degree> 85;
#25、查询出“计算机系“教师所教课程的成绩表。
select cno,degree from score b
where b.cno in (select cno from course a where a.tno in (select tno from teacher where depart="计算机系"));
#26、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof
select tname, prof from teacher where depart="计算机系" or depart="电子工程系";
#27、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序
select a.cno,a.sno,a.degree,b.cno,b.degree from
(select sno,degree,cno from score where cno="3-105") as a
inner join (select sno,degree,cno from score where cno="3-245") as b
on a.sno=b.sno
where a.degree > b.degree order by a.degree desc;
#28、查询所有教师和同学的name、sex和birthday.
select sname,ssex,date(sbirthday) from student
union all
select tname,tsex,date(tbirthday) from teacher;
#29、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname,ssex,date(sbirthday) from student where ssex="女"
union all
select tname,tsex,date(tbirthday) from teacher where tsex="女";
#30 查询成绩比该课程平均成绩低的同学的成绩表
select * from score a
where a.degree< all(select avg(degree) from score b group by cno);
#31 查询所有任课教师的Tname和Depart.
select tname,depart from teacher a where
a.tno in (select tno from course);
#32 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher a where
a.tno not in (select tno from course);
#33、查询至少有2名男生的班号。
select class from student where ssex="男" group by ssex having count(ssex)>1;
#34、查询Student表中不姓“王”的同学记录。
select * from student where sname not like "王%";
#35、查询Student表中每个学生的姓名和年龄。
select sname, datediff(curdate(),date(sbirthday)) from student;
#36、查询Student表中最大和最小的Sbirthday日期值
select date(max(sbirthday)),date(min(sbirthday)) from student;
#37、以班号和年龄从大到小的顺序查询Student表中的全部记录
select * from student order by class desc, sbirthday asc;
#38、查询“男”教师及其所上的课程
select b.tname, a.cname from
(select cname,cno,tno from course) a
inner join
(select tname,tno from teacher where tsex="男") b
on a.tno = b.tno;
#39、查询最高分同学的Sno、Cno和Degree列
select * from score a where a.degree = (select max(degree) from score);
#40、查询和“李军”同性别的所有同学的Sname
select sname from student a where a.ssex=(select ssex from student where sname="李军");
#41、查询和“李军”同性别并同班的同学Sname
select sname from student a where a.ssex=(select ssex from student where sname="李军")
and a.class=(select class from student where sname="李军");
#42、查询所有选修“计算机导论”课程的“男”同学的成绩表
select c.degree from
(select sno,sname,ssex from student where ssex="男") a
inner join
(select cno,cname from course where cname="计算机导论") b
inner join
(select sno,cno,degree from score ) c
on a.sno = c.sno and b.cno=c.cno ;
