oracle学生-教师-班级表

a)      建立下列管理用的数据表。注意，表名和字段名都是英文。

学生表（student）

字段	名称	数据类型	约束
学号	S_NO	CHAR(6)	主键
姓名	S_NAME	CHAR(10)	非空
性别	S_SEX	CHAR(2)	只取男、女
出生日期	S_BIRTHDAY	DATE
入学成绩	S_SCORE	NUMBER(5,1)
附加分	S_ADDF	NUMBER(5,1)
班级编码	CLASS_NO	CHAR(5)	外键，关联班级表CLASS_NO

班级表（class）

字段	名称	数据类型	约束
班级编码	CLASS_NO	CHAR(5)	主键
班级名	CLASS_NAME	CHAR(10)	非空
所属系部	DEPT_NO	CHAR(2)	外键，关联系部表DEPT_NO

系部表（department）

字段	名称	数据类型	约束
系部编码	DEPT_NO	CHAR(2)	主键
系部名	DEPT_NAME	CHAR(10)	非空

课程表（course）

字段	名称	数据类型	约束
课程编码	COURSE_NO	CHAR(5)	主键
课程名	COURSE_NAME	CHAR(20)	非空

教师表（teacher）

字段	名称	数据类型	约束
教师编码	T_NO	CHAR(6)	主键
教师姓名	T_NAME	CHAR(8)	非空
性别	T_SEX	CHAR(2)	只取男、女
出生日期	T_BIRTHDAY	DATE
职称	TECH_TITLE	CHAR(10)

选修表（choice）

字段	名称	数据类型	约束
学生编码	S_NO	CHAR(6)	主键，与学生表中S_NO外键关联，级联删除
课程编码	COURSE_NO	CHAR(5)	主键，与课程表中COURSE_NO外键关联
成绩	SCORE	NUMBER(5,1)

授课表（teaching）

字段	名称	数据类型	约束
教师编码	T_NO	CHAR(6)	主键，与教师表中T_NO外键关联，级联删除
课程编码	COURSE_NO	CHAR(5)	主键，与课程表中COURSE_NO外键关联

 

b)      为每个表添加1至2条测试数据。

c)      使用字典表user_constraints和user_cons_columns 查询学生表上有哪些约束？

d)      通过复制学生表和班级表创建 copy_student 、copy_class，并为复制的表添加和学生表和班级表相同的约束。班级表DEPT_NO 的外键不需要添加。

e)      使用字典表user_constraints和user_cons_columns 查询确认约束添加成功。

f)       删除copy_student、copy_class上的约束，使用字典表user_constraints和 user_cons_columns查询确认约束删除成功。

g)      查询编号为c002班级中所有女同学；

h)      查询所有学生出生日期，要求输出格式为'yyyy-mm-dd'；

i)       查询总分（入学成绩+附加分）在550和600之间的所有学生；

j)       查询名字中带“小”字的所有学生；

k)      查询所有学生，按总分（入学成绩+附加分）降序排列；

l)       查询入学成绩大于平均入学成绩的所有学生；

[sql]  view plain  copy

 

