经典SQL练习题

 

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

1. SELECT Sname,Ssex,Class

2. FROM student;

 

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

SELECT DISTINCT DEPART FROM TEACHER; 

 

3、查询Student表的所有记录

SELECT * FROM STUDENT;

 

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

1. SELECT * FROM SCORE

2. WHERE DEGREE BETWEEN 60 AND 80;

 

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

1. SELECT * FROM SCORE

2. WHERE DEGREE IN (85,86,88);

 

6、查询Student表中'95031'班或性别为'女'的同学记录。

1. SELECT * FROM STUDENT

2. WHERE CLASS='95031' OR SSEX='女';

 

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

1. SELECT * FROM STUDENT

2. ORDER BY CLASS DESC;

 

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

1. SELECT * FROM SCORE

2. ORDER BY CNO,DEGREE DESC;

 

9、查询‘95031’班的学生人数。

1. SELECT COUNT(*) FROM STUDENT

2. WHERE CLASS='95031';

 

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

1. SELECT SNO,CNO FROM SCORE

2. ORDER BY DEGREE DESC

3. LIMIT 1;

 

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

1. SELECT AVG(DEGREE) FROM SCORE

2. WHERE CNO='3-105';

 

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

1. SELECT A.avg

2. FROM (SELECT CNO,AVG(DEGREE) as avg,COUNT(*) FROM SCORE

3. WHERE CNO LIKE '3%'

4. GROUP BY CNO

5. HAVING COUNT(*) > 5) AS A;

 

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

1. SELECT SNO FROM SCORE

2. GROUP BY SNO

3. HAVING MAX(DEGREE)<90 AND MIN(DEGREE)>70;

 

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

1. SELECT A.SNAME,B.CNO,B.DEGREE

2. FROM STUDENT A INNER JOIN SCORE B

3. ON A.SNO=B.SNO;

 

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

 

1. SELECT B.SNO,A.CNAME,B.DEGREE

2. FROM COURSE A INNER JOIN SCORE B

3. ON A.CNO=B.CNO;

 

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

1. SELECT A.SNAME,B.CNAME,C.DEGREE

2. FROM STUDENT A INNER JOIN COURSE B INNER JOIN SCORE C

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

 

17、查询‘95033’班所选课程的平均分。

1. SELECT AVG(DEGREE)

2. FROM STUDENT A INNER JOIN SCORE B

3. ON A.SNO=B.SNO

4. WHERE CLASS='95033';

 

18、假设使用如下命令建立一个grade表。

查询所有同学的Sno、Cno和rank列。

1. SELECT SNO,CNO,RANK

2. FROM SCORE A,GRADE B

3. WHERE DEGREE BETWEEN B.LOW AND B.UPP;

 

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

1. SELECT A.*

2. FROM SCORE A JOIN SCORE B

3. WHERE A.CNO='3-105' AND A.DEGREE>B.DEGREE AND B.SNO=109 AND B.CNO='3-105' ;

1. SELECT A.*,B.DEGREE,CNO

2. FROM STUDENT A INNER JOIN SCORE B

3. ON A.SNO=B.SNO

4. WHERE CNO='3-105'

5. AND DEGREE > (SELECT B.DEGREE

6. FROM STUDENT A INNER JOIN SCORE B

7. ON A.SNO=B.SNO

8. WHERE A.SNO=109 AND B.CNO='3-105');

 

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

 

1. SELECT * FROM SCORE

2. WHERE DEGREE < (SELECT MAX(DEGREE) FROM SCORE)

3. GROUP BY SNO

4. HAVING COUNT(SNO)>1

5. ORDER BY DEGREE ;

 

 

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

1. SELECT * FROM SCORE

2. WHERE DEGREE > (SELECT DEGREE FROM SCORE WHERE SNO=109 AND CNO='3-105');

自联接方法得到相同的结果。

1. SELECT A.*

2. FROM SCORE A JOIN SCORE B

3. WHERE A.DEGREE > B.DEGREE AND B.SNO=109 AND B.CNO='3-105';

 

 

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

1. SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT

2. WHERE YEAR(SBIRTHDAY) =

3. (SELECT YEAR(SBIRTHDAY) FROM STUDENT

4. WHERE SNO='105');

 

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

1. SELECT C.CNAME,T.TNAME,S.DEGREE

2. FROM COURSE C INNER JOIN TEACHER T INNER JOIN SCORE S

3. ON C.TNO=T.TNO AND C.CNO=S.CNO

4. WHERE T.TNAME='张旭';

 

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

1. SELECT T.TNAME

2. FROM COURSE C INNER JOIN TEACHER T INNER JOIN SCORE S

3. ON C.TNO=T.TNO AND C.CNO=S.CNO

4. GROUP BY S.CNO

5. HAVING COUNT(*) > 5;

 

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

