Oracle作业答案

已于 2022-10-04 13:16:33 修改
阅读量940 收藏 9
点赞数 5
分类专栏： Oracle 数据库 文章标签： 数据库
于 2020-11-07 10:24:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/y_h_k_666/article/details/109543554
版权

2020-2021年度第一学期《高级数据库原理》综合实验

oracle实验四综合实验

1. 创建下列关系表及约束

关系表如下：

（1）CHOICE表（选课表）

字段名

字段说明

字段类型

是否允许为空

S_NO

学号

字符串

否

COURSE_NO

该学号学生选课课号

字符串

否

SCORE

该课程该学生的成绩

整数

是

主键：(S_No,Course_no)

CREATE TABLE choice(

       s_no         VARCHAR2(10),

       course_no    VARCHAR2(10),

       score        NUMBER(3),

       CONSTRAINT choice_prim PRIMARY KEY(s_no,course_no)

);

（2）CLASS表（班级表）

字段名

字段说明

字段类型

是否允许为空

CLASS_NO

班级编号

字符串

否

CLASS_NAME

班级名称

字符串

否

DEPT_NO

班级所在系部编号

字符串

否

主键：CLASS_NO

CREATE TABLE CLASS(

       class_no                VARCHAR2(10) PRIMARY KEY,

       class_name              VARCHAR2(50) NOT NULL,

       dept_no                 VARCHAR2(10) NOT NULL     

);

（3）COURSE表（课程表）

字段名

字段说明

字段类型

是否允许为空

COURSE_NO

课程编号

字符串

否

COURSE_NAME

课程名称

字符串

否

主键：COURSE_NO

CREATE TABLE course(

       course_no               VARCHAR2(10) PRIMARY KEY,

       course_name             VARCHAR2(50) NOT NULL

);　

（4）DEPARTMENT表（系部表）

字段名

字段说明

字段类型

是否允许为空

DEPT_NO

系部编号

字符串

否

DEPT_NAME

系部名称

字符串

否

主键：DEPT_NO

CREATE TABLE department(

       dept_no                 VARCHAR2(10) PRIMARY KEY,

       dept_name               VARCHAR2(50) NOT NULL

);

（5）STUDENT表（学生表）

字段说明：

字段名

字段说明

字段类型

是否允许为空

S_NO

学号

字符串

否

S_NAME

学生姓名

字符串

否

S_SEX

性别

字符串

否

S_BIRTHDAY

出生日期

日期

是

S_SCORE

入学总分

整数

是

S_ADDF

入学附加分

整数

是

CLASS_NO

所在班级编号

字符串

否

主键：S_NO

CREATE TABLE student(

       s_no                    VARCHAR2(10) PRIMARY KEY,

       s_name                  VARCHAR2(50) NOT NULL,

       s_sex                   VARCHAR2(50) NOT NULL,

       s_birthday              DATE,

       s_score                 NUMBER(3),

       s_addf                  NUMBER(3),

       class_no                VARCHAR2(10) NOT NULL

);

（6）TEACHER表（教师表）

字段说明：

字段名

字段说明

字段类型

是否允许为空

T_NO

教师编号

字符串

否

T_NAME

教师姓名

字符串

否

T_SEX

性别

字符串

否

S_BIRTHDAY

出生日期

日期

是

TECH_TITLE

职称

字符串

否

AGE

年龄

整数

是

SALARY

薪水

小数

是

主键：T_NO

CREATE TABLE teacher(

       t_no                    VARCHAR2(10) PRIMARY KEY,

       t_name                  VARCHAR2(50) NOT NULL,

       t_sex                   VARCHAR2(10) NOT NULL,

       s_birthday              DATE,

       tech_title              VARCHAR2(50) NOT NULL,

       age                     NUMBER(3),

       salary                  NUMBER    

);

（7）TEACHING表（教师授课表）

字段说明：

字段名

字段说明

字段类型

是否允许为空

T_NO

教师编号

字符串

否

COURSE_NO

所带课程编号

字符串

否

主键：（T_NO，COURSE_NO）

CREATE TABLE teaching(

       t_no VARCHAR2(10),

       course_no VARCHAR2(10),

       CONSTRAINT teaching_prim PRIMARY KEY(t_no,course_no)

);

各关系表的约束要求如下：

外键约束：

（1）CHOICE——COURSE关系

表名

字段名

关系

COURSE

COURSE_NO

主键

CHOICE

COURSE_NO

外键

CREATE TABLE teaching(

       t_no VARCHAR2(10),

       course_no VARCHAR2(10),

       CONSTRAINT teaching_prim PRIMARY KEY(t_no,course_no)

);

  1. CHOICE——STUDENT关系

表名

字段名

关系

STUDENT

S_NO

主键

CHOICE

S_NO

外键

ALTER TABLE choice

ADD CONSTRAINT fk_s_no

    FOREIGN KEY(s_no)

    REFERENCES student(s_no);

