In dire need of help ... related to SQL query (I'm using Oracle 9i version)?

Question

I've created a table called :FACULTY
It has the following details --

FacultyCode : Not null, Primary key, Starts with ‘F’
FacultyName : Not null
DateOfJoin : Not null
DeptCode: Must be either CSE,IT, CA, CHEM, MTHS, PHYS, HUM, BBA

Another table that I've created is :DEPARTMENT
It has following details --

DeptCode: Not null, Primary key
DeptName: Not null
HOD : Foreign key references FacultyCode of table FACULTY

Then I've inserted appropriate data into these 2 tables. Now the question is "Name the departments having highest number of faculties and display the names of faculties" . How to solve this in SQL queries ?

Answer 1

You can use the following SQL for the same:-

select c.DeptCode, c.DeptName, d.FacultyCode, d.FacultyName
from DEPARTMENT c, FACULTY d
where c.DeptCode in (
select b.DeptCode
from FACULTY b
group by b.DeptCode
having count(*) =
(
select max(count(*))
from FACULTY a
group by DeptCode
)
)
andc.DeptCode = d.DeptCode;

Breaking down the SQL statement:-
1. The SQL statement to retrieve the maximum count of the faculty within a department:-
select max(count(*))
from FACULTY a
group by DeptCode

2. Corresponding to this maximum count we find out the departments for which that many number of faculty are present.

select b.DeptCode
from FACULTY b
group by b.DeptCode
having count(*) =
(
select max(count(*))
from FACULTY a
group by DeptCode
)

3. Corresponding to these retrieved departments we retrieve the Department Code, Department Name, Faculty Code & Faculty Name:-

select c.DeptCode, c.DeptName, d.FacultyCode, d.FacultyName
from DEPARTMENT c, FACULTY d
where c.DeptCode in (
select b.DeptCode
from FACULTY b
group by b.DeptCode
having count(*) =
(
select max(count(*))
from FACULTY a
group by DeptCode
)
)
andc.DeptCode = d.DeptCode;

Answer 2

I have this on my machine at the office and to be honest I never use it because it does not help me very well when analyzing a query. With a query this big and that many joins it may be an issue running it in SQL. I would probably recomend breaking this up into sub queries within the SQL statement and you may get better results. Are you using any Outer Joins or are all of these Inner Joins?

Answer 3

select deptname, facultyname, count(facultycode)
from faculty, department
where faculty.deptcode = department.deptcode and
group by faculty.deptcode

in not sure.. but its something like that :)