如何相对于表的其他列的值在一列中插入值

I have the following table result:

我有以下表格结果:

roll integer 
c_id varchar(20)
marks number(6,2)
grade varchar(6)
gpa number(3,2)
cgpa number(3,2)
year varchar(10)
term varchar(10)

And courses and student tables. I have entered into result table the marks of courses and want to assign the corresponding grade in the grade column. I want to insert A+ in grade column for marks of greater than or equal to 240, A for greater than equal to 210 and less than 240.

和课程和学生表。我已在结果表中输入了课程标记,并希望在成绩栏中指定相应的成绩。我想在等级列中插入A +,大于或等于240的标记,A大于等于210且小于240。

How can I do it?

我该怎么做?

Here is the DDL:

这是DDL:

drop table result;
drop table courses;
drop table student;
create table student(
roll number(10),
fname varchar(30),
lname varchar(30),
department varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (roll)

create table courses(
c_id varchar(30) NOT NULL,
c_name varchar(30), 
credit integer CHECK(credit>0 and credit<5)
);    
ALTER TABLE COURSES ADD PRIMARY KEY (c_id)
ALTER TABLE COURSES ADD UNIQUE KEY (c_id)
create table result(
roll number(10),
c_id varchar(30),
marks number (6,2),
year varchar (10),
term varchar (10),
gpa number(3,2),
cgpa number(3,2),
grade varchar (4)
);
ALTER TABLE result ADD FOREIGN KEY (roll) REFERENCES student(roll)
ALTER TABLE result ADD FOREIGN KEY (c_id) REFERENCES courses(c_id)

Here is the result data:

这是结果数据:

insert into result (roll,c_id,marks,year,term) values (1307019,'cse 3103',200,'2nd','1st');
insert into result (roll,c_id,marks,year,term) values (1307019,'cse 3105',210,'2nd','1st');
insert into result (roll,c_id,marks,year,term) values (1307019,'cse 3107',190,'2nd','1st');

3 个解决方案

#1

You can use an update statement with a conditional expression to alter the existing data and fill the grade column.

您可以使用带有条件表达式的update语句来更改现有数据并填充成绩列。

This should work:

这应该工作:

update result 
set grade = 
  case 
    when marks >= 240 then 'A+'
    when marks >= 210 and marks < 240 then 'A'
    -- continue adding more clauses below
    -- else 'B' -- maybe a default value?
  end;

#2

try this database compound trigger

尝试此数据库复合触发器

here i give you database trigger which will be fire on table while you are inserting or updating records and this can be accomplished with specific condition

在这里,我给你数据库触发器,当你插入或更新记录时,它将在桌面上触发,这可以通过特定条件来完成

CREATE OR REPLACE TRIGGER trg_result
  FOR INSERT OR UPDATE ON result
  COMPOUND TRIGGER
  BEFORE EACH ROW IS
  BEGIN
    CASE

      WHEN INSERTING OR UPDATING THEN
        :NEW.GRADE := (CASE
                        WHEN :NEW.marks >= 240 THEN
                         'A+'
                        WHEN :NEW.marks >= 210 and :NEW.marks < 240 THEN
                         'A'
                        ELSE
                         'B'
                      END);

    END CASE;
  END BEFORE EACH ROW;

END trg_result;

main benefits of trigger is we can update automatically grade which is depends on marks while insert and update in your example.

触发器的主要好处是我们可以在您的示例中自动更新等级,这取决于插入和更新时的标记。

after create trigger fire your insert statement again and see result.your grade column is insert as specified condition.

在create trigger之后再次激活你的insert语句并看到result.your grade列被插入为指定的条件。

full description of compound trigger is here

复合触发器的完整描述在这里

https://oracle-base.com/articles/11g/trigger-enhancements-11gr1

i hope this may help.

我希望这可能有所帮助。

#3

There are two natural ways to do this. To illustrate I simplify the problem (in comments to the original question I pointed out a couple of things that should be changed in the setup).

有两种自然的方法可以做到这一点。为了说明我简化了问题(在对原始问题的评论中,我指出了一些应该在设置中改变的事情)。

Virtual column approach:

虚拟列方法:

create table results (
roll number,
c_id number,
mark number(6,2),                      -- why "marks"? it should be "mark"
grade varchar(6) as (                  -- oversimplified, change as needed
     case when mark >= 240 then 'A+'
          when mark >= 210 then 'A'
          else                  'F--'
          end
   )
);

In this arrangement, you only insert values for roll, c_id, s_id, mark - if you try to also enter a value for grade you will get an error message. Used like this:

在此安排中,您只插入roll,c_id,s_id,mark的值 - 如果您还尝试输入成绩值,则会收到错误消息。像这样使用:

SQL> insert into results (roll, c_id, mark) values (322245, 316, 235);

1 row created.

SQL> insert into results (roll, c_id, mark) values (322673, 316, 190);

1 row created.


SQL> select * from results;

      ROLL       C_ID       MARK GRADE
---------- ---------- ---------- ------
    322245        316        235 A
    322673        316        190 F--

2 rows selected.

The biggest weakness of this approach is that the breakpoints for the different letter grades are hard-coded directly in the table definition.

这种方法的最大弱点是不同字母等级的断点直接在表定义中进行了硬编码。

The better approach is:

更好的方法是:

View

Create a separate table with c_id, year, term, and minimum required score for each letter grade. This is maintained separately from the results table.

创建一个单独的表,其中包含每个字母等级的c_id,年份,期限和最低要求分数。这与结果表分开维护。

Then create a VIEW joining the results table and this helper table to compute the letter grade from the mark, joining on c_id, year, term and a more complicated condition on mark.

然后创建一个连接结果表和此帮助程序表的VIEW,以计算标记中的字母等级,加入c_id,年份,术语和标记上更复杂的条件。

Not sure the OP wants to go that far; I can post a simple implementation of this approach if desired.

不确定OP想要走那么远;如果需要,我可以发布这种方法的简单实现。

#1