SQL server 数据库练习题及答案（练习2）

使用你的名字创建一个数据库

创建表：

数据库中有三张表，分别为student,course,SC（即学生表，课程表，选课表）

问题：

--1.分别查询学生表和学生修课表中的全部数据。
--2.查询成绩在70到80分之间的学生的学号、课程号和成绩。
--3查询C01号课程成绩最高的分数
--4.查询学生都选修了哪些课程,要求列出课程号。
--8.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
--9.统计每个系的学生人数。
--10.统计每门课程的修课人数和考试最高分。
--11.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
--12.统计选修课的学生总数和考试的平均成绩。
--13.查询选课门数超过2门的学生的平均成绩和选课门数。
--14.列出总成绩超过200分的学生,要求列出学号、总成绩。
--16.查询选修了VB号课程的学生的姓名和所在系。
--17.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
--18.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
--19.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
--20.查询哪些课程没有人选,要求列出课程号和课程名。
--21.查询有考试成绩的所有学生的姓名、修课名称及考试成绩
--22.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,并要求将这两个查询结果合并成一个结果集,并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
--//此题用到了并union查询

--23.用子查询实现如下查询:
--(1)    查询选修了C01号课程的学生的姓名和所在系。
--(2)    查询数学系成绩80分以上的学生的学号、姓名。
--(3)    查询计算机系学生所选的课程名.
--24.将计算机系成绩高于80分的学生的修课情况插入到另一张表中,分两种情况实现：
--(1)    在插入数据过程中建表。
--(2)    先建一个新表,然后再插入数据。
--25.删除修课成绩小于50分的学生的修课记录
--26.将所有选修了"c01"课程的学生的成绩加10分。
答案：

/*创建数据库*/
create database gaohongyuan
on primary 
(name="gaohongyuan_data",
filename="c:\gao\gaohongyuan_data.mdf", 
size=8MB,
maxsize=100MB, 
filegrowth=10%) 
log on 
(name="gaohongyuan_log.ldf", 
filename="c:\gao\gaohongyuan_log.ldf", 
size=1MB, 
filegrowth=10%)

use gaohongyuan/*切换数据库*/

/*创建学生表*/
create table student(
Sno varchar(20) primary key not null,
Sname varchar(32) not null,
Ssex varchar(2) default('男'),
Sage int check(Sage>=0 and Sage<=100),
Sdept varchar(32) not null
)
go
/*创建课程表*/
create table course(
Cno varchar(20) primary key not null,
Cname varchar(32) unique not null,
Hours int check(Hours>0) not null
)
go
/*创建选课表*/
create table SC(
Sno varchar(20) references student(Sno),
Cno varchar(20) references course(Cno),
Grade int check(Grade>=0 and Grade<=100) null
)
go
/*插入数据*/
select * from student/*查询语句*/
insert student values
('9512101','李勇','男',19,'计算机系'),
('9512102','刘晨','男',20,'计算机系'),
('9512103','王敏','女',20,'计算机系'),
('9521101','张立','男',22,'信息系'),
('9521102','吴宾','女',21,'信息系'),
('9521103','张海','男',20,'信息系'),
('9531101','钱小力','女',18,'数学系'),
('9531102','王大力','男',19,'数学系')
go
select * from course/*查询语句*/
insert course values
('C01','计算机文化学',70),
('C02','VB',90),
('C03','计算机网络',80),
('C04','数据库基础',108),
('C05','高等数学',180),
('C06','数据结构',72)
go
select * from SC/*查询语句*/
insert SC(Sno,Cno,Grade) values
('9512101','C01',90),
('9512101','C02',86),
('9512101','C06',null),
('9512102','C02',78),
('9512102','C04',66),
('9512102','C01',82),
('9512102','C02',75),
('9512102','C04',92),
('9512102','C05',50),
('9521103','C02',68),
('9521103','C06',null),
('9531101','C01',80),
('9531101','C05',95),
('9531102','C05',85)
go

--1
select * from student
select * from SC
select * from course
--2
select Sno 学号,Cno 课程号,Grade 成绩 from SC where Grade between 70 and 80
--3
select Cno,max(Grade) from SC where Cno='C01' group by Cno
--4
select Sname,Cname,co.Cno from student st join SC on st.Sno=SC.Sno join course co on co.Cno=SC.Cno 
--8
select Sname 姓名,avg(Grade) 平均分,max(Grade) 最高分,min(Grade) 最低分 from student st join SC on st.Sno=Sc.Sno where Cno='C02' group by Sname
--9
select Sdept,count(Sdept) from student group by Sdept
--10
select Cname,count(SC.Cno),max(Grade) from SC join course co on SC.Cno=co.Cno group by Cname
--11
select Sname,count(SC.Cno) from student st join SC on SC.Sno=st.Sno group by Sname order by count(SC.Cno) asc
--12
select count(distinct Sno),avg(Grade) from SC 
--13
select Sname,avg(Grade),count(SC.Cno) from student st join SC on SC.Sno=st.Sno join course co on SC.Cno=co.Cno group by Sname having count(SC.Cno)>=2
--14
select Sno,sum(Grade) from SC group by Sno having sum(Grade)>200
--16
select Sname,Sdept,SC.Cno,Cname from student st join SC on st.Sno=SC.Sno join course co on co.Cno=SC.Cno where Cname='VB'
--17
select Sname,SC.Cno,Grade from student st join SC on st.Sno=SC.Sno order by Grade desc
--18
select Sname,Ssex,Cname,Grade from student st join SC on st.Sno=SC.SNo join course co on co.Cno=SC.Cno where Cname='数据库基础'
--19
select sname,sage from student where sage in (select Sage from student group by sage having count(Sage)>1)
--20
select distinct SC.Cno,Cname from SC left join course co on SC.Cno=co.Cno 
--21
select Sname,Cname,Grade from student st join SC on st.Sno=SC.Sno join course co on co.Cno=SC.Cno where Grade is not null
--22
select Sname 姓名,Ssex 性别,Cname 修课名称,Grade 修课成绩,Sdept 系别 from student st join SC on st.Sno=SC.Sno join course co on co.Cno=SC.Cno where Sdept='信息系'
union select Sname 姓名,Ssex 性别,Cname 修课名称,Grade 修课成绩,Sdept 系别 from student st join SC on st.Sno=SC.Sno join course co on co.Cno=SC.Cno where Sdept='计算机系'
--23
----1
select Sname,Sdept,Cno from student st join SC on st.Sno=SC.Sno where Cno='C01'
----2
select SC.Sno,Sname,Grade from student st join SC on st.Sno=SC.Sno where Grade>=80
----3
select Sname,Sdept,Cname from student st join SC on st.Sno=SC.Sno join course co on co.Cno=SC.Cno where Sdept='计算机系'
--24
----1
select * into SC1 from SC where Grade>80
select * from SC1
----2
create table SC2(
Sno varchar(20) references student(Sno),
Cno varchar(20) references course(Cno),
Grade int check(Grade>=0 and Grade<=100) null
)
insert into SC2 select * from SC where Grade>80
select * from SC2
--25
delete from SC where Grade<50
--26
update SC set Grade=Grade+10 where Cno='C01'