连接查询和子查询练习
- 谁和刘晨同岁
(子查询)
select sname
from student
where sage in (select sage from student where sname = ‘刘晨’)
and sname != ‘刘晨’; - 查询各个学生的姓名、所选修课程号及成绩。(连接查询)
select sname,cno,grade
from student join sc on student.sno = sc.sno; - 查询刘晨选修了几门课程。(两种方法)
(连接查询)
select count(cno)
from sc
join student on sc.sno = student.sno
where sname = ‘刘晨’; - 查询选修了1号课或2号课的学生的姓名。(两种方法)
(连接查询)
select sname
from sc
join student on sc.sno = student.sno
where cno = ‘c01’ or cno = ‘c02’;
(子查询)
select sname
from student
where sno in (select sno from sc where cno = ‘c01’ or cno = ‘c02’); - 查询李勇选修了几门课程。(两种方法)
(子查询)
select count(cno)
from sc
where sno in (select sno from student where sname = ‘李勇’);
(连接查询)
select count(cno)
from sc
join student on sc.sno = student.sno
where sname = ‘李勇’; - 查询王名的平均成绩。(两种方法)
(子查询)
select avg(grade)
from sc
where sno = (select sno from student where sname = ‘王名’);
(连接查询)
select avg(grade)
from student join sc on student.sno = sc.sno
where sname = ‘王名’; - 将学生的学号、姓名、课程名和成绩查询出来。(连接查询)
select student.sno,sname,cname,grade
from student join sc on student.sno = sc.sno
join course on course.cno = sc.cno; - 查询选修了3号课程且成绩在85分以上的所有学生的姓名和学号。(两种方法)
select sc.sno,sname
from student join sc on student.sno = sc.sno
where cno = ‘c03’ and grade > 85; - 查询数据库课程的最高成绩。(两种方法)
(子查询)
select max(grade)
from sc
where cno = (select cno
from course
where cname = ‘数据库’);
(连接查询)
select max(grade)
from sc join course on sc.cno = course.cno
where cname = ‘数据库’; - 查询刘晨所选课程的名称和成绩。
select cname,grade
from course join sc on course.cno=sc.cno
join student on student.sno=sc.sno
where sname=‘刘晨’; - 查询刘晨所得总学分。
select sum(ccredit)
from course join sc on course.cno=sc.cno
join student on student.sno=sc.sno
where sname=‘刘晨’; - 查询有哪些学生(姓名)选修了数据库课程。(两种方法)
select sname
from course join sc on course.cno=sc.cno
join student on student.sno=sc.sno
where cname =‘数据库’; - 将王名的年龄改为和刘晨一样;
- 删除选修了c04课程的学生信息;
delete from student
where sno in (select sno
from sc
where cno = ‘c04’); - 将大数据处理的学分改为和数据库一样;
- 将0002同学c02的成绩改为和0003的c02成绩一样。
- 删除没有人选修的课程信息;