PL/SQL编程之道之CASE使用(20)

8 毕业

每个学生所有课程都达到了毕业所需最低学分才可以毕业
两张表结构如下
 
CATEGORIES
CREDIT_CAT        VARCHAR2(1)           课程类别
RQD_CREDITS     INTEGER          毕业所需最低学分
 
CREDITSEARNED
STUDENT_NAME   VARCHAR2(10)        学生姓名
CREDIT_CAT          VARCHAR2(1)          课程类别
CREDITS                  INTEGER        获得学分
 
所含数据如下：

CATEGORIES
CREDIT_CAT    RQD_CREDITS
A    10
B    3
C    5
CREDITSEARNED
STUDENT_NAME    CREDIT_CAT    CREDITS
Joe           A    3
Joe           A    2
Joe           A    3
Joe           A    3
Joe           B    3
Joe           C    3
Joe           C    2
Joe           C    3
Bob           A    2
Bob           C    2
Bob           A    12
Bob           C    4
John          A    1
John          B    100
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          A    1
Mary          B    1
Mary          B    1
Mary          B    1
Mary          B    1
Mary          B    1
Mary          B    1
Mary          B    1
Mary          C    1
Mary          C    1
Mary          C    1
Mary          C    1
Mary          C    1
Mary          C    1
Mary          C    1
Mary          C    1
求可以毕业的学生姓名

create table CATEGORIES
(
  CREDIT_CAT  CHAR(1) not null,
  RQD_CREDITS INTEGER not null
)
;


create table CREDITSEARNED
(
  STUDENT_NAME CHAR(10) not null,
  CREDIT_CAT   CHAR(1) not null,
  CREDITS      INTEGER not null
)
;

insert into CATEGORIES (CREDIT_CAT, RQD_CREDITS)
values ('A', 10);
insert into CATEGORIES (CREDIT_CAT, RQD_CREDITS)
values ('B', 3);
insert into CATEGORIES (CREDIT_CAT, RQD_CREDITS)
values ('C', 5);

insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'A', 3);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'A', 2);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'A', 3);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'A', 3);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'B', 3);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'C', 3);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'C', 2);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Joe       ', 'C', 3);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Bob       ', 'A', 2);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Bob       ', 'C', 2);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Bob       ', 'A', 12);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Bob       ', 'C', 4);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('John      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('John      ', 'B', 100);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'A', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'B', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);
insert into CREDITSEARNED (STUDENT_NAME, CREDIT_CAT, CREDITS)
values ('Mary      ', 'C', 1);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24558043/viewspace-677560/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24558043/viewspace-677560/