Liny_@NotePad

沉迷ACG中

数据库对象练习

YOYO posted @ 2009年3月15日 22:39 in 【数据库】 with tags oracle 练习 , 2132 阅读

【HR/HR】

1、用学生选课场景创建表：
 1.1
 Student（id,sno,sname,telnum,email）
 Course（id,cno,cname,teachername）
 studentcourse(id,s_id,c_id)

  1. CREATE TABLE student(
  2. id NUMBER,
  3. sno VARCHAR2 (15) NOT NULL,
  4. sname VARCHAR2 (20) NOT NULL,
  5. telnum VARCHAR2 (12),
  6. email VARCHAR2 (50)
  7. );
  8. CREATE TABLE course(
  9. id NUMBER,
  10. cno VARCHAR2 (6) NOT NULL,
  11. cname VARCHAR2 (30) NOT NULL,
  12. teachername VARCHAR2(20)
  13. );
  14. CREATE TABLE studentcourse(
  15. id NUMBER,
  16. s_id NUMBER,
  17. c_id NUMBER
  18. );

1.2  为这三表建立主键和外键

  1. ALTER TABLE student ADD CONSTRAINT pkey_sid PRIMARY KEY(id);
  2. ALTER TABLE course ADD CONSTRAINT pkey_cid PRIMARY KEY(id);
  3. ALTER TABLE studentcourse ADD CONSTRAINT pkey_scid PRIMARY KEY(id);
  4.  
  5. ALTER TABLE studentcourse ADD CONSTRAINT fkey_sid FOREIGN KEY(s_id) REFERENCES student(id);
  6. ALTER TABLE studentcourse ADD CONSTRAINT fkey_cid FOREIGN KEY(c_id) REFERENCES course(id);

1.3    创建1个sequence  :seq_stu_crs

  1. CREATE SEQUENCE seq_stu_crs
  2. START WITH 1
  3. INCREMENT BY 1;
1.4    为三个表杜撰一批数据
  1. INSERT INTO student(id,sno,sname,telnum,email)
  2. VALUES (1,'12411200601','linyq','122','yahreso@qq.com');
  3. INSERT INTO student(id,sno,sname,telnum,email)
  4. VALUES (2,'12411200602','Cyin','123','');
  5. INSERT INTO student(id,sno,sname,telnum,email)
  6. VALUES (3,'12411200603','lee','124','');
  7. INSERT INTO student(id,sno,sname,telnum,email)
  8. VALUES (4,'12411200604','skittles','','');
  9. -- 这边的id本来应该再建一个sequence来nextval的 因为懒所以忽略了
  10.  
  11. INSERT INTO course(id,cno,cname,teachername)
  12. VALUES (1,'IT0631','Oracle','Wu Gang');
  13. INSERT INTO course(id,cno,cname,teachername)
  14. VALUES (2,'IT0634','Algorithm','Chen Haixia');
  15. INSERT INTO course(id,cno,cname,teachername)
  16. VALUES (3,'IT0633','Software Engieering','Zhou Lizhen');
  17. -- 这边的id本来应该再建一个sequence来nextval的 因为懒所以忽略了
  18.  
  19. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,1,1);
  20. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,1,2);
  21. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,1,3);
  22. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,2,1);
  23. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,3,1);
  24. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,2,3);
  25. INSERT INTO studentcourse(id, s_id, c_id) VALUES (seq_stu_crs.NEXTVAL,4,2);

1.4.1  为student.telnum列建立普通B-TREE索引

  1. CREATE INDEX sort_tel
  2. ON student(telnum);

1.5    输出学生选课具体信息报表（学生名字，课程名字，教师名字）

  1. SELECT sname, cname, teachername
  2. FROM student, course, studentcourse sc
  3. WHERE student.id = sc.s_id AND course.id = sc.c_id;

1.6    创建一个只读视图来固化1.5的报表 v_student_course

  1. CREATE VIEW v_student_course AS
  2. SELECT sname, cname, teachername
  3. FROM student, course, studentcourse sc
  4. WHERE student.id = sc.s_id AND course.id = sc.c_id
  5. WITH READ ONLY;

1.6.1  为v_student_course建立同义词 ssc，并把该同义词的查询权限授权给hr用户

  1. CREATE SYNONYM ssc FOR v_student_course;
  2.  
  3. GRANT SELECT ON ssc TO hr;

1.7    为Course表创建一个唯一性索引(teachername)，因为要求一个老师只能教一门课

  1. CREATE UNIQUE INDEX sort_tcname
  2. ON course(teachername);

1.8    用Delete 删除studentcourse表，分别回滚，和提交

  1. savepoint a;
  2. DELETE FROM studentcourse;
  3. ROLLBACK TO a;

1.9    用Truncate 删除studentcourse数据

  1. TRUNCATE TABLE studentcourse;

1.10   通过对应的数据字典查找上述3个表的信息，并做出解释

  1. SELECT * FROM user_tables;

1.11   删除3个表的所有约束

  1. ALTER TABLE studentcourse DROP CONSTRAINT fkey_sid;
  2. ALTER TABLE studentcourse DROP CONSTRAINT fkey_cid;
  3. ALTER TABLE student DROP CONSTRAINT pkey_sid;
  4. ALTER TABLE course DROP CONSTRAINT pkey_cid;

1.12   删除3个表，并再次查看对应的数据字典信息

  1. DROP TABLE student;
  2. DROP TABLE course;
  3. DROP TABLE studentcourse;
  4.  
  5. SELECT * FROM user_tables;
Small_feed 评论 (0)

登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter