1.创建测试表:
代码如下:DROP SEQUENCE student_sequence; CREATE SEQUENCE student_sequence START WITH 10000 INCREMENT BY 1;
DROP TABLE students; CREATE TABLE students ( id NUMBER(5) PRIMARY KEY, first_name VARCHAR2(20), last_name VARCHAR2(20), major VARCHAR2(30), current_credits NUMBER(3), grade varchar2(2));
INSERT INTO students (id,first_name,last_name,major,current_credits,grade) VALUES (student_sequence.NEXTVAL,'Scott','Smith','Computer Science',98,null);
INSERT INTO students (id,'Margaret','Mason','History',88,'Joanne','Junebug',75,'Manish','Murgratroid','Economics',66,null);
commit;
2.查看相应数据
代码如下:SQL> select * from students;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS GR ---------- -------------------- -------------------- ------------------------------ --------------- -- 10000 Scott Smith Computer Science 98 10001 Margaret Mason History 88 10002 Joanne Junebug Computer Science 75 10003 Manish Murgratroid Economics 66
3.更新语句
代码如下:update students set grade = ( select grade from ( select id, case when current_credits > 90 then 'a' when current_credits > 80 then 'b' when current_credits > 70 then 'c' else 'd' end grade from students ) a where a.id = students.id ) /
4.更新后结果
代码如下:SQL> select * from students;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS GR ---------- -------------------- -------------------- ------------------------------ --------------- -- 10000 Scott Smith Computer Science 98 a 10001 Margaret Mason History 88 b 10002 Joanne Junebug Computer Science 75 c 10003 Manish Murgratroid Economics 66 d
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|