数据库练习题重点整理
时间：2021-07-01 10:21:17
帮助过：4人阅读
select avg(degree)
from score
where cno
like‘3%‘ and cno
in(
select cno
from score
group by cno
having count(
*)
>4)
select avg(degree)
from score
group by cno
having count(
*)
>4 and cno
like ‘3%‘
假设使用如下命令建立了一个grade表：
create table grade(low
int(
3),upp
int(
3),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’)
现查询所有同学的Sno、Cno和rank列。
select sno,cno,rank
from score,grade
where degree
between low
and upp
查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select * from score
where sno
in(
select sno
from score
group by sno
having count(
*)
>1)
and degree
<(
select max(degree)
from score)
select * from score a
where sno
in(
select sno
from score
group by sno
having count(
*)
>1)
and degree
<(
select max(degree)
from score b
where b.cno
= a.cno)
查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno,sname,sbirthday
from student
where YEAR(sbirthday)
= (
select YEAR(sbirthday)
from student
where sno
=‘108‘)
查询选修某课程的同学人数多于5人的教师姓名。
select tname
from teacher
where tno
in(
select tno
from course
where cno
in(
select cno
from score
group by cno
having count(
*)
>5))
查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select Tname,Prof
from Teacher a
where Prof
not in(
select Prof
from Teacher b
where a.Depart
!=b.Depart)
数据库练习题重点整理
标签：
