SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 building a sql query problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

giorgosP
Starting Member

1 Posts

Posted - 09/29/2013 :  21:38:07  Show Profile  Reply with Quote
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?

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
             )
        )

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
165 Posts

Posted - 09/30/2013 :  00:45:00  Show Profile  Reply with Quote
Mention Having Max() Value Is <> Some Value.

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


veeranjaneyulu

Edited by - VeeranjaneyuluAnnapureddy on 09/30/2013 00:47:41
Go to Top of Page

johnson.tumble9
Starting Member

India
1 Posts

Posted - 09/30/2013 :  03:51:29  Show Profile  Reply with Quote
hi everyone i love this site and i have visited this great website on fixed interval of time my maximum problem are solved from here. from their i have make a new website for content writing please visit


[url=http://www.technobooster.tk] Article, blogs and content writing[/url]

Right
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/30/2013 :  09:59:17  Show Profile  Reply with Quote
quote:
Originally posted by giorgosP

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?

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
             )
        )




SELECT TOP 1 WITH TIES t.*
FROM TEACHERS t
INNER JOIN (SELECT Tid,COUNT(Cid) AS Cnt
            FROM ASSIGNMENTS
            WHERE Semester = 2
           )a
ON a.Tid = t.Tid
ORDER BY Cnt DESC


you ca make it more generic by using a parameter for Semester and then passing any values for it to get details for 1st, 3rd ,4th etc semesters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000