SQL课程设计报告Word文档格式.docx
《SQL课程设计报告Word文档格式.docx》由会员分享,可在线阅读,更多相关《SQL课程设计报告Word文档格式.docx(22页珍藏版)》请在冰点文库上搜索。
datetime
EmCardId
职工身份证号
varchar(18)
EmPosition
职称
char(20)
EmSex
职工性别
char
(2)
createtableDepartment
DepNointprimarykey,
DepNamevarchar(20),
DepNo
系别
DepName
系名
createtableDorm
DormNovarchar(8)constraintpk_DormNoprimarykeyconstraintck_DormNocheck(DormNolike'
[0-9][0-9]-[0-9][0-9][0-9]'
),
DormDepintconstraintfk_DormDepforeignkeyreferencesDepartment(DepNo),
DormNuintconstraintck_DormNucheck(DormNuin(4,6)),
DormNowNuintconstraintck_DormNowNucheck(DormNowNu>
=0andDormNowNu<
=6),
DormSexchar
(2)constraintck_DormSexcheck(DormSexin('
男'
'
女'
))
DormNo
宿舍号
varchar(8)
DormDep
Int
foreignkey
DormNu
容量
check
DormNowNu
剩余容量
DormSex
性别
createtableClass
ClassNamevarchar(20),
ClassNointconstraintpk_ClassNoprimarykey,
ClassDepNointconstraintfk_ClassDepNoforeignkeyreferencesDepartment(DepNo),
Classtypevarchar(20),
ClassScureint,
ClassTimeint
ClassName
课程名
ClassNo
课程号
ClassDepNo
ClassType
课程类型
ClassScure
学分
ClassTime
可是
createtableTeacher
TeacherNointconstraintfk_TeacherNoforeignkeyreferencesEmployee(EmNo)
constraintpk_Teacherprimarykey,
TeacherDepNointconstraintfk_TeacherDepNoforeignkeyreferencesDepartment(DepNo),
Ispartimebit
TeacherNo
教师工号
foreignkeyprimarykey
TeacherDepNo
教师系别
Ispartime
是否专职
bit
Notnull
createtableIncome
EmployeeNointconstraintfk_EmployeeNoforeignkeyreferencesEmployee(EmNo),
PayBaseint,
PayYearMondatetime,
PayOtherint,
YearRewordint,
ClassPayint,
EmployeeNo
PayBase
基本工资
PayYearMon
年月
PayIther
三江津贴
YearRword
年终奖
ClassPay
课时费
createtableStudent
SNovarchar(20)constraintpk_SNoprimarykeyconstraintck_SNocheck(SNolike'
[A,B,Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
SNamevarchar(20),
SBirthdaydatetime,
SCardIDvarchar(20),
DepNointconstraintfk_DepNoforeignkeyreferencesDepartment(DepNo),
SSexchar
(2)constraintck_SSexcheck(SSexin('
)),
DormNovarchar(8)constraintfk_DormNoforeignkeyreferencesDorm(DormNo),
SNo
学号
SName
姓名
SBirthday
生日
SCardId
身份证号
系号
SSex
DromNo
createtableStudentClass
ClassNointconstraintfk_ClassNoforeignkeyreferencesClass(ClassNo),
StudentNovarchar(20)constraintfk_StudentNoforeignkeyreferencesStudent(SNo)constraintpk_SCprimarykey(ClassNo,StudentNo),
Gradefloat
StudentNo
Grade
成绩
float
createtableEducateClass(
EducateClassNovarchar(20)constraintpk_EducateClassNoprimarykey,
ClassNointconstraintfk_ClassNoEduforeignkeyreferencesClass(ClassNo),
TeacherNointconstraintfk_TeacherNo1foreignkeyreferencesTeacher(TeacherNo),
ClassRoomNoint,
ClassTermvarchar(12)constraintck_ClassTermcheck(ClassTermlike'
[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9]'
ClassThinkvarchar(400)
EducateClassNo
教学班号
学期
班级号
ClassRoomNo
ClassTerm
varchar(12)
fcheck
ClassThink
评价
varchar(400)
录入表数据:
insertintoDepartmentvalues(1,'
Computer'
insertintoDepartmentvalues(2,'
English'
insertintoDepartmentvalues(3,'
Art'
insertintoDepartmentvalues(4,'
Plant'
insertintoDormvalues('
01-111'
1,6,5,'
02-111'
2,6,5,'
03-111'
3,6,5,'
04-111'
4,4,3,'
insertintostudentvalues('
A11111111'
Tomy'
1992-06-11'
,1,'
A11111112'
Tony'
1992-05-10'
A11111113'
Linda'
1992-05-9'
4'
2,'
A11111114'
Tods'
1992-05-8'
5'
1,'
insertintoClassvalues('
1,1,'
专业课'
2,24)
2,2,'
Musci'
3,3,'
Tree'
4,4,'
insertintoEmployeevalues(1,'
Weiyuanyuan'
1989-2-1'
,'
讲师'
insertintoEmployeevalues(2,'
Yuzhenghong'
1988-3-1'
副教授'
insertintoEmployeevalues(3,'
Weizhiqing'
教授'
insertintoEmployeevalues(4,'
Huanghui'
insertintoTeachervalues(1,1,0)
insertintoTeachervalues(2,2,0)
insertintoTeachervalues(3,3,0)
insertintoTeachervalues(4,4,0)
insertintoStudentClassvalues(1,'
90)
insertintoStudentClassvalues(2,'
80)
insertintoStudentClassvalues(3,'
75)
insertintoStudentClassvalues(4,'
85)
insertintoEducateClassvalues(001,1,1,3311,'
2011-2012-01'
Good'
insertintoEducateClassvalues(002,2,2,3202,'
VertyGood'
insertintoEducateClassvalues(003,3,3,2211,'
insertintoEducateClassvalues(004,4,4,1111,'
查询学生信息:
createprocedurepro_AskGrade
@SNovarchar(20),@CardIdvarchar(20)
as
begintry
if(exists(select*fromStudentwhereSNo=@SNoandSCardId=@CardId))
begin
Declare@ClassCountint,
@UnpassClassint,@AllScureint,@MaxGradefloat,@MinGradefloat
select@ClassCount=count(ClassNo)fromStudentClasswhereStudentNo=@SNo
select@UnpassClass=count(Grade)fromStudentClasswhereStudentNo=@SNoandGrade<
60
select@AllScure=sum(ClassScure)fromClasswhereClassNoin(selectClassNofromStudentClasswhereStudentNo=@SNoandGrade>
60)
select@MaxGrade=max(Grade)fromStudentClass
whereStudentClass.StudentNo=@SNo
select@MinGrade=min(Grade)fromStudentClass
selectClassNameas'
课程名'
Gradeas'
最高成绩'
fromStudentClassjoin
ClassonStudentClass.ClassNo=Class.ClassNo
whereStudentClass.StudentNo=@SNoandGrade=@MaxGrade
最低成绩'
whereStudentClass.StudentNo=@SNoandGrade=@MinGrade
print'
学生'
+cast(@SNoaschar(12))
选修课程数'
+cast(@ClassCountaschar
(2))+'
不及格的课程数'
+cast(@UnpassClassaschar
(2))
+'
总学分'
+cast(@AllScureaschar
(2))
select*fromStudentClasswhereStudentNo=@SNo
end
else
print'
用户名/密码错误'
endtry
begincatch
error'
endcatch
execpro_AskGrade'
发放教职工工资:
createprocedurepro_PayTeacher
declare@ClassTimeint,@TeacherTitlechar(10),@YearRewardint,@PayBaseint,@PayOtherint,@TeacherNoint
declarecur_PayTeachercursor
forselectEmNo,EmPositionfromEmployee
opencur_PayTeacher
fetchcur_PayTeacherinto@TeacherNo,@TeacherTitle
while(@@fetch_status=0)
select@ClassTime=sum(ClassTime)fromClasswhereClassNoin(selectClassNofromEducateClasswhereTeacherNo=@TeacherNo)
if(@ClassTime=null)
set@ClassTime=0
if((selectIspartimefromTeacherwhereTeacherNo=@TeacherNo)=0)
set@PayOther=1500
else
set@PayOther=0
if(@TeacherTitle='
初级'
begin
set@PayBase=1000
set@YearReward=1000
end
if(@TeacherTitle='
中级'
begin
set@PayBase=1200
set@YearReward=2000
end
else
set@PayBase=1500
set@YearReward=3000
if(month(getdate())!
=12)
insertintoIncomevalues(@TeacherNo,@PayBase,getdate(),@PayOther,0,@ClassTime*80/6)
insertintoIncomevalues(@TeacherNo,@PayBase,getdate(),@PayOther,@YearReward,@ClassTime*80/6)
select*fromIncomewhereEmployeeNo=@TeacherNo
fetchcur_PayTeacherinto@TeacherNo,@TeacherTitle
closecur_PayTeacher
deallocatecur_PayTeacher
execpro_PayTeacher
了解课程情况:
createprocedurepro_Teach
@TeacherNoint,@TeacherCardIdvarchar(18)
if(exists(select*fromEmployeewhereEmNo=@TeacherNoandEmCardId=@TeacherCardId))
if(exists(select*fromTeacherwhereTeacherNo=@TeacherNo))
declare@CountUnpassint,@ClassNoint,@ClassNamevarchar(20),@EducateClassNoint
declarecur_Countcursor
forselectClass.ClassNo,Class.ClassName,EducateClassNofromEducateClassjoin
ClassonClass.ClassNo=EducateClass.ClassNo
whereTeacherNo=@TeacherNo
orderbyClass.ClassName,EducateClassNo
opencur_Count
fetchcur_Countinto@ClassNo,@ClassName,@EducateClassNo
while(@@Fetch_status=0)
select@CountUnpass=count(Grade)fromStudentClasswhereClassNo=@ClassNoandGrade<
print'
+@ClassName+'
课程班号'
+cast(@EducateClassNoaschar
(2))+'
不及格学生人数'
+cast(@CountUnpassaschar
(2))
selectStudentNo,SName,GradefromStudentClassjoinStudentonSNo=StudentNowhereClassNo=@ClassNoandGrade<
selecttop3StudentNo,SName,GradefromStudentClassjoinStudentonSNo=StudentNowhereClassNo=@ClassNo
elseprint'
非合法教师'
execpro_Teach1,
新生报到:
Createprocedurepro_NewStudent
@StudentNovarchar(20),@StudentNamevarchar(20),