sql

 ----------------------------------------------------------------------
--  表ID      ：yuhlcard
--  表名      ： 借??
----------------------------------------------------------------------
CREATE TABLE kyouiku.yuhlcard
(
    CNO     CHAR(4) NOT NULL,
    CNAME    VARCHAR2(20),
    CLASS    CHAR(3),
    PRIMARY KEY (CNO)
);
----------------------------------------------------------------------
--  表ID      ：yuhlbook
--  表名      ：??
----------------------------------------------------------------------
   
CREATE TABLE kyouiku.yuhlbook
(
    BNO     CHAR(7) NOT NULL,
    BNAME    VARCHAR2(20),
    AUTHOR    VARCHAR2(20),
    PRICE    NUMBER(3,0),
    QUANTITY   NUMBER(3,0),
    PRIMARY KEY (BNO)
);
----------------------------------------------------------------------
--  表ID      ：yuhlborrow
--  表名      ：借???
----------------------------------------------------------------------
   
CREATE TABLE kyouiku.yuhlborrow
(
    CNO    CHAR(4) NOT NULL,
    BNO    CHAR(7) NOT NULL,
    RDATE   DATE,
    PRIMARY KEY (CNO, BNO),
    FOREIGN KEY (CNO) REFERENCES CARD (CNO),
    FOREIGN KEY (BNO) REFERENCES BOOKS (BNO)
);

 

 

----------------------------------------------------------------------
--  插入借????
----------------------------------------------------------------------
insert into kyouiku.yuhlcard values('0001','David','999');
insert into kyouiku.yuhlcard values('0002','David1','999');
insert into kyouiku.yuhlcard values('0003','David2','999');
insert into kyouiku.yuhlcard values('0004','David3','888');
insert into kyouiku.yuhlcard values('0005','Lily','888');
insert into kyouiku.yuhlcard values('0006','Lily1','888');
insert into kyouiku.yuhlcard values('0007','Mairy','777');

----------------------------------------------------------------------
--  插入????
----------------------------------------------------------------------
insert into kyouiku.yuhlbook values('2007001','Computer1','Lucy1','60','150');
insert into kyouiku.yuhlbook values('2007002','Computer2','Lucy1','60','150');
insert into kyouiku.yuhlbook values('2007003','Computer3','Lucy3','300','200');
insert into kyouiku.yuhlbook values('2007004','Computer4','Lucy4','150','100');
insert into kyouiku.yuhlbook values('2007005','Computer5','Lucy3','20','150');
insert into kyouiku.yuhlbook values('2007006','Computer6','Lucy1','20','100');
insert into kyouiku.yuhlbook values('2007007','Computer7','Tory','30','50');
insert into kyouiku.yuhlbook values('2007008','NetWork','Tory','60','30');
----------------------------------------------------------------------
--  插入借???
----------------------------------------------------------------------
insert into kyouiku.yuhlborrow values('001','2007001','20070305');
insert into kyouiku.yuhlborrow values('001','2007002','20070305');
insert into kyouiku.yuhlborrow values('001','2007003','20070305');
insert into kyouiku.yuhlborrow values('002','2007004','20070305');
insert into kyouiku.yuhlborrow values('003','2007005','20070305');
insert into kyouiku.yuhlborrow values('003','2007006','20070305');
insert into kyouiku.yuhlborrow values('007','2007007','20070305');

 

 

 

----------------------------------------------------------------------
--  ???名包括"Com"???的??，?出?号、?名、作者
----------------------------------------------------------------------
select bno,bname,author from kyouiku.yuhlbook where bname like '%Com%';
----------------------------------------------------------------------
-- ???有??中价格最高的??，?出?名及作者
----------------------------------------------------------------------
select bname,author from kyouiku.yuhlbook where price=(select max(price) from yuhlbook);
----------------------------------------------------------------------
--  ??当前借有"Computer5"或者"Computer3"之一的?者，?出其借??号和??的作者，并按?号降序排序?出
----------------------------------------------------------------------
select yuhlborrow.cno,yuhlbook.author from kyouiku.yuhlbook,kyouiku.yuhlborrow where yuhlbook.bno=yuhlborrow.bno and (yuhlbook.bname='Computer5' or yuhlbook.bname='Computer3') order by yuhlborrow.cno desc;
----------------------------------------------------------------------
--  ??学生分布在多少个不同的班?
----------------------------------------------------------------------
select count(distinct class) from kyouiku.yuhlcard ;
----------------------------------------------------------------------
--  ??作者?“Tory”的所有??及其??日期
----------------------------------------------------------------------
select yuhlbook.bno,yuhlbook.bname,yuhlbook.author,yuhlbook.price,yuhlbook.quantity,yuhlborrow.rdate from kyouiku.yuhlbook left outer join kyouiku.yuhlborrow on yuhlbook.bno=yuhlborrow.bno where yuhlbook.author='Tory';

 

 ??：exp kyouiku/kyouiku@jiaoyu_192.168.3.78 full=Y file="d:/yhl.dmp";
 恢?：imp kyouiku/kyouiku@jiaoyu_192.168.3.78 file="d:/yhl.dmp" tables=(yuhlborrow);