1. SELECT * FROM STUDENT

2. WHERE CLASS IN (95033,95031);

 

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

1. SELECT DISTINCT C.CNAME,C.CNO

2. FROM COURSE C INNER JOIN SCORE S

3. ON C.CNO=S.CNO

4. WHERE S.DEGREE > 85;

 

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

1. SELECT T.DEPART,TNAME,C.CNAME,S.DEGREE

2. FROM COURSE C INNER JOIN SCORE S INNER JOIN TEACHER T

3. ON C.CNO=S.CNO AND C.TNO=T.TNO

4. WHERE T.DEPART='计算机系';

 

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

1. SELECT DEPART,TNAME,PROF FROM TEACHER

2. WHERE DEPART='计算机系'

3. AND PROF NOT IN

4. (SELECT PROF FROM TEACHER WHERE DEPART='电子工程系');

 

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

1. SELECT * FROM SCORE

2. WHERE CNO='3-105' AND DEGREE >

3. ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245')

4. ORDER BY DEGREE DESC;

 

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

 

1. SELECT CNO,SNO,DEGREE FROM SCORE

2. WHERE CNO='3-105' AND DEGREE > (SELECT MAX(DEGREE) FROM SCORE WHERE CNO='3-245')

3. ORDER BY DEGREE DESC;

 

31、查询所有教师和同学的name、sex、和birthday。

1. SELECT SNAME name,SSEX sex,SBIRTHDAY birthday

2. FROM STUDENT

3. UNION

4. SELECT TNAME,TSEX,TBIRTHDAY

5. FROM TEACHER;

 

32、查询所有女教师和女同学的name、sex和birthday。

1. SELECT SNAME name,SSEX sex,SBIRTHDAY birthday

2. FROM STUDENT

3. WHERE SSEX='女'

4. UNION

5. SELECT TNAME,TSEX,TBIRTHDAY

6. FROM TEACHER

7. WHERE TSEX='女';

 

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

1. SELECT A.SNO,DEGREE,B.AVG

2. FROM SCORE A INNER JOIN (SELECT CNO,AVG(DEGREE) AVG FROM SCORE

3. GROUP BY CNO) B

4. ON A.CNO=B.CNO

5. WHERE A.DEGREE < B.AVG;

 

34、查询所有任课老师的Tname和Depart。

1. SELECT TNAME,DEPART

2. FROM TEACHER T INNER JOIN COURSE C

3. WHERE T.TNO=C.TNO;

 

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

 

SELECT TNAME,DEPART

FROM TEACHER

WHERE TNO NOT IN (SELECT TNO FROM COURSE);

 

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

1. SELECT SSEX,CLASS,COUNT(*) FROM STUDENT

2. GROUP BY CLASS,SSEX

3. HAVING COUNT(SSEX='男') > 2;

 

37、查询Student表中不姓王的同学记录。

1. SELECT * FROM STUDENT

2. WHERE SNAME NOT LIKE '王%';

 

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

1. select sname,timestampdiff(year,sbirthday,curdate()) age

2. from student;

 

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

 

1. select sname,sbirthday as THEMAX from student

2. where sbirthday =(select min(SBIRTHDAY) from student)

3. union

4. select sname,sbirthday as THEMIN from student

5. where sbirthday =(select max(SBIRTHDAY) from student);

SELECT MAX(SBIRTHDAY),MIN(SBIRTHDAY) FROM STUDENT;

 

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

1. SELECT CLASS,TIMESTAMPDIFF(YEAR,SBIRTHDAY,CURDATE()) AS YEAR FROM STUDENT

2. ORDER BY CLASS DESC,SBIRTHDAY;

 

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

1. SELECT T.*,C.CNAME

2. FROM TEACHER T INNER JOIN COURSE C

3. ON T.TNO = C.TNO

4. WHERE TSEX='男';

 

42、查询最高分同学的SNO、CNO和DEGREE列。

1. SELECT SNO,CNO,DEGREE

2. FROM SCORE

3. ORDER BY DEGREE DESC

4. LIMIT 1;

 

43、查询和李军同性别的所有同学的Sname。

1. SELECT SNAME FROM STUDENT

2. WHERE SSEX= (SELECT SSEX FROM STUDENT WHERE SNAME='李军');

 

44、查询和李军同性别且同班同学的SNAME。

1. SELECT SNAME FROM STUDENT

2. WHERE SSEX = (SELECT SSEX FROM STUDENT WHERE SNAME='李军')

3. AND CLASS = (SELECT CLASS FROM STUDENT WHERE SNAME='李军');

 

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

1. SELECT C.*

2. FROM STUDENT A INNER JOIN COURSE B INNER JOIN SCORE C

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

4. WHERE A.SSEX='男' AND B.CNAME='计算机导论';