- 统计“20级大数据3班”期末考试的总成绩。
方法一:
select
Sum(score)
from sc
where substring(sid,1,3)="20117701";
方法二:
SELECT
SUM(score)
From sc
Join stud11 on sc.sid=stud11.sid
Join class on stud11.sclass_id=class.class_id
Where class_name=”20级大数据3班”;
方法三:
SELECT
SUM(score)
From sc
Join stud11 on sc.sid=stud11.sid
Join class on stud11.sclass_id=class.class_id and class_name=”20级大数据3班”;
- .统计“20级大数据1班”学生平均成绩。
select
avg(a.score)
from sc as a
join stud11 as b on a.sid=b.sid
join class as c on b.sclass_id=class_id and c.class_name="20级大数据3班";
- 统计“邓通”同学选修的所有课程的平均成绩。
select
avg(sc.score)
from stud11 as s inner join sc as sc
on s.sid=sc.sid
where sc.sid='20117701001';
- 统计20级4个班参加考试学生的总人数。
select
count(*)
from stud11 as s,class as c
where s.sclass_id=c.class_id;
- 统计20级4个班年龄最大的学生信息。(重要)
select *
from stud11 where sage=(select max(sage) from stud11);
- 统计“20级大数据3班”年龄最大的学生信息。(重要)
Select * from stud11 where
Sage=(Select Max(sage) From stud11
Join class on class.class_id=stud11.sclass_id and class_name=”20级大数据3班”);
- 统计“20级大数据1班”姓张的学生人数。
select
count(*)
from stud11 as s
inner join class as c on c.class_id=s.sclass_id
where s.sname like "张%" and
c.class_name='20级大数据1班';
8.统计20级4个班学生的总人数。
select
count(s.sname)
from stud11 as s,class as c
where s.sclass_id=c.class_id;
- 查询20级4个班姓王的学生的人数。
select
count(*)
from stud11
where stud11.sname like "王%";
姓王的学生的信息:
select *
from stud11
where stud11.sname like "王%";
- 查询“Java面向对象”这门课程成绩排名前10的学生信息。
(由于数据不足,只查出6名同学的信息)
select stud11.sid,stud11.sname,stud11.ssex,stud11.sage
,stud11.sclass_id from stud11
join sc on sc.sid=stud11.sid
join course on course.course_id=sc.course_id
where course_name="Java面向对象" order by score desc limit 10;
11.统计学号为“20117702011”的学生选修的所有课程的平均成绩。
select
avg(sc.score)
from stud11 as s inner join sc as sc
on s.sid=sc.sid
where sc.sid='20117702011';
- 查询“Spark技术”这门课程成绩排名最后3位学生信息。
Select
stud11.sid,stud11.sname,stud11.ssex,stud11.sage,stud11.sclass_id from stud11
Join sc on sc.sid=stud11.sid
Join course on course.course_id=sc.course_id
Where course_name=”Spark技术” order by score asc limit 3;
- 查询“Spark技术”这门课程成绩排名前10的学生信息。(重要数据库里没有10个数据,暂时展示如下数据)
select * from
sc as a
inner join course as co
on a.course_id=co.course_id
inner join stud11 as s
on a.sid=s.sid
where co.course_id='C7';
- 查询20级每一个班“数据库原理”这么课程考试成绩排名前3的学生信息。
select stud11.sid,stud11.sname,stud11.ssex,stud11.sage
,stud11.sclass_id from stud11
join sc on sc.sid=stud11.sid
join course on course.course_id=sc.course_id
where course_name="数据库原理" order by score desc limit 3;
- 统计同时选修了Java面向对象和数据库原理两门课程的学生总人数。
Select count(*) from
(Select sid from sc where course_id=”C1”) a join
(Select sid from sc where course_id=”C2”) b
On a.sid=b.sid;