Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: SQL query help

  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: SQL query help

    Dear Members:
    I'm trying to query against two tables:
    1) tatimes (STUDENTID is the primary key)

    SQL> desc tatimes;
    Name Null? Type
    ----------------------------------------- -------- -------------
    STUDENTID NOT NULL VARCHAR2(11)
    FIRSTNAME NOT NULL VARCHAR2(10)
    LASTNAME NOT NULL VARCHAR2(10)
    COURSENAME NOT NULL VARCHAR2(12)
    SECTIONNUMBER VARCHAR2(7)
    COURSECODE VARCHAR2(8)
    CLASSDAY1 VARCHAR2(1)
    CLASSDAY2 VARCHAR2(1)
    CLASSDAY3 VARCHAR2(1)
    TRIM_CT VARCHAR2(5)
    LABDAY1 VARCHAR2(1)
    LABDAY2 VARCHAR2(1)
    TRIM_LT VARCHAR2(5)
    CT_BEG1 VARCHAR2(7)
    CT_END1 VARCHAR2(7)
    CT_BEG2 VARCHAR2(7)
    CT_END2 VARCHAR2(7)
    CT_BEG3 VARCHAR2(7)
    CT_END3 VARCHAR2(7)
    LT_BEG1 VARCHAR2(7)
    LT_END1 VARCHAR2(7)
    LT_BEG2 VARCHAR2(7)
    LT_END2 VARCHAR2(7)

    2) trimmedtimes (COURSECODE is the primary key)

    SQL> desc trimmedtimes
    Name Null? Type
    ----------------------------------------- -------- ------------
    COURSENAME NOT NULL VARCHAR2(12)
    SECTIONNUMBER VARCHAR2(7)
    COURSECODE NOT NULL VARCHAR2(8)
    CLASSDAY1 VARCHAR2(1)
    CLASSDAY2 VARCHAR2(1)
    CLASSDAY3 VARCHAR2(1)
    LABDAY1 VARCHAR2(1)
    LABDAY2 VARCHAR2(1)
    TRIM_CT VARCHAR2(5)
    TRIM_LT VARCHAR2(5)
    CT_BEG1 VARCHAR2(7)
    CT_END1 VARCHAR2(7)
    CT_BEG2 VARCHAR2(7)
    CT_END2 VARCHAR2(7)
    CT_BEG3 VARCHAR2(7)
    CT_END3 VARCHAR2(7)
    LT_BEG1 VARCHAR2(7)
    LT_END1 VARCHAR2(7)
    LT_BEG2 VARCHAR2(7)
    LT_END2 VARCHAR2(7)

    Table 1 gives information about students applied for the position of teaching assistants for the available courses from table 2. Table 2 gives the meeting times for courses. The terms CT and LT indicate the classtime and labtime. CT_BEG[n] gives the beginning classtime on nth day of a week for the correponding course. Also, table 1 has the scheduled times for the courses the students are currently enrolled for.

    I want to find a list of all students from table 1, whose own class times do not clash with the meeting times of courses from table 2.
    The CT_BEG1, CT_END1 ......are the timings that we want to compare from the two tables. Classday1, Classday2 give the day of the week the class has a scheduled meeting. We donot need to use this in query.

    Could somebody please help me?
    Thanks,
    Naveen.

  2. #2
    Join Date
    Jan 2004
    Posts
    9

    Can somebody please help me???

    Can somebody please help me???

    Thanks,
    Naveen.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Which school, class & instructor gave you this homework assignment?
    In addition to this assignment, I suggest that you study 3rd Normal Form
    and use it to improve the design of the tables you described.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by anacedent
    Which school, class & instructor gave you this homework assignment?
    In addition to this assignment, I suggest that you study 3rd Normal Form
    and use it to improve the design of the tables you described.
    just thinking about those two tables gives me a headache!
    I suggest redesign as noted above.
    This way all these queries you want to write will be a lot easier!

    my head hurts ...
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: SQL query help

    Originally posted by knaveen
    Dear Members:
    I'm trying to query against two tables:
    1) tatimes (STUDENTID is the primary key)

    SQL> desc tatimes;
    Name Null? Type
    ----------------------------------------- -------- -------------
    STUDENTID NOT NULL VARCHAR2(11)
    FIRSTNAME NOT NULL VARCHAR2(10)
    LASTNAME NOT NULL VARCHAR2(10)
    COURSENAME NOT NULL VARCHAR2(12)
    SECTIONNUMBER VARCHAR2(7)
    COURSECODE VARCHAR2(8)
    CLASSDAY1 VARCHAR2(1)
    CLASSDAY2 VARCHAR2(1)
    CLASSDAY3 VARCHAR2(1)
    TRIM_CT VARCHAR2(5)
    LABDAY1 VARCHAR2(1)
    LABDAY2 VARCHAR2(1)
    TRIM_LT VARCHAR2(5)
    CT_BEG1 VARCHAR2(7)
    CT_END1 VARCHAR2(7)
    CT_BEG2 VARCHAR2(7)
    CT_END2 VARCHAR2(7)
    CT_BEG3 VARCHAR2(7)
    CT_END3 VARCHAR2(7)
    LT_BEG1 VARCHAR2(7)
    LT_END1 VARCHAR2(7)
    LT_BEG2 VARCHAR2(7)
    LT_END2 VARCHAR2(7)

    2) trimmedtimes (COURSECODE is the primary key)

    SQL> desc trimmedtimes
    Name Null? Type
    ----------------------------------------- -------- ------------
    COURSENAME NOT NULL VARCHAR2(12)
    SECTIONNUMBER VARCHAR2(7)
    COURSECODE NOT NULL VARCHAR2(8)
    CLASSDAY1 VARCHAR2(1)
    CLASSDAY2 VARCHAR2(1)
    CLASSDAY3 VARCHAR2(1)
    LABDAY1 VARCHAR2(1)
    LABDAY2 VARCHAR2(1)
    TRIM_CT VARCHAR2(5)
    TRIM_LT VARCHAR2(5)
    CT_BEG1 VARCHAR2(7)
    CT_END1 VARCHAR2(7)
    CT_BEG2 VARCHAR2(7)
    CT_END2 VARCHAR2(7)
    CT_BEG3 VARCHAR2(7)
    CT_END3 VARCHAR2(7)
    LT_BEG1 VARCHAR2(7)
    LT_END1 VARCHAR2(7)
    LT_BEG2 VARCHAR2(7)
    LT_END2 VARCHAR2(7)

    Table 1 gives information about students applied for the position of teaching assistants for the available courses from table 2. Table 2 gives the meeting times for courses. The terms CT and LT indicate the classtime and labtime. CT_BEG[n] gives the beginning classtime on nth day of a week for the correponding course. Also, table 1 has the scheduled times for the courses the students are currently enrolled for.

    I want to find a list of all students from table 1, whose own class times do not clash with the meeting times of courses from table 2.
    The CT_BEG1, CT_END1 ......are the timings that we want to compare from the two tables. Classday1, Classday2 give the day of the week the class has a scheduled meeting. We donot need to use this in query.

    Could somebody please help me?
    Thanks,
    Naveen.
    The table has to normalised first, Mr. Naveen
    Thanks and Regards,

    Praveen Pulikunnu

  6. #6
    Join Date
    Jan 2004
    Posts
    9
    Thanks to all those who replied. I would change the design of the tables.

    Naveen.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Basically what we are saying is that you put data in row of one table that should/could have 2-4 tables and looks like about 2-3 rows each.

    With some restructuring it will be very easy and efficient to query data.
    Especially when you start talking about a large amount of data.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jan 2004
    Posts
    9
    Here are the scaled down/normalized versions of the tables.

    SQL> desc n_tatimes;
    Name Null? Type
    ----------------------------------------- -------- --------------------
    STUDENTID NOT NULL VARCHAR2(11)
    COURSECODE VARCHAR2(8)
    CT_BEG1 VARCHAR2(7)
    CT_END1 VARCHAR2(7)
    CT_BEG2 VARCHAR2(7)
    CT_END2 VARCHAR2(7)

    SQL> select * from n_tatimes;

    STUDENTID COURSECO CT_BEG1 CT_END1 CT_BEG2 CT_END2
    ----------- -------- ------- ------- ------- -------
    111223333 517043 3800 3915 8600 8715
    111223333 522293 3500 3615 8300 8415
    111223333 485043 0930 1020 3330 3420
    111223333 485123 1400 1450 6200 6250
    111224444 516913 3800 3915 8600 8715
    111224444 390773
    999001111 513343 1010 1100 5810 5900
    999001111 514363 0930 1045 5730 5845
    999001111 291153
    666008888 389083
    666008888 514363 0930 1045 5730 5845

    The following is the course table

    SQL> desc n_trimmedtimes;
    Name Null? Type
    ----------------------------------------- -------- -------------------
    COURSECODE NOT NULL VARCHAR2(8)
    CT_BEG1 VARCHAR2(7)
    CT_END1 VARCHAR2(7)
    CT_BEG2 VARCHAR2(7)
    CT_END2 VARCHAR2(7)

    The query I want to write should give me the list of people table n_tatimes whose own class timings don't interfere with the timings for the courses from table n_trimmedtimes. The table N_TATIMES gives us the timings for all the courses that the students currently enrolled in. We can not query using the coursecode from the second table against that in the first table as there may be coursecodes in table 1 that don't have an entry in table 2. We can only compare the ct_beg1, ct_end1 etc from both tables. They are times in minutes on a given day of the week, starting from sunday 00:00.
    I hope I have provided you with enough details and far less complex tables this time. Can somebody help me. Thanks so much.
    Naveen.

  9. #9
    Join Date
    Jan 2004
    Posts
    9
    Could somebody take a look at this problem please??
    Thanks,
    Naveen.

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by knaveen
    Could somebody take a look at this problem please??
    Thanks,
    Naveen.
    How could a single course have 2 different starting and ending times?
    The course table should have unique classes -

    I would envision it to look something like this

    course_id
    course_name
    course_dept
    instructor
    location (maybe a code from a location table listing all classrooms)
    day (maybe a code like MWF or TuTH - possibly a separate times table)
    course_begin
    course_end

    This way a specific course would have a starting and ending time.

  11. #11
    Join Date
    Jan 2004
    Posts
    9
    Hi,
    thanks for the reply. The second set of starting and end times correspond to the second classday in the week. Could be tuesday, wednesday, thursday ....and so on. It usually would have an entry for the day of the week, but for better readability, the second set of beginning and end times have been calculated from the day. Hope this is clear.
    Thanks,
    Naveen.

  12. #12
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by knaveen
    Hi,
    thanks for the reply. The second set of starting and end times correspond to the second classday in the week. Could be tuesday, wednesday, thursday ....and so on. It usually would have an entry for the day of the week, but for better readability, the second set of beginning and end times have been calculated from the day. Hope this is clear.
    Thanks,
    Naveen.
    why dont u compare CT_BEG1, CT_END1, and CT_BEG2, CT_END2 from two tables based on the COURSECODE....

  13. #13
    Join Date
    Jan 2004
    Posts
    9
    I can't compare the coursecodes from the two tables as some coursecodes from table 1 don't have a corresponding entry in table 2.
    Thanks,
    naveen.

  14. #14
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by knaveen
    I can't compare the coursecodes from the two tables as some coursecodes from table 1 don't have a corresponding entry in table 2.
    Thanks,
    naveen.
    thats fine...user an outer join

  15. #15
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by knaveen
    I can't compare the coursecodes from the two tables as some coursecodes from table 1 don't have a corresponding entry in table 2.
    Thanks,
    naveen.
    You have a master table of all courses (course table)

    You have a table of students, and the courses they are taking. Why can you not compare these using course id? If you dont want to do a direct join, why not just outer join it to get all the data?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •