发布时间 : 星期三 文章数据库原理实验报告更新完毕开始阅读
二 实验结果
/* 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) 无*/