oracle9i学习笔记之十三 SQL练习

 1.图书借阅系统包含以下表：
    会员表（member）
    图书表（title）
    库存图书备份表（title_copy）
    图书借阅表（borrow）
    预定表（reservation）

2.表信息
1)会员表包含以下字段：
  会员ID(序列号)、会员名、地址、城市、电话、加入时间（默认为系统时间）、会员ID是主键

2)图书表包含以下字段：
  图书编号(序列号)、书名、书的描述信息、出版时间、作者、图书的类别（只能是'java'、'c'、'c++'、'db'、'web'）图书编号为主键

3.库存备份表包含以下信息：
  图书编号、库存数量（最低数量为0）、缺少数量（默认为0），图书编号为主键

4.图书借阅表包含以下字段（每个会员可以借阅多本书）：
  序列号、图书编号、会员ID、借阅日期（默认为系统当前日期）、还书日期、到期日期（默认为系统单签日期后的一个月），序列号为主键

5.预定表包含以下字段（每个会员可以预定多本书）：
  会员ID、图书编号、预定日期，会员ID、图书编号、预定日期为联合主键

3.操作步骤
1)创建用户neu，分配用户create session、unlimited tablespace、create table、createsequence、create view权限

  SQL>CREATE USER neusoft IDENTIFIED BY neu;

  SQL> GRANT create session,unlimited tablespace,create table,
             create sequence,create view
       TO    neu;

2)创建各个表，选择适当的数据类型并添加完整性约束

   CREATE TABLE member
    (member_id  NUMBER(10) CONSTRAINT member_id_pk PRIMARY KEY,
     m_name  VARCHAR2(25) NOT NULL,
     address  VARCHAR2(100),
     city  VARCHAR2(30),
     phone  VARCHAR2(15),
     join_date  DATE DEFAULT SYSDATE NOT NULL);

  CREATE TABLE title
  (title_id    NUMBER(10) CONSTRAINT title_id_pk PRIMARY KEY,
   title       VARCHAR2(60) NOT NULL,
   description VARCHAR2(400) NOT NULL,
   pub_date    DATE,
   author      VARCHAR2(40),
   label       VARCHAR2(20) CONSTRAINT label_ck
                            CHECK(label IN('java','c','c++','db','web')));

  CREATE TABLE title_copy
    (title_id  NUMBER(10) CONSTRAINT title_bak_pk PRIMARY KEY,
     bak_num   NUMBER(10) CONSTRAINT bak_num_c CHECK(bak_num>=0),
     lack_num  NUMBER(10) DEFAULT 0,
     CONSTRAINT title_id_bak_fk FOREIGN KEY(title_id) REFERENCES title(title_id));

   CREATE TABLE  reservation
    (member_id   NUMBER(10) CONSTRAINT r_member_id_fk REFERENCES member(member_id),
     title_id   NUMBER(10) CONSTRAINT r_title_id_fk REFERENCES title(title_id), 
     res_date   DATE,
     CONSTRAINT res_s_pk PRIMARY KEY(member_id,title_id, res_date));

   CREATE TABLE borrow
   (borrow_seq  NUMBER(10) CONSTRAINT borrow_seq_pk PRIMARY KEY,
    title_id    NUMBER(10) CONSTRAINT b_title_id_fk REFERENCES title(title_id),
    borrow_date DATE DEFAULT SYSDATE,
    return_date DATE,
    exp_date    DATE DEFAULT ADD_MONTHS(SYSDATE,1));
  
3)检查数据字典，验证表和约束已被完全创建

   SELECT * FROM user_tables;
   SELECT * FROM user_constraints;

   或：
   SELECT table_name
   FROM   user_tables
   WHERE  table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY', 'BORROW', 'RESERVATION');

   SELECT constraint_name, constraint_type, table_name
   FROM user_constraints
   WHERE table_name IN ('MEMBER', 'TITLE', 'TITLE_COPY', 'BORROW', 'RESERVATION');

