建立学生信息数据库STUDENT,(学号姓名)、(课程号课程名)、(学号课程成绩）三个表。表里包含一定数量记录

Question

针对数据库STUDENT给出下列查询的SQL语句：
1、查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
2、查询选修了课程的学生人数
3、求各个课程号及相应的选课人数
4、查询至少选修了2门课程的学生学号
5、查询每个学生的学号、姓名、选修的课程名及成绩查询选修了课程名为“信息系统”的学生学号和姓名

Answer 1

一 创建相关表和数据
create table student(id number(10) primary key, name varchar2(20));--id不能重复
create table course(id number(10) primary key, name varchar2(20));--id不能重复
create table stu_cou(s_id number(10), c_id number(10), grade number(4), foreign key(s_id) references student(id), foreign key(c_id) references course(id));--创建表时设置了外键
create unique index uk_stu_cou on stu_cou(s_id,c_id);--设置了唯一索引

insert into student values (1,'zz');
insert into student values (2,'rr');
insert into student values (3,'ff');
insert into student values (4,'yhy');
insert into student values (5,'aqw');
insert into student values (6,'sgh');
commit;

insert into course values (1,'china');
insert into course values (2,'math');
insert into course values (3,'english');
insert into course values (4,'music');
commit;

insert into stu_cou values (1,1,98);
insert into stu_cou values (1,2,78);
insert into stu_cou values (1,3,77);
insert into stu_cou values (1,4,100);
insert into stu_cou values (2,1,26);
insert into stu_cou values (2,2,78);
insert into stu_cou values (2,3,99);
insert into stu_cou values (2,4,100);
insert into stu_cou values (5,1,26);
insert into stu_cou values (5,2,78);
insert into stu_cou values (6,2,33);
commit;
二 各项查询的语句如下
--s_id 学号；c_id 课程号
1 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
select * from stu_cou where c_id = 3 order by grade desc;

2 查询选修了课程的学生人数
select count(distinct s_id) from stu_cou;

3 求各个课程号及相应的选课人数
select cou.id 课程号, (select count(*) from stu_cou where c_id = cou.id) 选课人数 from course cou;

4 查询至少选修了2门课程的学生学号
select * from (
select stu.id 学号, (select count(*) from stu_cou where s_id = stu.id) sum_data from student stu) where sum_data >= 2;

5 查询每个学生的学号、姓名、选修的课程名及成绩查询
select sc.s_id 学号, (select name from student where id = sc.s_id) 姓名, sc.c_id 课程号, (select name from course where id = sc.c_id) 课程名, sc.grade 成绩 from stu_cou sc;

6 查询选修了课程名为“信息系统”的学生学号和姓名
select sc.s_id 学号, (select name from student where id = sc.s_id) 姓名 from stu_cou sc where sc.c_id = (select id from course where name = 'china');