数据的常用练习语句及答案

数据库语句练习及答案

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

​ select sname,ssex,class from students；

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

​ select DISTINCT depart from teachers；

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

​ SELECT * FROM Students;

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

​ select * from scores WHERE degree BETWEEN 60 and 80;

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

​ SELECT * from scores WHERE degree in(85,86,88);

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

​ select * from students WHERE class = ‘95031’ or ssex = ‘女’;

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

select * from students ORDER BY class DESC;

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

​ SELECT * from scores ORDER BY cno, degree DESC;

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

​ select count(*) from students WHERE class = ‘95031’;

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

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

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

​ select avg(degree) from scores WHERE cno = ‘3-105’;

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

​ select avg(degree) from scores WHERE cno LIKE ‘3%’ GROUP BY cno HAVING count(cno) >5;

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

​ SELECT Sno FROM Scores GROUP BY Sno HAVING MAX(Degree)<90 AND MIN(Degree)>70;

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

​ SELECT Sname,Cno,Degree FROM Students INNER JOIN Scores ON(Students.Sno=Scores.Sno) ORDER BY Sname;

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

​ SELECT Sno,Cname,Degree FROM Scores INNER JOIN Courses ON(Scores.Cno=Courses.Cno) ORDER BY Sno;

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

SELECT Sname,Cname,Degree FROM Students INNER JOIN Scores ON(Students.Sno=Scores.Sno) INNER JOIN Courses ON(Scores.Cno=Courses.Cno) ORDER BY Sname;

17.查询“95033”班所选课程的平均分。

​ 查询95033班的所选课程

​ select sno from students WHERE class = ‘95033’；

计算课程所选课程的平均分

SELECT cno,avg(degree) from scores WHERE sno in (select sno from students WHERE class = ‘95033’) GROUP BY cno；

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

​ select * FROM scores；

​ SELECT * from grade；

select s.sno,s.cno,g.rank from scores s , grade g WHERE s.degree BETWEEN g.low and g.upp ORDER BY rank；

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

​ 查询109号同学最高成绩

​ SELECT max（degree） from scores WHERE sno =‘109’；

筛选3-105高于109号同学的成绩

select * from scores WHERE cno =‘3-105’ and degree > (SELECT max(degree) from scores WHERE sno =‘109’)；

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

​ 查询一门课程以上的同学

​ SELECT sno FROM scores GROUP BY sno HAVING count(sno)>1；

​ 查询非一门课程以上成绩最高的分数

​ select sno, max(degree) from scores WHERE sno in ( SELECT sno FROM scores GROUP BY sno HAVING count(sno)>1)GROUP BY sno；

查询非一门课程分数为非最高分记录

select * from scores WHERE sno in (SELECT sno FROM scores GROUP BY sno HAVING count(sno)>1) and degree not in (
select max(degree) from scores WHERE sno in ( SELECT sno FROM scores GROUP BY sno HAVING count(sno)>1) GROUP BY sno)；

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

​ 查询学号109最高成绩

​ SELECT max(degree) from scores WHERE sno =‘109’；

查询课程3-105成绩大于109学号成绩的记录

select * from scores WHERE cno =‘3-105’ and degree > (SELECT max(degree) from scores WHERE sno =‘109’)；

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

​ 查询学号108同学出生的年份

​ select YEAR(sbirthday) from students WHERE sno =‘108’

查询和108同年出生的学生

SELECT sno,sname,sbirthday from students WHERE year(sbirthday) in (select YEAR(sbirthday) from students WHERE sno =‘108’)

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

SELECT scores.degree from teachers INNER JOIN courses on (teachers.tno=courses.tno) INNER JOIN scores on (courses.cno=scores.cno) WHERE teachers.tname=‘张旭’;

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

SELECT DISTINCT Tname FROM Scores INNER JOIN Courses ON(Scores.Cno=Courses.Cno) INNER JOIN Teachers ON(Courses.Tno=Teachers.Tno) WHERE Courses.Cno IN(SELECT Cno FROM Scores GROUP BY(Cno) HAVING COUNT(Sno)>5);

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

SELECT * FROM Students WHERE Class IN (‘95033’,‘95031’) ORDER BY Class;

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

SELECT DISTINCT Cno FROM Scores WHERE Degree>85;

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

