文档详情

SQL Sever 2005机考题答案.doc

发布:2017-08-02约1.65千字共3页下载文档
文本预览下载声明
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;
显示全部
相似文档