Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 building a sql query problem

Author  Topic 

giorgosP
Starting Member

1 Post

Posted - 2013-09-29 : 21:38:07
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

169 Posts

Posted - 2013-09-30 : 00:45:00
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
Go to Top of Page

johnson.tumble9
Starting Member

1 Post

Posted - 2013-09-30 : 03:51:29
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

52326 Posts

Posted - 2013-09-30 : 09:59:17
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
   

- Advertisement -