Database actual combat Full notes exercises (1) Teaching information query

This instance runs through the oracle10g:

First, the topic 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
--------------------------------
-----------------------------------------------------------------
Design the query, output the following information (the number of days in the week: corresponding to the class of Teachers 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

-----------------------------------------------------------------

Second, Analytical solutions

1. Build a Table statement

2. Query statements
The first step is to get the following result:
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 statements are:

Select  teacherid,case when  Weekday=1 then 1 else 0 end monday,case If  weekday=2 then 1 else 0 end Tuesday, c ASE when  weekday=3 then 1 else 0 end wednesday,case when  weekday=4 and 1 else 0 end thursday,case when  weekda Y=5 then 1 else 0 end Fridayfrom SCHEDULE;

In the second step, the results of the first step are grouped and summed to get the final result:

Select  teacherid,sum (case if  Weekday=1 then 1 else 0 end) Monday,sum (case if  weekday=2 then 1 else 0 end) Tuesday,sum (case if  weekday=3 then 1 else 0 end) Wednesday,sum (case if  weekday=4 then 1 else 0 end) Thursday,s Um (case if  weekday=5 then 1 else 0 end) Fridayfrom schedulegroup by Teacherid;

If the data contains null, you can call the NVL function with the following statement, as follows:

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;

Query results such as:

Database actual combat Full notes exercises (1) Teaching information query