use tsjy
create table ts(bh char(6) primary key,flh char(8),tsmc char(16),zz char(6),cbdw char(16),dj numeric(7,2) ,cszl int,xykc int)
create table dz(jszh char(4) primary key,dw char(10),xm char(6),xb  char(2),zz char(6),dz char(16) )
EXEC sp_addtype rq, datetime, 'NULL'  --自定义类型rq
create table jy (jszh char (4),bh char(6),jyrq rq,primary key (bh,jszh,jyrq),
	foreign key (bh) references ts(bh),
	foreign key (jszh) references dz(jszh))
create table gly (zgh char(6) primary key,xm char(6),xb char(2) default '男',csrq rq,zc char(6),jbgz numeric(7,2))
--2
select * from ts where zz like '刘%'	
--3
select * from ts where cbdw='高等教育出版社出版' and dj<25
--4
select count(*) from dz			
--5
select max(dj) zgj ,min(dj) zdj ,avg(dj) pjj from ts
--6
select jszh,count(*) from jy where jszh='112'
--7
select flh ,tsmc ,zz from ts order by flh desc
--8
select * from ts where cbdw='高等教育出版社' order by dj
--9
select * from ts where cbdw in('清华大学出版社','电子工业出版社') order by dj
--10
select tsmc,zz,count(*) from ts group by tsmc,zz
--11
select count(*) from ts where dj between 15 and 25
--12
select * from ts where tsmc like '应用基础%'
--13
select jszh,count(*) from jy group by jszh
--14
select dw,count(*) from dz group by dw
--15
select jszh,count(*) from jy where jyrq<'1997-12-31'  group by jszh having count(*)>1
--16
select jszh,jy.bh,jyrq,flh,zz,cbdw,dj,cszl,xykc from jy,ts where jy.bh=ts.bh
--17
select jy.jszh,xm,dw from jy,dz where jy.jszh=dz.jszh
--18
select jy.jszh,xm,dw,tsmc from jy,dz,ts where jy.jszh=dz.jszh and jy.bh=ts.bh
--19
select * from ts where exists(select 1 from jy where bh=ts.bh)
--20
select dw,count(*) from dz,jy where dz.jszh=jy.jszh group by dw
--21
select dw,count(*) from dz where jszh in(select distinct jszh from jy) group by dw
--22
select * from dz where jszh in (select distinct jszh from jy )
--23
select * from gly where zc in('教授','副教授') and csrq>='1950-01-01'
--24
select * from gly where jbgz between 250 and 380
--25
update gly set jbgz=jbgz+100 where jbgz<500
--26
select tsmc,zz,cbdw from ts where  cszl>10
--27
select xykc from ts where tsmc='数据库系统原理教程' and zz='王珊' and cbdw='清华大学出版社'