SQL 学生选课创建，插入，修改，删除以及SQL2005安装

sql平台为SQL2005/WIN7

初学SQL，有什么问题大家一起交流。

SQL2005(x64,x86集合)下载地址：http://pan.baidu.com/s/1c0bSPhQ

安装教程：http://jingyan.baidu.com/article/046a7b3efaaa5cf9c37fa951.html

SQL2005如何完全安装完成，出现只有配置工具的现象，

x64请安装：http://pan.baidu.com/s/1kTEUnzT

x86请安装：http://pan.baidu.com/s/1hqlUcBU

--创建表

SQL创建sc,course,student

sc(sno,cno,grade);选课表（学号，课程号，成绩）

course(cno,cname,ccredit);课程表（课程号，课程名，学分）

student(sno,sname,sage,sdept,ssex);学生表（学号，学生名，年龄，系别，系别）

if Object_id('sc') is not null
  Drop table sc
if Object_id('student') is not null
  Drop table student
if Object_id('course') is not null
  Drop table course

--定义SC表
create table sc(
  sno char(20),
  cno char(20),
  grade smallint,
  primary key(sno,cno)
);

--定义student表
create table student(
   sno char(10) primary key,
   sname char(20) not null,
   sage smallint,
   ssex char(2),
   sdept varchar(20)
);


--定义course表
create table course(
    cno char(10) primary key,
    cname varchar(20) not null,
    credit smallint

);

--插入student表数据

insert into student values('001','张三','20','f','CS');
insert into student values('002','李晨','21','f','E');
insert into student values('003','李丽','21','m','E');
insert into student values('004','高飞','23','f','MA');
insert into student values('005','苏瑞','20','f','CS');
insert into student values('006','赵达','22','f','MA');
insert into student values('007','韩愈','22','m','C');
insert into student values('008','郑乐','24','m','CS');
insert into student values('009','许三朵','23','f','MA');
insert into student values('010','王晓','20','m','C');
insert into student values('011','丁二','21','f','E');
insert into student values('012','钟瑜','23','f','CS');
insert into student values('013','刘莉莉','21','m','MA');
insert into student values('014','柳叶青','23','f','C');
insert into student values('015','张其安','22','m','CS'); 


--插入course表信息
insert into course values('c01','数据库','2');
insert into course values('c02','数学','4');
insert into course values('c03','物理','3');
insert into course values('c04','C语言','3');
insert into course values('c05','历史','2');
insert into course values('c06','操作系统','3');
insert into course values('c07','iOS开发','4');
insert into course values('c08','软件工程','2');
insert into course values('c09','汇编语言','2');
insert into course values('c10','英语','3');


--插入SC选课表

--001学生选课
insert into sc values('001','c02','89');
insert into sc values('001','c04','70');
insert into sc values('001','c08','74');
insert into sc values('001','c10','87');
insert into sc values('001','c03','60');

--002学生选课
insert into sc values('002','c01','77');
insert into sc values('002','c03','59');
insert into sc values('002','c02','90');
insert into sc values('002','c04','100');
insert into sc values('002','c05','45');
insert into sc values('002','c06','65');
insert into sc values('002','c07','80');
insert into sc values('002','c08','98');
insert into sc values('002','c09','66');
insert into sc values('002','c10','88');


--003学生选课
--未选课

--004学生选课
insert into sc values('004','c09','90');
insert into sc values('004','c06','43');
insert into sc values('004','c10','85');
insert into sc values('004','c05','86');
insert into sc values('004','c07','60');
insert into sc values('004','c08','78');

--005学生选课

insert into sc values('005','c10','88');
insert into sc values('005','c02','69');
insert into sc values('005','c04','84');
insert into sc values('005','c05','88');
insert into sc values('005','c03','65');
insert into sc values('005','c06','70');
insert into sc values('005','c07','81');


--006学生选课

insert into sc values('006','c10','62');
insert into sc values('006','c02','87');
insert into sc values('006','c04','60');
insert into sc values('006','c05','74');
insert into sc values('006','c03','63');
insert into sc values('006','c06','70');
insert into sc values('006','c07','91');
insert into sc values('006','c01','70');
insert into sc values('006','c09','81');


--007学生选课

