常规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') )