1. --删除表  
2. DROP TABLE department CASCADE CONSTRAINT;  
3. DROP TABLE course CASCADE CONSTRAINT;  
4. DROP TABLE teacher CASCADE CONSTRAINT;  
5. DROP TABLE class CASCADE CONSTRAINT;  
6. DROP TABLE student CASCADE CONSTRAINT;  
7. DROP TABLE choice CASCADE CONSTRAINT;  
8. DROP TABLE teaching CASCADE CONSTRAINT;  
9.   
10. --创建表  
11. CREATE TABLE department(  
12.        dept_no char(2) CONSTRAINT pk_department_dept_no PRIMARY KEY,  
13.        dept_name char(10) CONSTRAINT nn_department_dept_name NOT NULL  
14. );  
15. CREATE TABLE course(  
16.        course_no char(5) CONSTRAINT pk_course_course_no PRIMARY KEY,  
17.        course_name char(20) CONSTRAINT nn_course_course_name NOT NULL  
18. );  
19. CREATE TABLE teacher(  
20.        t_no CHAR(6) CONSTRAINT pk_teacher_t_no PRIMARY KEY,  
21.        t_name CHAR(8) CONSTRAINT nn_teacher_t_name NOT NULL,  
22.        t_sex NCHAR(1) CONSTRAINT ck_teacher_t_sex CHECK(t_sex IN('男','女')),  
23.        t_birthday DATE，  
24.        tech_title CHAR(10)  
25. );  
26. CREATE TABLE class(  
27.        class_no CHAR(5) CONSTRAINT pk_class_class_no PRIMARY KEY,  
28.        class_name CHAR(10) CONSTRAINT nn_class_class_name NOT NULL,  
29.        dept_no CHAR(2)  
30. );  
31. CREATE TABLE student(  
32.        s_no CHAR(6) CONSTRAINT pk_student_s_no PRIMARY KEY,  
33.        s_name CHAR(10) CONSTRAINT nn_student_s_name NOT NULL,  
34.        s_sex NCHAR(2) CONSTRAINT ck_student_s_sex CHECK(s_sex IN('男','女')),  
35.        s_birthday DATE,  
36.        s_score NUMBER(5,1),  
37.        s_addf NUMBER(5,1),  
38.        class_no CHAR(5)  
39. );  
40. CREATE TABLE choice(  
41.        s_no CHAR(6),  
42.        course_no CHAR(5),         
43.        score NUMBER(5,1),  
44.        CONSTRAINT pk_choice_s_no_course_no PRIMARY KEY(s_no,course_no)  
45. );  
46. CREATE TABLE teaching(  
47.        t_no CHAR(6),  
48.        course_no CHAR(5),  
49.        CONSTRAINT pk_teaching_t_no_course_no PRIMARY KEY(t_no,course_no)  
50. );  
51. --向表中插入记录  
52. INSERT INTO department VALUES('01','工商');  
53. INSERT INTO department VALUES('02','旅游');  
54. INSERT INTO department VALUES('03','信息');  
55. INSERT INTO department VALUES('04','机电');  
56. INSERT INTO department VALUES('05','汽车');  
57.   
58. INSERT INTO course VALUES('01','C语言');  
59. INSERT INTO course VALUES('02','营养与健康');  
60. INSERT INTO course VALUES('03','大学英语');  
61. INSERT INTO course VALUES('04','线性代数');  
62. INSERT INTO course VALUES('05','计算机网络');  
63.   
64. INSERT INTO teacher VALUES('0101','司马','男',TO_DATE('13-07-1987','dd-mm-yyyy'),'甲');  
65. INSERT INTO teacher VALUES('0201','诸葛','男',TO_DATE('21-03-1976','dd-mm-yyyy'),'乙');  
66. INSERT INTO teacher VALUES('0302','慕容','女',TO_DATE('7-05-1985','dd-mm-yyyy'),'丙');  
67. INSERT INTO teacher VALUES('0403','夏侯','男',TO_DATE('18-08-1982','dd-mm-yyyy'),'甲');  
68. INSERT INTO teacher VALUES('0502','欧阳','女',TO_DATE('25-02-1989','dd-mm-yyyy'),'丙');  
69.   
70. INSERT INTO class VALUES('1101','工商','01');  
71. INSERT INTO class VALUES('1201','旅游','02');  
72. INSERT INTO class VALUES('1102','信息','03');  
73. INSERT INTO class VALUES('1202','机电','04');  
74. INSERT INTO class VALUES('1103','汽车','05');  
75.   
76. INSERT INTO student VALUES('0001','小明','男',TO_DATE('13-07-1990','dd-mm-yyyy'),89,12,'1101');  
77. INSERT INTO student VALUES('0002','小红','女',TO_DATE('13-07-1991','dd-mm-yyyy'),83,17,'1201');  
78. INSERT INTO student VALUES('0003','小强','男',TO_DATE('13-07-1990','dd-mm-yyyy'),80,11,'1102');  
79. INSERT INTO student VALUES('0004','小刚','男',TO_DATE('13-07-1991','dd-mm-yyyy'),75,19,'1202');  
80. INSERT INTO student VALUES('0005','小花','女',TO_DATE('13-07-1991','dd-mm-yyyy'),90,15,'1103');  
81.   
82. INSERT INTO choice VALUES('0001','01',98);  
83. INSERT INTO choice VALUES('0002','02',87);  
84. INSERT INTO choice VALUES('0003','03',91);  
85. INSERT INTO choice VALUES('0004','04',94);  
86. INSERT INTO choice VALUES('0005','05',83);  
87.   
88. INSERT INTO teaching VALUES('0101','01');  
89. INSERT INTO teaching VALUES('0201','02');  
90. INSERT INTO teaching VALUES('0302','03');  
91. INSERT INTO teaching VALUES('0403','04');  
92. INSERT INTO teaching VALUES('0502','05');  
93.   
94. --添加外键约束  
95. ALTER TABLE student ADD CONSTRAINT fk_student_class_no FOREIGN KEY(class_no) REFERENCES class(class_no);  
96. ALTER TABLE class ADD CONSTRAINT fk_class_dept_no FOREIGN KEY(dept_no) REFERENCES department(dept_no);  
97. ALTER TABLE choice ADD CONSTRAINT fk_choice_s_no FOREIGN KEY(s_no) REFERENCES student(s_no) ON DELETE CASCADE;  
98. ALTER TABLE choice ADD CONSTRAINT fk_choice_course_no FOREIGN KEY(course_no) REFERENCES course(course_no) ON DELETE CASCADE;  
99. ALTER TABLE teaching ADD CONSTRAINT fk_t_no FOREIGN KEY(t_no) REFERENCES teacher(t_no) ON DELETE CASCADE;  
100. ALTER TABLE teaching ADD CONSTRAINT fk_course_no FOREIGN KEY(course_no) REFERENCES course(course_no) ON DELETE CASCADE;  
101.   
102. SELECT TABLE_NAME FROM USER_TABLES;  
103.   
104. SELECT * FROM department;  
105. SELECT * FROM course;  
106. SELECT * FROM teacher;  
107. SELECT * FROM class;  
108. SELECT * FROM student;  
109. SELECT * FROM choice;  
110. SELECT * FROM teaching;