--查询和张三同年出生的学生信息
select * from student where stuname!='张三' and to_char(birthday,'yyyy') in (select to_char(s.birthday,'yyyy') from student s where s.stuname='张三');
--查询山东的未成年女生的姓名、年龄，并以年龄倒序排列
select s.stuname,s.age from student s inner join diqv d on s.areaid=d.id where d.areaname='山东' and s.sex='女' and s.age<18 order by s.age desc;
--查询2000班中的学生的姓名、地区名
select s.stuname,d.areaname from student s inner join ban b on s.classid=b.id inner join diqv d on d.id=s.areaid where b.classname='2000班';
--查询山东最近三天内出生的名字中带”java”的男生信息
select * from student s inner join diqv d on s.classid=d.id where d.areaname='山东' and (s.birthday between sysdate-3 and sysdate+3)and s.stuname like'%java%' and s.sex='男';
--查询山东的年龄在18-30之间的男生的姓名、班级名、年龄、体重
select s.stuname,b.classname,s.age,s.weight from student s inner join diqv d on s.classid=d.id inner join ban b on s.areaid=b.id where d.areaname='山东' and (s.age between 18 and 30)and s.sex='男';
--查询河南和山东姓名的体重在100以下的未成年女生的姓名、地区名、班级名，并以班级名排序
select s.stuname,d.areaname,b.classname from student s inner join diqv d on s.classid=d.id inner join ban b on s.classid=b.id where d.areaname in('山东','河南')and s.weight<100 and s.age<18 and s.sex='女';
--查询山东的在1999年到2000年出生女生的
select * from student s inner join diqv d on s.classid=d.id where d.areaname='山东' and (s.birthday between to_date('1999-1-1','yyyy-mm-dd') and to_date('2000-12-31','yyyy-mm-dd'))and s.sex='女';
--查询2007班中山东的体重在100-140之间的张姓男生的姓名、年龄、体重
select s.stuname,s.age,s.weight from student s left join diqv d on s.classid=d.id left join ban b on s.areaid=b.id where b.classname='2007班'
and d.areaname='山东' and (s.weight between 100 and 140) and s.stuname like'张%' and s.sex='男';
--查询山东年龄最大的学生的姓名、班级名。
select s.stuname,b.classname from student s left join diqv d on s.classid=d.id left join ban b on s.areaid=b.id where
d.areaname='山东' and s.age=(select max(t.age) from student t left join diqv dd on t.areaid=dd.id where d.areaname='山东');
--查询最近三个月内出生的学生的姓名、班级名。
select s.stuname,b.classname from student s left join ban b on s.classid=b.id where s.birthday between add_months(sysdate,-3) and add_months(sysdate,3);
--查询体重大于女生最大体重的男生的姓名、年龄、体重、班级名。
select s.stuname,s.age,s.weight,b.classname from student s left join ban b on s.classid=b.id where s.weight>(select max(ss.weight)from student ss where ss.sex='女')
and s.sex='男';
--查询各个地区下的学生数量
select d.areaname,count(1) from student s left join diqv d on s.classid=d.id group by d.areaname;
--查询山东的年龄大于18岁的男女生各自的人数、平均年龄、最大体重
select s.sex,count(1),avg(s.age),max(s.weight) from student s left join diqv
d on s.areaid=d.id where d.areaname='山东' and s.age>18 group by s.sex;
--查询体重总和大于1000的地区名
select d.areaname from student s left join diqv d on s.classid=d.id group by d.areaname having sum(s.weight)>1000;
--查询平均年龄大于20的班级名
select b.classname from student s left join ban b on s.classid=b.id group by b.classname having avg(s.age)>20;
---分页查询按年龄从大到小排序过后学生表的信息，假设每页展示15条，查询第13页的数据
select * from
(select t.*,rownum rn from
(select * from student s order by s.age asc)t where rownum<=15*13)
where rn>(13-1)*15;
--将体重在80-100之间的张姓未成年男生的体重设为120.
update student s set s.weight=120 where (s.weight between 80 and 100)and s.stuname like'张%' and s.age<18 and s.sex='男';
--将2006班山东未成年男生的年龄改为18
update student s set s.age=18 where s.classid=(select id from ban b where b.classname='2006班')
and s.areaid=(select id from diqv d where d.areaname='山东')and s.sex='男' and s.age<18;
--将山东年龄最大的男生的体重在原有基础上翻倍，年龄在原有基础上减2-----------
update student s set s.weight=s.weight * 2,s.age=s.age-2 where s.areaid=(select d.id from diqv d where d.areaname='山东')and
s.age=(select max(ss.age) from student ss left join diqv dd on ss.areaid=dd.id where dd.areaname='山东' and ss.sex='男')
and s.sex='男';
--删除2006班中
--山东的年龄在18-30之间的男生。
delete student s where s.classid=(select id from ban b where b.classname='2006班')and
s.areaid=(select id from diqv d where d.areaname='山东')and
(s.age between 18 and 30)and
s.sex='男';
--删除学生表的重复记录，认为姓名、性别、班级、地区一样的是重复的
delete student where rowid not in(select max(rowid) from student s group by s.stuname,s.sex,s.classid,s.areaid);
--将2006班山东未成年男生的年龄改为18
update t_student set age = 18 where classid = (
select id from t_class where classname = '2006'
) and areaid = (
select id from t_area where areaname = '山东'
) and age < 18 and sex = '男';
--将山东年龄最大的男生的体重在原有基础上翻倍，年龄在原有基础上减2
update t_student set weight = weight * 2 , age = age - 2
where areaid = (
select id from t_area where areaname = '山东'
) and age = (
select max(s.age) from t_student s left join t_area a on s.areaid = a.id
where a.areaname = '山东' and s.sex = '男'
) and sex = '男';
--查询最近三个月内出生的学生的姓名、班级名。
select s.stuname,c.classname from t_student s left join t_area a on s.areaid = a.id
left join t_class c on s.classid = c.id where( s.birthday between add_months(sysdate,-3) and sysdate);
--查询体重大于女生最大体重的男生的姓名、年龄、体重、班级名。
select s.stuname,c.classname,s.age,s.weight from t_student s left join t_area a on s.areaid = a.id
left join t_class c on s.classid = c.id where s.weight > (
select max(weight) from t_student where sex = '女'
) and s.sex = '男';
--查询山东的年龄大于18岁的男女生各自的人数、平均年龄、最大体重
select s.sex,count(1),avg(s.age),max(s.weight) from t_student s left join t_area a on s.areaid = a.id
where a.areaname = '山东' and s.age > 18 group by s.sex;
--查询河南和山东的体重在100以下的未成年女生的姓名、地区名、班级名，并以班级名排序
select c.classname,a.areaname,s.stuname from t_student s left join t_area a on s.areaid = a.id
left join t_class c on s.classid = c.id where a.areaname in ('山东','河南') and s.weight < 100
and s.age < 18 and s.sex = '女' order by c.classname;
--查询山东的在1999年到2000年出生女生的姓名
select s.stuname from t_student s left join t_area a on s.areaid = a.id
left join t_class c on s.classid = c.id where a.areaname = '山东' and s.sex = '女'
and (s.birthday between to_date('1999-1-1','yyyy-mm-dd') and to_date('2000-12-31','yyyy-mm-dd'));
--查询2007班中山东的体重在100-140之间的张姓男生的姓名、年龄、体重
select s.stuname,s.age,s.weight from t_student s left join t_area a on s.areaid = a.id
left join t_class c on s.classid = c.id where c.classname = '2007' and a.areaname = '山东'
and (s.weight between 100 and 140) and s.stuname like '张%' and s.sex = '男';
--查询山东年龄最大的学生的姓名、班级名。
select s.stuname,c.classname from t_student s left join t_area a on s.areaid = a.id
left join t_class c on s.classid = c.id where a.areaname = '山东' and s.age = (
select max(ts.age) from t_student ts
left join t_area ta on ts.areaid = ta.id where ta.areaname = '山东'
);
