数据库课程设计 教学管理系统.docx
《数据库课程设计 教学管理系统.docx》由会员分享,可在线阅读,更多相关《数据库课程设计 教学管理系统.docx(16页珍藏版)》请在冰豆网上搜索。
数据库课程设计教学管理系统
学校教务管理的数据库设计
学生表
droptablestudent
createtablestudent
(Snochar(40)primarykey,
Snamechar(40),
Ssexchar
(2),
Sclasschar(20),
Sclassnochar(20),
Sbirthdaydatetime,
Shomechar(40),
Sdeptchar(40),
);
insert
intostudentvalues('12232617','Victor','男','英语','082','2/1/1990','America','07');
insert
intostudentvalues('12232621','Susan','女','英语','083','1/25/1990','japan','07');
insert
intostudentvalues('12232625','Carlos','男','广告设计','082','2/12/1990','America','01');
insert
intostudentvalues('12232627','Dave','男','电子商务','082','10/1/1989','America','02');
insert
intostudentvalues('12232628','Gaby','女','金融','081','1/12/1990','Italy','04');
insert
intostudentvalues('12232629','Lynette','女','会计','082','9/12/1989','Britain','05');
insert
intostudentvalues('12232636','Bree','女','会计','082','11/18/1989','Italy','05');
insert
intostudentvalues('12232645','tom','男','数学','081','12/22/1989','Britain','06');
insert
intostudentvalues('12232631','zach','男','国际贸易','081','4/16/1990','Japan','03');
insert
intostudentvalues('12232641','Karl','男','计算机','082','6/8/1990','Britain','08');
insert
intostudentvalues('12232633','Edie','女','广告设计','083','12/11/1989','Holland','01');
insert
intostudentvalues('12232643','Dylan','女','数学','082','4/20/1990','Britain','06');
insert
intostudentvalues('12232610','John','男','金融','081','11/25/1989','America','04');
insert
intostudentvalues('12232620','Mike','男','计算机','082','12/30/1989','China','08');
系表
droptabledepartment
createtabledepartment
(Sdeptchar(40)primarykey,
Dnamechar(9),
Dmasterchar(20)
);
insert
intodepartmentvalues('01','广告设计','zt');
insert
intodepartmentvalues('02','电子商务','bc');
insert
intodepartmentvalues('03','国际贸易','kl');
insert
intodepartmentvalues('04','金融','nn');
insert
intodepartmentvalues('05','会计','jr');
insert
intodepartmentvalues('06','数学','ed');
insert
intodepartmentvalues('07','英语','mj');
insert
intodepartmentvalues('08','计算机','ry');
教师表
droptableteacher
createtableteacher
(Tnochar(9)primarykey,
Tnamechar(20),
Tclasschar(20),
Tworkchar(20)
);
insert
intoteachervalues('001','赵老师','特级教师','线性代数');
insert
intoteachervalues('002','钱老师','教授','解析几何');
insert
intoteachervalues('003','孙老师','教授','金融');
insert
intoteachervalues('004','李老师','高级教师','数据库');
insert
intoteachervalues('005','彭老师','高级教师','课程设计');
insert
intoteachervalues('006','贾老师','高级教师','会计');
insert
intoteachervalues('007','尤老师','高级教师','计算机');
insert
intoteachervalues('008','耿老师','教授','英语');
课程表
droptablecourse
createtablecourse
(Cnochar(4)primarykey,
Cnamechar(40),
Ccreditsmallint
);
altertablecourseaddsortchar(10);
insert
intocoursevalues('0001','线性代数','3','必修');
insert
intocoursevalues('0002','二外日语','4','修');
insert
intocoursevalues('0003','数据库','2','必修');
insert
intocoursevalues('0004','第三方物流','2','选修');
insert
intocoursevalues('0005','大学英语','4','必修');
insert
intocoursevalues('0006','课程设计','2','任选');
项目表
droptableproject
createtableproject
(Pnochar(9)primarykey,
Pnamechar(20),
Pmanchar(20)
);
insert
intoprojectvalues('07','project1','cici')
insert
intoprojectvalues('08','project2','gigi')
insert
intoprojectvalues('09','project3','coco')
insert
intoprojectvalues('10','project4','june')
选课表
droptablesc
createtablesc
(Snochar(9),
Cnochar(4),
Gradesmallint,
primarykey(Sno,Cno),
);
insert
intoscvalues('12232617','0001','85');
insert
intoscvalues('12232617','0002','68');
insert
intoscvalues('12232617','0003','96');
insert
intoscvalues('12232621','0005','81');
insert
intoscvalues('12232621','0001','92');
insert
intoscvalues('12232621','0002','92');
insert
intoscvalues('12232621','0003','92');
insert
intoscvalues('12232625','0005','92');
insert
intoscvalues('12232625','0004','92');
insert
intoscvalues('12232625','0001','78');
insert
intoscvalues('12232625','0002','78');
insert
intoscvalues('12232627','0003','78');
insert
intoscvalues('12232627','0005','78');
insert
intoscvalues('12232627','0004','78');
insert
intoscvalues('12232627','0006','78');
insert
intoscvalues('12232628','0001','61');
insert
intoscvalues('12232628','0002','61');
insert
intoscvalues('12232628','0003','61');
insert
intoscvalues('12232628','0005','61');
insert
intoscvalues('12232629','0004','61');
insert
intoscvalues('12232629','0006','61');
insert
intoscvalues('12232629','0001','85');
insert
intoscvalues('12232629','0002','85');
insert
intoscvalues('12232636','0003','85');
insert
intoscvalues('12232636','0005','85');
insert
intoscvalues('12232636','0006','85');
insert
intoscvalues('12232636','0003','65');
insert
intoscvalues('12232645','0005','65');
insert
intoscvalues('12232645','0001','65');
insert
intoscvalues('12232645','0002','65');
insert
intoscvalues('12232645','0005','90');
insert
intoscvalues('12232631','0003','90');
insert
intoscvalues('12232631','0001','90');
insert
intoscvalues('12232631','0004','90');
insert
intoscvalues('12232631','0002','90');
insert
intoscvalues('12232641','0001','80');
insert
intoscvalues('12232641','0002','80');
insert
intoscvalues('12232641','0003','80');
insert
intoscvalues('12232641','0005','80');
insert
intoscvalues('12232633','0004','80');
insert
intoscvalues('12232633','0006','80');
insert
intoscvalues('12232633','0002','82');
insert
intoscvalues('12232633','0001','82');
insert
intoscvalues('12232637','0003','82');
insert
intoscvalues('12232637','0005','82');
insert
intoscvalues('12232637','0006','82');
insert
intoscvalues('12232637','0001','79');
insert
intoscvalues('12232643','0002','79');
insert
intoscvalues('12232643','0003','79');
insert
intoscvalues('12232643','0005','79');
insert
intoscvalues('12232643','0004','79');
insert
intoscvalues('12232610','0001','83');
insert
intoscvalues('12232610','0003','83');
insert
intoscvalues('12232610','0002','83');
insert
intoscvalues('12232610','0005','83');
insert
intoscvalues('12232620','0006','83');
insert
intoscvalues('12232620','0004','79');
insert
intoscvalues('12232620','0005','62');
insert
intoscvalues('12232620','0002','83');
所有来自china的男生
selectSname
fromestudent
whereShome='china'andSsex='男';
所有0005号课程成绩>90的同学
selectSname
fromstudent,sc
wherestudent.Sno=sc.Snoandsc.Cno='0005'andsc.Grade>'90'
教授课程设计的老师
selectTname
fromteacher
whereTwork='课程设计'
某班年龄最大的2名同学
selectTop2*
fromstudent
whereSclass='会计'ANDSclassno='082'
orderbySbirthdayasc;
1989年以后出生的女同学
selectSname
fromstudent
whereSbirthday>'1/1/1989'andSsex='女'
选修0004课程的学生及其成绩
selectSname,Grade
fromstudent,sc
wherestudent.Sno=sc.snoandCno='0004'
没有授课的教师
selectTname
fromteacher
whereTwork=''
某学生所选课程的总学分
selectsum(Ccredit)
fromsc,course
whereSno='12232620'ando=o
电子商务学生所学的所有课程
selectdistinctcourse.Cname
fromstudent,course,sc,department
wherestudent.Sno=sc.Sno
ando=o
andstudent.Sdept=department.Sdept
andDname='电子商务'
按总学分找出某系学习最好的2名同学
selecttop5Sname,sum(Ccredit)
fromstudent,sc,course,department
wherestudent.Sno=sc.Sno
andcourse.Cno=sc.Cno
andstudent.Sdept=department.Sdept
andDname='金融'
groupbystudent.Sname
按总学分积找出某系学习最好的5名同学
selecttop5Sname,sum((Grade-50)/10*Ccredit)
fromstudent,sc,course,department
wherestudent.Sno=sc.Sno
andcourse.Cno=sc.Cno
andstudent.Sdept=department.Sdept
andDname='会计'
groupbystudent.Sname
教授12232620学生必修课程的老师情况
selectTno,Tname,teacher.Tclass,Twork
fromstudent,sc,course,teacher
wherestudent.Sno=’12232610’
andteacher.Twork=course.Cname
andcourse.Cno=sc.Cno
12232610选修了那些系的课程
selectDISTINCTDname
fromsc,teacher,department
whereoin
(selectcno
fromsc
wheresno='12232610')
数据库心得