ITPub博客

--测试数据create table PROJECT(id int,name nvarchar(20),parent_id int)insert PROJECT select 1,'所有项目',nullunion all select 2,'项目1',1union all select 3,'项目2',1create table task(id int,name nvarchar(20),outline varchar(10))insert task select 1 ,'任务1' ,'1'union all select 2 ,'任务1.1' ,'1.1'union all select 3 ,'任务1.1.1' ,'1.1.1'union all select 4 ,'任务1.1.2' ,'1.1.2'union all select 5 ,'任务2' ,'2'union all select 6 ,'任务2.1' ,'2.1'union all sele

--测试数据CREATE TABLE [dbo].[BOM_DETAIL] ( [PKID] [int] ,--IDENTITY (1, 1) NOT NULL , [BOM_HEAD_PKID] [int] NOT NULL , [CHILDREN_ITEM] [int] NOT NULL , [QTY] [decimal](10, 0) NOT NULL ) ON [PRIMARY]CREATE TABLE [dbo].[BOM_HEAD] ( [PKID] [int] ,--IDENTITY (1, 1) NOT NULL , [MASTER_ITEM] [int] NOT NULL , [QTY] [int] NOT NULL , [VER] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [STATUS] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,) ON [PRIMARY]CREATE TABLE [dbo].[item] ( [item] [int] ,

--邹建2003.12(引用请保留此信息)--*/--测试数据create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered,pid int,name varchar(20))insert tb select 0,'中国'union all select 0,'美国'union all select 0,'加拿大'union all select 1,'北京'union all select 1,'上海'union all select 1,'江苏'union all select 6,'苏州'union all select 7,'常熟'union all select 6,'南京'union all select 6,'无锡'union all select 2,'纽约'union all select 2,'旧金山'go--1.自定义函数--获取编码累计create fun

--实现代码：ALTER TABLE CARD ADD 系名 varchar(20)[@more@]

--实现代码：ALTER TABLE CARD ALTER COLUMN NAME varchar(10)[@more@]

--实现代码：ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)[@more@]

--实现代码：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME IN(N'计算方法',N'组合数学')GROUP BY a.CNOHAVING COUNT(*)=2ORDER BY a.CNO DESC[@more@]

--实现代码：CREATE VIEW V_VIEWASSELECT a.NAME,b.BNAMEFROM BORROW ab,CARD a,BOOKS bWHERE ab.CNO=a.CNO AND ab.BNO=b.BNO AND a.CLASS=N'力01'[@more@]

--实现代码：CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)[@more@]

--实现代码：CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF @@ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i.BNO=b.BNO AND b.BNAME=N'数据库技术及应用'[@more@]

--实现代码：UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)FROM CARD a,BORROW bWHERE a.CNO=b.CNO AND a.CLASS=N'C01'[@more@]

--实现代码：DELETE A FROM BOOKS aWHERE NOT EXISTS( SELECT * FROM BORROW WHERE BNO=a.BNO)[@more@]

--实现代码：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME=N'计算方法' AND NOT EXISTS( SELECT * FROM BORROW aa,BOOKS bb WHERE aa.BNO=bb.BNO AND bb.BNAME=N'计算方法习题集' AND aa.CNO=a.CNO)ORDER BY a.CNO DESC[@more@]

--实现代码：SELECT * FROM BORROW WHERE RDATE<GETDATE()[@more@]

--实现代码：SELECT * FROM CARD cWHERE EXISTS( SELECT * FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME=N'水浒' AND a.CNO=c.CNO) [@more@]

--实现代码：SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE BNAME LIKE N'%网络%'[@more@]

--实现代码：SELECT CNO,借图书册数=COUNT(*)FROM BORROWGROUP BY CNOHAVING COUNT(*)>5[@more@]

--实现代码：CREATE TABLE BORROW( CNO int FOREIGN KEY REFERENCES CARD(CNO), BNO int FOREIGN KEY REFERENCES BOOKS(BNO), RDATE datetime, PRIMARY KEY(CNO,BNO))[@more@]

问题描述：本题用到下面三个关系表：CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。要求实现如下15个处理： 1． 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。 2． 找出借书超过5本的读者,输出借书卡号及所借图书册数。 3． 查询借阅了"水浒"一书的读者，输出姓名及班级。

--实现代码：SELECT S.SNO,S.SNAMEFROM S,SC SC1,SC SC2 WHERE SC1.CNO='1' AND SC2.SNO='2' AND SC1.CNO=S.CNO AND SC1.SCGRADE>SC2.SCGRADE[@more@]