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='数据库');