Database practice full notes comprehensive exercises (1) query of teaching information and comprehensive exercise information
This instance runs on Oracle10g through:
I. Question requirements
The instructor's teaching information is stored in the SCHEDULE table:
--------------------------------
TEACHERID WEEKDAY HASCOURSE
------------------------------
1 2 1
1 3 1
2 1 1
3 2 1
1 2 1
--------------------------------
-----------------------------------------------------------------
The design query outputs the following information (the number of classes in the week indicates the number of classes in the teacher's day in the week ):
TEACHERID MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
------------------------------------------------------------
1 0 2 1 0 0
2 1 0 0 0 0
3 0 1 0 0 0
-----------------------------------------------------------------
Ii. Analysis and answer
1. Table creation statement
Create table schedule (teacherid number (1), weekday number (1), hascourse number (1); -- insert data statement insert into schedule values (1, 2, 1 ); insert into schedule values (, 1); insert into schedule values (, 2); insert into schedule values (, 1); insert into schedule values (, 1 ); -- submit statement commit;
2. query statement
Step 1: obtain the following results:
TEACHERID MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
-----------------------------------------------------------
1 0 1 0 0 0
1 0 0 1 0 0
2 1 0 0 0 0
3 0 1 0 0 0
1 0 1 0 0 0
The query statement is:
select teacherid,case when weekday=1 then 1 else 0 end MONDAY,case when weekday=2 then 1 else 0 end TUESDAY, case when weekday=3 then 1 else 0 end WEDNESDAY,case when weekday=4 then 1 else 0 end THURSDAY,case when weekday=5 then 1 else 0 end FRIDAYfrom SCHEDULE;
Step 2: group and sum the result in step 1 to get the final result:
select teacherid,sum(case when weekday=1 then 1 else 0 end) MONDAY,sum(case when weekday=2 then 1 else 0 end) TUESDAY,sum(case when weekday=3 then 1 else 0 end) WEDNESDAY,sum(case when weekday=4 then 1 else 0 end) THURSDAY,sum(case when weekday=5 then 1 else 0 end) FRIDAYfrom SCHEDULEgroup by teacherID;
If the data contains null, you can use the following statement to call the nvl function:
select teacherid,nvl ((SUM(decode(weekday, '1', 1))),0) as MONDAY,nvl ((SUM(decode(weekday, '2', 1))),0) as TUESDAY,nvl ((SUM(decode(weekday, '3', 1))),0) as WEDNESDAY,nvl ((SUM(decode(weekday, '4', 1))),0) as THURSDAY,nvl ((SUM(decode(weekday, '5', 1))),0) as FRIDAYfrom SCHEDULEgroup by teacherid;
The query result is as follows: