Database practice full notes comprehensive exercises (1) query of teaching information and comprehensive exercise information

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: