SQL Sever 2005机考题答案.doc
文本预览下载声明
1.
Create Database 刘盛旺
On primary
(Name=
Filename=D:\mdf,
Size= 10,
Maxsize=20,
Filegrowth=10%
),
(Name=sPC63,
Filename=D:\sPC63.ndf,
Size= 10,
Maxsize=20,
Filegrowth=10%
)
Log on
(Name=s刘,
Filename=D:\ldf,
Size=5,
Maxsize=10,
Filegrowth=1
);
2.
Create table Teacher
(
Tno char(8) primary key not null,
Tn char(8) not null,
Tsex char(2) default 男 not null,
Ta int not null,
Td varchar(20) not null
);
3.
Create table Course
(
Cno char(8) primary key not null,
Cn char(20) not null,
Ccredit int not null,
Cpno char(8),
Foreign key(Cpno) references Course(Cno)
);
4.
Create table CT
(
Cno char(8) not null,
Tno char(8) not null,
Primary key (Cno,Tno),
Foreign key(Cno) references Course(Cno),
Foreign key(Tno) references Teacher(Tno)
);
5.
Alter table Teacher
Alter column Tn varchar(8) not null;
6.
Insert
into Teacher
Values刘盛旺,男,20,e01);
7.
Select Tn
From Teacher
Where Ta=(
Select max(Ta)
From Teacher);
8.
Select Tn
From Teacher
Where not exists
(Select *
From CT
Where Tno=Teacher.Tno
);
9.
Create procedure P_Ta
@num char(8)
As
Declare @age int
Set @age=(Select Ta From Teacher Where Tno=@num)
If @age between 0 and 34
Begin
Print 青年
END
Else
Begin
If @age between 35 and 50
Begin
Print 中年
END
ELSE
Begin
Print 老年
END
END;
10.
Create trigger T_CT
On CT
After insert
As
Begin
If(Exists (Select * From inserted,CT Where CT.Cno=inserted.Cno))
Begin
Rollback tran
Print 添加的课程已经存在,插入记录无效!
End
End;
显示全部