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

Source: Internet
Author: User

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:





Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.