SELECT Tname,Cname,SName,Degree FROM Teachers INNER JOIN Courses ON(Teachers.Tno=Courses.Tno) INNER JOIN Scores ON(Courses.Cno=Scores.Cno) INNER JOIN Students ON(Scores.Sno=Students.Sno) WHERE Teachers.Depart=‘计算机系’ ORDER BY Tname,Cname,Degree DESC;

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

​ 查询计算机与电子工程系教师对应职位：

 SELECT prof,count(DISTINCT depart) add from teachers WHERE depart in ('计算机系','电子工程系') GROUP BY prof HAVING add=1;

去重

select Tname,prof from teachers WHERE prof in (SELECT prof from teachers WHERE depart in (‘计算机系’,‘电子工程系’) GROUP BY prof HAVING count(DISTINCT depart)=1)

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

​ 查询课程3-105：

​ select * from scores where cno = ‘3-105’

​ 查询可能3-245显示成绩：

​ select degree from scores WHERE cno = ‘3-245’

筛选3-105成绩任意大于3-245并排序：

select cno,sno,degree from scores where cno = ‘3-105’ and degree > any (select degree from scores WHERE cno = ‘3-245’) ORDER BY degree desc;

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

​ 查询课程3-105：

​ select * from scores where cno = ‘3-105’

​ 查询可能3-245显示成绩：

​ select degree from scores WHERE cno = ‘3-245’

筛选3-105成绩所有大于3-245并排序：

select cno,sno,degree from scores where cno = ‘3-105’ and degree > all (select degree from scores WHERE cno = ‘3-245’) ORDER BY degree desc;

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

​ 查询教师tname，tsex，tbirthday

​ select tname,tsex,tbirthday from teachers

​ 查询学生sname，ssex，sbirthday

​ select sname,ssex,sbirthday from students

利用关键字UNION进行整合

select tname,tsex,tbirthday from teachers UNION select sname,ssex,sbirthday from students

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

​ 查询女教师的tname,tsex,tbirthday

​ select tname,tsex,tbirthday from teachers WHERE tsex=‘女’

​ 查询女学生sname，ssex，sbirthday

利用关键字UNION进行整合***

select tname,tsex,tbirthday from teachers WHERE tsex=‘女’ UNION
select sname,ssex,sbirthday from students WHERE ssex='女’

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

​ 查询每个课程的平均成绩

​ SELECT cno,avg(degree) from scores GROUP BY cno

筛选比较成绩比平均成绩低的同学

select * from scores WHERE cno in (SELECT cno from scores GROUP BY cno)
and degree < ALL (SELECT avg(degree) from scores GROUP BY cno)

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

​

select tname,depart from teachers WHERE tno in (select DISTINCT tno from courses）

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

SELECT Tname,Depart FROM Teachers WHERE Tno NOT IN( SELECT Tno FROM Courses )

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

select class from students WHERE ssex = ‘男’ GROUP BY class HAVING count(ssex)>=2

37.查询Student表中不姓“王”的同学记录

select * from students WHERE sname not like '王%'

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

SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) AS Age FROM Students;

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

select max(Year(sbirthday)),min(Year(sbirthday)) from students

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

select * from students ORDER BY sbirthday DESC,Class DESC

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

​ select cname,tno from courses

​ SELECT tname,tno from teachers

select teachers.tname,courses.cname from teachers INNER JOIN courses on (teachers.tno=courses.tno) WHERE tsex = '男’

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

select * from scores ORDER BY degree DESC LIMIT 1

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

​ 查询李军的性别

​ SELECT ssex FROM students WHERE sname = ‘李军’

查询于李军同性别的同学

SELECT sname from students WHERE ssex in (SELECT ssex FROM students WHERE sname = ‘李军’)

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

​ 查询所有男同学的选修课

​ select * from scores WHERE sno in (select sno from students WHERE ssex = ‘男’ )

​ 查询所有选修计算机导论课的同学

​ SELECT * from scores WHERE cno in (select cno from courses WHERE cname=‘计算机导论’)

结果进行合并

SELECT * FROM Scores WHERE Sno IN (SELECT Sno FROM Students WHERE Ssex=‘男’) AND Cno IN ( SELECT Cno FROM Courses WHERE Cname=‘计算机导论’);