全网SQL题库大全-带答案

create database lxt;

use lxt;

select database();

#建学生信息表student

create table student(sno varchar(20) ,sname varchar(20) ,ssex varchar(20) ,sbirthday date,class varchar(20));

#建立教师表

create table teacher(tno varchar(20) ,tname varchar(20) ,tsex varchar(20),tbirthday date,prof varchar(20),

depart varchar(20));

#建立课程表course

create table course(cno varchar(20) ,cname varchar(20) ,tno varchar(20) );

#建立成绩表score

create table score(sno varchar(20) ,cno varchar(20),sco int(20));

#添加学生信息

insert into student values('108','曾华','男','1977-09-01','95033');insert into student values('105','匡明','男','1975-10-02','95031');insert into student values('107','王丽','女','1976-01-23','95033');insert into student values('101','李军','男','1976-02-20','95033');insert into student values('109','王芳','女','1975-02-10','95031');insert into student values('103','陆君','男','1974-06-03','95031');

#添加教师表

insert into teacher values('804','李诚','男','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','助教','电子工程系');

insert into teacher values('804','郭凯莉','男','1958-12-03','副教授','计算机系');

#添加课程表

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','高等数学','831');

#添加成绩表

insert into score values('103','3-245','86');insert into score values('105','3-245','75');insert into score values('109','3-245','68');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('103','9-888','64');insert into score values('105','9-888','91');insert into score values('109','9-888','78');insert into score values('103','6-166','85');insert into score values('105','6-166','79');insert into score values('109','6-166','81');

 

-- 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 sco between 60 and 80;

-- 5、查询Score表中成绩为85，86或88的记录

select * from score where sco in (85,86,88);

-- 6、查询Student表中“95031”班性别为“女”的同学记录。

select * from student where class =95031 and ssex="女";

-- 7、以Class降序查询Student表的所有记录。

select * from student order by class desc;

-- 8 以Cno升序、sco降序查询Score表的所有记录。

select * from score order by cno asc ,sco desc;

-- 9、查询“95031”班的学生人数。

select count(*) from student where class =95031;

-- 10、查询Score表中的最高分的学生学号和课程号。

select * from score where sco =(select max(sco) from score);

-- 11、查询每门课的平均成绩。

select cno,avg(sco)from score group by cno;

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

select cno,count(*),avg(sco) from score group by cno having count(*)>=3 and cno like "3%";

-- 13、查询分数大于70，小于90的Sno列。

select * from score where sco>70 and sco<90;

-- 14、查询所有学生的Sname、Cno和sco列。

select student.sname,score.cno,score.sco from student,score where student.sno=score.sno;

-- 15、查询所有学生的Sno、Cname和sco列。

select score.sno,score.sco,course.cname from score,course where score.cno=course.cno;

-- 16、查询所有学生的Sname、Cname和sco列。

select student.sname,score.sco,course.cname from student,score,course where student.sno=score.sno and score.cno=course.cno;

-- 17、查询“95033”班学生的平均分。

select avg(sco) from student,score where student.sno=score.cno and class=95033;

-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * from score where cno="3-105" and cno>(select sno from score where sno =109 and cno ="3-105");

-- 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

/*理解1：选学多门分数小于所有学生中最高分：课程的同学中，

1.select * from score where sno in（2）and degree<（3）

2.select sno from score group by sno having count(*)>1

3.select max(degree) from score where sno in（4）

4.select sno from score group by sno having count(*)>1*/

 

select * from score where sno in(select sno from score group by sno having count(*)>1) and sco<(select max(sco) from score where sno in(select sno from score group by sno having count(*)>1));

-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from score where cno="3-105" and cno>(select sno 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');

 

-- 23、查询“张旭“教师任课的学生成绩。

select sco from score join course on score.cno=course.cno join teacher on course.tno=teacher.tno where tname ="张旭";

-- 24、查询选修某课程的同学人数大于等于3人的教师姓名。

select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>3));

-- 25、查询95033班和95031班全体学生的记录。

select * from student where class=95033 or class=95031;

-- 26、 查询存在有85分以上成绩的课程Cno.

select cno from score group by cno having max(sco)>85;

-- 27、查询出“计算机系“教师所教课程的成绩表

select sco from teacher,score,course where teacher.tno=course.tno and course.cno=score.cno and teacher.depart='计算机系';

-- 28、查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。

select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');

-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。（any的用法）

select * from score where cno='3-105' and sco>(select min(sco)from score where cno='3-245')order by sco desc;

-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.（all的用法）

select * from score where cno='3-105' and sco>all(select sco from score where cno='3-245');

-- 31、 查询所有教师和同学的name、sex和birthday. (union可以直接连接另一个表)(前后字段数量要一样)

select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student;

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

select tname,tsex,tbirthday from teacher where tsex='女' union select sname,ssex,sbirthday from student where ssex='女';

-- 33、 查询成绩比该课程平均成绩低的同学的成绩表。

select * from score,(select cno,avg(sco)`平均成绩` from score group by cno)`平均成绩表`where score.cno=`平均成绩表`.cno and sco<`平均成绩`;

 

 

select * from score;

select * from student;

select * from teacher;

 

-- 34、 查询所有任课教师的Tname和Depart

select tname,depart from teacher;

-- 35 、 查询所有未讲课的教师的Tname和Depart.

select tname,depart from teacher where not exists (select * from course where teacher.tno=course.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,year(now())-year (sbirthday) from student;

-- 39、查询Student表中最大和最小的Sbirthday日期值。

select max(date (student.sbirthday)),min(date(student.sbirthday)) from student;

-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。(asc升序)

select * from student order by class desc,sbirthday desc;

select * from student order by class desc,date(sbirthday) asc;

-- 41、查询“男”教师及其所上的课程。

select teacher.tname,teacher.tsex,course.cname from teacher,course where teacher.tno=course.cno and tsex='男';

-- 42、查询最高分同学的Sno、Cno和sco列。

select sno,cno,sco from score where sco=(select max(sco) from score);

-- 43、查询和“李军”同性别的所有同学的Sname.

select sname from student where ssex=(select ssex from student where sname='李军');

-- 44、查询和“李军”同性别并同班的同学Sname.

select sname from student where ssex=(SELECT ssex from student where sname='李军') and class=(select class from student where sname='李军');

-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

select * from score where sno in (select sno from student where ssex='男')and cno=(SELECT cno from course where cname='计算机导论');

 

 

select * from score;

select * from teacher;

select * from course;

select * from student;