基础知识------SQL练习

我在网上找到这样的一道sql基础知识的练习题，想通过这次分享来让我更加熟练的掌握sql语句的基础知识下面为题目：

表结构如下：

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL, 
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL, 
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL, 
CNAME VARCHAR(10) NOT NULL, 
TNO VARCHAR(10) NOT NULL)

CREATE TABLE SCORE 
(SNO VARCHAR(3) NOT NULL, 
CNO VARCHAR(5) NOT NULL, 
DEGREE NUMERIC(10, 1) NOT NULL) 

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 STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' 
,'男' ,1977-09-01,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' 
,'男' ,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-20,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' 
,'女' ,1975-02-10,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' 
,'男' ,1974-06-03,95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 

VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

1、查询Student表中的所有记录的Sname、Ssex和Class列。

select * from student

2、查询教师所有的单位即不重复的Depart列。

select distinct depart from teacher  

ps：这道题主要考察的是对distinct关键字的使用，能够排除重复项。

3、查询Student表的所有记录。

select * from student

4、查询Score表中成绩在60到80之间的所有记录。

select * from score where degree between 60 and 80  

ps：这道题主要考察的是between关键字用法的使用，在a与b之间格式为 between  a and b 。 

5、查询Score表中成绩不等于88的所以记录

select * from score where degree <> 88

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

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

ps：考察in关键字的使用，其格式为 in ( a, ..., c )

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

select * from student where class='95031' or ssex='女'

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

select * from student order by class desc 

ps：考察order by 的排序， 默认为升序排列 asc  ，降序为desc

9、以Cno升序、Degree降序查询Score表的所有记录。

select * from score order by cno,degree desc 

结果如下图：

ps:考察多个字段同时升降排序，格式为 order by a (asc/desc),..., a (asc/desc) ,还有一点需要注意的是在我测试的时候，字段的排序是从左到右的，先找到第一个对应的排序字段，排序之后再找表的下一个，如果下一个在已排序字段的左侧则排序无效，如果我写成了 select * from score order by degree desc ，cno 则会得到 degree 排序，而cno不排序的结果

结果如下：

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

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

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

a.select sno,cno from score where degree= (select max(degree) from score)

b.select sno,cno from score where degree >= all(select degree from score)

ps:这里考察的是子查询，连接的子查询语句直接连接则只能有一条数据否则报错，多条数据需要与any或者all连接使用。

12、查询‘3-105’号课程的平均分。

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

select avg(degree) from score group by cno having cno='3-105'

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

select cno, avg(degree) from score where cno like '3%' group by cno having count(sno)>=5

ps：这里考察的是group by和聚合函数的使用，以及聚合函数之后的筛选条件是用having 而不是where。

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

select sno from score  group by sno having min(degree)>70 and max(degree)<90

15、查询所有学生的Sname、Cno和Degree列。

select a.sname, a.cno, b.degree from student a left join  score b on a.sno=b.sno

16、查询所有学生的Sno、Cname和Degree列。

select a.sno, b.cname, c.degree from ((student a  join  score c on a.sno=c.sno)  join course b on c.cno=b.cno)

select a.sno, b.cname, c.degree from student a  join ( score c,  course b) on a.sno=c.sno and  c.cno=b.cno

17、查询所有学生的Sname、Cname和Degree列。

写法1：

select a.sname, c.cname, b.degree from (student as a  join score b on a.sno=b.sno)

join course as c on c.cno=b.cno

写法2：

SELECT A.SNAME,B.CNAME,C.DEGREE FROM STUDENT A JOIN (COURSE B,SCORE C)

ON A.SNO=C.SNO AND B.CNO =C.CNO;

18、查询“95033”班所选课程的平均分。

SELECT AVG(A.DEGREE) FROM SCORE A JOIN STUDENT B ON A.SNO = B.SNO WHERE B.CLASS='95033';

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

写法1：

select * from score where cno='3-105' and degree>(select max(degree) from score where sno='109')

写法2：

select * from score where cno='3-105' and degree>all(select degree from score where sno='109')

写法3（自连接）：

SELECT A.* FROM SCORE A JOIN SCORE B WHERE A.CNO='3-105' AND A.DEGREE>B.DEGREE AND 
B.SNO='109' AND B.CNO='3-105'

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

select * from score  group by SNO having count(cno)>'1' and degree<MAX(degree)

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

select * from score where degree> all(select degree where sno='109' and cno='3-105')

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

select sno, sname, sbirthday from student where sbirthday = (select sbirthday from student where sno='108') 

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

select * from teacher a join  course c on a.tno=c.tno join score s where s.cno=c.cno where a.tname = '张旭'

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

select t.tname from teacher t join course c on t.tno=c.tno join score s on s.cno=c.cno group by c.cno having COUNT(sno)>5

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

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

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

select s.* from score s join (course c ,teacher t) on c.cno=s.cno and t.tno=c.tno where t.depart='计算机系'

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

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

select cno, sno, degree from score where CNO='3-105' and degree > any(select degree from score where cno='3-245') order by degree desc

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

select cno, sno, degree from score where CNO='3-105' and degree > all(select degree from score where cno='3-245') 

ps:30与29考察的是any和all的区别，any至少大于子查询里的其中一个，all则是比子查询里的都大。

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

select sname name, ssex sex, sbirthday birthday from student
union 
select tname , tsex , tbirthday  from teacher

ps：这里考察的是union的使用，当两张表没有具体的字段关系的时候就需要使用union，默认的union获取的是不重复的字段，如果要想获取所有的可以使用union all。另外对于查询列的名称，别名以第一条查询语句结果为主，两条语句查询的结果字段需一一对应，否则会出现数据错乱

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

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

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

SELECT A.* FROM SCORE A WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO);

33、查询所有任课教师的Tname和Depart.

写法1：

select tname, depart from teacher where tno in (select distinct tno from course)

写法2：

select tname,depart from teacher a where exists (select * from course b where a.tno=b.tno);

写法3：
select t.tname, t.depart from teacher t  join course c on t.tno=c.tno

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

select t.tname, t.depart from teacher t  left join course c on t.tno=c.tno where  isnull(c.cno)

ps：与34题语句类似，假如not 其中not in 效果最差，另外两个效率差不多

35、查询至少有2名男生的班号。

select class from student where ssex='男' group by class having count(ssex)>=2

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

select * from student where sname not like '王%'

ps：考察like 的用法 通配符的使用。

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

select sname ,(year(now())-year(sbirthday)) age from student

ps：考察year()，now()等时间方法的使用，year()获取年份，now()获取当前系统的日期和时间

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

select sname,sbirthday from student where sbirthday =(select min(sbirthday) 
from student) or sbirthday =(select max(sbirthday) from student);

39、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select class, year(now())-year(sbirthday) as age from student order by class desc, age desc

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

select t.*, c.cname from teacher t join course c USING(tno) where tsex='男'

41、查询最高分同学的Sno、Cno和Degree列。

写法1：

select sno, cno, degree from score where degree>=all(select degree from score)

写法2：

select sno, cno, degree from score where degree=(select max(degree) from score)

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

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

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

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

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

select a.* from score  a join (student b, course c) using(sno,cno)  where b.ssex='男' and c.cname='计算机导论'

Mysql 中联接SQL语句中，ON子句的语法格式为：table1.column_name = table2.column_name。
当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)