SQL query? - 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

SQL query?

Author

Topic

ngquochung
Starting Member

13 Posts

Posted - 2006-11-17 : 04:52:54

Hi everyone, I am quite new to SQL.I got stuck and don't know how to solve it.
The database:
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: Produce a list of departments located in the Victoria state that have at least 20 staff.
For each such department, return the department number, institution and number of staff in the
department. Output should be in descending order of staff number. It is known that Victoria is
kept in the database as 'Vic', 'VIC' or 'vic'.

My solution:
SELECT DISTINCT dept.state, COUNT(ac.acnum)
FROM department dept, academic ac
WHERE dept.state ='VIC'
OR dept.state='Vic'
OR dept.state='vic'
GROUP BY dept.state
HAVING COUNT(ac.acnum)>=20
ORDER BY dept.state DESC

The only problem is that I don't know how to select at least 20 staffs in Academic, and I don't know how to select some more attributes because it will break the group by function.
Could u help me? Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-17 : 05:14:23

[code]
select d.DeptNum, d.State, cnt
from Department d
inner join
(
select d.DeptNum, cnt = count(*)
from Department d inner join Academic a
on d.DeptNum = a.DeptNum
where d.state in ('Vic', 'VIC', 'vic')
group by d.DeptNum
having count(*) > 20
) c
on d.DeptNum = c.DeptNum
order by cnt desc
[/code]

ngquochung
Starting Member

13 Posts

Posted - 2006-11-17 : 05:54:44

I don't know why it still has an error
ORA-00923: FROM keyword not found where expected
Could u please help me again? Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-17 : 07:28:37

quote:
Originally posted by ngquochung

I don't know why it still has an error
ORA-00923: FROM keyword not found where expected
Could u please help me again? Thanks

because ths is a MS SQL Server site. The query will works on SQL Server but may not on Oracle.

Try posting at dbforums.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 10:13:58

or at www.ORAFAQ.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources