tsql高级查询
反之查询他选修的门数,如果超过5 门,打印“选修不能超过5 门”,如果没有超过则判断‘120 ’课程的选修人数,
如果大于6 人则打印“该课程已经报满”,反之则插入“2017071101 ”,“120 ”,Null 。
if exists ( select * from SC where SC. Sid= '2017071101' and Cid= '120' )
print '不能重复选修'
else
begin
if ( select COUNT ( * ) from SC where Sid= '2017071101' ) >= 5
print '选修不能超过5门'
else if ( select COUNT ( * ) from SC where Cid= '120' ) >= 6
print '该课程已经报满'
else
insert into SC values ( '2017071101' , '120' , Null )
end
if exists ( select * from sys. objects where name= 'examstate' and type = 'U' )
drop table examstate
create table examstate(
Sid char ( 10 ) ,
sname nvarchar( 20 ) ,
Cid nchar ( 3 ) ,
state int default 0 ,
score int default Null ,
remarks nvarchar( 20 ) default Null )
insert into examstate( Sid, sname, Cid)
select S. Sid, S. sname, Cid from SC, S where Cid= '101' and S. Sid= SC. Sid
如果存在则查看考试状态:如果考试状态为0 ,则将考试状态置为1 ,打印“登录成功”。
考试状态为3 ,则打印“已经交卷,不能重复登录”;
考试状态为4 ,则打印“需要录入二次登录密码”。
if not exists ( select * from examstate where Sid= '2017071101' )
print '不存在该考生'
else
begin
declare @state int
select @state = state from examstate where Sid= '2017071101'
if @state = 0
begin
update examstate set state= 1 where Sid= '2017071101'
print '登录成功'
end
else if @state = 3
print '已经交卷,不能重复登录'
else if @state = 4
print '监考人员录入二次登录密码'
end
反之修改examstate表,将state为0 的remarks改为缺考,将state为3 的remarks改为正常。
if exists ( select * from examstate where state in ( 1 , 2 , 4 ) )
print '不能收卷,有学生在考试'
else
begin
update examstate set remarks= '缺考' where state= 0
update examstate set remarks= '正常' where state= 3
end
数据表
create database jxgl
go
use jxgl
go
create table T(
Tid nchar ( 5 ) primary key ,
name nchar ( 10 ) not null ,
sex nchar ( 2 ) default '男' ,
workingtime datetime ,
plandscape nchar ( 20 ) ,
degree nchar ( 10 ) ,
title nvarchar( 6 ) ,
department nvarchar( 20 ) ,
tel nchar ( 11 )
)
create table S(
Sid nchar ( 10 ) primary key ,
sname nchar ( 10 ) ,
sex nchar ( 2 ) default '男' ,
birthday Date ,
age as datediff( yy, birthday, getdate( ) ) ,
entrydate date ,
plandscape nchar ( 10 ) default '团员' ,
nativeplace nvarchar( 20 ) ,
class as substring( Sid, 7 , 2 )
)
create table C(
Cid nchar ( 3 ) primary key ,
cname nvarchar( 20 ) Not null ,
category nchar ( 4 ) Not null check ( category in ( '考试' , '考查' ) ) default '考试' ,
credit int ,
Tid nchar ( 5 ) foreign key references T( Tid) on update cascade
)
create table SC(
Sid nchar ( 10 ) foreign key references S( Sid) on update cascade on delete cascade ,
Cid nchar ( 3 ) ,
score int check ( score between 0 and 100 ) ,
primary key ( Sid, Cid) ,
foreign key ( Cid) references C( Cid) on update cascade on delete cascade
)
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95011' , N'赵西明 ' , N'男 ' , CAST( 0x0000768300000000 AS DateTime ) , N'群众 ' , N'硕士 ' , N'副教授' , N'软件' , N'13733152369' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95012' , N'李小平 ' , N'男 ' , CAST( 0x0000775E00000000 AS DateTime ) , N'党员 ' , N'硕士 ' , N'教授' , N'计算机系' , N'13733152370' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95013' , N'张爽 ' , N'男 ' , CAST( 0x00007BC700000000 AS DateTime ) , N'群众 ' , N'本科 ' , N'副教授' , N'计算机系' , N'13733152371' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95014' , N'李丽宁 ' , N'女 ' , CAST( 0x00007BC700000000 AS DateTime ) , N'党员 ' , N'硕士 ' , N'副教授' , N'计算机系' , N'13733152372' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95015' , N'张金明 ' , N'男 ' , CAST( 0x0000812C00000000 AS DateTime ) , N'群众 ' , N'硕士 ' , N'讲师' , N'计算机系' , N'13733152373' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95016' , N'李燕 ' , N'女 ' , CAST( 0x000082C800000000 AS DateTime ) , N'党员 ' , N'硕士 ' , N'讲师' , N'计算机系' , N'13733152374' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95017' , N'宛平 ' , N'女 ' , CAST( 0x0000840B00000000 AS DateTime ) , N'群众 ' , N'博士 ' , N'副教授' , N'软件' , N'13733152375' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95018' , N'陈江川 ' , N'男 ' , CAST( 0x0000883900000000 AS DateTime ) , N'群众 ' , N'博士 ' , N'讲师' , N'软件' , N'13733152376' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95019' , N'郭新 ' , N'男 ' , CAST( 0x00008CAA00000000 AS DateTime ) , N'党员 ' , N'博士 ' , N'讲师' , N'软件' , N'13733152377' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071101' , N'张佳 ' , N'女 ' , CAST( 0x3F1D0B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'团员 ' , N'江西南昌' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071102' , N'好生 ' , N'男 ' , CAST( 0xCD1E0B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'团员 ' , N'广州顺德' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071203' , N'徐克 ' , N'男 ' , CAST( 0xBF210B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'团员 ' , N'江西南昌' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071204' , N'叶飞 ' , N'女 ' , CAST( 0xD1210B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'党员 ' , N'上海' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071205' , N'任伟 ' , N'男 ' , CAST( 0x7B220B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'团员 ' , N'北京顺义' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071206' , N'江贺 ' , N'男 ' , CAST( 0x02200B00 AS Date ) , CAST( 0x3D3D0B00 AS Date ) , N'党员 ' , N'福建厦门' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'101' , N'计算机基础' , N'考试 ' , 2 , N'95011' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'102' , N'C语言' , N'考试 ' , 3 , N'95012' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'103' , N'计算机组成原理' , N'考试 ' , 3 , N'95012' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'104' , N'汇编语言' , N'考试 ' , 3 , N'95014' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'105' , N'Java语言' , N'考查 ' , 2 , N'95015' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'106' , N'操作系统' , N'考试 ' , 3 , N'95016' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'107' , N'数据结构' , N'考试 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'108' , N'编译原理' , N'考试 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'109' , N'网络基础' , N'考试 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'110' , N'数据库原理' , N'考试 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'120' , N'SQLServer' , N'考查 ' , 2 , N'95018' )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'101' , 90 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'102' , 70 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'103' , 48 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'105' , 80 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'102' , 90 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'104' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'106' , 68 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'107' , 85 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'109' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'110' , 65 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'120' , 48 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'102' , 65 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'104' , 82 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'105' , 79 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'107' , 55 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'110' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'120' , 67 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'101' , 86 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'102' , 86 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'104' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'105' , 84 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'106' , 95 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'108' , 91 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'110' , 82 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071205' , N'101' , 63 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071205' , N'102' , 84 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071205' , N'103' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071206' , N'107' , 58 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071206' , N'108' , 74 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071206' , N'109' , 74 )