1
、 查询Student表中的所有记录的Sname、Ssex和Class列。
2 select sname,ssex,
class from student;
3 2
、 查询教师所有的单位即不重复的Depart列。
4 select distinct depart from teacher;
5
6 3
、 查询Student表的所有记录。
7 select *
from student;
8 4
、 查询Score表中成绩在60到80之间的所有记录。
9 select * from score where degree between 60 and 80
;
10 5
、 查询Score表中成绩为85，86或88的记录。
11 select * from score where degree in (85 , 86 , 88
) ;
12 6、 查询Student表中“95031
”班或性别为“女”的同学记录。
13 select * from student where
class = "95031" or ssex = "女"
;
14 以Class降序查询Student表的所有记录。
15 以Cno升序、Degree降序查询Score表的所有记录。
16 查询“95031
”班的学生人数。
17 10
、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
18 select cno,sno from score where degree = (select
max(degree) from score);
19 查询每门课的平均成绩。
20 12
、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
21 select avg(degree) from score group by cno having
count(*) >=5 and cno like "3%"
22 查询分数大于70，小于90的Sno列。
23 Select sno from score where
24 查询所有学生的Sname、Cno和Degree列。
25 15
、查询所有学生的Sno、Cname和Degree列。
26 select a.sname,b.cname,c.
degree
27 from student
as a,course
as b,score
as c
28 where a.sno = c.sno and b.cno = c.
cno
29 16
、查询所有学生的Sname、Cname和Degree列。
30 select sname,cname,
degree from student
31 join score on student.sno = score.
sno
32 join course on course.cno = score.
cno
33
34 17、 查询“95033
”班学生的平均分。
35 select avg(degree) from score where sno in (select sno from student where
class = "95033"
);
36 20
、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
37 select *
from score where sno in(select sno from score group by sno)
38 and degree not in (select
max(degree) from score);
39 或select *
from score where sno in(select sno from score group by sno)
40 and degree not in (select
max(degree) from score group by cno);
41
42 21、 查询成绩高于学号为“109”、课程号为“3-105
”的成绩的所有记录。
43 select degree from score where degree
44 > (select
max(degree) from score where cno = "3-105" and sno ="109"
)
45 22
、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
46 select left(sbirthday,4)
as date from student;
47 select sno,sname,sbirthday from student where sbirthday like concat((select left(sbirthday,4)
as date from student where sno="108"),"","%"
);
48
49 23
、查询“张旭“教师任课的学生成绩。
50 1
、从成绩表 查询学生成绩
51
52 2
、条件是这个课程是 “张旭“教师任课
53 A)从老师表 中查名字叫 张旭 编号
54 B)根据老师编号 在课程表中找到对应的 课程号
55 select depart from teacher where tname="张旭"
;
56 select cno from course where depart=(select depart from teacher where tname="张旭"
);
57 查询考计算机导论的学生成绩
58
59 查询 所有的列/
字段 从 成绩表(score) 条件是
60 课程编号是：课程表(course)中 课程名(cname)称为计算机导论 的课程编号
61
62 课程表(course)中 课程名(cname)称为计算机导论 的课程编号===3-105
63
64 查询 所有的列/字段 从 成绩表(score) 条件是 课程编号是：3-105
65
66 查询李诚老师教的课程名称
67
68 教高等数学的老师是哪个系的
69
70
71 24
、查询选修某课程的同学人数多于5人的教师姓名。
72 select cno from score group by cno having
count(*)>"5"
;
73 select tno from course where cno in(select cno from score group by cno having
count(*)>"5"
);
74 select tname from teacher where tno=(select tno from course where cno in(select cno from score group by cno having
count(*)>"5"
));
75
76 25
、查询95033班和95031班全体学生的记录。
77 select * from student where
class in(95033,95031
);
78 26、 查询存在有85分以上成绩的课程Cno.
79 Select cno from score where degree >”85
”
80 27
、查询出“计算机系“教师所教课程的成绩表。
81 select tno from teacher where depart="计算机系"
;
82 select cno from course where tno in(select tno from teacher where depart="计算机系"
);
83 select * from score where cno in(select cno from course where tno in(select tno from teacher where depart="计算机系"
));
84
85 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,
并按Degree从高到低次序排序。
86 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
87 select * from Score where Cno=‘3-105‘ and Degree>(select
max(Degree) from Score where Cno =‘3-245‘
)
88
89 31、 查询所有教师和同学的name、sex和birthday.
90 select sname,ssex,
sbirthday from student
91 union
92 select tname,tsex,
tbirthday from teacher;
93 32、查询所有“女”教师和“女”同学的name、sex和birthday.
94 select sname,ssex,sbirthday from student where ssex="女"
95 union
96 select tname,tsex,tbirthday from teacher where tsex="女"
;
97 33
、 查询成绩比该课程平均成绩低的同学的成绩表。
98 select * from score group by cno having degree<
avg(degree);
99 34、 查询所有任课教师的Tname和Depart.
100 select cno from score group by cno;
101 select tno from course where cno in(select cno from score group by cno);
102 select tname,
depart from teacher where tno in(select tno from course where cno in(select cno from score group by cno));
103 35 、 查询所有未讲课的教师的Tname和Depart.
104 select tname,
depart from teacher where tno not in(select tno from course where cno in(select cno from score group by cno));
105 36
、查询至少有2名男生的班号。
106 select
class from student where ssex=‘男‘ group by
class having
count(*)>1
107 37
、查询Student表中不姓“王”的同学记录。
108 select * from student where sname not like"王%"
;
109 38
、查询Student表中每个学生的姓名和年龄。
110 select sname,(2018-left(sbirthday,4))
as age from student;
111 39
、查询Student表中最大和最小的Sbirthday日期值。
112 select
max(sbirthday),
min(sbirthday) from student;
113 40
、以班号和年龄从大到小的顺序查询Student表中的全部记录。
114 select * from student order by
class desc,(2018-left(sbirthday,4
)) desc;
115 41
、查询“男”教师及其所上的课程。
116 select tno from teacher where tsex="男"
;
117 select teacher.tname,course.cname from teacher,course where course.tno in(select tno from teacher where tsex="男") and course.tno= teacher.
tno;
118 42
、查询最高分同学的Sno、Cno和Degree列。
119 select
max(degree) from score ;
120 select * from score where degree=(select
max(degree) from score);
121 43、查询和“李军”同性别的所有同学的Sname.
122 select ssex from student where sname="李军"
;
123 select sname from student where ssex=(select ssex from student where sname="李军"
);
124 44、查询和“李军”同性别并同班的同学Sname.
125 select
class from student where sname="李军"
;
126 select sname from student where ssex=(select ssex from student where sname="李军") and
class=(select
class from student where sname="李军"
);
127 45
、查询所有选修“计算机导论”课程的“男”同学的成绩表。
128 select cno from course where cname="计算机导论"
;
129 select sno from student where ssex="男"
;
130 select * from score where sno in(select sno from student where ssex="男") and cno in(select cno from course where cname="计算机导论");
45个操作代码练习
数据库表格老师学生教师表练习题
标签：== .com lap group by null avg each max core
