Learn SQL | 基礎操作綜合練習

01-24

距離資料庫知識的學習有一段時間了，不常回顧的話再使用過來難免手生，最近正好在CSDN上看到一篇關於SQL練習題的博客文章，題目數量不是很多，但卻涉及到絕大多數的常用基礎操作。因此我決定在練習時順便把這些題目整理成一份完整的文章，一方面幫助自己複習鞏固；另一方面也可以為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));

創建表course，特徵包括課程編號、課程名稱和教師編號。

create table course(CNO VARCHAR(5) NOT NULL, CNAME VARCHAR(10) NOT NULL,TNO VARCHAR(10) NOT NULL);

創建表score，特徵包括學生編號、課程編號與分數。

create table score( SNO VARCHAR(3) NOT NULL, CNO VARCHAR(5) NOT NULL, DEGREE NUMERIC(10, 1) NOT NULL);

創建表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);

二、插入表數據

接下來依次向創建好的四張表插入數據，操作如下：

向student表插入6條學生信息數據

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);

向course表插入4條課程數據

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);

向score表插入12條得分數據

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);

向teacher表插入4條教師信息數據

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

5. 查詢score表中成績為85，86或88的記錄

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

6. 查詢student表中"95031"班或性別為"女"的同學記錄

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

7. 以class降序查詢student表的所有記錄

8. 以cno升序、degree降序查詢score表的所有記錄

select * from student order by class desc;select * from score order by cno asc,degree desc;

9. 查詢"95031"班的學生人數

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

10. 查詢score表中的最高分的學生學號和課程號

select sno,cno from score where degree in (select max(degree) from score);

11. 查詢"3-105"號課程的平均分

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

12. 查詢score表中至少有5名學生選修的並以3開頭的課程的平均分數

select avg(degree) from score where cno like "3%" group by cno having count(*) >= 5;

13. 查詢最低分大於70，最高分小於90的sno列

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

14. 查詢所有學生的sname、cno和degree列
15. 查詢所有學生的sno、cname和degree列
16. 查詢所有學生的sname、cname和degree列

# 14select a.sname,b.cno,b.degree from student as a join score as b on a.sno = b.sno# 15select a.cname,b.sno,b.degree from course as ajoin score as b on a.cno = b.cno# 16select a.sname,b.cname,c.degree from student as ajoin course as bjoin score as con a.sno = c.sno and b.cno = c.cno;# 第二種解法select a.sname,b.cname,c.degree from student as ajoin (course b,score c)on a.sno = c.sno and b.cno = c.cno;

17. 查詢"95033"班所選課程的平均分

select avg(degree) from score where sno in (select sno from student where class = "95033");# 進階解法select avg(a.degree) from score ajoin student b on a.sno = b.sno where b.class = "95033";

18. 假設使用如下命令建立了一個grade表，現查詢所有同學的Sno、Cno和rank列，並按照rank列排序

create table grade(low numeric(3,0),upp numeric(3,0),rank char(1));insert into grade values (90,100,"A");insert into grade values (80,89,"B");insert into grade values (70,79,"C");insert into grade values (60,69,"D");insert into grade values (0,59,"E");

解法如下：

select a.sno,a.cno,b.rank from score a join grade b where a.degree between b.low and b.upporder by rank;

19. 查詢score表中選修"3-105"課程的成績高於"109"號同學成績的所有同學的記錄

# 解法一select * from score where cno = "3-105"and degree > (select degree from score where sno = 109 and cno = "3-105");# 解法二select a.* from score a where a.cno = "3-105" and a.degree > all(select degree from score b where b.sno = "109" and b.cno = "3-105");

20. 查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄

select * from score s inner join (select ss.sno, max(ss.degree) as maxd from score ss group by ss.sno having count(ss.cno)> 1 ) a on s.sno=a.sno and s.degree <> a.maxd;

21. 查詢和學號為107的同學同年出生的所有學生的sno、sname和sbirthday列

考察日期與時間函數的運用

select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student wehre sno = 107);

22. 查詢"張旭"教師任課的學生成績