（3）CLASS—— DEPARTMENT关系

表名

字段名

关系

DEPARTMENT

DEPT_NO

主键

CLASS

DEPT_NO

外键

ALTER TABLE CLASS

ADD CONSTRAINT fk_dept_no

    FOREIGN KEY(dept_no)

    REFERENCES department(dept_no);

（4）STUDENT——CLASS关系

表名

字段名

关系

CLASS

CLASS_NO

主键

STUDENT

CLASS_NO

外键

ALTER TABLE student

ADD CONSTRAINT fk_class_no

    FOREIGN KEY(class_no)

    REFERENCES CLASS(class_no);

（5）TEARCHING——TEARCHER关系

表名

字段名

关系

TEARCHER

T_NO

主键

TEARCHING

T_NO

外键

ALTER TABLE teaching

ADD CONSTRAINT fk_t_no

    FOREIGN KEY(t_no)

    REFERENCES teacher(t_no);

（6）TEARCHING——COURSE关系

表名

字段名

关系

COURSE

COURSE_NO

主键

TEARCHING

COURSE_NO

外键

ALTER TABLE teaching

ADD CONSTRAINT fk_teaching_course_no

    FOREIGN KEY(course_no)

    REFERENCES course(course_no);

检查性约束：

   STUDENT表的S_SEX字段和TEACHER表的T_SEX字段取值均为’男’或’女’。

   CHOICE表的SCORE的取值范围为0-100

ALTER TABLE student

ADD CONSTRAINT ck_s_sex

CHECK(s_sex IN ('男','女'));

ALTER TABLE teacher

ADD CONSTRAINT ck_t_sex

CHECK(t_sex IN ('男','女'));

ALTER TABLE choice

ADD CONSTRAINT ck_score

    CHECK(score BETWEEN 0 AND 100);

2.各表中插入下列所示的数据

CHOICE表

CLASS表

COURSE表

TEARCHING表

DEPARTMENT表

STUDENT表

TEARCHER表

3.完成以下查询要求，给出实现该要求的SQL语句

（1）查询入学总分高于平均总分的学生的学号、姓名、总分、班级编号、班级名信息，并将显示结果按班级编号、学号排序。

SELECT st.s_no                  学号,

       st.s_name                姓名,

       st.s_score          总分,

       st.class_no              班级编号,

       (SELECT class_name

               FROM CLASS

               WHERE class_no = st.class_no)                       班级名信息      

  FROM STUDENT ST

 WHERE st.s_score >

       (SELECT SUM(ALL_GRADE) / COUNT(S_NO) AVG_ALL_GRADE              --平均总分

          FROM (SELECT S_NO, COUNT(S_NO) COUNT_NUM, SUM(SCORE) ALL_GRADE

                  FROM CHOICE

                 GROUP BY S_NO

                 ORDER BY S_NO) TMP

        );

（2）列出《计算机文化》课程的成绩高于“王五”的学生名单。

SELECT st.s_no    学号,

       st.s_name  姓名,

       (SELECT course_name FROM course WHERE course_no = ch.course_no) 课程名称,

       ch.score   分数

       FROM student    st,

            choice     ch

       WHERE st.s_no = ch.s_no

       AND   ch.course_no = (

             SELECT course_no

                    FROM course

                    WHERE course_name = '计算机文化'   --计算机文化课对应的课程编号

       )

       AND   ch.score > (

             SELECT score

                    FROM choice ch

                    WHERE s_no = (

                          SELECT s_no FROM student WHERE s_name = '王五'

                    )

                    AND course_no = (

                       SELECT course_no

                              FROM course

                              WHERE course_name = '计算机文化'

                    )

       );

（3）.分组统计选修各门课程的学生人数，要求显示课号，课程名称和学生人数。

SELECT course_no                                     课程编号,

        (SELECT course_name

                FROM course

                WHERE course_no = c.course_no)        课程名称,

        COUNT(c.s_no)                                   学生编号 

        FROM choice c

        GROUP BY c.course_no

        ORDER by c.course_no;

（4）将DEPARTMENT表中“计算机系”的系部编号（DEPT_NO）修改为“01”，然后插入一条新记录，系部编号为“02”，系部名称（DEPT_NAME）“土木系”。

--禁用外键约束

ALTER TABLE CLASS DISABLE CONSTRAINT FK_DEPT_NO;

UPDATE department d

SET d.dept_no = '01'  

WHERE dept_name = '计算机系'; 

INSERT INTO department VALUES('02','土木系');

--开启外键约束

ALTER TABLE CLASS ENABLE CONSTRAINT FK_DEPT_NO;

4. 完成以下PL/SQL编程的相关要求

（1）设计一个存储过程，用来接受用户输入的学号，然后显示出该学号对应学生的所有选修课程名及课程成绩。

create or replace procedure GetCourseScore(p_s_no in choice.s_no%TYPE) IS

begin

  FOR v_choice IN

  (SELECT ch.s_no                                                      ,--学号

       (SELECT course_name                             

               FROM course

               WHERE course_no = ch.course_no) course_name              ,--课程名称

       ch.score                                                         --分数

       FROM choice  ch

       WHERE ch.s_no = p_s_no) LOOP

   dbms_output.put_line(v_choice.s_no || '  '||  v_choice.course_name ||  '  ' || v_choice.score);          

   END LOOP;

EXCEPTION

  WHEN no_data_found THEN

    dbms_output.put_line('该学号对应的choice表没有数据');

end GetCourseScore;

（2）设计一个函数，用来接受用户输入的课程名，显示该课程任课教师的姓名，并返回该课程的平均成绩。

create or replace function GetTeacherNameAvgGrade(p_course_name in course.course_no%type)

RETURN choice.score%TYPE

AS

  v_tname    teacher.t_name%TYPE;

  v_avgGrade choice.score%TYPE ;

begin

 

SELECT te.t_name,

       (SELECT AVG(ch.score)

               FROM choice ch

               WHERE ch.course_no = c.course_no) avgGrade

               INTO v_tname,v_avgGrade

       FROM course   c,

            teaching t,

            teacher  te

       WHERE c.course_no = t.course_no

       AND   t.t_no = te.t_no

       AND   c.course_name = p_course_name;--'邓小平理论'

  dbms_output.put_line(v_tname);    

  return(v_avgGrade);

EXCEPTION

  WHEN no_data_found THEN

    dbms_output.put_line('error!'); 

 

end GetTeacherNameAvgGrade;

(3) 设计一个包，包中一个存储过程和一个函数。存储过程以教师姓名为参数，统计该教师任课的门数，并显示该教师任课的所有课程号和课程名；函数以班级号为参数，显示该班级入学总分最高的学生姓名和入学总分并返回该学生的学号。

create or replace package pkg_GetCouseAndSno is

--声明 函数和过程

  FUNCTION GetMaxScoreInfo(f_classno class.Class_No%TYPE) RETURN student.s_no%TYPE;  

  PROCEDURE GetCoursenoCoursename(p_t_name teacher.t_name%TYPE);

end pkg_GetCouseAndSno;

create or replace package body pkg_GetCouseAndSno is

  -- Function and procedure implementations

  function GetMaxScoreInfo(f_classno class.Class_No%TYPE) return student.s_no%TYPE as

    v_sname                          student.s_name%TYPE;

    v_score                          student.s_score%TYPE;                       

    v_sno                            student.s_no%TYPE;

  begin

    SELECT st.s_name,

           st.s_score,

           st.s_no

           INTO v_sname,v_score,v_sno

       FROM CLASS       c,

            student     st

       WHERE c.class_no = st.class_no

       AND   st.s_score = (SELECT MAX(s_score)

                                  FROM student

                                  WHERE class_no = '0303')                          

       AND   c.class_no = f_classno;

    RETURN v_sno;

    dbms_output.put_line(v_sno);

    EXCEPTION

    WHEN NO_DATA_FOUND THEN

         DBMS_OUTPUT.PUT_LINE('no data!');

  end;

PROCEDURE GetCoursenoCoursename(p_t_name teacher.t_name%TYPE)

  AS

  v_coursenoCount                        NUMBER(2);

begin

  FOR cur IN (

 

  SELECT te.t_no                 ,         --课程编号

         course_no               ,                        --课程号

         (SELECT c.course_name

               FROM course             c

                    WHERE t.course_no = c.course_no)  course_name--课程名称                   

       FROM teacher     te,

            teaching    t

            WHERE       te.t_no = t.t_no

            AND         te.t_name = p_t_name) LOOP

   dbms_output.put_line(cur.t_no  || ' ' || cur.course_no || ' ' || cur.course_name);  

   END LOOP;     

  

   SELECT COUNT(t.course_no)  

          INTO v_coursenoCount    

          FROM teacher     te,

               teaching    t

            WHERE       te.t_no = t.t_no

            AND         te.t_name = p_t_name;

 

   dbms_output.put_line(v_coursenoCount);

   EXCEPTION

    WHEN NO_DATA_FOUND THEN

         DBMS_OUTPUT.PUT_LINE('error!');     

   END GetCoursenoCoursename;

  

end pkg_GetCouseAndSno;

--调用过程

BEGIN

  dbms_output.put_line(pkg_GetCouseAndSno.GetMaxScoreInfo('0303'));

  pkg_GetCouseAndSno.GetCoursenoCoursename( '教师一');

END;

附代码：

