数据表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
/*Navicat SQLite Data TransferSource Server : schoolSource Server Version : 30808Source Host : :0Target Server Type : SQLiteTarget Server Version : 30808File Encoding : 65001Date: 2021-12-23 16:06:04*/PRAGMA foreign_keys = OFF;-- ------------------------------ Table structure for Course-- ----------------------------DROP TABLE IF EXISTS "main"."Course";CREATE TABLE Course( courseid integer primary key autoincrement, courseme varchar(32), teacherid int);-- ------------------------------ Records of Course-- ----------------------------INSERT INTO "main"."Course" VALUES (3001, '语文', 1001);INSERT INTO "main"."Course" VALUES (3002, '数学', 1002);-- ------------------------------ Table structure for Mark-- ----------------------------DROP TABLE IF EXISTS "main"."Mark";CREATE TABLE Mark( userid integer, courseid integer not null, score int default 0);-- ------------------------------ Records of Mark-- ----------------------------INSERT INTO "main"."Mark" VALUES (2001, 3001, 89);INSERT INTO "main"."Mark" VALUES (2001, 3002, 90);INSERT INTO "main"."Mark" VALUES (2002, 3001, 66);INSERT INTO "main"."Mark" VALUES (2003, 3002, 85);-- ------------------------------ Table structure for sqlite_sequence-- ----------------------------DROP TABLE IF EXISTS "main"."sqlite_sequence";CREATE TABLE sqlite_sequence(name,seq);-- ------------------------------ Records of sqlite_sequence-- ----------------------------INSERT INTO "main"."sqlite_sequence" VALUES ('Teacher', 1002);INSERT INTO "main"."sqlite_sequence" VALUES ('Student', 2002);INSERT INTO "main"."sqlite_sequence" VALUES ('Course', 3002);-- ------------------------------ Table structure for Student-- ----------------------------DROP TABLE IF EXISTS "main"."Student";CREATE TABLE Student( userid integer primary key autoincrement, username varchar(32), userage int, usersex varchar(32));-- ------------------------------ Records of Student-- ----------------------------INSERT INTO "main"."Student" VALUES (2001, '小明', 18, '男');INSERT INTO "main"."Student" VALUES (2002, '小红', 18, '女');-- ------------------------------ Table structure for Teacher-- ----------------------------DROP TABLE IF EXISTS "main"."Teacher";CREATE TABLE Teacher( teacherid integer primary key autoincrement, teachername varchar(32));-- ------------------------------ Records of Teacher-- ----------------------------INSERT INTO "main"."Teacher" VALUES (1001, '张三');INSERT INTO "main"."Teacher" VALUES (1002, '李四'); |
问题：
1、查询“语文”课程比“数学”课程成绩低的所有学生的学号
|
1
2
3
4
|
select a.userid from(select userid,score from Mark where courseid ='3001')a,(select userid,score from Mark where courseid ='3002')b where a.userid = b.userid and a.score<b.score; |
2、查询平均成绩大于60分的同学的学号和平均成绩
|
1
2
3
|
select userid,avg(score) from Markgroup by userid having avg(score)>60; |
3、查询所有同学的学号、姓名、选课数、总成绩
|
1
2
3
4
5
6
7
|
select s.userid ,s.username ,count_courseid as 选课数, sum_score as 总成绩from Student sleft join(select userid,count(courseid ) as count_courseid,sum(score) as sum_score from Mark group by userid )scon s.userid = sc.userid; |
4、查询姓‘李'的老师的个数：
|
1
2
3
|
select count(teachername )from Teacher where teachername like '张%'; |
5、检索语文课程分数小于60，按分数降序排列的同学学号：
|
1
2
3
4
5
|
select userid ,scorefrom Markwhere courseid ='3001'and score<60order by score desc; |
6、查询学/没学过”张三”老师讲授的任一门课程的学生姓名
|
1
2
3
4
5
6
7
8
|
select username from Student where userid in ( select userid from Mark,Course,Teacher where Course.teacherid = Teacher.teacherid and Mark.courseid = Course.courseid and Teacher.teachername ='张三'); |
7、查询全部学生选修的课程和课程号和课程名：
|
1
2
3
|
select courseid ,courseme from Course where courseid in (select courseid from Mark group by courseid); |
8、检索选修两门课程的学生学号：
|
1
2
3
4
|
select userid from Markgroup by userid having count(8) == 2; |
9、查询各个课程及相应的选修人数
|
1
|
select courseid ,count(*) from Course group by courseid ; |
10、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
|
1
2
3
4
5
6
7
8
9
10
|
select Student.username ,Mark.scorefrom Mark left join Student on Mark.userid = Student.userid left join Course on Mark.courseid = Course.courseid left join Teacher on Course.teacherid = Teacher.teacherid where Teacher.teachername = '张三'and Mark.score = (select max(score) from Mark sc_1 where Mark.courseid = sc_1.courseid); |
11、求选了课程的学生人数：
|
1
2
|
select count(2) from(select distinct userid from Mark)a; |
12、查询课程编号为“语文”且课程成绩在80分以上的学生的学号和姓名
|
1
2
3
4
|
select Mark.userid,Student.username from Mark left join Student on Mark.userid = Student.userid where Mark.courseid = '3001' and Mark.score>80; |
13、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
|
1
2
3
4
|
select courseid ,avg(score)from Mark group by courseid order by avg(score),courseid desc; |
14、查询课程名称为“数学”，且分数高于85的学生名字和分数：
|
1
2
3
4
5
|
select c.courseme ,Student.userid ,Student.username ,Mark.scorefrom Course cleft join Mark on Mark.courseid = c.courseid LEFT JOIN Student on Student.userid = Mark.userid where c.courseme = '数学' and Mark.score>85; |
到此这篇关于SQL数据库十四种案例介绍的文章就介绍到这了,更多相关SQL数据库案例内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/qq_34623621/article/details/122110093
