SQL经典试题,自己一一做过(有所改动)

题目

  • 设有一数据库,包括四个表:学生表课程表分数表 以及教师信息表
1.建表
  • 四个表的结构分别如下面表一至表四所示。用SQL语句创建四个表并完成相关题目。
关键字 数据类型 可否为空 含义
s_no VARCHAR() 学号(主键)
s_name VARCHAR() 姓名
s_sex VARCHAR() 性别
s_birthday DATETIME 出生年月日
s_class VARCHAR() 班级

表一:学生表

关键字 数据类型 可否为空 含义
s_no VARCHAR() 学号(主键)
s_name VARCHAR() 姓名
s_sex VARCHAR() 性别
s_birthday DATETIME 出生年月日
s_class VARCHAR() 班级
关键字 数据类型 可否为空 含义
c_no VARCHAR() 课程编号(主键)
c_name VARCHAR() 课程名称
t_no VARCHAR() 授课老师编号

表二:课程表

关键字 数据类型 可否为空 含义
c_no VARCHAR() 课程编号(主键)
c_name VARCHAR() 课程名称
t_no VARCHAR() 授课老师编号
关键字 数据类型 可否为空 含义
s_no VARCHAR() 学号(外键)
c_no VARCHAR() 课程编号(外键)
degree VARCHAR() 分数

表三:分数表

关键字 数据类型 可否为空 含义
s_no VARCHAR() 学号(外键)
c_no VARCHAR() 课程编号(外键)
degree VARCHAR() 分数
关键字 数据类型 可否为空 含义
t_no VARCHAR() 老师编号(主键)
t_name VARCHAR() 姓名
t_sex VARCHAR() 性别
t_birthday DATETIME 出生年月日
t_prof VARCHAR() 职位
depart VARCHAR() 所在部门

表四:教师信息表

关键字 数据类型 可否为空 含义
t_no VARCHAR() 老师编号(主键)
t_name VARCHAR() 姓名
t_sex VARCHAR() 性别
t_birthday DATETIME 出生年月日
t_prof VARCHAR() 职位
depart VARCHAR() 所在部门
  • 建表
-- 学生
CREATE TABLE `学生`(
`s_no` VARCHAR(3) NOT NULL COMMENT '学号',
`s_name` VARCHAR(4) NOT NULL COMMENT '姓名',
`s_sex` VARCHAR(2) NOT NULL COMMENT '性别',
`s_birthday` DATETIME  COMMENT '出生年月日' , 
`s_class` VARCHAR(5) NOT NULL COMMENT '班级'
)ENGINE=MyISAM CHARSET=utf8;
-- 课程
CREATE TABLE `课程`(
`c_no` VARCHAR(5) NOT NULL COMMENT '课程编号',  
`c_name` VARCHAR(10) NOT NULL COMMENT '课程名称', 
`t_no` VARCHAR(10) NOT NULL  COMMENT '授课老师编号'
)ENGINE=MyISAM CHARSET=utf8;
-- 分数
CREATE TABLE `分数`(
`s_no` VARCHAR(3) NOT NULL COMMENT '学号', 
`c_no` VARCHAR(5) NOT NULL COMMENT '课程编号', 
`degree` NUMERIC(10, 1) NOT NULL COMMENT '分数'
)ENGINE=MyISAM CHARSET=utf8;
-- 老师
CREATE TABLE `教师` (
`t_no` VARCHAR(3) NOT NULL COMMENT '老师编号', 
`t_name` VARCHAR(4) NOT NULL COMMENT'姓名',
`t_sex` VARCHAR(2) NOT NULL COMMENT '性别', 
`t_birthday` DATETIME COMMENT '出生年月日', 
`t_prof` VARCHAR(6) COMMENT '职位', 
`depart` VARCHAR(10) NOT NULL COMMENT '所在部门'
)ENGINE=MyISAM CHARSET=utf8;
2.插入数据
INSERT INTO `学生`(s_no,s_name,s_sex,s_birthday,s_class) VALUES(108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO `学生` VALUES(105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO `学生` VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO `学生` VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO `学生` VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO `学生` VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

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

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

INSERT INTO `教师` VALUES(804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO `教师` VALUES(856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO `教师` VALUES(825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO `教师` VALUES(831,'刘冰','女','1977-08-14','助教','电子工程系');
3.查询数据题目

1、 查询学生表中的所有记录的s_name、s_sex和s-class列。
2、 查询教师所有的单位即不重复的depart列(去重后的教师职业)。
3、 查询学生表的所有记录。
4、 查询分数表中成绩在60到80之间的所有记录。
5、 查询分数表中成绩为85,86或88的记录。
6、 查询学生表中“95031”班或性别为“女”的同学记录。
7、 以s_class降序查询学生表的所有记录。
8、 以c_no升序、degree降序查询分数表的所有记录。
9、 查询“95031”班的学生人数。
10、查询分数表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70,最高分小于90的s_no列。
14.查询分数在70到90之间不包括70和90分的s_no列。
15、查询所有学生的s_name、c_no和degree列。(INNER JOIN 关键字在表中存在至少一个匹配时返回行)
16、查询所有学生的s_name、c_name和degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个分数等级表,并设置分数等级:
CREATE TABLE 等级(
low TINYINT COMMENT '最低分',
upp TINYINT COMMENT '最高分',
rank CHAR(1) COMMENT '等级'
);
INSERT INTO 等级 VALUES(90,100,'A');
INSERT INTO 等级 VALUES(80,89,'B');
INSERT INTO 等级 VALUES(70,79,'C');
INSERT INTO 等级 VALUES(60,69,'D');
INSERT INTO 等级 VALUES(0,59,'E');
现查询所有同学的s_no、c_no和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询分数中选学一门以上课程的同学中分数为非最高分成绩的记录。
21、查询和学号为101的同学同年出生的所有学生的s_no、s_name和s_birthday列。
22、查询“张旭“教师任课的学生成绩。
23、查询选修某课程的同学人数多于5人的教师姓名。
24、查询95033班和95031班全体学生的记录。
25、查询存在有85分以上成绩的课程c_no.
26、查询出“计算机系“教师所教课程的成绩表。
27、查询“计算机系”与“电子工程系“不同职称的教师的t_name 和 t_prof。
28、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的c_no、s_no和degree,并按degree从高到低次序排序。
29、查询所有教师和同学的name、sex和birthday.
30、查询所有“女”教师和“女”同学的name、sex和birthday.
31、查询成绩比该课程平均成绩低的同学的成绩表。
32、查询所有任课教师的t_name和depart.
33 查询所有未讲课的教师的t_name和depart.
34、查询至少有2名男生的班号。
35、查询学生表中不姓“王”的同学记录。
36、查询学生表中每个学生的姓名和年龄。
37、查询学生表中最大和最小的s_birthday日期值。
38、以班号和年龄从大到小的顺序查询Student表中的全部记录。
39、查询“男”教师及其所上的课程。
40、查询最高分同学的s_no、c_no和degree列。
41、查询和“李军”同性别的所有同学的s_name。
42、查询和“李军”同性别并同班的同学s_name。
43、查询所有选修“计算机导论”课程的“男”同学的成绩表。

4.答案

1、 查询学生表中的所有记录的s_name、s_sex和s-class列。

SELECT s_name,s_sex,s_class FROM 学生;

2、 查询教师所有的单位即不重复的depart列(去重后的教师职业)。

SELECT DISTINCT depart FROM 教师;

3、 查询学生表的所有记录。

SELECT * FROM 学生;

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

SELECT * FROM 分数  WHERE degree BETWEEN 60 AND 80;

5、 查询分数表中成绩为85,86或88的记录。

SELECT * FROM 分数 WHERE degree IN(85,86,88);

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

SELECT * FROM 学生 WHERE s_class = 95031 OR s_sex = '女';

7、 以s_class降序查询学生表的所有记录。

SELECT * FROM 学生 ORDER BY `s_class` DESC;

8、 以c_no升序、degree降序查询分数表的所有记录。

SELECT * FROM 分数  ORDER BY c_no ,degree DESC; /*默认升序*/

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

SELECT  COUNT(1) AS StnNum FROM 学生 WHERE s_class = '95031';

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

SELECT s_no,c_no FROM 分数 ORDER BY degree DESC LIMIT 1; 

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

SELECT AVG(degree) FROM 分数 WHERE c_no = '3-105';

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

SELECT c_no, AVG(degree) FROM 分数 WHERE c_no LIKE '3%' GROUP BY c_no HAVING COUNT(s_no) >= 5;

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

SELECT s_no FROM 分数 GROUP BY s_no HAVING max(degree) < 90 AND min(degree)>70;

14.查询分数在70到90之间不包括70和90分的s_no列。

SELECT s_no FROM 分数 WHERE degree > 70 AND degree < 90;

15、查询所有学生的s_name、c_no和degree列。(INNER JOIN 关键字在表中存在至少一个匹配时返回行)

SELECT s_name,c_no,degree FROM 学生 INNER JOIN 分数 ON 学生.s_no = 分数.s_no ORDER BY s_name;

16、查询所有学生的s_name、c_name和degree列。

SELECT s_name,c_name,degree FROM 学生 INNER JOIN 分数 ON(学生.s_no = 分数.s_no) INNER JOIN 课程 ON(分数.c_no = 课程.c_no) ORDER BY c_name;

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

SELECT c_name ,avg(degree) FROM 学生 INNER JOIN 分数 ON(学生.s_no = 分数.s_no) INNER JOIN 课程 ON(分数.c_no = 课程.c_no) WHERE s_class = '95033' GROUP BY 课程.c_no ORDER BY c_name;

18、假设使用如下命令建立了一个分数等级表,并设置分数等级:
CREATE TABLE 等级(
low TINYINT COMMENT '最低分',
upp TINYINT COMMENT '最高分',
rank CHAR(1) COMMENT '等级'
);
INSERT INTO 等级 VALUES(90,100,'A');
INSERT INTO 等级 VALUES(80,89,'B');
INSERT INTO 等级 VALUES(70,79,'C');
INSERT INTO 等级 VALUES(60,69,'D');
INSERT INTO 等级 VALUES(0,59,'E');
现查询所有同学的s_no、c_no和rank列。

SELECT s_no,c_no,rank FROM 分数 INNER JOIN 等级 ON(分数.degree >= 等级.low AND 分数.degree <= 等级.upp) ORDER BY s_no; 

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

SELECT s1.s_no,s1.degree FROM 分数 AS s1 INNER JOIN 分数 AS s2 ON(s1.c_no = s2.c_no AND s1.degree > s2.degree) WHERE s1.c_no ='3-105' AND s2.s_no = '109' ORDER BY s1.s_no;

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

SELECT * FROM 分数 GROUP BY s_no HAVING count(c_no)>1 AND degree != max(degree);

21、查询和学号为101的同学同年出生的所有学生的s_no、s_name和s_birthday列。

SELECT s1.s_no,s1.s_name,s1.s_birthday FROM 学生 AS s1 INNER JOIN 学生 AS s2 ON(YEAR(s1.s_birthday) = YEAR(s2.s_birthday)) WHERE s2.s_no = '101';

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

SELECT 学生.s_no, s_name,c_name,degree 
FROM 教师 
INNER JOIN 课程 ON(教师.t_no = 课程.t_no) 
INNER JOIN 分数 ON(课程.c_no = 分数.c_no) 
INNER JOIN 学生 ON(分数.s_no = 学生.s_no) 
WHERE 教师.t_name = '张旭' 
ORDER BY degree; 

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

SELECT t_name 
FROM 教师 
INNER JOIN 课程 ON(.教师.t_no = 课程.t_no) 
INNER JOIN 分数 ON(课程.c_no = 分数.c_no)
WHERE 课程.c_no IN (SELECT c_no FROM 分数 GROUP BY(c_no) HAVING count(分数.s_no) > 5)
ORDER BY 教师.t_no;

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

SELECT *
FROM 学生
WHERE s_class = '95033' OR s_class = '95031'
ORDER BY s_class;
或者
SELECT *
FROM 学生
WHERE s_class IN('95033','95031') 
ORDER BY s_class;

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

SELECT c_no
FROM 分数
WHERE degree > '85';

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

SELECT t_name,c_name ,s_name ,degree
FROM 教师
INNER JOIN 课程 ON(教师.t_no = 课程.t_no) 
INNER JOIN 分数 ON(课程.c_no = 分数.c_no)
INNER JOIN 学生 ON(学生.s_no = 分数.s_no)
WHERE depart = '计算机系' 
ORDER BY  t_name;

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

SELECT t_name,t_prof
FROM 教师
WHERE depart = '计算机系'
AND t_prof NOT IN (
SELECT t_prof
FROM 教师
WHERE depart = '电子工程系'
)
UNION
SELECT t_name,t_prof
FROM 教师
WHERE depart = '电子工程系'
AND t_prof NOT IN (
SELECT t_prof
FROM 教师
WHERE depart = '计算机系'
);
或者
SELECT t_name,t_prof 
FROM 教师 a 
WHERE t_prof NOT IN(
SELECT t_prof 
FROM 教师 b 
WHERE a.depart!=b.depart
);

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

SELECT c_no,s_no,degree 
FROM 分数
WHERE c_no = '3-105' AND degree > ANY(
SELECT degree 
FROM 分数
WHERE c_no = '3-245'
)
ORDER BY degree DESC;

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

SELECT s_name,s_sex,s_birthday
FROM 学生
UNION
SELECT t_name,t_sex,t_birthday
FROM 教师;

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

SELECT s_name,s_sex,s_birthday
FROM 学生
WHERE s_sex = '女'
UNION
SELECT t_name,t_sex,t_birthday
FROM 教师
WHERE t_sex = '女';

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

SELECT * 
FROM 分数 AS s1
WHERE s1.degree < (
SELECT avg(degree) 
FROM  分数 AS s2 
)
ORDER BY degree;
或
SELECT * 
FROM 分数 AS s1
WHERE s1.degree BETWEEN 0 AND (
SELECT avg(degree) 
FROM  分数 AS s2 
)
ORDER BY degree;

32、查询所有任课教师的t_name和depart.

SELECT t_name,depart 
FROM 教师
WHERE t_no IN(
SELECT t_no
FROM 课程
);

33 查询所有未讲课的教师的t_name和depart.

SELECT t_name,depart 
FROM 教师
WHERE t_no NOT IN(
SELECT t_no
FROM 课程
);

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

SELECT s_class
FROM 学生
WHERE s_sex = '男'
GROUP BY s_class 
HAVING count(s_no) > 1;

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

SELECT * 
FROM 学生
WHERE s_name NOT LIKE('王%');

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

SELECT s_name,YEAR(now()) - YEAR(s_birthday) AS s_age 
FROM 学生;

37、查询学生表中最大和最小的s_birthday日期值。

SELECT max(s_birthday) AS maxb, min(s_birthday) AS minb
FROM 学生;

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

SELECT * FROM 学生 ORDER BY s_class DESC, s_birthday ASC;

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

SELECT t_name,c_name
FROM 课程
INNER JOIN 教师 ON (教师.t_no = 课程.t_no)
WHERE t_sex = '男';

40、查询最高分同学的s_no、c_no和degree列。

SELECT s_no,c_no,degree
FROM 分数
GROUP BY c_no
HAVING degree = max(degree);

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

SELECT s1.s_name
FROM 学生 s1
INNER JOIN 学生 s2 ON(s1.s_sex = s2.s_sex)
WHERE s2.s_name = '李军';

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

SELECT s1.s_name
FROM 学生 s1
INNER JOIN 学生 s2 ON(s1.s_sex = s2.s_sex AND s1.s_class = s2.s_class)
WHERE s2.s_name = '李军';

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

SELECT * 
FROM 分数
WHERE s_no IN(
SELECT s_no 
FROM 学生
WHERE s_sex = '男')
AND c_no IN(
SELECT c_no 
FROM 课程
WHERE c_name = '计算机导论'
);
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,558评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,002评论 3 387
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 159,036评论 0 349
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,024评论 1 285
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,144评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,255评论 1 292
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,295评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,068评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,478评论 1 305
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,789评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,965评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,649评论 4 336
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,267评论 3 318
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,982评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,223评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,800评论 2 365
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,847评论 2 351

推荐阅读更多精彩内容

  • 50个常用的sql语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cna...
    最美的太阳WW阅读 3,170评论 0 23
  • 说明:以下五十个语句都按照测试数据进行过测试,最好每次只单独运行一个语句。 问题及描述: --1.学生表 Stud...
    lijun_m阅读 1,299评论 0 1
  • 学习《提升能力的三个底层方法》有感 一、学习的本质 复盘每天的生活:复盘每一天的决策都是在什么样的假设下做出的,又...
    娜样快乐ing阅读 156评论 0 0
  • ——读特朗普《永不放弃》点滴 中美之间的贸易战由特朗普挑起之后,我就比较关注特朗普其人。我在海口美兰机场候机楼的蔚...
    闻丁阅读 1,068评论 3 6
  • 你好像又进步多一点了,现在的行动力变强了,该做的事情没有拖一两个月那么久,看,来新家的第一天,你就做了好多事。 凌...
    天野丢阅读 199评论 1 1