--1.创建下列表及约束
CREATE TABLE choice(
       s_no         VARCHAR2(10),
       course_no    VARCHAR2(10),
       score        NUMBER(3),
       CONSTRAINT choice_prim PRIMARY KEY(s_no,course_no)
); 
SELECT * FROM yanghk.choice;
--给表加注释：comment on table 表名 is '注释内容'
COMMENT ON TABLE choice IS '选课表';
--给字段加注释：comment on column 表名.字段名 is '注释内容';
COMMENT ON COLUMN choice.s_no IS '学号';
COMMENT ON COLUMN choice.course_no IS '该学号学生选课课号';
comment ON COLUMN choice.score IS '该课程该学生的成绩';
COMMENT ON COLUMN teacher.t_no IS '教师编号';

CREATE TABLE CLASS(
       class_no                VARCHAR2(10) PRIMARY KEY,
       class_name              VARCHAR2(50) NOT NULL,
       dept_no                 VARCHAR2(10) NOT NULL      
);

CREATE TABLE course(
       course_no               VARCHAR2(10) PRIMARY KEY,
       course_name             VARCHAR2(50) NOT NULL
);
CREATE TABLE department(
       dept_no                 VARCHAR2(10) PRIMARY KEY,
       dept_name               VARCHAR2(50) NOT NULL
);
CREATE TABLE student(
       s_no                    VARCHAR2(10) PRIMARY KEY,
       s_name                  VARCHAR2(50) NOT NULL,
       s_sex                   VARCHAR2(50) NOT NULL,
       s_birthday              DATE,
       s_score                 NUMBER(3),
       s_addf                  NUMBER(3),
       class_no                VARCHAR2(10) NOT NULL
);

CREATE TABLE teacher(
       t_no                    VARCHAR2(10) PRIMARY KEY,
       t_name                  VARCHAR2(50) NOT NULL,
       t_sex                   VARCHAR2(10) NOT NULL,
       s_birthday              DATE,
       tech_title              VARCHAR2(50) NOT NULL,
       age                     NUMBER(3),
       salary                  NUMBER     
);

CREATE TABLE teaching(
       t_no VARCHAR2(10),
       course_no VARCHAR2(10),
       CONSTRAINT teaching_prim PRIMARY KEY(t_no,course_no) 
);
--2.添加外键约束 : 可以在创建表的时候创建，也可以使用alter语句添加
--语法：
/*ALTER TABLE table_name
ADD CONSTRAINT constraint_name
    FOREIGN KEY(column1,column2,...column_n)
    REFERENCES parent_table(column1,column2...column_n)  --注意：参照的单词是复数形式*/
--(1)
ALTER TABLE choice 
ADD CONSTRAINT fk_course_no
    FOREIGN KEY(course_no) 
    REFERENCES course(course_no);    
--(2)
ALTER TABLE choice 
ADD CONSTRAINT fk_s_no
    FOREIGN KEY(s_no) 
    REFERENCES student(s_no); 
--(3)
ALTER TABLE CLASS 
ADD CONSTRAINT fk_class_no
    FOREIGN KEY(dept_no) 
    REFERENCES department(dept_no); 
--(4)
ALTER TABLE teaching 
ADD CONSTRAINT fk_teaching_course_no
    FOREIGN KEY(course_no) 
    REFERENCES course(course_no);
--添加检查性约束(check约束)
/*ALTER TABLE table_name
ADD constraint_name CHECK(column_name condition)[DISABLE]; 
如果使用disable，则创建的约束不会立即生效*/
ALTER TABLE student 
ADD CONSTRAINT ck_s_sex
    CHECK(s_sex IN ('男','女'));

ALTER TABLE teacher 
ADD CONSTRAINT ck_t_sex
    CHECK(t_sex IN ('男','女'));

ALTER TABLE choice 
ADD CONSTRAINT ck_score
    CHECK(score BETWEEN 0 AND 100);
--3.向表中插入数据 先插如department表和course表
SELECT * FROM department;
INSERT INTO choice VALUES('020201','07',83);   
INSERT INTO student VALUES('020201','张三','男',NULL,560,0,'0202');  
INSERT INTO CLASS VALUES('0202','计算机2班','02');
INSERT INTO department VALUES('02','计算机');

INSERT INTO course VALUES('01','数学分析');


SELECT * FROM class;
INSERT INTO department VALUES('03','数学系');
INSERT INTO department VALUES('04','电气系');

INSERT INTO class VALUES('0302','数学2班','03');
INSERT INTO class VALUES('0303','数学3班','03');
INSERT INTO class VALUES('0401','电气1班','04');

SELECT * FROM teaching;
INSERT INTO student VALUES('020202','李四','男',NULL,578,0,'0202');
INSERT INTO student VALUES('030201','王五','女',NULL,545,10,'0302');
INSERT INTO student VALUES('030202','丁一','女',NULL,589,0,'0302');
INSERT INTO student VALUES('030301','周一','女',NULL,570,0,'0303');
INSERT INTO student VALUES('030302','牛二','男',NULL,609,0,'0303');
INSERT INTO student VALUES('030303','阮七','女',NULL,603,0,'0303');
INSERT INTO student VALUES('040101','苏三','女',NULL,548,50,'0401');

