SQL查询练习

1、查询student表中的所有数据

select * from student;

2、从course表中查询cid、cname、choure、tname;

select cid , cname , chour , tname from course ;

3、对course的列进行重命名

select cname as '课程名称' , tname as '教师名称' from corese;

4、选择前3条记录

select * from student where rownum < 4;

选择后3条记录

select * from student where rownum < 4 order by sid desc;

5、排序

从小到大

select * from course order by char asc;

从大到小

select * from course order by char desc;

6、统计记录数

select count(*) from student;

select count(sid) from student;

select count(ssex) from student where ssex='m';

统计年龄在20岁以下的人数

select count(sage) from student where sage<20;

统计20岁以下及30岁以上的人数

select count(sage) from student where sage<20 or sage>30;

统计20岁到30岁之间的人数

select count(sage) from student where sage>20 and sage<30;

select count(sage) from student where sage between 20 and 30;

7、查询姓名为“宋峰”的老师

select * from course where tname="宋峰";

查询姓“吴”的老师

select * from course where tname="吴%";

查询最后一个字是“红”的老师

select * from course where tname="%红";

查询包含“小”的老师

select * from course where tname="%小%";

查询姓名为3个字的老师

select * from course where tname="_ _ _";

查询姓陈并且后面跟两个字的老师

select * from course where tname=" 陈_ _";

8、聚合函数count、max、min、sum、avg

查询课程的数量

select count(*) from course;

查询年龄最大的学生

select * from student where ( select max(age) from student );

查询年龄最小的学生

select * from student where ( select min(age) from student );

统计课时的平均数

select avg(choure) from course

9、嵌入查询

查询比年龄最小的男生都大的学生

select * from student where age > ( select min(age) from student where six = 'm' );

查询比年龄最大的男生都大的学生

select * from student where age > ( select max(age) from student where six = 'm' );

10、内连接

查询出所有人的订购

select Persons.LastName, Persons.FirstName, Orders.OrderNo

from Persons

inner join Orders

on Persons.Id_P=Orders.Id_P

oyder by Persons.LastName