select degree from score where cno in (select cno from course where tno in (select tno from teacher where teacher = "張旭"));# 進階解法select a.degree from score a join (teacher b,course c)on a.cno = c.cno and b.tno = c.tnowhere b.tname = "張旭";

23. 查詢選修某課程的同學人數多於5人的教師姓名

select a.tname from teacher a join(course b,score c)on a.tno = b.tno and b.cno = c.cnogroup by c.cno having count(*) > 5;

24. 查詢所有表中關於"95033"班和"95031"班全體學生的信息記錄

select * from student a inner join score b on a.sno = b.sno inner join course con b.cno = c.cno inner join teacher don c.tno = d.tno where a.class = "95033" or a.class = "95031";

25. 查詢存在有85分以上成績的課程cno

# 解法一：select distinct cno from score where degree > 85;# 解法二：select cno from score group by cno having max(degree) >85;

26. 查詢出"計算機系"教師所教課程的成績表

select a.*,b.cname,c.tname,c.depart from score ajoin (course b, teacher c)on a.cno = b.cno and b.tno = c.tnowhere c.depart = "計算機系";

27. 查詢"計算機系"中與"電子工程系"沒有相同職稱的教師的tname和prof

select tname,prof from teacher where depart = "計算機系" and prof not in(select prof from teacher where depart = "電子工程系");

28. 查詢選修編號為"3-105"課程且成績高於選修編號為"3-245"的同學的cno、sno和degree,並按degree從高到低次序排序。

select * from score as a,score as bwhere a.cno = "3-105" and b.cno = "3-245"and a.sno = b.snoand a.degree > b.degreeorder by a.degree desc;

29. 查詢所有教師和同學的name、sex和birthday
30. 查詢所有女教師和女同學的name、sex和birthday

# 29select sname as name, ssex as sex, sbirthday as birthday from studentunionselect tname as name, tsex as sex, tbirthday as birthday from teacher;# 30select sname as name, ssex as sex, sbirthday as birthday from studentwhere ssex = "女"unionselect tname as name, tsex as sex, tbirthday as birthday from teacherwhere tsex = "女";

31. 查詢成績比該課程平均成績低的同學的成績表

select a.* from score a where degree < (select avg(degree) from score b where a.cno = b.cno );

32. 查詢所有任課教師的tname和depart
33. 查詢所有未講課的教師的tname和depart

# 32# 解法一select a.tname,a.depart from teacher a join course b on a.tno = b.tno;# 解法二select a.tname,a.depart from teacher awhere exists (select * from course b where a.tno = b.tno);# 33select a.tname,a.depart from teacher awhere not exists (select * from course b where a.tno = b.tno);

34. 查詢至少有2名男生的班號

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

35. 查詢Student表中不姓「王」的同學記錄

select * from student where sname not like "王%";

36. 查詢student表中每個學生的姓名和年齡

# 解法一select sname, year(curdate())-year(sbirthday) age from student;# 解法二select sname, year(now())-year(sbirthday) age from student;

37. 查詢student表中最大和最小的sbirthday日期值

select sname,max(sbirthday) birthday from studentwhere sbirthday in (select max(sbirthday) from student )unionselect sname,min(sbirthday) birthday from studentwhere sbirthday in (select min(sbirthday) from student );

38. 以班號和年齡從大到小的順序查詢student表中的全部記錄

select * from student order by class desc,year(now())-year(sbirthday()) desc;

39. 查詢"男"教師及其所上的課程

select a.tname,b.cname from teacher a join course bon a.tno = b.tnowhere a.tsex = "男";

40. 查詢和「李軍」同性別並同班的同學sname

select sname from student where ssex in (select ssex from student where sname = "李軍")and class in (select class from student where sname = "李軍")and sname != "李軍";

41. 查詢所有選修「計算機導論」課程的「男」同學的成績表

# 解法一select a.* from score a join (course b,student c)on a.cno = b.cno and a.sno = c.snowhere c.ssex = "男" and a.cno in (select cno from course where cname = "計算機導論");# 解法二select a.* from score a join (course b,student c)using (sno,cno)where c.ssex = "男" and b.cname = "計算機導論";

References：

深入淺出MySQL (豆瓣)
MySQL必知必會 (豆瓣)
經典SQL練習題 - CSDN博客