Weekly (Course) Schedule for Students

Transact-SQL
1

I have a table that includes student's registered courses with day (1-6), start and end hours (9-21).

Basic query is like that:SELECT courseName, courseDay, courseStartHour, coursEndHour
FROM courses WHERE studentId = 1

I want to show weekly schedule for a student like that:

34

This is hard for me already and there is a harder part. Two courses can conflict.

So Monday 10-11 can include CourseName3. Are these possible with just SQL?

Sample Data:

CREATE TABLE StudentCourses ( courseCode varchar2(8) NOT NULL, courseName varchar2(64) NOT NULL, day number(10), startHour number(10), endHour number(10));

INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 332','Positioning Strategy in Advertising','2','9','12' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'COMM 324','Persuasion and Perception','2','14', '17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 312','Corporate Communications Practicum','3','14','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','13','15' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','14','16' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','15','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','16','18' );
2

is this microsoft sql server? if so maybe this will work?

SELECT _hour, [MON], [TUE],[WED] , [THU],[FRI],[SAT],[SUN]
  from   (  
		 select weekdayname, 
                startHour  _hour, 
				CourseName    
		   from dbo.StudentCourses j    
		   join WeekDays jt on j.weekdayId = jt.weekdayId  
		 ) x  
 pivot   (  min(CourseName)   
            for weekdayname in ([FRI],[MON],[SAT],[SUN],[THU],[TUE],[WED])  
) p 
 order by _hour
3

It's Oracle but we use lots of Sql Server also. Usually does not matter. Sorry. I didn't specify.

4 
with cte_hours
  as (select h
        from (values(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)) as cte_hours(h)
     )
select a.h
      ,nullif(concat(min(case when b.[day]=1 then b.coursename else null end)
                    ,case
                        when min(case when b.[day]=1 then b.coursename else null end)
                            =max(case when b.[day]=1 then b.coursename else null end)
                        then ''
                        else concat('/',max(case when b.[day]=1 then b.coursename else null end))
                     end
                    )
             ,'/'
             )
       as mon
      ,nullif(concat(min(case when b.[day]=2 then b.coursename else null end)
                    ,case
                        when min(case when b.[day]=2 then b.coursename else null end)
                            =max(case when b.[day]=2 then b.coursename else null end)
                        then ''
                        else concat('/',max(case when b.[day]=2 then b.coursename else null end))
                     end
                    )
             ,'/'
             )
       as tue
      ,nullif(concat(min(case when b.[day]=3 then b.coursename else null end)
                    ,case
                        when min(case when b.[day]=3 then b.coursename else null end)
                            =max(case when b.[day]=3 then b.coursename else null end)
                        then ''
                        else concat('/',max(case when b.[day]=3 then b.coursename else null end))
                     end
                    )
             ,'/'
             )
       as wed
      ,nullif(concat(min(case when b.[day]=4 then b.coursename else null end)
                    ,case
                        when min(case when b.[day]=4 then b.coursename else null end)
                            =max(case when b.[day]=4 then b.coursename else null end)
                        then ''
                        else concat('/',max(case when b.[day]=4 then b.coursename else null end))
                     end
                    )
             ,'/'
             )
       as thu
      ,nullif(concat(min(case when b.[day]=5 then b.coursename else null end)
                    ,case
                        when min(case when b.[day]=5 then b.coursename else null end)
                            =max(case when b.[day]=5 then b.coursename else null end)
                        then ''
                        else concat('/',max(case when b.[day]=5 then b.coursename else null end))
                     end
                    )
             ,'/'
             )
       as fri
      ,nullif(concat(min(case when b.[day]=6 then b.coursename else null end)
                    ,case
                        when min(case when b.[day]=6 then b.coursename else null end)
                            =max(case when b.[day]=6 then b.coursename else null end)
                        then ''
                        else concat('/',max(case when b.[day]=6 then b.coursename else null end))
                     end
                    )
             ,'/'
             )
       as sat
  from cte_hours as a
       left outer join studentcourses as b
                    on b.starthour<=a.h
                   and b.endhour>a.h
 group by a.h
;