建立学生成绩管理系统

建立学生成绩管理系统

一、 该系统由三个表组成，其详细情况如下：

1. 学生信息表(StuInfo)：

该表包括的字段如下：

1) 学号(StuID)

2) 姓名(StuName)

3) 班级(Class)

4) 年龄(StuAge)

5) 性别(StuSex)

6) 电话(StuTel)

7) 区域(StuArea)

8) 详细住址(StuAddress)

2. 课程信息表(CourseInfo)

该表包括的字段如下：

1) 课程号(CourseID)

2) 课程名(CourseName)

3) 课时(CourseHour)：就是多少次课把该教材学完。

4) 任课老师(CourseTeacher)

3. 成绩详情表(StuExam)

该表包括的字段如下：

1) 学号(StuID)

2) 课程号(CourseID)

3) 分数(Score)

二、 要求：

1) 通过T-SQL脚本建库、建表、建约束。

2) 选择合适的数据类型。

3) 定义必要的约束。

4) 要求用T-SQL语句向数据表中插入正确可靠的数据。并且保证学生信息表中至少是10条数据，课程信息表中至少是5条数据，成绩表中至少是30条数据。

5) 假设学生信息表中的Class字段中有内容为：”15班”

假设课程信息表中的CourseID字段有内容为：”A001”

假设学生信息表中的区域只有三种情况”汉口”、“汉阳”、“武昌”

三、 数据库的简单查询操作：

1) 求学生的所有信息

2) 求学生的姓名和、性别、住址和电话

3) 求”15班”学生的学号、姓名、性别、住址和电话

4) 求参加了考试的学生学号

5) 求”15班”后两名学生的信息

6) 求”15班”前百分之二学生的信息

7) 求'A001'的学生学号和成绩

8) 求' A001'的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同，则按学号的升序排列

9) 求' A001'成绩在90到100之间的学生学号和成绩

10) 求' A001'成绩在90到100之间的学生学号和成绩，并将成绩乘以系数0.8输出

11) 求'15班'和'7班'姓张的学生信息

12) 求'15班'和'7班'姓张的并且只有两个字的学生信息

13) 求缺少了成绩的学生学号和课程号

14) 求学生的总人数

15) 求学生的总人数，并且用三种方法给其取别名

16) 求参加了考试的总人数(假设只要学号在成绩表中出现过，就代表此人参加过考试)

17) 求课程号和参加考试的人数

18) 求各班的总人数

19) 求男女生各有多少人

20) 求“15班”男女生各有多少人

21) 求三个区各有多少人

22) 求各科的总分

23) 求各科的平均分

24) 求各科的最高分

25) 求各科的最低分

26) 求各科的总分、平均分、最高分、最低分

27) 求‘15班’各科的总分、平均分、最高分、最低分

28) 求’ A001’没有及格的人数

29) 求’15班’A001没及格的人数

30) 求有两门以上不及格的学生学号

create database Student
on primary
(
name=Student,
filename='d:/Student.mdf',
filegrowth=10%,
size=5
)
log on
(
name=Student_log,
filename='d:/Student_log.ldf',
size=1,
filegrowth=10%
)

use Student

--1.学生信息表
create table stuinfo
(
stuid int not null identity(1,1) primary key,
stuname varchar(20),
class varchar(4),
stuage int ,
stusex char(2),
stutel varchar(30),
stuarea varchar(20),
stuAddress varchar(50)
)
drop table stuinfo
drop table stuexam
alter table stuexam drop constraint FK__stuexam__Coursei__07020F21
--2.课程信息表
create table Courseinfo
(
Courseid varchar(10) not null primary key ,
Couname varchar(10),
couhour varchar(10),
couter varchar(8)
)
drop table Courseinfo
--3.成绩详情表
create table stuexam
(
stuid int not null references stuinfo(stuid),
Courseid varchar(10) references Courseinfo(Courseid),
Score int
)

---向学生信息表添加数据
select *from stuinfo

