數據表
|
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
