画出学生选课ER 图要求有如下,学生有如下属性,学生ID, 姓名,年龄,年级; 课程有如下属性, 课程ID,课程名称,学分; 另外还有一个分数属性,根据情况放ER图的元素上;
-
根据ER图 转换为关系模式;
学生(学生ID, 姓名,年龄,年级)
课程(课程ID,课程名称,学分)
选课(学生ID,课程ID,分数)
2.根据关系模式建立数据库的表,写出建表语句;
create table student (s_id varchar2(20), sname varchar2(20), age number,grade varchar2(10));
create table course (c_id varchar2(20),cname varchar2(20),credit number);
create table student_course (s_id varchar2(20),c_id varchar2(20),score number);
3.建表后插入两条学员选课记录,和成绩情况;
insert into student_course values('2021211110','010102',88);
insert into student_course values('2021211111','010103',95);
4.查询选课的详细信息(姓名,年级,年龄,课程名称,选课时间, 学分, 成绩)
select sname,grade,age,cname,time,credit,score
from student s
inner join student_course s_c on s.s_id = s_c.s_id
inner join course c on s_c.c_id = c.c_id;
5.查询选课的详细信息(姓名,年级,年龄,课程名称,选课时间, 学分, 成绩),成绩为null 的显示为0, 按科目,成绩排序倒序排序;(多表查询)
select sname,grade,age,cname,time,credit,nvl(score,0)
from student s
inner join student_course s_c on s.s_id = s_c.s_id
inner join course c on s_c.c_id = c.c_id
order by cname,score desc;
6.查询java 成绩在 80 到90 分之间的 选课信息
select *
from student_course
Inner join course on student_course.c_id = course.c_id
Where course.cname = ‘Java’ and score between 80 and 90;
7.查询未参加考试的学生情况(成绩为null);
select *
from student s
inner join student_course s_c on s.s_id = s_c.s_id
inner join course c on s_c.c_id = c.c_id
where score is null;
8.查询每个科目的最高分,最底分
select cname,max(score),min(score)
from student_course,course
group by cname;
9.查询每个年级,每个科目的最高分,最底分;
select grade,cname,max(score),min(score)
from student s
inner join student_course s_c on s.s_id = s_c.s_id
inner join course c on s_c.c_id = c.c_id
group by grade, cname;
10.查询每个科目的选课人数且选课人数大于2 的科目
select cname,count(*)
from student_course
inner join course on student_course.c_id = course.c_id
group by cname
having count(*) >2 ;
11.查询哪些小伙伴选择了课程;(DISTINCT)
select *
from student
inner join student_course on student.s_id = student_course.s_id
12.查询哪些小伙伴没有选择课程?
select *
from student
inner join student_course on student.s_id = student_course.s_id
inner join course on student_course.c_id = course.c_id
where student_course.c_id is null;
13.查询名字为” XX明”的学生?
select *
from student
where sname like '%明';
14.查询名字为” 张XX”的学生?
select *
from student
where sname like '张%';