常规Sql语句练习题

常规sql语句练习题

//1.创建表student

drop table student
create table student(
sno char(6) primary key,
sname varchar(8),
ssex char(4),
sage smallint,
sdept varchar(15)
)
//2.创建表course
create table course(
cno char(4) primary key,
cname varchar(20),
cpno char(4),
ccredit tinyint
)
//3.创建表sc
create table sc(
sno char(6),
cno char(4),
grade decimal(12.2),
primary key(sno,cno)
)
//4.在student表中插入信息
select *from student
insert into student values('4001','赵茵','男','20','sx')
insert into student values('4002','杨华','女','21','jsj')
//5.在student表添加属性sbirthdate 类型datetime
alter table student add sbirthdate datetime
******************************练习*********************************************
delete
//1.删除所有jsj系的男生
delete from student where sdept='jsj'and ssex='男';
//2.删除“原理”的课的选课纪录
delete from sc where cno in(select cno from course where cname='数据库原理')
//update
1修改0001学生的系科为:jsj
update student set sdept='jsj' where sno='0001'
2把陈小明的年龄加1岁，性别改为女。
update student set ssex='女',sage=sage+1 where sname="陈小明"
//select
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
select sno,sname,sage from student where ssex='女' and sage between 19 and 21 order by sage desc
2查询姓名中第2个字为“明”字的学生学号、性别。
select sno,ssex from student where sname like '_明%'
3查询 1001课程没有成绩的学生学号、课程号
select sno,cno from sc where cno='1001' and grade is null
4查询jsj 、sx、wl 系的年龄大于25岁的学生学号,姓名，结果按系排列
select sno.sname from student where sdept in('jsj','sx','wl') and sage >'25' group by sdept
5查询 student 表中的学生共分布在那几个系中。（distinct）
select distinct sdept from student
6查询0001号学生1001，1002课程的成绩。
select grade from sc where sno='0001' and cno in('1001','1002')
******************************统计*********************************************
1查询姓名中有“明”字的学生人数。
select count(*) from student where sname like '%明%'
2计算‘jsj’系的平均年龄及最大年龄。
select avg(sage) as a,max(sage) as b from student where sdept='jsj'
3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列
select sum(grade),avg(grade),max(grade),min(grade) from sc where group by cno order by avg(grade) desc
4计算 1001,1002 课程的平均分。
select avg(grade) from sc where cno in('1001','1002') order by cno
5查询平均分大于80分的学生学号及平均分
select sno , avg(grade) from sc group by sno having avg(grade) >'80'
6统计选修课程超过 2 门的学生学号
select sno from sc group by sno having count(*) >'2'
7统计有10位成绩大于85分以上的课程号。
select cno from sc grade>'85' group by cno having count(*)='10'
8统计平均分不及格的学生学号
select sno from sc group by sno having avg(grade) <'60'
9 统计有大于两门课不及格的学生学号
select sno from sc where grade<'60' group by sno having count(*)>'2'
***************************三 嵌套、相关及其他 **********************************************
1 查询平均分不及格的学生人数
select count(*) from sc group by sno having avg(grade)<'60'
2 查询没有选修1002 课程的学生的学生姓名
select sname from student where sno not in(select sno from sc where cno='1002')
3 查询没有选修1001，1002课程的学生姓名。
select sname from student where sno not in(select sno from sc where cno in('1001','1002') )