******
14.07.09: Number of driving lesson in the morning: 1
14.07.09: Number of driving lesson in afternoon: 1
******
15.07.09: Number of driving lesson in the morning: 2
15.07.09: Number of driving lesson in afternoon: -no driving-
******

CREATE TABLE driving
(
  dno     CHAR(8),
  dep_date    DATE,
  typ     CHAR(10),
  dep_time  NUMBER(4,2)
);

INSERT INTO driving
     VALUES ('DR-56', '14.07.09', 'TF', '9,15');

INSERT INTO driving
     VALUES ('DR-13','14.07.09', 'TM', '15,15');

INSERT INTO driving
     VALUES ('DR-13','15.07.09', 'TF', '10,15');

INSERT INTO driving
     VALUES ('DR-19','15.07.09', 'GF', '10,45');

INSERT INTO driving
     VALUES ('DR-56', '18.07.09', 'BCA', '10,45');


select * from driving;
			
DNO	DEP_DATE	TYP	DEP_TIME
DR-56 	14.07.09	TF 	9,15
DR-13 	14.07.09	TM 	15,15
DR-13 	15.07.09	TF 	10,15
DR-19 	15.07.09	GF 	10,45
DR-56 	18.07.09	BCA 	10,45

with t1 as (
            select  distinct dep_date dep_date
              from  driving
           ),
     t2 as (
            select  dep_date,
                    case lvl
                      when 1 then 9
                      else 15
                    end dep_time
              from  t1,
                    (
                     select  level lvl
                       from  dual
                       connect by level <= 2
                    )
           )
select  to_char(t2.dep_date,'dd.mm.rr') || ' Number of driving lesson in the ' ||
        case t2.dep_time
          when 9 then 'morning: '
          else 'afternoon: '
        end ||
        case count(d.dep_time)
          when 0 then '-no driving-'
          else to_char(count(d.dep_time))
        end lesson_count
  from      t2
        left join
            driving d
          on (
                  d.dep_date = t2.dep_date
              and
                  d.dep_time between t2.dep_time and t2.dep_time + 3
             )
  group by t2.dep_date,
           t2.dep_time
  order by t2.dep_date,
           t2.dep_time
/

LESSON_COUNT
-------------------------------------------------------------------
14.07.09 Number of driving lesson in the morning: 1
14.07.09 Number of driving lesson in the afternoon: 1
15.07.09 Number of driving lesson in the morning: 2
15.07.09 Number of driving lesson in the afternoon: -no driving-
18.07.09 Number of driving lesson in the morning: 1
18.07.09 Number of driving lesson in the afternoon: -no driving-

6 rows selected.

SQL>