Oracle Database Build table and SQL programming classification

--Create student tables
CREATE TABLE xs_543
(XH char (6) NOT NULL,
XM varchar2 () NOT NULL,
Zym VARCHAR2 (10),
XB char (4) Default ' male ',
CSSJ date,
ZXF Number (2),
BZ varchar2 (100),
Constraint Pk_xh primary KEY (XH)
);

--Create a curriculum

CREATE TABLE kc_543
(
KCH char (3) NOT NULL,
KCM varchar2 () NOT NULL,
KKXQ Number (1) Default 1,
XS Number (2),
XF number (1),
Constraint Pk_kch primary KEY (KCH),
Constraint ck_kkxq Check (KKXQ between 1 and 8)
);

--Create a score table

CREATE TABLE xs_kc_543
(
XH Char (6) NOT NULL,
KCH char (3) NOT NULL,
CJ Number (3),
Constraint Pk_xh_kch primary KEY (XH,KCH),
Constraint fk_xh foreign KEY (XH) references xs_543 (XH),
Constraint Fk_kch foreign KEY (KCH) references kc_543 (KCH)
);

--Inserting data

INSERT into xs_543 values (' 061101 ', ' Wang Lin ', ' computer ', ' Male ', to_date (' 1986-2-01 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061102 ', ' Cheng ', ' computer ', ' Male ', to_date (' 1987-2-10 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061103 ', ' Wang ', ' computer ', ' Female ', to_date (' 1985-1-06 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061104 ', ' Wei 10 Lianping ', ' computer ', ' Male ', to_date (' 1986-8-26 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061106 ', ' Li Fang ', ' computer ', ' Male ', to_date (' 1986-11-20 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061107 ', ' Li Ming ', ' computer ', ' Male ', to_date (' 1986-5-01 ', ' Yyyy-mm-dd '), 54, ' pre-completed data structure ');
INSERT into xs_543 values (' 061108 ', ' Lin fan ', ' computer ', ' Male ', to_date (' 1985-8-05 ', ' yyyy-mm-dd '), 50, ' has completed a course in advance ');
INSERT into xs_543 values (' 061109 ', ' Zhang Qiang min ', ' computer ', ' Male ', to_date (' 1984-8-11 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061110 ', ' Zhang Wei ', ' computer ', ' Female ', to_date (' 1987-7-22 ', ' yyyy-mm-dd '), 50, ' Miyoshi students ');
INSERT into xs_543 values (' 061111 ', ' Zhao Lin ', ' computer ', ' Female ', to_date (' 1986-3-18 ', ' yyyy-mm-dd '), 50,null);
INSERT into xs_543 values (' 061113 ', ' strict red ', ' computer ', ' Female ', to_date (' 1985-8-11 ', ' yyyy-mm-dd '), 48, ' A course failed ');
INSERT into xs_543 values (' 061201 ', ' Wang Min ', ' Communication engineering ', ' Male ', to_date (' 1984-6-10 ', ' yyyy-mm-dd '), 42,null);
INSERT into xs_543 values (' 061202 ', ' Wang Lin ', ' Communication engineering ', ' Male ', to_date (' 1985-1-29 ', ' yyyy-mm-dd '), 42,null);
INSERT into xs_543 values (' 061203 ', ' minis ', ' Communication engineering ', ' Male ', to_date (' 1986-3-26 ', ' yyyy-mm-dd '), 42,null);
INSERT into xs_543 values (' 061204 ', ' Marlene ', ' Communication engineering ', ' Female ', to_date (' 1984-2-10 ', ' yyyy-mm-dd '), 42,null);
INSERT into xs_543 values (' 061206 ', ' li-meter ', ' Communication engineering ', ' Female ', to_date (' 1985-9-20 ', ' yyyy-mm-dd '), 42,null);
INSERT into xs_543 values (' 061210 ', ' Li Hongqing ', ' Communication engineering ', ' Female ', to_date (' 1985-5-10 ', ' yyyy-mm-dd '), 44, ' early completion of a course ');
INSERT into xs_543 values (' 061216 ', ' Sun Xiangxin ', ' Communication engineering ', ' Female ', to_date (' 1984-3-09 ', ' yyyy-mm-dd '), 42,null);
INSERT into xs_543 values (' 061218 ', ' Sun Qi ', ' Communication engineering ', ' Male ', to_date (' 1986-10-9 ', ' yyyy-mm-dd '), 42,null);
INSERT into kc_543 values (' 101 ', ' Computer Basics ', 1,80,5);
INSERT into kc_543 values (' 102 ', ' programming language ', 2,68,4);
INSERT into kc_543 values (' 206 ', ' discrete math ', 2,68,4);
INSERT into kc_543 values (' 208 ', ' data structure ', 5,68,4);
INSERT into kc_543 values (' 209 ', ' operating system ', 6,68,4);
INSERT into kc_543 values (' 210 ', ' computer principle ', 7,85,5);
INSERT into kc_543 values (' 212 ', ' Database Principle ', 7,68,4);
INSERT into kc_543 values (' 301 ', ' computer network ', 7,51,3);
INSERT into kc_543 values (' 302 ', ' Software engineering ', 7,51,3);
INSERT into xs_kc_543 values (' 061101 ', ' 101 ', 80);
INSERT into xs_kc_543 values (' 061101 ', ' 102 ', 78);
INSERT into xs_kc_543 values (' 061101 ', ' 206 ', 76);
INSERT into xs_kc_543 values (' 061103 ', ' 101 ', 62);
INSERT into xs_kc_543 values (' 061103 ', ' 206 ', 81);
INSERT into xs_kc_543 values (' 061103 ', ' 102 ', 70);
INSERT into xs_kc_543 values (' 061104 ', ' 101 ', 90);
INSERT into xs_kc_543 values (' 061104 ', ' 102 ', 84);
INSERT into xs_kc_543 values (' 061104 ', ' 206 ', 65);
INSERT into xs_kc_543 values (' 061102 ', ' 102 ', 78);
INSERT into xs_kc_543 values (' 061102 ', ' 206 ', 78);
INSERT into xs_kc_543 values (' 061107 ', ' 101 ', 78);
INSERT into xs_kc_543 values (' 061107 ', ' 102 ', 80);
INSERT into xs_kc_543 values (' 061107 ', ' 206 ', 68);
INSERT into xs_kc_543 values (' 061108 ', ' 101 ', 85);
INSERT into xs_kc_543 values (' 061108 ', ' 102 ', 64);
INSERT into xs_kc_543 values (' 061108 ', ' 206 ', 87);
INSERT into xs_kc_543 values (' 061109 ', ' 101 ', 66);
INSERT into xs_kc_543 values (' 061109 ', ' 102 ', 83);
INSERT into xs_kc_543 values (' 061109 ', ' 206 ', 70);
INSERT into xs_kc_543 values (' 061111 ', ' 206 ', 76);
INSERT into xs_kc_543 values (' 061113 ', ' 101 ', 63);
INSERT into xs_kc_543 values (' 061113 ', ' 102 ', 79);
INSERT into xs_kc_543 values (' 061113 ', ' 206 ', 60);
INSERT into xs_kc_543 values (' 061201 ', ' 101 ', 80);
INSERT into xs_kc_543 values (' 061202 ', ' 101 ', 65);
INSERT into xs_kc_543 values (' 061203 ', ' 101 ', 87);
INSERT into xs_kc_543 values (' 061204 ', ' 101 ', 91);
INSERT into xs_kc_543 values (' 061210 ', ' 101 ', 76);
Commit

Declare
V_xh Xs_kc_543.xh%type;
V_kch Xs_kc_543.kch%type;
V_CJ Xs_kc_543.cj%type;
Rank char (2);
Begin
v_xh:=&v_xh;
v_kch:=&v_kch;
Select CJ into V_CJ from xs_kc_543 where Xh=v_xh and kch=v_kch;
Case V_CJ/10

--The last time this place went wrong.

When 9 then rank:= ' a ';
When 8 then rank:= ' B ';
When 7 then rank:= ' C ';
When 6 then rank:= ' d ';
When 5 then rank:= ' E ';
End case;
Dbms_output.put_line (rank);
End

Oracle Database Build table and SQL programming classification