oracle学生成绩表的创建,下面是学生成绩表(score)结构说明

-- Create table

create table SC_SCORE

(

SC_NUMBER   VARCHAR2(8),

SC_NAME     VARCHAR2(50),

SC_SEX      VARCHAR2(2),

SC_COURSEID VARCHAR2(5),

SC_SCORE    VARCHAR2(3),

SC_ISMAKEUP VARCHAR2(2),

COURSEID    VARCHAR2(8)

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

-- Add comments to the columns

comment on column SC_SCORE.SC_NUMBER

is '学号';

comment on column SC_SCORE.SC_NAME

is '姓名';

comment on column SC_SCORE.SC_SEX

is '性别';

comment on column SC_SCORE.SC_COURSEID

is '课程号';

comment on column SC_SCORE.SC_SCORE

is '分数';

comment on column SC_SCORE.SC_ISMAKEUP

is '当前考试是否为补考';

insert into sc_course values('101','语文','内容介绍');

insert into sc_course values('102','数学','数学内容介绍');

select *from sc_score

select *from sc_course

--1如果序号的前两位表示年纪，要找出98级的女生的姓名，请写出相应的sql语句

select sc_name

from sc_score

where sc_number like '98%'

and sc_sex = '女'

--2查找所有需要补考的学生姓名和这门课程的名称和成绩

select sc.sc_name, c.co_name, sc.sc_score

from sc_score sc, sc_course c

where sc.courseid = c.co_id

and sc.sc_score < 60

--3查询每个学生需要补考的课程的平均分，并以平均分排序

select sc.sc_name,c.co_name,sc.sc_score

from sc_score sc,sc_course c

where sc.courseid = c.co_id and sc_number in

(select sc_number

from sc_score,sc_course

where courseid = co_id

and sc_score < 60)

--4查询所有参加了补考的学生的补考成绩和非补考成绩

(select sc.sc_name, sc.sc_score

From sc_score sc, sc_course c

where sc.courseid = c.co_id

and sc.sc_ismakeup = '1'

union

select sc.sc_name, sc.sc_score

from sc_score sc, sc_course c

where sc.courseid = c.co_id

and sc.sc_ismakeup = '2') order by sc_score