Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: building an sql query problem

    I am trying to build a query in sql.
    The relations are
    TEACHERS(Tid, Name, LastName, Gender, Scale)
    COURSES(Cid, Title, Type, Grades)
    ASSIGNMENTS(Tid, Cid, Semester)

    where Tid is the id of teacher and Cid is the id of course and what i am trying to do is to give a query for retrieving the id and the last name of teachers who taught the most courses during the second semester.

    I have tried this but i dont think is correct. I dont have a way to test it on sql server, so i dont know if it is correct. Does anybody have an idea?

    Code:
    SELECT Tid, LastName
        FROM TEACHERS
        WHERE Tid IN (
            SELECT Tid
            FROM ASSIGNMENTS
            WHERE Semester=2
            GROUP BY Tid
            HAVING MAX(
                 SELECT COUNT(Cid)
                 FROM COURSES AS C, ASSIGNMENTS AS A
                 WHERE A.Cid=C.Cid 
                 AND A.Semester=2
                 GROUP BY Cid
                 )
            )

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, that won't get the name of the teacher who taught the most courses during second semester.

    There are quite literally thousands of ways to get this tidbit of information, but probably only one using the tools that you've been taught so far. This makes it difficult for us to help you because while we can guess a lot we don't know what you've covered so far.

    Start from the beginning. How would you find the TID value for the teacher who taught the most courses for the second semester? I'll guess that this will use a subquery or a Common Table Expression.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you have no idea). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Mopw e have to correct the bad design, guess at everything and do all the typing that you did not do.

    CREATE TABLE Teachers
    (teacher_id CHAR(10) NOT NULL PRIMARY KEY,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    sex_code SMALLINT NOT NULL
    CHECK (sex_code IN (0,1,2,9)), --- ISO 5218 standard
    pay_scale CHAR(5) NOT NULL);

    did I guess the key correctly?

    CREATE TABLE Courses
    (course_nbr CHAR(5) NOT NULL PRIMARY KEY,
    course_title VARCHAR(15) NOT NULL,
    course_type CHAR)3) NOT NULL,
    );

    You cannot put multiple grades in a column! A grade is a value in a relationship among students (we have no students in your schema!)and a course. I invented a semester encoding using the year and semester number. Gee, sure wish we had DDL. And sample data. And specs.

    CREATE TABLE Teaching_Assignments
    (course_nbr CHAR(10) NOT NULL
    REFERENCES Courses(course_nbr),
    teacher_id CHAR(10) NOT NULL Teachers
    REFERENCES Teachers (teacher_id),
    semester CHAR(6) NOT NULL
    CHECK (semester LIKE '[12][0-9][0-9][0-9]S[1-4]')
    PRIMARY KEY (course_nbr, semester));

    CREATE TABLE Grade_Book
    (course_nbr CHAR(10) NOT NULL
    REFERENCES Courses(course_nbr),
    semester CHAR(6) NOT NULL
    CHECK (semester LIKE '[12][0-9][0-9][0-9]S[1-4]'),
    student_id CHAR(10) NOT NULL
    REFERENCES Students(student_id),
    PRIMARY KEY (course_nbr, semester, strident_id),
    test_grade SMALLINT DEFAULT 0 NOT NULL
    CHECK (test_grade BETWEEN 0 AND 100));

    >> where teacher_id is the id of teacher and course_nbr is the id of course and what I am trying to do is to give a query for retrieving the id and the last name of teachers who taught the most courses during the second semester. <<

    Her is my guess:

    WITH X1
    AS
    (SELECT teacher_id, COUNT(course_nbr) AS course_load
    FROM Teaching_Assignments
    WHERE semester = '2013S2'
    GROUP BY teacher_id),
    X2
    AS
    (SELECT teacher_id, course_load,
    MAX(course_load)OVER (PARTITION BY teacher_id)
    AS course_load_max
    FROM X1)

    SELECT T.teacher_id, T.last_name
    FROM X2, Teachers AS T
    WHERE X2.course_load_max = X2.course_load
    AND X2.teacher_id = T.teacher_id

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't know. I prefer to Keep It Simple S... I can't see any reason to join with the Course table, seems Joe thought the same in his example. Plus based on your question I don't think that you have covered CTEs as yet. You are overthinking your problem and therefore getting to complex with your solution. This is a common problem in the industry that you have to try to avoid. This doesn't give you your entire answer for you, but will put you on the right route. Now just figure out which of those rows you want and how to get that one. Post back your final solution or what you think should be your solution, as I think you should just have two short lines added to the end of this SQL.

    Code:
    SELECT t.Tid, t.LastName, count(*) as nbr_crs
        FROM TEACHERS   t
    inner join ASSIGNMENTS  a
       on t.Tid = a.Tid
      and a.Semester=2
    group by t.Tid, t.LastName
    Dave

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have a look at Detecting joined records that number only 1. You have basically the same problem.

    Apply the same method of breaking down - as Pat also stated - your problem in simpler steps, and working your way up from those.

    As a first step, install a free database system on your computer.

    You will get a lot of errors when you start writing scripts and give them to a real system that will flag each and every typo you entered, and you will make heavy use of Google, this forum, ... and at the end you will have learned a ton.

    Create a few simple tables, populate them with some sample data and start from there on. Have a look at the CREATE TABLE scripts Celko posted, use those as a starting point.
    Always determine up front what you expect to get in your result set before you start writing a script. Compare both results before you proceed.
    Take small steps.

    When you're struck, post what scripts you got (include the CREATE TABLE and INSERT scripts too) and what you expected to get. We will not do your homework for you, but we will try to help you the best we can if you show us you did your very best to solve it yourself.

    Have fun.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dave's solution is the same one I came up with.
    I think you, and everybody else, was over-thinking the problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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