ACCP课程体系通过结合先进的多模式教学法，使学习者在掌握理论知识与工具的同时，具备良好的自我学习能力和个人素质，成为符合21世纪企业要求的IT人才。下面是关于ACCP7.0优化myschool数据库设计，欢迎大家参考!
上机1
use MySchool
go
begin transaction
declare @errornum int
set @errornum=0
insert into Result values('23219',1,90,'2013-09-12')
set @errornum+=@@ERROR
insert into Result values('23219',1,90,'2013-09-13')
set @errornum+=@@ERROR
insert into Result values('23219',1,90,'2014-09-12')
set @errornum+=@@ERROR
insert into Result values('23219',1,90,'2013-09-11')
set @errornum+=@@ERROR
insert into Result values('23219',1,90,'2003-09-12')
set @errornum+=@@ERROR
if @errornum<>0
begin
print '操作失败,回滚事务'
rollback transaction
end
else
begin
print '操作成功,保存事务'
commit transaction
end
--上机2
go
begin transaction
declare @errornum int
set @errornum=0
select * into historyresult from Result
where StudentNo in(select StudentNo from Student
where GradeId=(select GradeId from Grade where GradeName='Y2'))
set @errornum+=@@ERROR
delete from Result
where StudentNo in(select StudentNo from Student
where GradeId=(select GradeId from Grade where GradeName='Y2'))
set @errornum+=@@ERROR
select * into historystudent from Student
where GradeId=(select GradeId from Grade where GradeName='Y2')
set @errornum+=@@ERROR
delete from Student
where GradeId=(select GradeId from Grade where GradeName='Y2')
set @errornum+=@@ERROR
if @errornum<>0
begin
print '操作失败,回滚事务'
rollback transaction
end
else
begin
print '操作成功,保存事务'
commit transaction
end
--上机3
go
CREATE VIEW vw_student_result_info
AS
SELECT 姓名=StudentName,学号=entNo,
联系电话=Phone,学期=GradeName,成绩=Total
FROM Student
LEFT OUTER JOIN (
SELECT entNo,GradeName,SUM(StudentResult) Total
FROM Result r
INNER JOIN (
SELECT StudentNo,SubjectId,MAX(ExamDate) ExamDate
FROM Result
GROUP BY StudentNo,Subjectid) tmp
ON Date=Date
AND ectid = ectid AND entNo = entNo
INNER JOIN Subject sub ON ectid = ectid
INNER JOIN Grade g ON eId = eId
GROUP By entNo,GradeName ) TmpResult2
ON entNo = entNo
GROUP BY StudentName,entNo,Phone,GradeName,Total
GO
SELECT * FROM vw_student_result_info
--上机4
go
create nonclustered index index_result
on result(studentresult)
select studentname,Subjectname,ExamDate,StudentResult from Result
with(index=index_result)
inner join Student on entNo=entNo
inner join Subject on ectId=ectId
where StudentResult between 80 and 90
