SQL_oracle测试及答案

--学生表                       
create table student(               
    sid number(11),
    sname varchar2(20),
    age number(3),
    enterdate date,
    scid number(11)
);
insert into student values (1,'illu',22,to_date('1998-2-12','yyyy-mm-dd'),1);
insert into student values (2,'nancy',25,to_date('2005-4-15','yyyy-mm-dd'),1);
insert into student values (3,'jack',21,to_date('2002-10-9','yyyy-mm-dd'),2);
insert into student values (4,'stephen',18,to_date('2000-11-4','yyyy-mm-dd'),1);
insert into student values (5,'satan',45,to_date('1997-6-3','yyyy-mm-dd'),2);
insert into student values (6,'panza',27,to_date('2006-7-30','yyyy-mm-dd'),1);
insert into student values (7,'jojo',28,to_date('2008-11-30','yyyy-mm-dd'),2);
insert into student (sid, sname, age, enterdate) values (8,'lucy',17,to_date('2008-6-11','yyyy-mm-dd'));
insert into student (sid, sname, age, enterdate) values (9,'lily',32,to_date('2003-5-2','yyyy-mm-dd'));

--班级表
create table clazz(
    cid number(11),
    cname varchar2(20)
);
insert into clazz values(1,'java');
insert into clazz values(2,'c++');
注：假设有些学生隶属班级id为空

 1.列出所有学生的学生id、学生姓名、学生年龄、入学时间信息，入学时间最早的排在最前，
   如果入学时间相同，则年龄大的排在最前，如果年龄相同，以名字的升序排列
    select sid, sname, age, enterdate from student order by enterdate, age desc,sname;
 2.列出年龄大于20且小于30的学生姓名，学生年龄。
    select sname, age from student where age>20 and age<30;
 3.列出在2003年以前入学的学生姓名和入学时间
    select sname, enterdate from student where to_number(to_char(enterdate,'yyyy'))<2003;
 4.列出所有不姓张的学生姓名。
    select sname from student where sname not like '张%';
 5.列出学生从入学时间到今日共在学校学习了多少年，多少月，多少日。(大概)。
    select enterdate,to_number(to_char(sysdate,'yyyy'))-to_number(to_char(enterdate,'yyyy')) years,months_between(sysdate,enterdate) months,sysdate-enterdate days from student;s
 6.列出年龄大于所有学生平均年龄的学生信息。
    select * from student where age>(select avg(age) from student);
 7.列出学生id，学生姓名，所属班级名称(没有所属班级的学生班级名称显示'no class')。
    select sid, sname, nvl(cname,'no class') from student, clazz where scid=cid(+);
 8.列出每个班的班级名称、学生的最大年龄、最小年龄、平均年龄和年龄之和。
    select cname,max(age),min(age),avg(age),sum(age) from student, clazz where scid=cid group by cname;
 9.列出在java班中年龄大于20或者在c++班中年龄大于25的学生id、学生姓名、学生年龄和所在班级。
    select sid, sname, age, cname from student, clazz where scid=cid and (cname='java' and age>20 or cname='c++' and age>25);
10.列出班级平均年龄大于20的班级名称和平均年龄。
    select cname, avg(age) from student,clazz where scid=cid group by cname having avg(age)>20;
11.写一段plsql程序要求打印所有学生的姓名和入学时间(要求以yyyy-mm-dd格式化时间)
declare
    cursor cur_stu is
    select sname, to_char(enterdate, 'yyyy-mm-dd') enterdate
    from student;
begin
    for cur in cur_stu loop
    dbms_output.put_line(cur.sname||' '||cur.enterdate);
    end loop;
end;
/
12.写一个触发器防止有入学时间大于今天的错误数据录入。
create or replace trigger vali_date
before insert on student
for each row
begin
    if :new.enterdate>sysdate then
        raise_application_error(-20000,'cannot insert');
    end if;
end vali_date;
/