Thread: SQL query...please help
01-24-04, 23:20 #1Registered User
- Join Date
- Jan 2004
Unanswered: SQL query...please help
I'm trying to write a query against 2 tables, the details of which are given below.
TABLE CLASSTIMES : coursename starttime1 endtime1 starttime2 endtime2
phy101 1200 1300 3500 3600
chem250 1350 1400 6500 7000
TABLE STUDENTTIMES : studentname course starttime1 endtime1 starttime2 endtime2
x course1 1000 1100 3400 3500
x course2 1300 1400 6400 6450
x course3 1200 1250 3600 3730
y course... 1100 1150 3200 3300
y course... 0950 1030 3650 3700
Each row in table 'CLASSTIMES' give the classtimes of that particular course on day1
(read starttime1 and endtime1) and day2 (starttime2 and endtime2) of a week.
Each row in table 'STUDENTTIMES' give the timings of the courses that the student is currently
enrolled for. starttime1 & starttime2 correspond to the times of the first & second days of that
class in a week.
Now, I want to write a query that takes into account starttimes and endtimes for all the courses
for a student from table 'STUDENTTIMES', and tell whether
the student is available to enroll in a given course from table 'CLASSTIMES'. Please note the columns
'COURSENAME' from table 'CLASSTIMES' column 'COURSE' from table 'STUDENTTIMES' are not the same. So,
we can not compare these two. We can only compare the times.
I don't know PL/SQL, so I can't use stored procedures and all. Can somebody tell me how to write the query using SQL? I appreciate your help.
01-27-04, 07:56 #2Registered User
- Join Date
- Sep 2003
- The extremely Royal borough of Kensington, London
The columns 'ST1' and 'ST2' represent Day 1 and Day 2 respectively.
Create or replace view ST_RESULT AS
select st_name, name, start_time, end_time, start_time2, end_time2, st1, st2, count(*) AS COUNT
(select s.name As ST_NAME, s.start_time As ST_START_TIME, s.end_time As ST_END_TIME, s.start_time2 As
ST_START_TIME2, s.end_time2 As ST_END_TIME2, c.name, c.start_time, c.end_time, c.start_time2, c.end_time2,
(case WHEN (c.start_time NOT between s.start_time+1 AND s.end_time-1) AND
(c.end_time <= s.start_time OR c.start_time >= s.end_time) THEN 'TRUE' ELSE 'FALSE' END) As ST1,
(case WHEN (c.start_time2 NOT between s.start_time2+1 AND s.end_time2-1) AND
(c.end_time2 <= s.start_time2 OR c.start_time2 >= s.end_time2) THEN 'TRUE' ELSE 'FALSE' END) As ST2
from students s, courses c)
group by st_name, name, start_time, end_time, start_time2, end_time2, st1, st2
having st1 = 'TRUE' OR st2 = 'TRUE';
from st_result V
where (ST1='TRUE' AND COUNT = (select count(distinct start_time) from students where name = V.st_name)) OR
(ST2='TRUE' AND COUNT = (select count(distinct start_time2) from students where name = V.st_name));
Last edited by r123456; 01-27-04 at 08:37.Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.