整理一下
建表
-- Create table 学生信息
create table HAND_STUDENT
(
STUDENT_NO VARCHAR2(10) not null,
STUDENT_NAME VARCHAR2(20),
STUDENT_AGE NUMBER(2),
STUDENT_GENDER VARCHAR2(5)
);
-- Add comments to the table
comment on table HAND_STUDENT
is '学生信息表';
-- Add comments to the columns
comment on column HAND_STUDENT.STUDENT_NO
is '学号';
comment on column HAND_STUDENT.STUDENT_NAME
is '姓名';
comment on column HAND_STUDENT.STUDENT_AGE
is '年龄';
comment on column HAND_STUDENT.STUDENT_GENDER
is '性别';
-- Create/Recreate primary, unique and foreign key constraints
alter table HAND_STUDENT add primary key (STUDENT_NO);
-- Create table 教师信息表
create table HAND_TEACHER
(
TEACHER_NO VARCHAR2(10) not null,
TEACHER_NAME VARCHAR2(20),
MANAGER_NO VARCHAR2(10)
);
-- Add comments to the table
comment on table HAND_TEACHER
is '教师信息表';
-- Add comments to the columns
comment on column HAND_TEACHER.TEACHER_NO
is '教师编号';
comment on column HAND_TEACHER.TEACHER_NAME
is '教师名称';
comment on column HAND_TEACHER.MANAGER_NO
is '上级编号';
-- Create/Recreate primary, unique and foreign key constraints
alter table HAND_TEACHER add primary key (TEACHER_NO);
-- Create table 课程信息表
create table HAND_COURSE
(
COURSE_NO VARCHAR2(10) not null,
COURSE_NAME VARCHAR2(20),
TEACHER_NO VARCHAR2(20) not null
);
-- Add comments to the table
comment on table HAND_COURSE
is '课程信息表';
-- Add comments to the columns
comment on column HAND_COURSE.COURSE_NO
is '课程号';
comment on column HAND_COURSE.COURSE_NAME
is '课程名称';
comment on column HAND_COURSE.TEACHER_NO
is '教师编号';
-- Create/Recreate primary, unique and foreign key constraints
alter table HAND_COURSE add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO);
-- Create table 成绩信息表
create table HAND_STUDENT_CORE
(
STUDENT_NO VARCHAR2(10) not null,
COURSE_NO VARCHAR2(10) not null,
CORE NUMBER(4,2)
);
-- Add comments to the table
comment on table HAND_STUDENT_CORE
is '学生成绩表';
-- Add comments to the columns
comment on column HAND_STUDENT_CORE.STUDENT_NO
is '学号';
comment on column HAND_STUDENT_CORE.COURSE_NO
is '课程号';
comment on column HAND_STUDENT_CORE.CORE
is '分数';
-- Create/Recreate primary, unique and foreign key constraints
alter table HAND_STUDENT_CORE add constraint PK_SC primary key (STUDENT_NO, COURSE_NO);
/*******初始化学生表的数据******/
insert into HAND_STUDENT values ('s001','张三',23,'男');
insert into HAND_STUDENT values ('s002','李四',23,'男');
insert into HAND_STUDENT values ('s003','吴鹏',25,'男');
insert into HAND_STUDENT values ('s004','琴沁',20,'女');
insert into HAND_STUDENT values ('s005','王丽',20,'女');
insert into HAND_STUDENT values ('s006','李波',21,'男');
insert into HAND_STUDENT values ('s007','刘玉',21,'男');
insert into HAND_STUDENT values ('s008','萧蓉',21,'女');
insert into HAND_STUDENT values ('s009','陈萧晓',23,'女');
insert into HAND_STUDENT values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into HAND_TEACHER values ('t001', '刘阳','');
insert into HAND_TEACHER values ('t002', '谌燕','t001');
insert into HAND_TEACHER values ('t003', '胡明星','t002');
insert into HAND_TEACHER values ('t004', '肖芳','t002');
insert into HAND_TEACHER values ('t005', '吴峰','t004');
commit;
/***************初始化课程表****************************/
insert into HAND_COURSE values ('c001','J2SE','t002');
insert into HAND_COURSE values ('c002','Java Web','t002');
insert into HAND_COURSE values ('c003','SSH','t001');
insert into HAND_COURSE values ('c004','Oracle','t001');
insert into HAND_COURSE values ('c005','SQL SERVER 2005','t003');
insert into HAND_COURSE values ('c006','C#','t003');
insert into HAND_COURSE values ('c007','JavaScript','t002');
insert into HAND_COURSE values ('c008','DIV+CSS','t001');
insert into HAND_COURSE values ('c009','PHP','t003');
insert into HAND_COURSE values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into HAND_STUDENT_CORE values ('s001','c001',58.9);
insert into HAND_STUDENT_CORE values ('s002','c001',80.9);
insert into HAND_STUDENT_CORE values ('s003','c001',81.9);
insert into HAND_STUDENT_CORE values ('s004','c001',60.9);
insert into HAND_STUDENT_CORE values ('s001','c002',82.9);
insert into HAND_STUDENT_CORE values ('s002','c002',72.9);
insert into HAND_STUDENT_CORE values ('s003','c002',81.9);
insert into HAND_STUDENT_CORE values ('s001','c003','59');
commit;
SQL篇：
--1 找出比“张三”同学年龄小的其他同学中年龄最大的学生信息，显示（学号、姓名、年龄）（5分）
SELECT student_no, student_name, student_age
FROM hand_student
WHERE student_age = (SELECT MAX(student_age)
FROM hand_student
WHERE student_name != '张三' --觉得可以省略掉这个条件
AND hand_student.student_age <
(SELECT student_age
FROM hand_student
WHERE student_name = '张三'));
--2 查询老师授课数量最多的教师信息，显示（教师编码、教师姓名）（5分）
SELECT h.teacher_no, h.teacher_name
FROM (SELECT teacher_no, COUNT(teacher_no) n
FROM hand_course
GROUP BY teacher_no) c,
hand_teacher h
WHERE n =
(SELECT MAX(COUNT(teacher_no)) FROM hand_course GROUP BY teacher_no)
AND h.teacher_no = c.teacher_no;
--3 查询教师“刘阳”的第三级下属(直接下属的直接下属)的教师信息，显示（教师编号、教师名称、上级主管编号、主管名称）（5分）
SELECT h.teacher_no, h.teacher_name, h.manager_no, h2.teacher_name
FROM (SELECT teacher_no, teacher_name, manager_no, LEVEL lev
FROM hand_teacher
START WITH teacher_name = '刘阳'
CONNECT BY PRIOR teacher_no = manager_no) h,
hand_teacher h2
WHERE h.manager_no = h2.teacher_no(+)
AND h.lev = 3;
--4 查询没有学全所有课的同学，显示（学号、姓名）（5分）
SELECT hs.student_no, hs.student_name
FROM hand_student hs, hand_student_core hsc
WHERE hs.student_no = hsc.student_no(+)
GROUP BY hs.student_no, hs.student_name
HAVING COUNT(hsc.course_no) < (SELECT COUNT(hc.course_no)
FROM hand_course hc);
--5 查询课程名称为“J2SE”的学生成绩信息，90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以---下为“不及格”，显示（学号、姓名、课程名称、成绩、等级) （5分）
SELECT hsc.student_no,
hs.student_name,
hc.course_name,
hsc.core,
CASE
WHEN hsc.core >= 90 THEN
'优秀'
WHEN hsc.core < 90 AND hsc.core >= 80 THEN
'良好'
WHEN hsc.core < 80 AND hsc.core >= 60 THEN
'及格'
WHEN hsc.core < 60 THEN
'不及格'
END core_level
FROM hand_student_core hsc, hand_course hc, hand_student hs
WHERE hsc.course_no = hc.course_no
AND hsc.student_no = hs.student_no
AND hc.course_name = 'J2SE';
--6查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数）（5分）
SELECT student_no, course_no, core
FROM (SELECT hsc.student_no,
hsc.course_no,
hsc.core,
RANK() OVER(PARTITION BY hsc.course_no ORDER BY hsc.core DESC) ranks
FROM hand_student_core hsc)
WHERE ranks <= 3;
--7 查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认,显示（学号、成绩) （5分）
SELECT hs.student_no, hs.core
FROM (SELECT ROWNUM rn,
hsc.student_no,
hsc.core,
row_number() OVER(ORDER BY hsc.core DESC) ranks
FROM hand_student_core hsc) hs
ORDER BY CASE
WHEN ranks <= 3 THEN
-ranks
ELSE
NULL
END,
rn;
--8 查询没学过“谌燕”老师课的同学，显示（学号、姓名）（5分）
SELECT s.student_no, s.student_name
FROM HAND_STUDENT s
WHERE s.student_no NOT IN
(SELECT sc.student_no
FROM HAND_TEACHER t, HAND_STUDENT_CORE sc, HAND_COURSE c
WHERE sc.course_no = c.course_no
AND t.teacher_no = c.teacher_no
AND s.student_no = sc.student_no
AND t.teacher_name = '谌燕');
--9 统计列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程----号、课程名称（提示使用case when句式）（5分）
SELECT hsc.course_no,
hc.course_name,
SUM(CASE
WHEN hsc.core BETWEEN 85 AND 100 THEN
1
ELSE
0
END) AS "[100-85]",
SUM(CASE
WHEN hsc.core BETWEEN 70 AND 85 THEN
1
ELSE
0
END) AS "[85-70]",
SUM(CASE
WHEN hsc.core BETWEEN 60 AND 70 THEN
1
ELSE
0
END) AS "[70-60]",
SUM(CASE
WHEN hsc.core < 60 then
1
ELSE
0
END) AS "[<60]"
FROM hand_student_core hsc, hand_course hc
WHERE hsc.course_no = hc.course_no
GROUP BY hsc.course_no, hc.course_name;
--10 分别根据，教师和课程、教师和课程三个条件统计选课的学生数量：
--（使用rollup),显示（教师名称、课程名称、选课数量）（5分）
SELECT ht.teacher_name, hc.course_name, COUNT(hsc.student_no) nums
FROM hand_student_core hsc, hand_teacher ht, hand_course hc
WHERE hsc.course_no = hc.course_no
AND hc.teacher_no = ht.teacher_no
GROUP BY ROLLUP(ht.teacher_name, hc.course_name);
PLSQL篇
create or replace package cux_exam_26820_pkg is
--1. 定义游标
type ref_cursor is ref cursor;
--2. 定义数据类型
type score_data_type is record(
student_no hand_student.student_no%type,
student_name hand_student.student_name%type,
student_age hand_student.student_age%type,
student_gender hand_student.student_gender%type,
course_no hand_course.course_no%type,
course_name hand_course.course_name%type,
teacher_name hand_teacher.teacher_name%type,
core hand_student_core.core%type,
avg_core hand_student_core.core%type,
max_core hand_student_core.core%type,
min_core hand_student_core.core%type);
-- 3. 获得低于平均分数的学生信息
function get_grounded_info return ref_cursor;
--4. 编写一个函数， 根据学号、课程号返回学生的学科成绩
FUNCTION get_course_core(p_student_no IN VARCHAR2,
p_course_no IN VARCHAR2) RETURN NUMBER;
--5. 编写一个存储过程，根据学号、课程号 按成绩的20%进行加分
PROCEDURE process_add_core(p_student_no IN VARCHAR2,
p_course_no IN VARCHAR2,
x_core OUT NUMBER);
--6.创建一个过程，调用上面的方法，输出这些学生中，给所有成绩低于平均分的学生加1分，如果加分后的学生成绩低于60分，则输出这个学生的信息。处理成功则返回 S 状态并提交事物；失败则返回 E 状态及回错误消息，同时回滚事物。
procedure update_student_core(p_std_score in number,
x_return_status out nocopy varchar2,
x_msg_data out nocopy varchar2);
--7. 个人所得税计算
function tax_calc(p_salary number) return number;
--8.输入一个日期，计算该日期在该年内第几天
procedure date_num(p_date varchar2);
end cux_exam_26820_pkg;
/
create or replace package body cux_exam_26820_pkg is
-- 3.获得低于平均分数的学生信息
function get_grounded_info return ref_cursor is
ref_cursor_return ref_cursor;
begin
-- 计算每门课程的平均分，并取出所有成绩低于平均分的学生
open ref_cursor_return for 'select *
from (select hs.student_no,
hs.student_name,
hs.student_age,
hs.student_gender,
hc.course_no,
hc.course_name,
ht.teacher_name,
hsc.core,
round(avg(hsc.core) over(partition by hsc.course_no), 2) avg_core,
max(hsc.core) over(partition by hsc.course_no) max_core,
min(hsc.core) over(partition by hsc.course_no) min_core
from hand_course hc,
hand_student hs,
hand_student_core hsc,
hand_teacher ht
where hc.course_no = hsc.course_no
and hs.student_no = hsc.student_no
and hc.teacher_no = ht.teacher_no)
where core < avg_core';
return ref_cursor_return;
end get_grounded_info;
--4.编写一个函数， 根据学号、课程号返回学生的学科成绩
FUNCTION get_course_core(p_student_no IN VARCHAR2,
p_course_no IN VARCHAR2) RETURN NUMBER IS
l_core NUMBER;
BEGIN
SELECT hsc.core
INTO l_core
FROM hand_student_core hsc
WHERE hsc.student_no = p_student_no
AND hsc.course_no = p_course_no;
RETURN l_core;
EXCEPTION
WHEN no_data_found THEN
RETURN - 1;
WHEN too_many_rows THEN
RETURN - 2;
WHEN OTHERS THEN
RETURN - 3;
END get_course_core;
--5.编写一个存储过程，根据学号、课程号 按成绩的20%进行加分
PROCEDURE process_add_core(p_student_no IN VARCHAR2,
p_course_no IN VARCHAR2,
x_core OUT NUMBER) IS
CURSOR cur_core IS
SELECT hsc.core
FROM hand_student_core hsc
WHERE hsc.student_no = p_student_no
AND hsc.course_no = p_course_no
FOR UPDATE OF hsc.core;
BEGIN
FOR rec_core IN cur_core LOOP
IF (rec_core.core + rec_core.core * 0.2) <= 100 THEN
UPDATE hand_student_core hsc
SET hsc.core = hsc.core + hsc.core * 0.2
WHERE CURRENT OF cur_core;
x_core := rec_core.core + rec_core.core * 0.2;
ELSE
x_core := rec_core.core;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END process_add_core;
--6.创建一个过程，调用上面的方法，输出这些学生中，给所有成绩低于平均分的学生加1分，如果加分后的学生成绩低于60分，则输出这个学生的信息。处理成功则返回 S 状态并提交事物；失败则返回 E 状态及回错误消息，同时回滚事物;
procedure update_student_core(p_std_score in number,
x_return_status out nocopy varchar2,
x_msg_data out nocopy varchar2) is
l_data_cur ref_cursor;
l_data_rec score_data_type;
l_new_score number;
l_savepoint_name varchar2(30) := 'sp_score_adjustment';
begin
x_return_status := 'S';
x_msg_data := null;
savepoint l_savepoint_name;
l_data_cur := get_grounded_info;
loop
fetch l_data_cur
into l_data_rec;
exit when l_data_cur%notfound;
update hand_student_core hsc
set hsc.core = nvl(hsc.core, 0) + 1
where hsc.student_no = l_data_rec.student_no
and hsc.course_no = l_data_rec.course_no
returning hsc.core into l_new_score;
if l_new_score < nvl(p_std_score, 60) then
dbms_output.put_line('student_no.: ' || l_data_rec.student_no ||
', student_name: ' || l_data_rec.student_name ||
', course_name: ' || l_data_rec.course_name ||
', teacher_name: ' || l_data_rec.teacher_name ||
', core: ' || l_data_rec.core ||
', avg_core: ' || l_data_rec.avg_core ||
', max_core: ' || l_data_rec.max_core ||
',min_core: ' || l_data_rec.min_core);
end if;
end loop;
commit;
exception
when others then
rollback to l_savepoint_name;
x_return_status := 'E';
x_msg_data := substr(sqlerrm, 1, 2000);
end update_student_core;
--7.个人所得税计算
function tax_calc(p_salary number) return number is
tax_sum number;
begin
if p_salary > 0 and p_salary < 3500 then
tax_sum := 0;
elsif p_salary > 3501 and p_salary < 4500 then
tax_sum := (p_salary - 3500) * 0.1;
elsif p_salary > 4501 and p_salary < 9000 then
tax_sum := 1000 * 0.1 + (p_salary - 4500) * 0.2;
elsif p_salary >= 9000 then
tax_sum := 1000 * 0.1 + 4500 * 0.2 + (p_salary - 9000) * 0.25;
end if;
dbms_output.put_line('the tax you should pay is : ' || tax_sum);
return tax_sum;
end tax_calc;
--8.输入一个日期，计算该日期在该年内第几天
procedure date_num(p_date varchar2) is
first_day date;
n number := 0;
begin
first_day := TRUNC(to_date(p_date, 'yyyy-mm-dd'), 'YEAR');
n := to_date(p_date, 'yyyy-mm-dd') - first_day + 1;
dbms_output.put_line('It was the ' ||n||' day of the year');
end date_num;
end cux_exam_26820_pkg;
/
扫一扫
2731
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
