Oracle上机考试第五套题

[上机考试技巧]不要使用快捷键! 很多人在使用TC的时候习惯于用F9，CTRL+F9或者其他的热键，首先F9只是编译了，并没有运行程序，考试一定要运行一下你的程序才会有输出文件，否则一定是0分。热键在考...+阅读

Oracle上机考试第五套题

CREATETABLES(snovarchar2(32)NOTNULLUNIQUE,snamevarchar2(64)UNIQUE,statusCHAR

(1),cityvarchar2(128))CREATETABLEP(pnovarchar2(32)NOTNULLUNIQUE,pnamevarchar2(64)UNIQUE,colorvarchar2(32),weightvarchar2(32),cityvarchar2(128))CREATETABLES_P(snovarchar2(32)NOTNULL,pnovarchar2(64)NOTNULL）三。。

（1）SELECTSALFROMEMPORDERBYSALDESC;

(2)SELECTDNAMEFROMDEPTORDERBYDEPTNO;

(3)SELECT((SAL+COMM)*12)AS年收入FROMEMPORDERBY年收入DESCENAME;

谁能帮我做一下这个机试试题

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 1. insert into student values(1,'jack','1234567','computer','yyyy3322163.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 '男' else '女' 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 = '安意如'；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 是你要输入的值基础数据表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;

ORACLE期末考试机试试卷求解

--一create table class(class_no char(4) primary key, class_name char(10), class_dept char(10));create table student(stu_no char(6) primary key, stu_name char(8), stu_sex char(2), stu_birth date, stu_score int, stu_escore int, class_no char(4) references class(class_no)）；--二--1insert into class values('0101'，'软件01班'，'软件工程系')；insert into class values('0201'，'软件02班'，'软件工程系')；insert into student values('010201'，'赵明'，'男'，to_date('1985-12-1','yyyy-mm-dd'),550,50,'0101');insert into student values('010202'，'赵以'，'女'，to_date('1984-1-11','yyyy-mm-dd'),530,40,'0101');insert into student values('010203'，'马力'，'男'，to_date('1985-5-24','yyyy-mm-dd'),560,20,'0201');insert into student values('010204'，'王蕾'，'女'，to_date('1986-2-6','yyyy-mm-dd'),590,10,'0201');insert into student values('010205'，'马明'，'女'，to_date('1985-4-17','yyyy-mm-dd'),520,30,'0201');--2、查询学生表中所有学生的全部信息。

select * from student--3、查询student表中入学总分在520和550之间的学生的学号、姓名、入学总分信息。select stu_no,stu_name,stu_score+stu_escore 入学总分 from student where stu_score+stu_escore between 520 and 550--4、查询student表中班级编码为“0101”和“0201”的学生的学号、姓名、入学总分信息。select stu_no,stu_name,stu_score+stu_escore 入学总分 from student where class_no in('0101','0201')--5、查询入学总分大于550的学生的学号、姓名、总分和班级名称等信息。select stu_no,stu_name,stu_score+stu_escore 入学总分，class_name from student join class on student.class_no=class.class_no where stu_score+stu_escore>550--6、将student表中马力的学生编号改为“010206”，班级编号改为“0102”。update student set stu_no='010206',class_no='0102' where stu_name='马力'--7、将student表中王蕾的班级编号改为马力的班级编号。

update student set class_no=(select class_no from student where stu_name='马力') where stu_name='王蕾'--8、将student表中王蕾的记录删除。delete from student where stu_name='王蕾'