4)创建序列来唯一地标识在会员表、图书表、图书借阅表中的每一行

   CREATE SEQUENCE member_seq
          INCREMENT BY 1
          START WITH 1
          NOCACHE;

   CREATE SEQUENCE title_seq
          INCREMENT BY 1
          START WITH 1
          NOCACHE;

   CREATE SEQUENCE borrow_seq
          INCREMENT BY 1
          START WITH 1
          NOCACHE;
5)验证在数据字典中已经存在的序列

   SELECT * FROM user_sequences;

   或：
   SELECT sequence_name, increment_by, last_number
   FROM   user_sequences
   WHERE  sequence_name IN ('MEMBER_SEQ','TITLE_ID_SEQ','BORROW_SEQ'); 

6)添加数据到表中，注意：数据的添加要使用序列

 
   INSERT INTO title
   VALUES (title_seq.NEXTVAL, '世界是平的','介绍世界为什么是平的','14-8月-07','xiyou','java');
 
   INSERT INTO member
   VALUES (member_seq.NEXTVAL, 'yang','陕西韩城','韩城',83660002,sysdate);

   INSERT INTO title_copy
   VALUES (title_seq.CURRVAL,10,0);

   INSERT INTO borrow(borrow_seq, title_id , member_id , borrow_date, exp_date )
   VALUES (borrow_seq.NEXTVAL, title_seq.CURRVAL,member_seq.currval, sysdate, ADD_MONTHS(SYSDATE,1));

   INSERT INTO reservation
   VALUES (member_seq.currval,title_seq.CURRVAL, sysdate);

7)创建一个名为title_avail的视图，显示书的编号、书名、出版时间和书的库存数量，从视图中查询所有出版日期大于2007-01-01的数据，用书的编号排序

   CREATE OR REPLACE VIEW title_avail
   AS
 SELECT t.title_id ,t.title, c.bak_num , t.pub_date
 FROM title t, title_copy c
 WHERE t.title_id = c.title_id;

   SELECT *
   FROM   title_avail
   WHERE  pub_date>TO_DATE('2007-01-01','yyyy-mm-dd')
   ORDER BY title_id;

8)在图书借阅表上以会员ID创建非惟一索引
  
   CREATE INDEX bow_mem_idx
   ON borrow(member_id );

9)更新库存图书备份表，将其中一本书名为XXX的库存量在原有基础上增加10本

   UPDATE title_copy
   SET    bak_num=bak_num+10
   WHERE  title_id=
                (SELECT title_id
                 FROM   title
                 WHERE  title='世界是平的')

10)修改表结构，添加一个price列到title表，记录书的购买价格。该列应该有8位数字长度和2位小数，验证你的修改。

   ALTER TABLE title
     ADD (price NUMBER(8,2));

   DESC title

11)为每本书添加价格

    UPDATE title SET price=50 WHERE title_id=1;

12)更改title表中价格字段为非空，验证该约束

   ALTER TABLE title MODIFY (price NOT NULL);
  
   SELECT * FROM user_constraints;
   或：
   SELECT constraint_name, constraint_type, search_condition
     FROM user_constraints
    WHERE table_name = 'TITLE';
  
13)查询客户租书信息，包含客户编号、客户名、书名、书的价格、借阅日期、还书日期、到期日期
   SELECT m.member_id,m.m_name,t.title,t.price,b.borrow_date,b.return_date,b.exp_date
     FROM member m,title t,borrow b
    WHERE m.member_id=b.member_id
      AND t.title_id=b.title_id;
14)删除各个表中的数据

   DELETE FROM borrow;
   DELETE FROM reservation;
   DELETE FROM title_copy;
   DELETE FROM title;
   DELETE FROM member;

15)删除member表上的主键约束，并且任何与其相依赖的约束也被一起删除

   ALTER TABLE member
          DROP PRIMARY KEY CASCADE;