一 在数据库 school 中建立student , sc, course 表。
学生表、课程表、选课表属于数据库 School ,其各自的数据结构如下:
学生 Student (Sno,Sname,Ssex,Sage,Sdept)
序号 1 2 3 4 5 Sno Sname Ssex Sage sdept 列名 含义 学号 姓名 性别 年龄 系科 数据类型 字符型(char) 字符型(varchar) 字符型(char) 整数 (smallint) 字符型(varchar) 长度 6 8 2 15
课程表 course(Cno,Cname,Cpno,Ccredit)
序号 1 2 3 4 列名 Cno cname Cpno Ccredit 含义 课程号 课程名 先修课 学分 数据类型 字符型(char) 字符型(varchar) 字符型(char) 短整数 (tinyint) 长度 4 20 4 学生选课 SC(Sno,Cno,Grade)
序号 1 2 3
Sno Cno Grade 列名 含义 学号 课程号 成绩 数据类型 字符型(char) 字符型(char) 小数(decimal) 长度 6 4 12,2 二 设定主码
1 Student表的主码:sno 2 Course表的主码:cno 3 Sc表的主码:sno,cno
1写出使用 Create Table 语句创建表 student , sc, course 的SQL语句
2在student表中插入信息 学号 4001 4002 姓名 赵茵 杨华 男 女 性别 20 21 年龄 SX JSJ 系科
3 删除student表中的元组
4在数据库school中删除关系student
5在student表添加属性sbirthdate 类型 datetime
练习 Delete
1 删除所有 JSJ 系的男生 delete from Student where Sdept=’JSJ’ and Ssex=’男’; 2 删除“数据库原理”的课的选课纪录
delete from SC where Cno in (select Cno fromCourse where Cname=’数据库原理’);
Update
1 修改 0001 学生的系科为: JSJ
2 把陈小明的年龄加1岁,性别改为女。 2 修改李文庆的1001课程的成绩为 93 分 3 把“数据库原理”课的成绩减去1分
Select 查询语句
一 单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 2查询姓名中第2个字为“明”字的学生学号、性别。 3查询 1001课程没有成绩的学生学号、课程号
4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名,结果按系排列 5按10分制查询学生的sno,cno,10分制成绩
(1-10分 为1 ,11-20分为2 ,30-39分为3,。。。90-100为10) 6查询 student 表中的学生共分布在那几个系中。(distinct) 7查询0001号学生1001,1002课程的成绩。
二 统计
1查询姓名中有“明”字的学生人数。 2计算‘JSJ’系的平均年龄及最大年龄。 3查询学生中姓名为张明、赵英的人数
4计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列 5 计算 1001,1002 课程的平均分。
6 查询平均分大于80分的学生学号及平均分 7 统计选修课程超过 2 门的学生学号
8 统计有10位成绩大于85分以上的课程号。 9 统计平均分不及格的学生学号
10 统计有大于两门课不及格的学生学号
三 连接
1查询 JSJ 系的学生选修的课程号
2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法) 3查询数据库原理不及格的学生学号及成绩
4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法) 5查询平均分不及格的学生的学号,姓名,平均分。 6查询女学生平均分高于75分的学生姓名。
7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出,不能
遗漏)
四 嵌套、相关及其他
1 查询平均分不及格的学生人数
2 查询没有选修1002 课程的学生的学生姓名
3 查询平均分最高的学生学号及平均分 (2种方法 TOP , any , all) *4 查询没有选修1001,1002课程的学生姓名。
5 查询1002课程第一名的学生学号(2种方法) 6 查询平均分前三名的学生学号
7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 9 查询每门课程成绩都高于该门课程平均分的学生学号 10 查询大于本系科平均年龄的学生姓名
答案
参考答案
1 create table student (sno char(6), sname varchar(8), ssex char(2), sage smallint, sdept varchar(15), primary key(sno));
create table sc
(sno char(6), cno char(4),
grade decimal(12,2), primary key(sno,cno));
insert into student
values( ’4001’,’赵茵’,’男’,20,’SX’)
delete from student
drop table student
alter table student add sbirthdate datetime
1 select sno, sname, sage from student
where ssex=’女’ and sage between 19 and 21 order by sage desc; 2 select sno, ssex from student
where sname like ’_明% ’ ; 3 select sno, cno from sc
where grade is null and cno=’1001’ ; 4 select sno, sname from student
where sdept in (’JSJ’,’SX’,’WL’) and sage>25 group by sdept;
select sno, cno, grade/10.0+1 as level from sc ;
select distinct sdept from student ; select grade from sc
where sno=’0001’ and (cno=’1001’ or cno=’1002’) ;
select count(*) from student where sname like ’%明% ’ ; select avg(sage),max(sage) from student where sdept=’JSJ’ ; select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno
order by avg(grade) desc ;
select cno, avg(grade) from sc where cno in(‘1001’,’1002’) group by cno ;
select sc.sno ,avg(grade) from sc group by sc.sno
having avg(grade)>80 ;
select sno from sc group by sno having count(*)>2 ;
select cno from sc where grade>85 group by cno having count(*)=10 ; select sno from sc group by sno having avg(grade)<60 ;
select sno from sc where grade<60 group by sno having count(*)>2 ;
select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’ ; a:select sname from student,sc where student.sno=sc.sno and cno=’1002’
b:select sname from student where sno in (select sno from sc where cno=’1002’)
select sno,grade from sc,course
where sc.cno=course.cno and cname=’数据库原理’ and grade <60 a:select sname from student ,sc,course
where student.sno=sc.sno and sc.cno=course.cno and grade>80 and cname=’ 数据库原理’ b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname=’ 数据库原理’)) select sno,sname,avg(grade) from sc,student where student.sno=sc.sno group by student.sno having avg(grade)<60
a:select sname from student where ssex=’女’ and sno in(select sno from sc group by sno having avg(grade)>75)
b:select sname from sc,student where student.sno=sc.sno and ssex=’女’ group by student.sno having avg(grade)>75
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=’男’
select count(*) from student where sno in( select sno from sc group by sno having avg(grade)<60)
select sname from student where sno not in(select sno from sc where cno=’1002’)
student
0001 aa X 0002 bb
0003 cc X Sc
0001 1001 0001 1002 0002 1001 0003 1002
Select sname from student where not exists(select* from sc where cno=’1002’ and sc.sno=student.sno)
a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc b:select sno, avg(grade) from sc group by sno
having avg(grade)=(select top 1 avg(grade) from sc group by sno order by avg(grade) desc) c:select sno, avg(grade) from sc group by sno
having avg(grade)>=all ( select avg(grade) from sc group by sno)
select sname from student where not exists(
select * from course where cno in(‘1001’,’1002’) and
not exists(select * from sc where sno =student.sno and cno=course.cno) ) a:select top 1 sno from sc cno=’1002’ order by grade desc b:select sno from sc where cno=’1002’ and grade >=all (
select grade from sc where cno=’1002’)
select top 3 sno from sc group by sno order by avg(grade)desc a:select*from student where sdept=’JSJ’ and sage>19
b:select*from student where sdept=’JSJ’ except select* from student where sage<19 select student.sno,sname from student,sc where cno=’1001’ and grade>90 union select sno,sname from student where sno in ( select sno from sc group by sno having avg(grade)>85)
select sno from student where sno not in(select sno from sc x where grade< ( select avg(grade) from sc where cno=x.cno) ) select sname from student x where sage> (
select avg(sage) from student where sdept=x.sdept)