insert into stuinfo values('蔡甜','一班',20,'男','1110','武昌','武汉南湖华科校区')
insert into stuinfo values('安帅','一班',28,'男','1111','汉阳','武汉南湖华科校区')
insert into stuinfo values('蔡和森','二班',40,'男','1112','汉口','武汉南湖华科校区')
insert into stuinfo values('蔡畅','二班',22,'女','1113','武昌','武汉南湖华科校区')
insert into stuinfo values('','三班',23,'男','1114','汉阳','武汉南湖华科校区')
insert into stuinfo values('周恩来','四班',30,'男','1115','武昌','武汉南湖华科校区')
insert into stuinfo values('杨开慧','三班',19,'女','1116','武汉','武汉南湖华科校区')
insert into stuinfo values('陶斯咏','四班',19,'女','1156','武昌','武汉南湖华科校区')
insert into stuinfo values('邓颖超','五班',24,'女','1596','汉口','武汉南湖华科校区')
insert into stuinfo values('陈庚','五班',50,'男','1336','汉口','武汉南湖华科校区')
insert into stuinfo values('张菲','一班',29,'女','1456','汉阳','武汉南湖华科校区')
insert into stuinfo values('邓飞','15班',24,'女','1836','武昌','武汉南湖华科校区')
insert into stuinfo values('陈亮','15班',26,'男','1963','汉口','武汉南湖华科校区')
insert into stuinfo values('王风','15班',29,'女','1478','武昌','武汉南湖华科校区')
insert into stuinfo values('林峰','15班',22,'女','1789','武昌','武汉南湖华科校区')
insert into stuinfo values('张哲','15班',21,'男','1369','汉口','武汉南湖华科校区')
insert into stuinfo values('杨强','15班',20,'女','1784','武昌','武汉南湖华科校区')
insert into stuinfo values('张强','10班',20,'男','1563','汉口','武汉南湖华科校区')
insert into stuinfo values('张小凤','一班',19,'女','1758','汉阳','武汉南湖华科校区')
insert into stuinfo values('张小强','15班',20,'男','1569','汉口','武汉南湖华科校区')

--向Courseinfo添加数据
select *From courseinfo
insert into courseinfo values('A001','语文','45课时','蔡老师')
insert into courseinfo values('A002','数学','25课时','梅老师')
insert into courseinfo values('A003','音乐','15课时','谢老师')
insert into courseinfo values('A004','英语','20课时','罗老师')
insert into courseinfo values('A005','JAVA','50课时','李老师')
insert into courseinfo values('A006','SQL','35课时','刘老师')
insert into courseinfo values('A007','HTML','40课时','林老师')
insert into courseinfo values('A008','WINFO','30课时','宋老师')

--向stueaxm中添加数据
select *from stuexam
insert into stuexam values(1,'A001',70)
insert into stuexam values(1,'A002',90)
insert into stuexam values(1,'A003',80)
insert into stuexam values(1,'A004',95)
insert into stuexam values(1,'A005',93)
insert into stuexam values(2,'A001',69)
insert into stuexam values(2,'A003',93)
insert into stuexam values(2,'A005',99)
insert into stuexam values(3,'A004',86)
insert into stuexam values(3,'A005',78)
insert into stuexam values(4,'A001',84)
insert into stuexam values(4,'A002',89)
insert into stuexam values(5,'A005',82)
insert into stuexam values(6,'A004',90)
insert into stuexam values(7,'A004',83)
insert into stuexam values(7,'A005',78)
insert into stuexam values(8,'A001',95)
insert into stuexam values(8,'A003',96)
insert into stuexam values(9,'A001',100)
insert into stuexam values(9,'A004',82)
insert into stuexam values(10,'A005',87)
insert into stuexam values(10,'A003',90)
insert into stuexam values(12,'A004',55)
insert into stuexam values(13,'A005',60)
insert into stuexam values(14,'A003',59)
insert into stuexam values(12,'A001',58)
insert into stuexam values(13,'A004',45)
insert into stuexam values(14,'A002',49)
insert into stuexam values(14,'A003',59)
insert into stuexam values(15,'A001',60)
insert into stuexam values(15,'A004',85)
insert into stuexam values(16,'A005',70)
insert into stuexam values(16,'A003',58)
insert into stuexam values(17,'A001',55)
insert into stuexam values(20,'A002',80)
insert into stuexam values(20,'A004',49)

--要求
--1.求学生的所有信息
select *from stuinfo
--2)求学生的姓名和、性别、住址和电话
select stuname,stusex,stutel,stuAddress from stuinfo
--3)求”15班”学生的学号、姓名、性别、住址和电话
select stuid,stuname,stusex,stuAddress,stutel from stuinfo where class='15班'
--4)求参加了考试的学生学号（没参考的学生为11，15，16，17）
select top 1 *from stuinfo
select top 1 *from stuexam

