谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!

谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!
谁能帮我做一下这个机试试题?用Oracle






不用全做对,70分就好!

谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!
create table student(
stuID NUMBER(10) primary key,
stuName VARCHAR2(20) not null,
password VARCHAR2(20),
major VARCHAR2(50),
email VARCHAR2(30) unique,
gender char(1) default '0',
birthday date
);


create table book(
bid number(10) primary key,
title varchar2(20),
author varchar2(20),
price number(6,2));


create table borrow(
borrowid number(10) primary key,
stuid number(10),
id number(10),
t_time date default sysdate,
b_time date);


alter table student add constraint ch_passwd check (password>6);
alter table student add constraint ch_gender check (gender in ('0','1'));
alter table borrow add constraint fk_borrow_student foreign key (stuid)  references student(stuID);
alter table borrow add constraint fk_borrow_book foreign key (id)  references book(bid); 
alter table borrow add constraint ch_borrow check (b_time < t_time);


1.insert into student values(1,'jack','1234567','computer','yyyy3322@163.com','0',to_date('19950505','yyyymmdd'));
   insert into book values(1,'oracle','thomas kyte',200);
2.select t.stuid,t.stuname,(case when t.gender = 0 then '鐢?#39; else '濂?#39; end) from student t,borrow t1 
      where t.stuid = t1.stuid 
        and to_char(t1.t_time,'yyyy-mm-dd') between '2012-12-15' and '2013-1-8';
3.select max(count(t.stuid)) from borrow t group by t.stuid;
4.select t.stuname,t2.title,t1.t_time,t1.b_time from student t,borrow t1,book t2 where t.stuid = t1.stuid and t1.id = 


t2.bid and t2.author = '瀹夋剰濡?#39;;
5.select t.* from student t where t.stuid not in (select t1.stuid from borrow t1 group by t1.stuid);
6.create or replace package login
 is PROCEDURE login_student
  (student_id in NUMBER,
   passwd  in varchar2,
   return_flag out varchar2);
 end;


create or replace package body login is
  PROCEDURE login_student(student_id  in NUMBER,
                          passwd      in varchar2,
                          return_flag out varchar2) as
    student_num1 number(2) := 0;
    student_num2 number(2) := 0;
  begin
    select count(1)
      into student_num1
      from student t
     where t.stuid = student_id;
    if student_num1 = 1 then
      select count(1)
        into student_num2
        from student t
       where t.stuid = student_id
         and t.password = passwd;
      if student_num2 = 1 then
        select t.stuname
          into return_flag
          from student t
         where t.stuid = student_id
           and t.password = passwd;
      else
        return_flag := '0';
      end if;
    else
      return_flag := '-1';
    end if;
  end;
end login;




begin
  login.login_student(student_id => :student_id,
                      passwd => :passwd,
                      return_flag => :return_flag);
end;
杩欓噷鏄?祴璇曡?:student_id :passwd 綘瑕佽緭鍏ョ殑鍊稽/p>







鍩虹?鏁版嵁琛?/p>





7.
CREATE OR REPLACE FUNCTION del_book (BOOKID NUMBER)
   RETURN NUMBER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   delete from borrow t where t.id = BOOKID;
   COMMIT;
   delete from book t1 where t1.bid = BOOKID;
   commit;
END;