INSERT INTO teacher VALUES('000001','教师一','男',NULL,'教授',56,4800);
INSERT INTO teacher VALUES('000002','教师二','女',NULL,'教授',55,4333);
INSERT INTO teacher VALUES('000003','教师三','男',NULL,'副教授',43,3342);
INSERT INTO teacher VALUES('000005','教师四','女',NULL,'讲师',29,2234);
INSERT INTO teacher VALUES('000006','教师五','男',NULL,'讲师',30,2312);
INSERT INTO teacher VALUES('000007','教师六','女',NULL,'讲师',45,2313);


SELECT * FROM teaching;
INSERT INTO teaching VALUES('000001','01');
INSERT INTO teaching VALUES('000001','06');
INSERT INTO teaching VALUES('000001','13');
INSERT INTO teaching VALUES('000002','02');
INSERT INTO teaching VALUES('000002','03');
INSERT INTO teaching VALUES('000003','07');
INSERT INTO teaching VALUES('000003','08');
INSERT INTO teaching VALUES('000005','05');
INSERT INTO teaching VALUES('000006','04');
INSERT INTO teaching VALUES('000006','12');
INSERT INTO teaching VALUES('000007','11');

SELECT DISTINCT course_no FROM choice;
SELECT DISTINCT s_no FROM student;
SELECT * FROM choice;
INSERT INTO choice VALUES('020201','08',79);
INSERT INTO choice VALUES('020201','09',78);
INSERT INTO choice VALUES('020201','11',92);
INSERT INTO choice VALUES('020201','12',95);
INSERT INTO choice VALUES('020201','04',86);
INSERT INTO choice VALUES('020202','13',67);
INSERT INTO choice VALUES('020202','07',89);
INSERT INTO choice VALUES('020202','08',77);
INSERT INTO choice VALUES('020202','09',80);
INSERT INTO choice VALUES('020202','11',90);
INSERT INTO choice VALUES('020202','12',93);
INSERT INTO choice VALUES('020202','04',88);
INSERT INTO choice VALUES('030201','01',79);
INSERT INTO choice VALUES('030201','04',77);
INSERT INTO choice VALUES('030201','07',88);
INSERT INTO choice VALUES('030201','08',86);
INSERT INTO choice VALUES('030201','10',77);
INSERT INTO choice VALUES('030202','01',55);
INSERT INTO choice VALUES('030202','04',54);
INSERT INTO choice VALUES('030202','07',67);
INSERT INTO choice VALUES('030202','08',65);
INSERT INTO choice VALUES('030202','10',70);
INSERT INTO choice VALUES('040101','02',78);
INSERT INTO choice VALUES('040101','03',82);  
INSERT INTO choice VALUES('040101','04',78);
INSERT INTO choice VALUES('040101','07',67);
INSERT INTO choice VALUES('040101','08',76);
INSERT INTO choice VALUES('040101','09',86);
/*INSERT INTO choice VALUES('030201','13',78);
INSERT INTO choice VALUES('030201','05',80);
INSERT INTO choice VALUES('030201','13',86);
INSERT INTO choice VALUES('030201','05',79);*/

--3.查询
SELECT * FROM choice;
SELECT * FROM student;
--(1)
SELECT st.s_no                  学号,
       st.s_name                姓名,
       st.s_score          总分,
       st.class_no              班级编号,
       (SELECT class_name
               FROM CLASS
               WHERE class_no = st.class_no)                       班级名信息       
  FROM STUDENT ST
 WHERE st.s_score >
       (SELECT SUM(ALL_GRADE) / COUNT(S_NO) AVG_ALL_GRADE              --平均总分
          FROM (SELECT S_NO, COUNT(S_NO) COUNT_NUM, SUM(SCORE) ALL_GRADE
                  FROM CHOICE
                 GROUP BY S_NO
                 ORDER BY S_NO) TMP
        
        );
--(2)
SELECT * FROM choice WHERE s_no = '030201' AND course_no = '04';
SELECT * FROM course;
SELECT * FROM student WHERE s_name = '王五';
(SELECT course_no FROM course WHERE course_name = '计算机文化');

SELECT st.s_no    学号,
       st.s_name  姓名,
       (SELECT course_name FROM course WHERE course_no = ch.course_no) 课程名称,
       ch.score   分数
       FROM student    st,
            choice     ch
       WHERE st.s_no = ch.s_no
       AND   ch.course_no = (
             SELECT course_no
                    FROM course
                    WHERE course_name = '计算机文化'   --计算机文化课对应的课程编号
       )
       AND   ch.score > (
             SELECT score
                    FROM choice ch
                    WHERE s_no = (
                          SELECT s_no FROM student WHERE s_name = '王五'
                    ) 
                    AND course_no = (
                       SELECT course_no
                              FROM course
                              WHERE course_name = '计算机文化' 
                    )
       );
