SQL解题

学习有一段时间了，再次练习题目；

#创建表 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 ;