数据库原理实验报告

二 实验结果

/* 1 */ /*(1)*/

create database student use student

create table T0418(

Title varchar(30), author varchar(15), t_no char(6),

price numeric(6,2) )

insert into T0418(Title,author,t_no,price) select '计算机原理','张一平','S3092','' union all

select 'C语言程序设计','李华','H1298','' union all

select '数据库原理','王家树','D1007','' union all

select '计算机网络','高明','S5690','' union all

select 'Artificial intelligence','','D2008','' union all

select 'Expert systems','','H3067','17' union all

select '软件工程','鲁廷璋','S2005','35' union all

select 'Fortran程序设计','顾学峰','S5006','18'

alter table T0418 add QTY int

update T0418 set QTY=200 where t_no='S3092' update T0418 set QTY=300 where t_no='H1298' update T0418 set QTY=150 where t_no='D1007' update T0418 set QTY=230 where t_no='S5690' update T0418 set QTY=400 where t_no='D2008' update T0418 set QTY=370 where t_no='H3067' update T0418 set QTY=200 where t_no='S2005' update T0418 set QTY=180 where t_no='S5006'

create table S0418(

T_no char(6), page int, pub_date date) insert into S0418

select 'S3092','304','1986' union all

select 'D1007','280','1993' union all

select 'S5006','315','1987' union all

select 'S5690','300','1993' union all

select 'H1298','210','1989' union all

select 'D2008','358','1994' union all

select 'S2005','298','1995' union all

select 'H3067','307','1995'

/*(2)*/

alter table T0418

alter column Title varchar(35)

/*(3)*/

select Title,price into ST0418 from T0418

/*(4)*/

create index IT63 on T0418(t_no)

/*(5)*/

create view VT0418 as select * from T0418

/*(6)*/

drop view VT0418

/*(7)*/

drop table ST0418

/* 2 */ /*(1)*/

select T_no,Title,price into ST0418 from T0418

/*(2)*/

insert into ST0418

select 'S7028','Digital Image Processing','36'

/*(3)*/ delete

from ST0418

where Title='Fortran程序设计'

/*(4)*/ delete

from ST0418

where t_no like 'H%'

/*(5)*/

update ST0418 set price=price*

/*(6)*/

update ST0418 set price=price-2 where t_no like 'D%'

/*(7)*/

update ST0418 set t_no='S1135'

where Title='计算机原理'

/*(8) 无*/ /*(9) 无*/

/* 3 */ /*(1)*/

select max(price) MAX,min(price) MIN,avg(price) AVG from T0418

/*(2)*/

select count(Title) 种类 from T0418

/*(3)*/

select count(*) from S0418

where DATEDIFF(year,pub_date,'1990')<0

/*DATEDIFF(year,time1,time2) time1>time2为负 第一个参数：year,month,day,hour,second*/

/*(4)*/

select sum(QTY) 总库存量 from T0418

/*(5) 无*/