--（3）分组统计选修各门课程的学生人数，要求显示课号，课程名称和学生人数
SELECT * FROM student;
SELECT * FROM choice;
SELECT * FROM course;
 SELECT course_no                                     课程编号,
        (SELECT course_name
                FROM course
                WHERE course_no = c.course_no)        课程名称,
        COUNT(c.s_no)                                   学生编号  
        FROM choice c
        GROUP BY c.course_no
        ORDER by c.course_no;
--（4） 
SELECT * FROM department WHERE dept_name = '计算机系';   

--class表的dept_no参照department表的dept_no  ，故先修改class表的dept_no信息
--禁用外键约束
ALTER TABLE CLASS DISABLE CONSTRAINT FK_DEPT_NO; 

UPDATE department d
SET d.dept_no = '01'   
WHERE dept_name = '计算机系';  

INSERT INTO department VALUES('02','土木系');

--开启外键约束
ALTER TABLE CLASS ENABLE CONSTRAINT FK_DEPT_NO;

SELECT * FROM department;
SELECT * FROM user_constraints WHERE constraint_type = 'R' AND constraint_name = 'FK_DEPT_NO';

--4.PL/SQL编程
--(1)
create or replace procedure GetCourseScore(p_s_no in choice.s_no%TYPE) IS

begin
  FOR v_choice IN 
  (SELECT ch.s_no                                                      ,--学号
       (SELECT course_name                              
               FROM course
               WHERE course_no = ch.course_no) course_name              ,--课程名称
       ch.score                                                         --分数
       FROM choice  ch
       WHERE ch.s_no = p_s_no) LOOP
   dbms_output.put_line(v_choice.s_no || '  '||  v_choice.course_name ||  '  ' || v_choice.score);           
   END LOOP;
EXCEPTION 
  WHEN no_data_found THEN 
    dbms_output.put_line('该学号对应的choice表没有数据');
end GetCourseScore;
--(2)设计一个函数，用来接受用户输入的课程名，显示该课程任课教师的姓名，并返回该课程的平均成绩。
create or replace function GetTeacherNameAvgGrade(p_course_name in course.course_no%type) 
RETURN choice.score%TYPE 
AS
  v_tname    teacher.t_name%TYPE;
  v_avgGrade choice.score%TYPE ;
begin
  
SELECT te.t_name,
       (SELECT AVG(ch.score)
               FROM choice ch
               WHERE ch.course_no = c.course_no) avgGrade
               INTO v_tname,v_avgGrade
       FROM course   c,
            teaching t,
            teacher  te
       WHERE c.course_no = t.course_no
       AND   t.t_no = te.t_no
       AND   c.course_name = p_course_name;--'邓小平理论'
  dbms_output.put_line(v_tname);     
  return(v_avgGrade);
EXCEPTION
  WHEN no_data_found THEN 
    dbms_output.put_line('error!');  
  
end GetTeacherNameAvgGrade;
            
--(3) 设计一个包，包中一个存储过程和一个函数。
--存储过程以教师姓名为参数，统计该教师任课的门数，并显示该教师任课的所有课程号和课程名；
--函数以班级号为参数，显示该班级入学总分最高的学生姓名和入学总分并返回该学生的学号。
create or replace package pkg_GetCouseAndSno is

--声明 函数和过程
  FUNCTION GetMaxScoreInfo(f_classno class.Class_No%TYPE) RETURN student.s_no%TYPE;   
  PROCEDURE GetCoursenoCoursename(p_t_name teacher.t_name%TYPE);


end pkg_GetCouseAndSno;

create or replace package body pkg_GetCouseAndSno is

  -- Function and procedure implementations
  function GetMaxScoreInfo(f_classno class.Class_No%TYPE) return student.s_no%TYPE as
    v_sname                          student.s_name%TYPE;
    v_score                          student.s_score%TYPE;                        
    v_sno                            student.s_no%TYPE;
  begin
    SELECT st.s_name,
           st.s_score,
           st.s_no
           INTO v_sname,v_score,v_sno
       FROM CLASS       c,
            student     st
       WHERE c.class_no = st.class_no
       AND   st.s_score = (SELECT MAX(s_score) 
                                  FROM student
                                  WHERE class_no = '0303')                           
       AND   c.class_no = f_classno;
    RETURN v_sno;
    dbms_output.put_line(v_sno);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('no data!');
  end;


PROCEDURE GetCoursenoCoursename(p_t_name teacher.t_name%TYPE)
  AS
  v_coursenoCount                        NUMBER(2);