insert into sc values('007','c01','80');
insert into sc values('007','c02','67');
insert into sc values('007','c04','62');
insert into sc values('007','c05','74');
insert into sc values('007','c03','45');
insert into sc values('007','c08','68');
insert into sc values('007','c09','99');
insert into sc values('007','c10','100');


--008学生选课

insert into sc values('008','c08','61');
insert into sc values('008','c02','82');
insert into sc values('008','c04','88');


--009学生选课

insert into sc values('009','c02','67');
insert into sc values('009','c06','34');
insert into sc values('009','c08','78');
insert into sc values('009','c10','74');
insert into sc values('009','c03','80');
insert into sc values('009','c04','70');
insert into sc values('009','c07','94');


--010学生选课

insert into sc values('010','c10','80');
insert into sc values('010','c02','84');
insert into sc values('010','c04','65');
insert into sc values('010','c05','72');
insert into sc values('010','c03','87');
insert into sc values('010','c08','70');
insert into sc values('010','c07','91');
insert into sc values('010','c01','70');
insert into sc values('010','c09','81');


--011学生选课

insert into sc values('011','c04','64');
insert into sc values('011','c07','75');
insert into sc values('011','c09','60');
insert into sc values('011','c02','84');


--012学生选课

insert into sc values('012','c01','97');
insert into sc values('012','c02','83');
insert into sc values('012','c03','60');
insert into sc values('012','c04','70');
insert into sc values('012','c05','88');
insert into sc values('012','c06','64');
insert into sc values('012','c07','91');
insert into sc values('012','c08','70');
insert into sc values('012','c09','84');
insert into sc values('012','c10','81');


--013学生选课

insert into sc values('013','c01','95');
insert into sc values('013','c02','86');
insert into sc values('013','c04','70');
insert into sc values('013','c06','57');
insert into sc values('013','c08','63');
insert into sc values('013','c10','54');

--014学生选课

insert into sc values('014','c01','78');
insert into sc values('014','c02','87');
insert into sc values('014','c03','75');
insert into sc values('014','c05','99');
insert into sc values('014','c06','34');
insert into sc values('014','c07','48');
insert into sc values('014','c09','84');
insert into sc values('014','c10','89');


--015学生选课

insert into sc values('015','c02','87');
insert into sc values('015','c07','96');

--以下是老师布置的练习题，我觉得做完以后大有收获，拿出来大家交流一下

1、在Course表中添加“教师”列（20个长度的变长字符串）

2、为每门课程添加教师信息。

3、将教师列修改为非空列。

4、查询选修了刘老师的课程的学生。

5、检索选修了课程号为C01或C02课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。

6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列。

7. 列出没有选课的学生姓名

8. 列出平均分最高的学生所在系的所有学生的姓名

9.查询CS系C01课程的成绩比C01课程的平均分高的学生学号

10.查询既选修了C01又选修了C02的学生

11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。

12.检索所有CS系学生都选修了的课程（列出课程号）

13.查询年龄高于其所在系的平均年龄的学生姓名

14.查询每位同学的选课中成绩最高的课程对应的学号，姓名，课程名，成绩

15.为MA系学生选修必修课C05

16.将CS系,C01课程学生的成绩加10分

17.将每位同学的最低分加10分（选）

18.将”数据库”的选课记录全部删除

--1、检索所有学生中年龄最大的学生的姓名及年龄。
select sno,sname from student
where sage=(select max(sage) from student)

select sno,sname from student
where sage>=all(select sage from student)
--2、求每一个学生的最高分和最低分。
select sno,max(grade) as '最高分',min(grade) as '最低分'from sc
group by  sno;

select sno,grade from sc
where grade>=all(select grade from student) or grade<=all(select grade from student)

--3、查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓名，成绩，并按成绩降序排列。
select student.sno,sname,grade from sc,student
where sdept='CS' and cno='c05' 
and student.sno =sc.sno
order by grade desc;

select sno,grade from sc
where sno in(select sno from student where sdept='cs')and cno='c05'
order by grade desc;
--4、检索选修了“C语言”课程的学生的姓名（可用子查询—IN或Exists）
--子查询in
select sname from student
where sno in(
select sno from sc 
where cno in (
select cno from course where cname='C语言'))

--子查询exists
select sname from student
where exists(
select * from sc 
where sno=student.sno and exists(
select * from course where cno=sc.cno and cname='C语言'
)
)

