Exam results table
create table exam_results (
student_id integer not null,
exam_id integer not null,
percent_correct number(5, 2)
constraint exre_pct_0_100_c
check ( percent_correct between 0 and 100 ),
constraint exam_result_pk
primary key ( student_id, exam_id )
)
Table created.
Load sample data
begin
insert into exam_results
with rws as (
select level - 1 x from dual
connect by level <= 1000
)
select mod ( x, 100 ) + 1,
floor ( x / 100 ) + 1,
round ( least ( greatest ( ( dbms_random.normal * 10 ) + 70, 0 ), 100 ), 2 )
from rws;
/* Test student & exam */
insert into exam_results values ( 0, 1, 100 );
insert into exam_results values ( 1, 0, 100 );
insert into exam_results values ( 0, 0, 100 );
insert into exam_results values ( 0, 2, null );
insert into exam_results values ( 2, 0, null );
commit;
end;
Statement processed.
select * from exam_results
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | 85 | 5 | 72.09 | 86 | 5 | 68.63 | 87 | 5 | 87.38 | 88 | 5 | 65.6 | 89 | 5 | 55.63 | 90 | 5 | 69.15 | 91 | 5 | 58.41 | 92 | 5 | 67.46 | 93 | 5 | 69.28 | 94 | 5 | 69.21 |
---|
Simple CASE expression
select exam_id,
case exam_id
when 1 then 'SQL'
when 2 then 'Java'
when 3 then 'Python'
when 4 then 'Javascript'
end exam_name,
count (*)
from exam_results
group by exam_id
order by exam_id
EXAM_ID | EXAM_NAME | COUNT(*) | 0 | - | 3 | 1 | SQL | 101 | 2 | Java | 101 | 3 | Python | 100 | 4 | Javascript | 100 | 5 | - | 100 | 6 | - | 100 | 7 | - | 100 | 8 | - | 100 | 9 | - | 100 | 10 | - | 100 |
---|
ELSE clause
select exam_id,
case exam_id
when 1 then 'SQL'
when 2 then 'Java'
when 3 then 'Python'
when 4 then 'Javascript'
else 'Other language'
end exam_name
from exam_results
group by exam_id
order by exam_id
EXAM_ID | EXAM_NAME | 0 | Other language | 1 | SQL | 2 | Java | 3 | Python | 4 | Javascript | 5 | Other language | 6 | Other language | 7 | Other language | 8 | Other language | 9 | Other language | 10 | Other language |
---|
Expressions must have same return type
select exam_id,
case exam_id
when 1 then '1'
when 2 then 2
end exam_name,
count (*)
from exam_results
group by exam_id
order by exam_id
ORA-00932: inconsistent datatypes: expected CHAR got NUMBERMore Details: https://docs.oracle.com/error-help/db/ora-00932
Searched CASE expression
select exam_id,
case
when exam_id = 1 then 'SQL'
when exam_id = 2 then 'Java'
when exam_id = 3 then 'Python'
when exam_id = 4 then 'Javascript'
else 'Other language'
end exam_name
from exam_results
group by exam_id
order by exam_id
EXAM_ID | EXAM_NAME | 0 | Other language | 1 | SQL | 2 | Java | 3 | Python | 4 | Javascript | 5 | Other language | 6 | Other language | 7 | Other language | 8 | Other language | 9 | Other language | 10 | Other language |
---|
Simple CASE limitations
select student_id, exam_id, percent_correct,
case percent_correct
when null then 'U'
when 100 then 'A'
when 99.99 then 'A'
when 99.98 then 'A'
when 99.97 then 'A'
when 99.96 then 'A'
-- etc.
end grade
from exam_results
order by percent_correct nulls first
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | 0 | 2 | - | - | 2 | 0 | - | - | 20 | 10 | 35.96 | - | 7 | 4 | 39.86 | - | 83 | 1 | 43.36 | - | 13 | 8 | 43.51 | - | 22 | 4 | 44.28 | - | 50 | 8 | 44.61 | - | 48 | 3 | 45.63 | - | 98 | 10 | 46.53 | - |
---|
Searched CASE for exam grades
select student_id, exam_id, percent_correct,
case
when percent_correct is null then 'U'
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end grade
from exam_results
order by percent_correct nulls first
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | 0 | 2 | - | U | 2 | 0 | - | U | 20 | 10 | 35.96 | F | 7 | 4 | 39.86 | F | 83 | 1 | 43.36 | F | 13 | 8 | 43.51 | F | 22 | 4 | 44.28 | F | 50 | 8 | 44.61 | F | 48 | 3 | 45.63 | F | 98 | 10 | 46.53 | F |
---|
Top-to-bottom evaluation
select student_id, exam_id, percent_correct,
case
-- top line checked first; no-one will get grades A-D!
when percent_correct >= 50 then 'E'
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
else 'F'
end grade
from exam_results
order by percent_correct desc, grade
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | 2 | 0 | - | F | 0 | 2 | - | F | 0 | 0 | 100 | E | 57 | 9 | 100 | E | 1 | 0 | 100 | E | 0 | 1 | 100 | E | 89 | 4 | 97.74 | E | 76 | 8 | 95.56 | E | 63 | 8 | 95.47 | E | 48 | 6 | 94.5 | E |
---|
Complex conditions
select student_id, exam_id, percent_correct,
case
when percent_correct >= 50 and percent_correct < 60 then 'E'
when percent_correct >= 60 and percent_correct < 70 then 'D'
when percent_correct >= 70 and percent_correct < 80 then 'C'
when percent_correct >= 80 and percent_correct < 90 then 'B'
when percent_correct >= 90 then 'A'
else 'F'
end grade
from exam_results
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | 85 | 5 | 72.09 | C | 86 | 5 | 68.63 | D | 87 | 5 | 87.38 | B | 88 | 5 | 65.6 | D | 89 | 5 | 55.63 | E | 90 | 5 | 69.15 | D | 91 | 5 | 58.41 | E | 92 | 5 | 67.46 | D | 93 | 5 | 69.28 | D | 94 | 5 | 69.21 | D |
---|
Different input values
select exam_id, student_id, percent_correct,
case
when exam_id = 0 then 'Test exam'
when student_id = 0 then 'Test student'
when percent_correct >= 90 then 'A'
-- etc.
end grade
from exam_results
where 0 in ( exam_id, student_id ) or percent_correct > 99
EXAM_ID | STUDENT_ID | PERCENT_CORRECT | GRADE | 9 | 57 | 100 | A | 1 | 0 | 100 | Test student | 0 | 1 | 100 | Test exam | 0 | 0 | 100 | Test exam | 2 | 0 | - | Test student | 0 | 2 | - | Test exam |
---|
Overlapping conditions
select exam_id, student_id, percent_correct,
case
when exam_id = 0 and student_id = 0 then 'Test exam and student'
when exam_id = 0 and student_id <> 0 then 'Test exam'
when student_id = 0 and exam_id <> 0 then 'Test student'
when percent_correct >= 90 then 'A' --etc.
end grade
from exam_results
where 0 in ( exam_id, student_id ) or percent_correct > 99
EXAM_ID | STUDENT_ID | PERCENT_CORRECT | GRADE | 9 | 57 | 100 | A | 1 | 0 | 100 | Test student | 0 | 1 | 100 | Test exam | 0 | 0 | 100 | Test exam and student | 2 | 0 | - | Test student | 0 | 2 | - | Test exam |
---|
Nesting & chaining CASE expressions
select exam_id, student_id, percent_correct,
case
when 0 not in ( exam_id, student_id ) then
case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
-- etc.
end
else
'Test ' || case
when exam_id = 0 then 'exam '
end || case
when student_id = 0 then 'student'
end
end grade
from exam_results
where 0 in ( exam_id, student_id ) or percent_correct > 99
EXAM_ID | STUDENT_ID | PERCENT_CORRECT | GRADE | 9 | 57 | 100 | A | 1 | 0 | 100 | Test student | 0 | 1 | 100 | Test exam | 0 | 0 | 100 | Test exam student | 2 | 0 | - | Test student | 0 | 2 | - | Test exam |
---|
CASE in WHERE
select student_id, exam_id, percent_correct
from exam_results
where 'A' = case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | 48 | 6 | 94.5 | 22 | 8 | 92.91 | 63 | 8 | 95.47 | 76 | 8 | 95.56 | 24 | 9 | 90.43 | 57 | 9 | 100 | 44 | 10 | 90.04 | 93 | 10 | 91.66 | 0 | 1 | 100 | 1 | 0 | 100 |
---|
CASE in SELECT & WHERE
select student_id, exam_id, percent_correct,
case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end grade
from exam_results
where case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end in ( 'A', 'B', 'C' )
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | 85 | 5 | 72.09 | C | 87 | 5 | 87.38 | B | 100 | 5 | 82.37 | B | 5 | 6 | 71.01 | C | 8 | 6 | 79.85 | C | 10 | 6 | 84.01 | B | 12 | 6 | 88.09 | B | 16 | 6 | 73.52 | C | 18 | 6 | 77.81 | C | 19 | 6 | 78.17 | C |
---|
CASE in ORDER & GROUP BY
select case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end grade, count(*)
from exam_results
group by case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end
order by case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end
GRADE | COUNT(*) | A | 18 | B | 137 | C | 339 | D | 330 | E | 151 | F | 30 |
---|
Sort & group by alias
select case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end grade, count(*)
from exam_results
group by grade
order by grade
fetch first 10 rows only
ORA-00904: "GRADE": invalid identifierMore Details: https://docs.oracle.com/error-help/db/ora-00904
CASE expressions in virtual columns
alter table exam_results
add ( grade as ( case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end )
)
Table altered.
Using virtual columns
select grade, count(*)
from exam_results
where grade in ( 'A', 'B', 'C' )
group by grade
order by grade
GRADE | COUNT(*) | A | 18 | B | 137 | C | 339 |
---|
View virtual column CASE expression
select column_name, data_default
from user_tab_cols
where table_name = 'EXAM_RESULTS'
COLUMN_NAME | DATA_DEFAULT | STUDENT_ID | - | EXAM_ID | - | PERCENT_CORRECT | - | GRADE | CASE WHEN "PERCENT_CORRECT">=90 THEN 'A' WHEN "PERCENT_CORRECT">=80 THEN 'B' WHEN "PERCENT_CORRECT">=70 THEN 'C' WHEN "PERCENT_CORRECT">=60 THEN 'D' WHEN "PERCENT_CORRECT">=50 THEN 'E' ELSE 'F' END |
---|
Remove the virtual column
alter table exam_results
drop ( grade )
Table altered.
Returning many values with CASE
select student_id, exam_id, percent_correct,
case
when percent_correct >= 90 then 'A', 'Pass'
when percent_correct >= 80 then 'B', 'Pass'
when percent_correct >= 70 then 'C', 'Pass'
when percent_correct >= 60 then 'D', 'Fail'
when percent_correct >= 50 then 'E', 'Fail'
else 'F', 'Fail'
end grade
from exam_results
fetch first 10 rows only
ORA-00905: missing keywordMore Details: https://docs.oracle.com/error-help/db/ora-00905
Returning many values: duplicating
select student_id, exam_id, percent_correct,
case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end grade,
case
when percent_correct >= 90 then 'Pass'
when percent_correct >= 80 then 'Pass'
when percent_correct >= 70 then 'Pass'
when percent_correct >= 60 then 'Fail'
when percent_correct >= 50 then 'Fail'
else 'Fail'
end outcome
from exam_results
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | OUTCOME | 85 | 5 | 72.09 | C | Pass | 86 | 5 | 68.63 | D | Fail | 87 | 5 | 87.38 | B | Pass | 88 | 5 | 65.6 | D | Fail | 89 | 5 | 55.63 | E | Fail | 90 | 5 | 69.15 | D | Fail | 91 | 5 | 58.41 | E | Fail | 92 | 5 | 67.46 | D | Fail | 93 | 5 | 69.28 | D | Fail | 94 | 5 | 69.21 | D | Fail |
---|
Returning many values: complex type
select student_id, exam_id, percent_correct,
case
when percent_correct >= 90 then json_object ( 'grade' : 'A', 'outcome' : 'Pass' )
when percent_correct >= 80 then json_object ( 'grade' : 'B', 'outcome' : 'Pass' )
when percent_correct >= 70 then json_object ( 'grade' : 'C', 'outcome' : 'Pass' )
when percent_correct >= 60 then json_object ( 'grade' : 'D', 'outcome' : 'Fail' )
when percent_correct >= 50 then json_object ( 'grade' : 'E', 'outcome' : 'Fail' )
else json_object ( 'grade' : 'F', 'outcome' : 'Fail' )
end grade
from exam_results
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | 85 | 5 | 72.09 | {"grade":"C","outcome":"Pass"} | 86 | 5 | 68.63 | {"grade":"D","outcome":"Fail"} | 87 | 5 | 87.38 | {"grade":"B","outcome":"Pass"} | 88 | 5 | 65.6 | {"grade":"D","outcome":"Fail"} | 89 | 5 | 55.63 | {"grade":"E","outcome":"Fail"} | 90 | 5 | 69.15 | {"grade":"D","outcome":"Fail"} | 91 | 5 | 58.41 | {"grade":"E","outcome":"Fail"} | 92 | 5 | 67.46 | {"grade":"D","outcome":"Fail"} | 93 | 5 | 69.28 | {"grade":"D","outcome":"Fail"} | 94 | 5 | 69.21 | {"grade":"D","outcome":"Fail"} |
---|
Returning many values: JOIN
with exam_grades as (
select 'A' grade, 'Pass' outcome from dual union all
select 'B' grade, 'Pass' outcome from dual union all
select 'C' grade, 'Pass' outcome from dual union all
select 'D' grade, 'Fail' outcome from dual union all
select 'E' grade, 'Fail' outcome from dual union all
select 'F' grade, 'Fail' outcome from dual
)
select student_id, exam_id, percent_correct,
grade, outcome
from exam_results
join exam_grades
on grade = case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | OUTCOME | 48 | 6 | 94.5 | A | Pass | 22 | 8 | 92.91 | A | Pass | 63 | 8 | 95.47 | A | Pass | 76 | 8 | 95.56 | A | Pass | 24 | 9 | 90.43 | A | Pass | 57 | 9 | 100 | A | Pass | 44 | 10 | 90.04 | A | Pass | 93 | 10 | 91.66 | A | Pass | 0 | 1 | 100 | A | Pass | 1 | 0 | 100 | A | Pass |
---|
Table of exam grades
create table exam_grades (
grade char(1) not null
primary key,
lower_bound number
unique,
upper_bound number
references exam_grades ( lower_bound ),
outcome varchar2(10) not null
)
Table created.
Load exam grade details
begin
insert into exam_grades values ( 'A', 90, null, 'Pass' );
insert into exam_grades values ( 'B', 80, 90, 'Pass' );
insert into exam_grades values ( 'C', 70, 80, 'Pass' );
insert into exam_grades values ( 'D', 60, 70, 'Fail' );
insert into exam_grades values ( 'E', 50, 60, 'Fail' );
insert into exam_grades values ( 'F', null, 50, 'Fail' );
commit;
end;
Statement processed.
CASE in JOIN
select student_id, exam_id, percent_correct,
lower_bound, upper_bound, grade, outcome
from exam_results
join exam_grades
on grade = case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | LOWER_BOUND | UPPER_BOUND | GRADE | OUTCOME | 85 | 5 | 72.09 | 70 | 80 | C | Pass | 86 | 5 | 68.63 | 60 | 70 | D | Fail | 87 | 5 | 87.38 | 80 | 90 | B | Pass | 88 | 5 | 65.6 | 60 | 70 | D | Fail | 89 | 5 | 55.63 | 50 | 60 | E | Fail | 90 | 5 | 69.15 | 60 | 70 | D | Fail | 91 | 5 | 58.41 | 50 | 60 | E | Fail | 92 | 5 | 67.46 | 60 | 70 | D | Fail | 93 | 5 | 69.28 | 60 | 70 | D | Fail | 94 | 5 | 69.21 | 60 | 70 | D | Fail |
---|
select student_id, exam_id, percent_correct, grade, outcome
from exam_results
join exam_grades
on ( lower_bound <= percent_correct or lower_bound is null )
and ( upper_bound > nvl ( percent_correct, 0 ) or upper_bound is null )
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | GRADE | OUTCOME | 48 | 6 | 94.5 | A | Pass | 22 | 8 | 92.91 | A | Pass | 63 | 8 | 95.47 | A | Pass | 76 | 8 | 95.56 | A | Pass | 24 | 9 | 90.43 | A | Pass | 57 | 9 | 100 | A | Pass | 44 | 10 | 90.04 | A | Pass | 93 | 10 | 91.66 | A | Pass | 0 | 1 | 100 | A | Pass | 1 | 0 | 100 | A | Pass |
---|
Reusing CASE expression joins
create or replace view exam_result_outcomes as
select student_id, exam_id, percent_correct,
lower_bound, upper_bound, grade, outcome
from exam_results
join exam_grades
on grade = case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end
View created.
select * from exam_result_outcomes
fetch first 10 rows only
STUDENT_ID | EXAM_ID | PERCENT_CORRECT | LOWER_BOUND | UPPER_BOUND | GRADE | OUTCOME | 85 | 5 | 72.09 | 70 | 80 | C | Pass | 86 | 5 | 68.63 | 60 | 70 | D | Fail | 87 | 5 | 87.38 | 80 | 90 | B | Pass | 88 | 5 | 65.6 | 60 | 70 | D | Fail | 89 | 5 | 55.63 | 50 | 60 | E | Fail | 90 | 5 | 69.15 | 60 | 70 | D | Fail | 91 | 5 | 58.41 | 50 | 60 | E | Fail | 92 | 5 | 67.46 | 60 | 70 | D | Fail | 93 | 5 | 69.28 | 60 | 70 | D | Fail | 94 | 5 | 69.21 | 60 | 70 | D | Fail |
---|
Dynamic WHERE with CASE
declare
res_type char(1);
res_value varchar2(10);
begin
res_type := 'G';
res_value := 'A';
for rws in (
select grade, outcome, count(*) c
from exam_result_outcomes
where case res_type
when 'G' then grade
when 'O' then outcome
end = res_value
group by grade, outcome )
loop
dbms_output.put_line ( rws.grade || ' ' || rws.outcome || ' ' || rws.c );
end loop;
dbms_output.put_line ( '*************' );
res_type := 'O';
res_value := 'Fail';
for rws in (
select grade, outcome, count(*) c
from exam_result_outcomes
where case res_type
when 'G' then grade
when 'O' then outcome
end = res_value
group by grade, outcome )
loop
dbms_output.put_line ( rws.grade || ' ' || rws.outcome || ' ' || rws.c );
end loop;
end;
Statement processed.
A Pass 18
*************
F Fail 30
D Fail 330
E Fail 151
Dynamic WHERE with NVL
declare
grade_value char(1);
outcome_value varchar2(10);
begin
grade_value := 'A';
outcome_value := null;
for rws in (
select grade, outcome, count(*) c
from exam_result_outcomes
where grade = nvl ( grade_value, grade )
and outcome = nvl ( outcome_value, outcome )
group by grade, outcome )
loop
dbms_output.put_line ( rws.grade || ' ' || rws.outcome || ' ' || rws.c );
end loop;
dbms_output.put_line ( '*************' );
grade_value := null;
outcome_value := 'Fail';
for rws in (
select grade, outcome, count(*) c
from exam_result_outcomes
where grade = nvl ( grade_value, grade )
and outcome = nvl ( outcome_value, outcome )
group by grade, outcome )
loop
dbms_output.put_line ( rws.grade || ' ' || rws.outcome || ' ' || rws.c );
end loop;
end;
Statement processed.
A Pass 18
*************
F Fail 30
D Fail 330
E Fail 151
CASE in COUNT
select exam_id,
count ( case when percent_correct >= 70 then 1 end ) pass,
count ( case when nvl ( percent_correct, 0 ) < 70 then 1 end ) fail
from exam_results
group by exam_id
EXAM_ID | PASS | FAIL | 6 | 46 | 54 | 7 | 47 | 53 | 1 | 62 | 39 | 8 | 43 | 57 | 2 | 42 | 59 | 5 | 52 | 48 | 10 | 46 | 54 | 4 | 49 | 51 | 9 | 53 | 47 | 3 | 52 | 48 | 0 | 2 | 1 |
---|
CASE in aggregates
select exam_id,
avg ( case when percent_correct >= 70 then percent_correct end ) pass_mean,
avg ( case when nvl ( percent_correct, 0 ) < 70 then percent_correct end ) fail_mean,
max ( case when percent_correct >= 70 then percent_correct end ) max_pass_pct,
min ( case when percent_correct >= 70 then percent_correct end ) min_pass_pct
from exam_results
group by exam_id
EXAM_ID | PASS_MEAN | FAIL_MEAN | MAX_PASS_PCT | MIN_PASS_PCT | 6 | 78.09956521739130434782608695652173913043 | 61.925 | 94.5 | 70.35 | 7 | 77.5 | 62.59245283018867924528301886792452830189 | 88.87 | 70.07 | 1 | 77.32741935483870967741935483870967741935 | 62.33128205128205128205128205128205128205 | 100 | 70.14 | 8 | 79.0486046511627906976744186046511627907 | 60.99526315789473684210526315789473684211 | 95.56 | 70 | 2 | 78.56047619047619047619047619047619047619 | 62.64672413793103448275862068965517241379 | 93.59 | 70.36 | 5 | 77.33442307692307692307692307692307692308 | 61.81145833333333333333333333333333333333 | 89.27 | 70.85 | 10 | 77.97478260869565217391304347826086956522 | 61.04518518518518518518518518518518518519 | 91.66 | 70.09 | 4 | 77.9226530612244897959183673469387755102 | 62.18078431372549019607843137254901960784 | 97.74 | 70.35 | 9 | 77.0864150943396226415094339622641509434 | 61.09765957446808510638297872340425531915 | 100 | 70.11 | 3 | 76.51576923076923076923076923076923076923 | 60.89833333333333333333333333333333333333 | 91.59 | 70.16 | 0 | 100 | - | 100 | 100 |
---|
Rows-to-columns using CASE
select exam_id,
count ( case when percent_correct >= 90 then 1 end ) a,
count ( case when percent_correct >= 80 and percent_correct < 90 then 1 end ) b,
count ( case when percent_correct >= 70 and percent_correct < 80 then 1 end ) c,
count ( case when percent_correct >= 60 and percent_correct < 70 then 1 end ) d,
count ( case when percent_correct >= 50 and percent_correct < 60 then 1 end ) e,
count ( case when percent_correct < 50 then 1 end ) f
from exam_results
group by exam_id
EXAM_ID | A | B | C | D | E | F | 6 | 1 | 13 | 32 | 34 | 19 | 1 | 7 | 0 | 12 | 35 | 38 | 11 | 4 | 1 | 2 | 15 | 45 | 26 | 12 | 1 | 8 | 3 | 15 | 25 | 39 | 13 | 5 | 2 | 2 | 12 | 28 | 41 | 16 | 1 | 5 | 0 | 16 | 36 | 30 | 14 | 4 | 10 | 2 | 15 | 29 | 34 | 17 | 3 | 4 | 3 | 16 | 30 | 33 | 15 | 3 | 9 | 2 | 12 | 39 | 26 | 19 | 2 | 3 | 1 | 11 | 40 | 29 | 15 | 4 | 0 | 2 | 0 | 0 | 0 | 0 | 0 |
---|
PIVOT a CASE expression
with rws as (
select exam_id, case
when percent_correct >= 90 then 'A'
when percent_correct >= 80 then 'B'
when percent_correct >= 70 then 'C'
when percent_correct >= 60 then 'D'
when percent_correct >= 50 then 'E'
else 'F'
end grade
from exam_results
)
select * from rws
pivot (
count(*) for grade in ( 'A', 'B', 'C', 'D', 'E' )
)
EXAM_ID | 'A' | 'B' | 'C' | 'D' | 'E' | 6 | 1 | 13 | 32 | 34 | 19 | 7 | 0 | 12 | 35 | 38 | 11 | 1 | 2 | 15 | 45 | 26 | 12 | 8 | 3 | 15 | 25 | 39 | 13 | 2 | 2 | 12 | 28 | 41 | 16 | 5 | 0 | 16 | 36 | 30 | 14 | 10 | 2 | 15 | 29 | 34 | 17 | 4 | 3 | 16 | 30 | 33 | 15 | 9 | 2 | 12 | 39 | 26 | 19 | 3 | 1 | 11 | 40 | 29 | 15 | 0 | 2 | 0 | 0 | 0 | 0 |
---|
PIVOT a CASE with many aggregates
with rws as (
select exam_id, percent_correct,
case
when percent_correct >= 70 then 'Pass'
else 'Fail'
end outcome
from exam_results
)
select * from rws
pivot (
count(*) num, avg ( percent_correct ) mean
for outcome in ( 'Pass' pass )
)
EXAM_ID | PASS_NUM | PASS_MEAN | 6 | 46 | 78.09956521739130434782608695652173913043 | 7 | 47 | 77.5 | 1 | 62 | 77.32741935483870967741935483870967741935 | 8 | 43 | 79.0486046511627906976744186046511627907 | 2 | 42 | 78.56047619047619047619047619047619047619 | 5 | 52 | 77.33442307692307692307692307692307692308 | 10 | 46 | 77.97478260869565217391304347826086956522 | 4 | 49 | 77.9226530612244897959183673469387755102 | 9 | 53 | 77.0864150943396226415094339622641509434 | 3 | 52 | 76.51576923076923076923076923076923076923 | 0 | 2 | 100 |
---|
CASE in window/analytic functions
select exam_id, student_id, percent_correct,
round (
avg ( case when percent_correct >= 70 then percent_correct end )
over ( partition by exam_id ), 2
) exam_pass_mean,
percent_correct - round (
avg ( case when percent_correct >= 70 then percent_correct end )
over ( partition by exam_id ), 2
) gap_to_avg_exam_pass
from exam_results
order by student_id
fetch first 10 rows only
EXAM_ID | STUDENT_ID | PERCENT_CORRECT | EXAM_PASS_MEAN | GAP_TO_AVG_EXAM_PASS | 0 | 0 | 100 | 100 | 0 | 2 | 0 | - | 78.56 | - | 1 | 0 | 100 | 77.33 | 22.67 | 0 | 1 | 100 | 100 | 0 | 6 | 1 | 68.27 | 78.1 | -9.83 | 5 | 1 | 88.48 | 77.33 | 11.15 | 4 | 1 | 83.94 | 77.92 | 6.02 | 3 | 1 | 69.85 | 76.52 | -6.67 | 2 | 1 | 58.69 | 78.56 | -19.87 | 1 | 1 | 87.94 | 77.33 | 10.61 |
---|
Implicit conversion in PL/SQL
declare
selector pls_integer := 1;
begin
dbms_output.put_line ( case selector
when 1 then '1'
when 2 then 2
end );
end;
Statement processed.
1
CASE statements in PL/SQL
declare
grade char(1);
outcome varchar2(10);
begin
for rws in ( select * from exam_results fetch first 10 rows only ) loop
case
when rws.percent_correct >= 90 then
grade := 'A'; outcome := 'Pass';
when rws.percent_correct >= 80 then
grade := 'B'; outcome := 'Pass';
when rws.percent_correct >= 70 then
grade := 'C'; outcome := 'Pass';
when rws.percent_correct >= 60 then
grade := 'D'; outcome := 'Fail';
when rws.percent_correct >= 50 then
grade := 'E'; outcome := 'Fail';
else
grade := 'F'; outcome := 'Fail';
end case;
dbms_output.put_line ( rws.percent_correct || ' ' || grade || ' ' || outcome );
end loop;
end;
Statement processed.
72.09 C Pass
68.63 D Fail
87.38 B Pass
65.6 D Fail
55.63 E Fail
69.15 D Fail
58.41 E Fail
67.46 D Fail
69.28 D Fail
69.21 D Fail
CASE_NOT_FOUND in PL/SQL
declare
grade char(1);
outcome varchar2(10);
begin
for rws in ( select * from exam_results ) loop
case
when rws.percent_correct >= 90 then
grade := 'A'; outcome := 'Pass';
when rws.percent_correct >= 80 then
grade := 'B'; outcome := 'Pass';
when rws.percent_correct >= 70 then
grade := 'C'; outcome := 'Pass';
when rws.percent_correct >= 60 then
grade := 'D'; outcome := 'Fail';
when rws.percent_correct >= 50 then
grade := 'E'; outcome := 'Fail';
end case;
dbms_output.put_line ( rws.percent_correct || ' ' || grade || ' ' || outcome );
end loop;
end;
ORA-06592: CASE not found while executing CASE statement ORA-06512: at line 17 ORA-06512: at line 17 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-06592
declare
grade char(1);
begin
for rws in ( select * from exam_results fetch first 10 rows only ) loop
grade := case
when rws.percent_correct >= 90 then 'A'
end;
dbms_output.put_line ( rws.percent_correct || ' ' || grade );
end loop;
end;
Statement processed.
72.09 C Pass
68.63 D Fail
87.38 B Pass
65.6 D Fail
55.63 E Fail
69.15 D Fail
58.41 E Fail
67.46 D Fail
69.28 D Fail
69.21 D Fail
72.09
68.63
87.38
65.6
55.63
69.15
58.41
67.46
69.28
69.21
PL/SQL non NULL return
declare
grade char(1);
begin
for rws in ( select * from exam_results fetch first 10 rows only ) loop
grade := case
when rws.percent_correct >= 90 then null
else null
end;
dbms_output.put_line ( rws.percent_correct || ' ' || grade );
end loop;
end;
ORA-06550: line 5, column 14: PLS-00617: at least one result in the CASE expression must not be NULLMore Details: https://docs.oracle.com/error-help/db/ora-06550
Extended CASE in 23c
declare
grade char(1);
begin
for rws in ( select * from exam_results fetch first 10 rows only ) loop
grade := case rws.percent_correct
when >= 90 then 'A'
when >= 80 then 'B'
when >= 70 then 'C'
when >= 60 then 'D'
when >= 50 then 'E'
else 'F'
end;
dbms_output.put_line ( rws.percent_correct || ' ' || grade );
end loop;
end;
ORA-06550: line 6, column 12: PLS-00103: Encountered the symbol "=" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specifMore Details: https://docs.oracle.com/error-help/db/ora-06550
Extended CASE in 23c
declare
grade varchar2(10);
begin
for rws in ( select * from exam_results fetch first 10 rows only ) loop
grade := case rws.percent_correct
when 100 then 'A*' -- implicit = 100
when in ( 50, 60, 70, 80, 90 ) then 'boundary'
when > 90 then 'A'
when > 80 then 'B'
when > 70 then 'C'
when > 60 then 'D'
when > 50 then 'E'
when is null, 0 then 'U' -- implicit = 0
else 'F'
end;
dbms_output.put_line ( rws.percent_correct || ' ' || grade );
end loop;
end;
ORA-06550: line 7, column 12: PLS-00103: Encountered the symbol "IN" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set speci ORA-06512: at line ORA-06512: at lineMore Details: https://docs.oracle.com/error-help/db/ora-06550