这个是我创建的表 请大家帮我回答下图中的3个问题,谢谢了!!
create table student(
studentId char(4) primary key,
studentName nvarchar2(10),
lessionName nvarchar2(10),
score number(3)
);1.编写一个函数,输入一个学生姓名和课程名称返回课程分数。
2.用sql语句查询出课程总分最高的学生姓名。
3.sql语句查询出每门课程都小于70分的学生姓名。写的函数请中文注释一下意思,万分感谢!!
--都是挺简单的题目,想学好ORACLE的话,这些是必须的,有空自己多练习练习.没人能老帮你的.呵呵
--没有测试过,可能会有部分问题,呵呵--1
CREATE OR REPLACE FUNCTION GET_LESSON_SCORE
(
V_StuName IN student.studentName%TYPE,
V_LessionName IN student.lessionName%TYPE,
N_Score OUT student.score %TYPE
)
RETURN NUMBER
IS
BEGIN
SELECT score INTO N_Score FROM student
WHERE student.studentName = V_StuName
AND student.lessionName = V_LessionName;
RETURN N_Score;
END GET_LESSON_SCORE;
--2
SELECT student.studentName FROM STUDENT WHERE STUDENT.score =
(SELECT MAX(SCORE) FROM STUDENT)--3
SELECT * FROM STUDENT WHERE STUDENT.STUDENTID NOT IN
(SELECT STUDENT.STUDENTID FROM STUDENT WHERE SCORE >= 70)
---- -------------------- -------------------- ----------
1 张三 语文 69
2 张三 数学 65
3 李四 语文 68
4 李四 数学 78
5 王五 语文 67----------------------------------------1--------------------------
SQL> ed
已写入 file afiedt.buf 1 CREATE OR REPLACE FUNCTION GET_LESSON_SCORE
2 (
3 V_StudentName IN student.studentName%TYPE,
4 V_LessonName IN student.lessonName%TYPE,
5 N_Score OUT student.score %TYPE
6 )
7 RETURN NUMBER
8 IS
9 BEGIN
10 SELECT score INTO N_Score FROM student
11 WHERE student.studentName = V_StudentName
12 AND student.lessonName = V_LessonName;
13 RETURN N_Score;
14* END;
SQL> /函数已创建。SQL> exec :score:=get_lesson_score('张三','语文',:score);PL/SQL 过程已成功完成。SQL> print score SCORE
----------
69----------------------------------------2--------------------------
SQL> ed
已写入 file afiedt.buf 1 SELECT studentname FROM
2 (SELECT studentname
3 FROM student
4 GROUP BY studentname
5 ORDER BY SUM(score) DESC
6 )
7* WHERE rownum<=1
SQL> /STUDENTNAME
--------------------
李四
----------------------------------------3--------------------------
SQL> ed
已写入 file afiedt.buf 1 SELECT DISTINCT studentname FROM student
2 WHERE studentname NOT IN (
3* select studentname from student where score>=70)
SQL> /STUDENTNAME
--------------------
王五
张三SQL>