begin
  FOR cur IN (
  
  SELECT te.t_no                 ,         --课程编号
         course_no               ,                        --课程号
         (SELECT c.course_name
               FROM course             c
                    WHERE t.course_no = c.course_no)  course_name--课程名称                    
       FROM teacher     te,
            teaching    t
            WHERE       te.t_no = t.t_no
            AND         te.t_name = p_t_name) LOOP
   dbms_output.put_line(cur.t_no  || ' ' || cur.course_no || ' ' || cur.course_name);   
   END LOOP;      
   
   SELECT COUNT(t.course_no)   
          INTO v_coursenoCount     
          FROM teacher     te,
               teaching    t
            WHERE       te.t_no = t.t_no
            AND         te.t_name = p_t_name;
  
   dbms_output.put_line(v_coursenoCount);
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('error!');      
   END GetCoursenoCoursename;
   
end pkg_GetCouseAndSno;


--调用过程
BEGIN
  dbms_output.put_line(pkg_GetCouseAndSno.GetMaxScoreInfo('0303'));
  pkg_GetCouseAndSno.GetCoursenoCoursename( '教师一');
END;
--(4) 设计一个触发器，用于在更新了课程表内容后，更新与课程表有关联的表的内容
CREATE OR REPLACE TRIGGER UPDATECOURSE
  AFTER UPDATE ON COURSE
  FOR EACH ROW
DECLARE
  -- local variables here
BEGIN

  UPDATE TEACHING TE
     SET TE.COURSE_NO = :NEW.COURSE_NO
   WHERE TE.COURSE_NO = :OLD.COURSE_NO;

  UPDATE CHOICE C
     SET C.COURSE_NO = :NEW.COURSE_NO
   WHERE C.COURSE_NO = :OLD.COURSE_NO;

END UPDATECOURSE;

