/*

借书卡表(CARD)。CNO(卡号),NAME(姓名),CLASS(班级)

图书表(BOOKS)。BNO(书号),BNAME(书名),AUTHOR (作者),

PRICE(单价),QUANTITY(库存册数) 

借书记录表(BORROW)。CNO(借书卡号),BNO(书号),RDATE(还书日期)

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

问题：

查询借阅了"水浒"一书的读者，输出姓名及班级

*/

--建表

CREATE TABLE CARD

(CNO CHAR(4) PRIMARY KEY,

NAME VARCHAR(20),

CLASS VARCHAR(20))

CREATE TABLE BOOKS

(BNO CHAR(4) PRIMARY KEY,

BNAME VARCHAR(30),

AUTHOR VARCHAR(20),

PRICE FLOAT,

QUANTITY INT)

CREATE TABLE BORROW

(CNO CHAR(4) PRIMARY KEY,

BNO CHAR(4),

RDATE DATETIME)

--插入数据

INSERT INTO dbo.CARD

( CNO, NAME, CLASS )

VALUES 

('0001','张三','三（5）班'),

('0002','李四','二（8）班'),

('0003','王五','三（1）班'),

('0004','马六','一（3）班'),

('0005','赵七','三（6）班'),

('0006','吴八','二（2）班')

INSERT INTO dbo.BOOKS

( BNO, BNAME, AUTHOR, PRICE, QUANTITY )

VALUES  

('0001','西游记','吴承恩',50.49,221),

('0002','红楼梦','曹雪芹/高鹗',120.62,453),

('0003','三国演义','罗贯中',68.21,148),

('0004','水浒','施耐庵',88.47,341)

INSERT INTO dbo.BORROW

( CNO, BNO, RDATE )

VALUES  

('0003','0001','2017-10-28' ),

('0002','0004','2017-11-12' ),

('0004','0003','2017-9-12' ),

('0006','0004','2017-11-3' ),

('0005','0002','2017-12-15' )

--查询

SELECT c.NAME,c.CLASS FROM dbo.CARD c

JOIN dbo.BORROW b ON b.CNO = c.CNO

JOIN dbo.BOOKS  s ON s.BNO = b.BNO

WHERE s.BNAME='水浒';

--清除表

DROP TABLE dbo.CARD;

DROP TABLE dbo.BOOKS;

DROP TABLE dbo.BORROW;