学校教务管理的数据库设计
学生表
drop table student
create table student
(Sno char(40) primary key,
Sname char(40),
Ssex char(2),
Sclass char(20),
Sclassno char(20),
Sbirthday datetime,
Shome char(40),
Sdept char(40),
);
insert
into student values ('12232617','Victor ','男','英语
','082','2/1/1990','America','07');
insert
into student values ('12232621','Susan','女','英语','083','1/25/1990','japan','07');
insert
into student values ('12232625','Carlos','男','广告设计
','082','2/12/1990','America','01');
insert
into student values ('12232627','Dave','男','电子商务
','082','10/1/19','America','02');
insert
into student values ('12232628','Gaby','女','金融','081','1/12/1990','Italy','04');
insert
into student values ('12232629','Lynette','女','会计
','082','9/12/19','Britain','05');
insert
into student values ('12232636','Bree','女','会计','082','11/18/19','Italy','05');
insert
into student values ('122325','tom','男','数学','081','12/22/19','Britain','06');
insert
into student values ('12232631','zach','男','国际贸易
','081','4/16/1990','Japan','03');
insert
into student values ('122321','Karl','男','计算机','082','6/8/1990','Britain','08');
insert
into student values ('12232633','Edie','女','广告设计
','083','12/11/19','Holland','01');
insert
into student values ('122323','Dylan','女','数','082','4/20/1990','Britain','06');
insert
into student values ('12232610','John','男','金','081','11/25/19','America','04');
insert
into student values ('12232620','Mike','男','计算','082','12/30/19','China','08');
系表
drop table department
create table department
学
融
机
(Sdept char(40)primary key,
Dname char(9),
Dmaster char(20)
);
insert
into department values ('01','广告设计','zt');
insert
into department values ('02','电子商务','bc');
insert
into department values ('03','国际贸易','kl');
insert
into department values ('04','金融','nn');
insert
into department values ('05','会计','jr');
insert
into department values ('06','数学','ed');
insert
into department values ('07','英语','mj');
insert
into department values ('08','计算机','ry');
教师表
drop table teacher
create table teacher
(Tno char(9)primary key,
Tname char(20),
Tclass char(20),
Twork char(20)
);
insert
into teacher values ('001','赵老师','特级教师','线性代数');
insert
into teacher values ('002','钱老师','教授','解析几何');
insert
into teacher values ('003','孙老师','教授','金融');
insert
into teacher values ('004','','高级教师','数据库');
insert
into teacher values ('005','彭老师','高级教师','课程设计');
insert
into teacher values ('006','贾老师','高级教师','会计');
insert
into teacher values ('007','尤老师','高级教师','计算机');
insert
into teacher values ('008','耿老师','教授','英语');
课程表
drop table course
create table course
(Cno char(4) primary key,
Cname char(40),
Ccredit smallint
);
alter table course add sort char (10);
insert
into course values ('0001','线性代数','3','必修');
insert
into course values ('0002','二外日语','4','修');
insert
into course values ('0003','数据库','2','必修');
insert
into course values ('0004','第三方物流','2','选修');
insert
into course values ('0005','大学英语','4','必修');
insert
into course values ('0006','课程设计','2','任选');
项目表
drop table project
create table project
(Pno char(9)primary key,
Pname char(20),
Pman char(20)
);
insert
into project values ('07','project1','cici')
insert
into project values ('08','project2','gigi')
insert
into project values ('09','project3','coco')
insert
into project values ('10','project4','june')
选课表
drop table sc
create table sc
(Sno char(9),
Cno char(4),
Grade smallint,
primary key (Sno,Cno),
);
insert
into sc values ('12232617','0001','85');
insert
into sc values ('12232617','0002','68');
insert
into sc values ('12232617','0003','96');
insert
into sc values ('12232621','0005','81');
insert
into sc values ('12232621','0001','92');
insert
into sc values ('12232621','0002','92');
insert
into sc values ('12232621','0003','92');
insert
into sc values ('12232625','0005','92');
insert
into sc values ('12232625','0004','92');
insert
into sc values ('12232625','0001','78');
insert
into sc values ('12232625','0002','78');
insert
into sc values ('12232627','0003','78');
insert
into sc values ('12232627','0005','78');
insert
into sc values ('12232627','0004','78');
insert
into sc values ('12232627','0006','78');
insert
into sc values ('12232628','0001','61');
insert
into sc values ('12232628','0002','61');
insert
into sc values ('12232628','0003','61');
insert
into sc values ('12232628','0005','61');
insert
into sc values ('12232629','0004','61');
insert
into sc values ('12232629','0006','61');
insert
into sc values ('12232629','0001','85');
insert
into sc values ('12232629','0002','85');
insert
into sc values ('12232636','0003','85');
insert
into sc values ('12232636','0005','85');
insert
into sc values ('12232636','0006','85');
insert
into sc values ('12232636','0003','65');
insert
into sc values ('122325','0005','65');
insert
into sc values ('122325','0001','65');
insert
into sc values ('122325','0002','65');
insert
into sc values ('122325','0005','90');
insert
into sc values ('12232631','0003','90');
insert
into sc values ('12232631','0001','90');
insert
into sc values ('12232631','0004','90');
insert
into sc values ('12232631','0002','90');
insert
into sc values ('122321','0001','80');
insert
into sc values ('122321','0002','80');
insert
into sc values ('122321','0003','80');
insert
into sc values ('122321','0005','80');
insert
into sc values ('12232633','0004','80');
insert
into sc values ('12232633','0006','80');
insert
into sc values ('12232633','0002','82');
insert
into sc values ('12232633','0001','82');
insert
into sc values ('12232637','0003','82');
insert
into sc values ('12232637','0005','82');
insert
into sc values ('12232637','0006','82');
insert
into sc values ('12232637','0001','79');
insert
into sc values ('122323','0002','79');
insert
into sc values ('122323','0003','79');
insert
into sc values ('122323','0005','79');
insert
into sc values ('122323','0004','79');
insert
into sc values ('12232610','0001','83');
insert
into sc values ('12232610','0003','83');
insert
into sc values ('12232610','0002','83');
insert
into sc values ('12232610','0005','83');
insert
into sc values ('12232620','0006','83');
insert
into sc values ('12232620','0004','79');
insert
into sc values ('12232620','0005','62');
insert
into sc values ('12232620','0002','83');
所有来自china的男生
select Sname
frome student
where Shome='china'and Ssex='男';
所有0005号课程成绩>90的同学
select Sname
from student,sc
where student.Sno=sc.Sno and sc.Cno='0005' and sc.Grade>'90'
教授课程设计的老师
select Tname
from teacher
where Twork='课程设计'
某班年龄最大的2名同学
select Top 2*
from student
where Sclass ='会计'AND Sclassno='082'
order by Sbirthday asc;
19年以后出生的女同学
select Sname
from student
where Sbirthday>'1/1/19' and Ssex='女'
选修0004课程的学生及其成绩
select Sname,Grade
from student,sc
where student.Sno=sc.sno and Cno='0004'
没有授课的教师
select Tname
from teacher
where Twork=' '
某学生所选课程的总学分
select sum(Ccredit)
from sc ,course
where Sno='12232620' and sc.cno=course.cno
电子商务学生所学的所有课程
select distinct course.Cname
from student,course,sc,department
where student.Sno=sc.Sno
and course.cno=sc.cno
and student.Sdept=department.Sdept
and Dname='电子商务'
按总学分找出某系学习最好的2名同学
select top 5 Sname,sum(Ccredit)
from student,sc,course,department
where student.Sno=sc.Sno
and course.Cno=sc.Cno
and student.Sdept=department.Sdept
and Dname='金融'
group by student.Sname
按总学分积找出某系学习最好的5名同学
select top 5 Sname,sum((Grade-50)/10*Ccredit)
from student,sc,course,department
where student.Sno=sc.Sno
and course.Cno=sc.Cno
and student.Sdept=department.Sdept
and Dname='会计'
group by student.Sname
教授12232620学生必修课程的老师情况
select Tno,Tname,teacher.Tclass,Twork
from student，sc，course，teacher
where student.Sno=’12232610’
and teacher.Twork=course.Cname
and course.Cno=sc.Cno
12232610选修了那些系的课程
select DISTINCT Dname
from sc,teacher,department
where sc.cno in
(select cno
from sc
where sno='12232610')
数据库心得