select stuid as'参考学生' from stuinfo
intersect
select stuid from stuexam
--5)求”15班”后两名学生的信息
select *from stuinfo where class='15班' and stuid=16 or stuid=17
--6)求”15班”前百分之二学生的信息
select top 20 percent *from stuinfo where class='15班'
--7)求'A001'的学生学号和成绩
select a.stuid, b.score
from stuinfo a,stuexam b
where a.stuid=b.stuid and b.Courseid='A001'
--8)求' A001'的学生学号和成绩，并要求对查询结果按成绩降序排列，
--如果成绩相同，则按学号的升序排列
select a.stuid, b.score
from stuinfo a,stuexam b
where a.stuid=b.stuid and b.Courseid='A001'
order by Score desc
--9)求' A001'成绩在90到100之间的学生学号和成绩
select a.stuid, b.score
from stuinfo a,stuexam b
where a.stuid=b.stuid and b.Courseid='A001'and score>=90 and score<=100
--10)求' A001'成绩在90到100之间的学生学号和成绩，并将成绩乘以系数0.8输出
select a.stuid, b.score*0.8 as '0.8后的分'
from stuinfo a,stuexam b
where a.stuid=b.stuid and b.Courseid='A001'and score>=90 and score<=100
--11)求'15班'和'1班'姓张的学生信息
select stuname,stuid from stuinfo where stuname like'张%'and class='一班'
union
select stuname ,stuid from stuinfo where stuname like'张%'and class='15班'
--12)求'15班'和'1班'姓张的并且只有两个字的学生信息
select stuname,stuid from stuinfo where stuname like'张%'and class='一班' and len(stuname)<3
union
select stuname ,stuid from stuinfo where stuname like'张%'and class='15班'and len(stuname)<3
--13)求缺少了成绩的学生学号和课程号
select top 1 *from stuinfo
select top 1 *from stuexam
select top 1*from Courseinfo

(select stuid as'缺考学生' from stuinfo
except
select stuid from stuexam)

select distinct courseid from stuexam
except
select courseid from Courseinfo
--14)求学生的总人数
select count(*)as '学生总数' from stuinfo
--15)求学生的总人数，并且用三种方法给其取别名
select count(*)as '学生总数' from stuinfoun
select count(*) '学生总数' from stuinfo
select '学生总数'=count(*) from stuinfo
--16)求参加了考试的总人数(假设只要学号在成绩表中出现过，就代表此人参加过考试)
select distinct count(*)'参考总人数' from stuexam
--17)求课程号和参加考试的人数
--18)求各班的总人数
select class +' '+count(*)as' 总数 'from stuinfo where class='一班'
union
select count(*)as' 总数 'from stuinfo where class='二班'
--19)求男女生各有多少人
select *from stuinfo
select stusex ,count(*) as'人数'from stuinfo group by stusex
--20)求“15班”男女生各有多少人
select class,stusex ,count(*) as'人数'from stuinfo group by stusex ,class having class='15班'
--21)求三个区各有多少人
select stuarea,count(*) as '人数' from stuinfo group by stuarea
--22)求各科的总分
select *from stuexam
select distinct courseid,sum(score) as'总分' from stuexam group by courseid
--23)求各科的平均分
select distinct courseid,avg(score) as'平均分' from stuexam group by courseid
--24)求各科的最高分
select distinct courseid,max(score) as'最高分' from stuexam group by courseid
--25)求各科的最低分
select distinct courseid,min(score) as'最低分' from stuexam group by courseid
--26)求各科的总分、平均分、最高分、最低分
select distinct courseid,sum(score) as'总分',avg(score) as'平均分' ,max(score) as'最高分',
min(score) as'最低分'from stuexam group by courseid
--27)求‘15班’各科的总分、平均分、最高分、最低分
select *from stuexam
select top 1 *from stuinfo
select class,courseid,sum(a.score) as'总分',avg(score) as'平均分' ,max(score) as'最高分',
min(score) as'最低分'from stuexam a,stuinfo b
where a.stuid=b.stuid and class='15班'
group by class,courseid
--28)求’ A001’没有及格的人数
select count(*) as '没及格'from stuexam where courseid='A001' and score<=60
--29)求’15班’A001没及格的人数
select class,courseid,count(*) as '没及格'
from stuexam a,stuinfo b
where a.stuid=b.stuid and class='15班' and score<=60and courseid='A001'
group by class,courseid
--30)求有两门以上不及格的学生学号
--有16.17.18.30题有疑问