select sname from student,sc
where sc.sno=student.sno and cno=(select cno from course where cname='C语言')


--5、检索选修了课程号为C01或C02课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。
select sname,cname,grade from student,sc,course
where student.sno=sc.sno 
and sc.cno=course.cno 
and sc.cno in('c01','c02') and grade>=70;

--6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列（使用外连接将没有选课的同学列出来）。
select sname,sc.cno,grade from course,student, sc
where student.sno=sc.sno and course.cno=sc.cno
order by grade desc;

--7. 列出没有选课的学生姓名
select sno,sname from student
where sno not in(select sno from sc)

select sno,sname from student
where not exists(select sno from sc where sno=student.sno)
--8. 列出平均分最高的学生所在系的所有学生的姓名
select sname,sdept from student
where sdept in(
select sdept from student  
where sno in(
select sno from sc 
group by sno
having avg(grade)>=all(select avg(grade)from sc group by sno))
)


--9.查询CS系C01课程的成绩比C01课程的平均分高的学生学号
select student.sno from student,sc
where sc.sno=student.sno and sdept='CS'and sc.cno='c01'and grade>
(select avg(grade) from sc
where cno='c01');


--10.查询既选修了C01又选修了C02的学生
--in
select sno from student
where sno in(select sno from sc where cno='c01') and sno in(select sno from sc where cno='c02')

select sno from student
where exists(select sno from sc where cno='c01' and sno=student.sno) and sno in(select sno from sc where cno='c02'and sno=student.sno)

select sno from student
where sno in(select sno from sc 
where cno='c01' 
intersect
select sno from sc
where cno='c02')

--11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。
select sno,avg(grade) from sc
where grade>=60
group by sno
having count(*)>4
order by avg(grade) desc;

--12.检索所有CS系学生都选修了的课程（列出课程号）

select cno from sc,student
where sc.sno=student.sno and student.sdept='CS'
group by cno
having count(*)=(select count(*) from student where sdept='CS')


select cno from course
where not exists(
select * from student
where sdept='CS' and not exists(
select * from sc
where student.sno=sc.sno and course.cno=sc.cno
)
)

--没有选课的学生
select sno from student
where not exists(
 select * from sc where sc.sno=student.sno 
)

--13.查询年龄高于其所在系的平均年龄的学生姓名
select sname from student x
where sage>(select avg(sage) from student y where x.sdept=y.sdept)


--14.查询每位同学的选课中成绩最高的课程对应的学号，姓名，课程名，成绩
select sc.sno,sname,cname,grade from sc,student,course
where sc.sno=student.sno and sc.cno=course.cno 
and grade=(select max(grade) from sc where sc.sno=student.sno)


select sc.sno,sname,cname,grade from sc,student,course
where sc.sno=student.sno and sc.cno=course.cno
and grade>=all(select grade from sc where student.sno=sc.sno)

--创建视图然后查询
create view s1(sno,sname,cname,grade)
as
select sc.sno,sname,cname,grade from sc,student,course
where sc.sno=student.sno and course.cno=sc.cno

--select sno,sname,max(grade) from s1
--group by (sno,sname)

--15.为MA系学生选修必修课C05
insert into sc(sno,cno) 
select sno,'c05' from student 
where sdept = 'MA' and 
sno not in (select sno from sc where cno='c05')

insert into sc(sno,cno)
select sno,'c05' from student 
where sdept='MA' and not exists(select sno from sc where cno='c05' and sno=student.sno )
--16.将CS系,C01课程学生的成绩加10分
update sc
set grade=grade-10
where cno='c01' and sno in(select sno from student where sdept='CS') 

select * from sc,student
where cno='c01' and student.sno=sc.sno and student.sdept='CS'
--17.将每位同学的最低分加10分（选）
update sc 
set grade=grade+10
where grade=(select min(grade) from sc y where sc.sno=y.sno)

update sc 
set grade=grade-10
where grade=(select min(grade) from sc y 
group by sno
having sc.sno=y.sno
)
--18.将”数据库”的选课记录全部删除
delete from sc
where '数据库'=(select cname from course where sc.cno=course.cno);

delete from sc
where cno=(select cno from course where course.cname='数据库');