ScrapingBoy
关注
  • 5
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
oracle作业答案
11-28
根据提供的标题、描述、标签及部分内容，可以看出这是关于Oracle数据库作业的一个示例文档。文档包含了个人信息、准备环境、以及两个具体的问题与解答。下面将详细解释文档中的知识点。 ### 个人信息 这部分列出了...
Oracle作业答案上次发的题的答案
08-07
针对“Oracle作业答案上次发的题的答案”这个主题，我们可以深入探讨一些Oracle数据库的基础知识以及可能涉及的作业题目类型。 1. **Oracle数据库基础**：Oracle数据库系统由多个组件构成，如数据库实例、数据文件...
ORACLE与数据库原理实验 实验四 查询与视图 （答案全）
m0_51456787的博客
01-23 1037
实验四 查询与视图[实验目的][预备知识][实验内容] [实验目的] 掌握 select 的基本使用。 集合运算的语法。 掌握建立视图的语法。 掌握和理解外部连接的使用。 掌握通过 select 方式建表和插入数据的方法。 [预备知识] select 语句的基本语法 create view 的语法 外部链接的含义及语法 [实验内容] 查询所有顾客的姓名。 select customer_name from customer; 查询所有分行的信息。 select * from branch;
数据库原理及安全技术教学实验报告SQL实践（七）
王陈锋的博客
05-15 7260
一、实验目的 1、掌握各种简单查询、复杂查询。 二、实验软硬件要求 1、SQL Server 2008 三、实验预习 1、单表查询、表连接等SQL语句。 四、实验内容（实验步骤、测试数据等） 书本P294综合练习： 在综合实验（一）的基础上，作如下查询： 简单的数据查询 (1)查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别 S_sex、出生日期s_birthday。 (2)查询所有同学，要求显示其学号s_no、姓名s_name...
MySQL语句面试实例
qq_29423387的博客
10-19 624
参考链接 需要数据库表：（使用phpmyadmin创建） 学生表-Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别； 课程表-Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号； 教师表-Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名； 成绩表-SC(SID,CID,score) --SID 学生编号,CID 课程编号,sc
EasyCode插件 踩坑 数据库类型int unsigned，没有找到映射关系，是否去添加？
mnimxq的博客
08-16 3026
数据库类型int unsigned，没有找到映射关系，是否去添加？ 使用EasyCode-MybatisCodeHelper插件，如何安装，省略 生成code是报错：数据库类型int unsigned，没有找到映射关系，是否去添加？ 吃亏在基础不牢，int类型，有无符号，这个定义不清楚，简单来说，范围不同 无符号：可以是负值 有符号：只能是正数 作为主键id，只能选择有符号的int类型 ...
Oracle表对象管理、sql语言数据类型——简单测试题以及答案
girls的博客
12-08 867
"create table ts"语句用于创建一个没有列的空表 答案：❌ 创建数据表时必须为字段设置数据类型。 答案：✅ 数据表中字段的创建顺序与其在数据库中存储的顺序相同。 答案：✅ 数据字典中的所有者是____。其数据字典表和视图都被保存在______表空间中。 答案：sys      system 要存储数据123.45，最合适使用_____数据类型。 A：number(5) B：number(6) C：number(6，2) D：number(5，.
oracle 创建表性别字段取'男'或者'女',默认为'男'
郭大侠的专栏
07-26 4万+
创建表时候就定义性别字段： DEFAULT  ’男‘  定义默认为 ’男‘，CHECK约束只能从’男‘、’女‘中选择。 CREATE TABLE pp(  ID      INT,  NAME    VARCHAR(40),  SEX     VARCHAR(2)  DEFAULT '男' CHECK(SEX IN ('男','女')),  ADDRESS VARCHAR(500)
oracle参考答案.pdf
12-27
- **OracleJobSchedulerORCL**：负责数据库作业调度。 - **OracleOraDB12Home1MTSRecoveryService**：使数据库能够作为微软事务服务器的一部分工作。 - **OracleRemExecServiceV2**：安装过程中的临时服务。 - **...
数据库原理与技术（ORACLE版）第3版作业参考答案
最新发布
05-16
【数据库原理与技术（ORACLE版）第3版作业参考答案】主要涵盖了数据库的基础理论以及在实际应用中的问题。在数据库领域，Oracle是一种广泛应用的关系型数据库管理系统，它提供了高效的数据存储和管理能力。 1. ...
oracle作业练习与答案
09-01
"Oracle作业练习与答案" 提供了一个很好的平台，帮助学习者通过实践来深化对Oracle的理解。 在Oracle中，基础知识点主要包括以下几个方面： 1. **SQL基础**：SQL（Structured Query Language）是用于管理关系...
mysql做题库_MySQL--题库解析
weixin_35698069的博客
01-19 594
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；思路：获取所有有生物课程的人(学号，成绩) - 临时表获取所有有物理课程的人(学号，成绩) - 临时表根据【学号】连接两个临时表：学号 物理成绩 生物成绩然后再进行筛选select A.student_id,sw,ty from(select student_id,num as sw from score left join cour...
MYSQL数据库系统第3次实验 数据表的修改与数据操作
qq_19716069的博客
06-21 1778
实验目的： 熟悉修改数据表； 熟悉数据表的数据操作。 实验设备及器材： PC，mysql 等 实验内容： 基于上次实验创建的数据库teaching，以及数据库中的6张数据表，完成以下操作： 查看数据库teaching已经创建的表； mysql> show tables; 2. 查看数据表student的基本结构； mysql> desc student; 3. 查看数据表course的建表语句； mysql> show create table course; 4. 通
Mysql workbench 建立表（Student,Course,SC） 每个表中插入数据
qq_44218805的博客
04-10 1万+
Mysql workbench 建立表（Student,Course,SC） 每个表中插入数据 首先默认你已经创建了数据库了，在该数据库中建立表(如果没有建立，请移驾https://editor.csdn.net/md/?articleId=105425265) 创建表Student 1,采用Sql语句 create table `taotao2`.`Student` /*注意标点·，不是...
查询学生 学号、姓名和平均成绩
热门推荐
大土的博客
12-06 6万+
三个表：Student（学生）,Course（课程）,SC（选课）：刚开始是这么写的：select Student.sname,Student.sno,avg(grade) as avg_grade from Student,SC where Student.sno=SC.Sno group by Student.Sno;结果在SQL Server中执行报错：我在网上查了很多
oracle数据库中VARCHAR2(50 CHAR) 和VARCHAR2(50) 有啥区别？
dbkahhs97022的博客
03-07 632
VARCHAR2（50 char）这种类型的字段最多放50个字符，不够50个用空格填充；而VARCHAR2(50)最大允许存放50个字符，但是不足50个也不用空格填充。varchar2是变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。 转载于:https://www.cnblogs.com/xinxin1994/p/651...
Mysql复习题
PZ的博客
05-21 3万+
1. 创建fuxi数据库； create database fuxi; 2. 删除fuxi数据库； drop database fuxi; 3. 显示所有数据库； show databases; 4. 使用/进入 fuxi这个数据库； use fuxi; 5. 显示fuxi数据库内的所有表； show tables; 6. 创建没有约束条件的student表 create table studen...
oracle课后习题答案,Oracle习题答案(课后题)
weixin_35886058的博客
04-13 432
实用信息。仅供参考。http://doc.docsou.com/zhenyulu/articles/330494.html第一章略第二章5、已知一个关系数据库的模式如下：S (SNO,SNAME,SCITY)P (PNO,PNAME,COLOR,WEIGHT)J (JNO,JNAME,JCITY)SPJ (SNO,PNO,JNO,QTY)供应商S由供应商代码SNO、供应商姓名SNAME、供应商所在...
Oracle数据库开发作业答案解析
"北语19春《Oracle数据库开发》作业1234满分答案.docx" 这篇文档涉及的是Oracle数据库开发的相关知识，主要涵盖了数据库的基本逻辑结构、iBatis框架中的关联关系定义、SQL语句的使用以及Oracle数据库的一些核心概念...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值