Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: SQL Revision Help

    Hi first post here, I am currently revising for an exam and I have been given certain questions that I need to produce the SQL for as revision. The problem is I just don't get the harder ones. I have managed to do the simple ones correct but the rest I just cant seem to get the hang of. I have posted the next 4 questions that I couldn't do, I'm hoping that if you can explain to me how to get the correct sql for them I will be able to do the other 7 by myself.

    5 A list of all the lecturers that deliver a part of a module that only they can teach, along with the name of the module and a description of the part.

    6 A list of students who have failed a module that was delivered in semester 1 by a lecturer who did not know their subject.

    7 A count of module parts being delivered in Semester 2 where the module parts were being delivered by a lecturer who knew how to teach the module part.

    8 A list of module part tutors who can deliver a part of a module that nobody else can, with a count of the number of module parts that can only be taught by them

    These are the tables that I have been given to work out the queries:

    The Tables:

    CANTEACH
    ----------------------------------------- -------- -------
    MODULID CHAR(4)
    MODPARTID CHAR(1)
    LECTURERID CHAR(4)

    COURSE
    ----------------------------------------- -------- --------
    COURSEID CHAR(4)
    COURSESHORT CHAR(10)
    COURSELONG CHAR(30)

    ENROL
    ----------------------------------------- -------- -------
    STUDENTID CHAR(8)
    COURSEID CHAR(4)
    ENROLDATE DATE

    FOUNDIN
    ----------------------------------------- -------- -------
    RSRCEID CHAR(3)
    ROOM CHAR(7)

    LECTURER
    ----------------------------------------- -------- --------
    LECTURERID CHAR(4)
    SURNAME CHAR(10)
    FORENAME CHAR(10)
    BOSSID CHAR(4)

    MODOCCUR
    ----------------------------------------- -------- -------
    MODULID CHAR(4)
    ACYEAR CHAR(2)
    SEMESTER CHAR(1)
    OCCLETTER CHAR(1)

    MODPART
    ----------------------------------------- -------- --------
    MODULID CHAR(4)
    MODPARTID CHAR(1)
    MODPARTDESC CHAR(10)

    MODPARTOCCUR
    ----------------------------------------- -------- ---------
    MODULID CHAR(4)
    ACYEAR CHAR(2)
    SEMESTER CHAR(1)
    OCCLETTER CHAR(1)
    MODPARTID CHAR(1)
    ROOM CHAR(7)
    LECTURERID CHAR(4)
    DOW CHAR(1)
    TIMESTART NUMBER(3)
    DURATION NUMBER(3)

    MODPARTRES
    ----------------------------------------- -------- --------
    MODULID CHAR(4)
    MODPARTID CHAR(1)
    RSRCEID CHAR(3)
    WHATFOR CHAR(10)

    MODUL
    ----------------------------------------- -------- --------
    MODULID CHAR(4)
    MODULNAME CHAR(10)
    MODULEVEL CHAR(1)

    MODULCOURSE
    ----------------------------------------- -------- -------
    COURSEID CHAR(4)
    MODULID CHAR(4)
    COMPOPT CHAR(1)

    PREREQ
    ----------------------------------------- -------- -------
    MODNEEDS CHAR(4)
    MODNEEDED CHAR(4)

    ROOM
    ----------------------------------------- -------- ---------
    ROOM CHAR(7)
    SEATS NUMBER(3)

    RSRCE
    ----------------------------------------- -------- --------
    RSRCEID CHAR(3)
    RSRCEDESC CHAR(20)

    STUDENT
    ----------------------------------------- -------- --------
    STUDENTID CHAR(8)
    SURNAME CHAR(15)
    INITS CHAR(15)
    SEX CHAR(1)
    PHONE CHAR(12)
    EMAIL CHAR(20)
    LOGON CHAR(8)

    STUDREGOCCUR
    ----------------------------------------- -------- -------
    MODULID CHAR(4)
    ACYEAR CHAR(2)
    SEMESTER CHAR(1)
    OCCLETTER CHAR(1)
    STUDENTID CHAR(8)
    RESULT CHAR(1)

    STUDTT
    ----------------------------------------- -------- -------
    MODULID CHAR(4)
    ACYEAR CHAR(2)
    SEMESTER CHAR(1)
    OCCLETTER CHAR(1)
    MODPARTID CHAR(1)
    STUDENTID CHAR(8)

    Your help would be appreciated

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Divide and conquer.

    You need to break each question down in order to build the WHERE clause conditions, for example:

    "5 A list of all the lecturers that deliver a part of a module that only they can teach, along with the name of the module and a description of the part."

    a) Required result:

    A list of all the lecturers along with the name of the module and a description of the part (they can teach).

    b) Condition:

    that deliver a part of a module that only they can teach (vg. a module they teach is not in the list of modules of any other lecturer)

    c) Possible solution:
    Code:
    SELECT l.surname || ', ' || l.forename, t.modulid, t.modpartid, p.modpartdesc
      FROM lecturer l, canteach t, modpart p
     WHERE t.lecturerid = l.lecturerid
       AND p.modulid = t.modulid
       AND p.modpartid = t.modpartid
       AND NOT EXISTS (
              SELECT '?'
                FROM canteach o
               WHERE t.modulid = o.modulid
                 AND t.modpartid = o.modpartid
                 AND o.lecturerid != l.lecturerid);

    Now you try to solve the rest of the questions
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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