Some idea - SQL Server Forums

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

Some idea

Author

Topic

ryan777
Starting Member

3 Posts

Posted - 2008-04-27 : 23:02:03

Hi I'm new to sql and it would be great if someone could give some idea on how to do the following

This is the relational model:

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum, AcNum)
Field(FieldNum, ID, Title)
Interest(FieldNum, AcNum, Descrip)

The question is :-
Which academics have the largest number of fields of interests? Return their academic number,
given name, family name, institution and total number of fields they are interested in. Your must use a
subquery in the sense that you must use 2 sql statements and use any of the following to connect both:-

1)Exists
2)Not exists
3)IN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 00:14:36

Looks like lab question. Can we see what you tried till now?

ryan777
Starting Member

3 Posts

Posted - 2008-04-28 : 00:29:49

This is what I did.....but there is more to it......this just brings a list of academic numbers and their interests in descending order with the academic with max interests at the top of the list.I have to not only show their respective academic number, given name, family name and institution on this table, but also use 2 queries.Maybe my approach is wrong....

select acnum,count(*) as interests
from interest
group by acnum
having count(*) >=1
order by interests desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 00:53:31

[code]SELECT a.AcNum,a.GiveName,a.FamName,
d.Instname,COUNT(i.FieldNum) AS NoOfFields
FROM Academic a
INNER JOIN Department d
ON d.DeptNum=a.DeptNum
INNER JOIN Interest i
ON i.AcNum=a.AcNum
WHERE i.AcNum IN
(SELECT TOP 1 a.AcNum
FROM Academic a
INNER JOIN Interest i
ON i.AcNum=a.AcNum
GROUP BY a.AcNum
ORDER BY COUNT(i.FieldNum) DESC)[/code]

ryan777
Starting Member

3 Posts

Posted - 2008-04-28 : 01:26:32

What exactly do i have to replace for your query where you typed
SELECT TOP 1.......

I removed top1 and tried to execute so I got a missing right parenthesis error ORA-00907.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 01:52:17

Aha....so you're using Oracle. I should say you're in wrong forum then. This is MS SQL Server forum. Post it in some oracle forums if you need correct syntax in oracle.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources