oracle中建立学生表,oracle建立学生，课程以及选课表并进行插入查询操作

--建立学生表

CREATE TABLE students(

id NUMBER(10) ,

name VARCHAR2(20) CONSTRAINT students_name_nn NOT NULL,

gender VARCHAR2(4) NOT NULL,

birthday DATE CONSTRAINT students_birthday_nn NOT NULL,

political VARCHAR2(20),

CONSTRAINT students_id_pk PRIMARY KEY(id),

CONSTRAINT students_gender_ck CHECK(gender IN('男','女'))

);

--建立课程表

CREATE TABLE course(

id NUMBER(10),

name VARCHAR2(20)  NOT NULL,

credit NUMBER CONSTRAINT course_credit_nn NOT NULL,

studyhours NUMBER CONSTRAINT course_studyhours_nn NOT NULL,

semester VARCHAR2(20)CONSTRAINT course_semester_nn NOT NULL,

CONSTRAINT course_id_pk PRIMARY KEY(id),

CONSTRAINT course_name_uk UNIQUE(name)

);

--建立选课表

CREATE TABLE elective(

student_id NUMBER NOT NULL,

course_id NUMBER NOT NULL,

score NUMBER CONSTRAINT elective_score_nn NOT NULL,

CONSTRAINT elective_sid_fk FOREIGN KEY(student_id) REFERENCES students(id) on delete cascade,

CONSTRAINT elective_cid_fk FOREIGN KEY(course_id) REFERENCES course(id) on delete cascade

);

--建立学生id序列

create sequence students_id

increment by 1

start with 100000

maxvalue 99999999

minvalue 1

--建立课程id序列

create sequence course_id

increment by 1

start with 1

maxvalue 99999999

minvalue 1

--select students_id.nextval from dual

--select course_id.currval from dual

--插入学生

INSERT INTO students (id, name, gender, birthday, political)

VALUES (students_id.nextval,'lala','男',TO_DATE('1983-05-21','yyyy-mm-dd'),'党员')

INSERT INTO students (id, name, gender, birthday, political)

values (students_id.nextval,'haha','女',to_date('1984-02-28','yyyy-mm-dd'),'群众')

INSERT INTO students (id, name, gender, birthday, political)

VALUES (students_id.nextval,'yaya','男',TO_DATE('1983-05-21','yyyy-mm-dd'),'党员')

INSERT INTO students (id, name, gender, birthday, political)

VALUES (students_id.nextval,'plutom','男',TO_DATE('1985-05-21','yyyy-mm-dd'),'团员')

INSERT INTO students (id, name, gender, birthday, political)

VALUES (students_id.nextval,'youyou','女',TO_DATE('1983-07-21','yyyy-mm-dd'),'党员')

INSERT INTO students (id, name, gender, birthday, political)

VALUES (students_id.nextval,'the','女',TO_DATE('1984-04-21','yyyy-mm-dd'),'群众')

select * from students

--插入课程

INSERT INTO course

VALUES (course_id.nextval,'大学物理',5,40,'大一上学期')

INSERT INTO course

VALUES (course_id.nextval,'大学英语',5,60,'大一上学期')

INSERT INTO course

VALUES (course_id.nextval,'C语言',5,80,'大一上学期')

INSERT INTO course

VALUES (course_id.nextval,'大学体育',4,36,'大一上学期')

INSERT INTO course

VALUES (course_id.nextval,'高等代数',5,70,'大一上学期')

INSERT INTO course

VALUES (course_id.nextval,'大学语文',4,25,'大一上学期')

select * from course

--插入选课

INSERT INTO elective VALUES(100021,21,70)

INSERT INTO elective VALUES(100021,22,86)

INSERT INTO elective VALUES(100021,23,79)

INSERT INTO elective VALUES(100021,24,80)

INSERT INTO elective VALUES(100021,25,65)

INSERT INTO elective VALUES(100022,21,80)

INSERT INTO elective VALUES(100022,23,55)

INSERT INTO elective VALUES(100022,24,58)

select * from elective

--修改学生出生日期

UPDATE students

SET birthday=to_date('1983-06-23','yyyy-mm-dd')

WHERE id=100022

--修改某门课程的学时

UPDATE course

SET studyhours=60

WHERE id=21

--查找那些学生没有选课

SELECT * from students

WHERE id not in (select student_id

from elective)

--删除某名学生

DELETE FROM students

WHERE id=100022;

--查找哪些学生有补考

SElECT * FROM students,elective

WHERE students.id=elective.student_id

AND elective.score<60;

--若某生有两门课程的成绩低于60分将其选课信息删除；

DELETE FROM elective

WHERE student_id IN (SELECT DISTINCT e1.student_id

FROM elective e1,elective e2

WHERE e1.student_id=e2.student_id

AND e1.course_id<>e2.course_id

AND e1.score<60

AND e2.score<60)

--查询某个学生选了哪些课程；

SELECT s.id,s.name,e.course_id,c.name

FROM students s,course c,elective e

WHERE e.student_id=s.id

AND e.course_id=c.id

--查询各门课程的平均分数；

SELECT (select name from course where e.course_id=id) course,AVG(e.score) avg FROM elective e

GROUP BY e.course_id

--查询每门课程的及格率，并以  ××课程的及格率为**%格式显示；

select (select name from course where id=e.course_id)||'课程的及格率为 '||

((select count(*) from elective where score>=60 and e.course_id=course_id group by course_id )/count(*))*100||'%' 每门课程的及格率

from elective e

group by e.course_id

--查找哪些学生选了甲(100022)所学的全部课程；

select distinct s.*

from elective e1,students s

where s.id=e1.student_id

and not exists

(select *

from elective e2

where e2.student_id=100022

and not exists

(select *

from elective e3

where e3.student_id= e1.student_id

and e2.course_id= e2.course_id));

--哪些学生选了所有的课程(与8类似)。

select *

from students s

where not exists

(select *

from course c

where not exists

(select *

from elective

where student_id=s.id

and course_id=c.id));

--创建一个视图，该视图(课程名称，及格率)体现每门课程的及格率。

create view rate

as

select (select name from course where id=e.course_id) 课程,

((select count(*) from elective where score>=60 and e.course_id=course_id group by course_id )/count(*))*100||'%' 及格率

from elective e

group by e.course_id

select * from rate