数据库查询

连接查询与子查询

列题：

1. 谁和刘晨同岁

select sage from student where sage = (select sage from student where sname=“刘晨”) and sname <>’刘晨’;

2. 查询各个学生的姓名、所选修课程号及成绩。

select sname,cno,grade from student join sc on student.sno=sc.sno；

3. 查询刘晨选修了几门课程。(两种方法)

select count(cno) from sc join student on student.sno=sc.sno where sname=“刘晨”; select count(cno) from sc where sno=(select sno from student where sname=“刘晨”);

4. 查询选修了1号课或2号课的学生的姓名。(两种方法)

Select(distinct(去掉重复)) sname,cno from student join sc on student.sno=sc.sno where cno=“c01” or cno=“c02”; select sname from student where sno in(select sno from sc where cno = ‘c01’ or cno = ‘c02’);

5. 查询李勇选修了几门课程。(两种方法)

select count(cno) from sc join student on student.sno=sc.sno where sname=“李勇”;

6. 查询王名的平均成绩。(两种方法)

select avg(grade) from sc join student on sc.sno=student.sno where sname=“王名”; select avg(grade) from sc where sno =(select sno from student where sname=‘王名’);

7. 将学生的学号、姓名、课程名和成绩查询出来。（连接查询

） select student.sno,sname,cname,grade from student join sc on student.sno=sc.sno join course on course.cno=sc.cno;

8.查询选修了3号课程且成绩在85分以上的所有学生的姓名和学号。(两种方法)

select sname,sc.sno from student join sc on student.sno=sc.sno where cno=“c03” and grade>85 group by sname; select sno,sname from student where sno in (select sno from sc where cno = ‘c03’ and grade >85);

8. 查询数据库课程的最高成绩。(两种方法)

select max(grade) from sc join course on course.cno=sc.cno where cname=‘数据库’; select max(grade) from sc where cno =(select cno from course where cname=‘数据库’);

9. 查询刘晨所选课程的名称和成绩。

select cname,grade from course join sc on course.cno=sc.cno join student on student.sno=sc.sno where sname=“刘晨”;

10. 查询刘晨所得总学分。

select sum(ccredit) from course join sc on sc.cno=course.cno join student on sc.sno=student.sno;

11. 查询有哪些学生（姓名）选修了数据库课程。(两种方法)

select sname from student join sc on student.sno=sc.sno join course on course.cno=sc.cno where cname=“数据库”; select sname from student where sno in(select sno from sc where cno =(select cno from course where cname=‘数据库’));

12. 将王名的年龄改为和刘晨一样；

update student set dno = (select dno from student where sname=“刘晨”) where sname=“王名”;

13. 删除选修了c04课程的学生信息；

delete from student where sno in(select sno from sc where cno=“c04”);

16. 删除没有人选修的课程信息；

delete from course where cno not in (select cno from sc);