CODEWITHSUNDEEP
sqloracle-database
B.Straat
B.Straat

Reputation: 83

Oracle SQL Query 2

Apologies, I hope I have put this in the right place, but I just have a couple of questions re my database which I cannot figure out why my queries aren't working.

My Database Relation Schema is as follows:

DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)
ACADEMIC(acnum, deptnum*, famname, givename, initials, title)
PAPER(panum, d.)
AUTHOR(panum*, acnum*)
FIELD(fieldnum, id, title)
INTEREST(fieldnum*, acnum*, descrip)

1) I need to find if there are any more academics who have written less than 20 papers. I need to list the acnum, title, famname, givename and number of papers they have written. See my attempt below:

SELECT a.acnum, a.title, a.famname, a.givename, count(p.panum)
FROM ACADEMIC a 
   INNER JOIN PAPER p 
   INNER JOIN AUTHOR au ON a.acnum = au.acnum and au.panum = p.panum 
GROUP BY a.ACNUM, a.title, a.famname, a.givename 
HAVING COUNT(p.panum) < 20;

2) I also need to find the departments with the largest number of academics and print their deptnum, deptname and instname, but im getting a syntax error. See my attempt:


SELECT deptnum, deptname, instname, count(acnum) 
FROM department, academic, 
WHERE academic.deptnum = department.deptnum 
GROUP BY deptnum 
HAVING MAX(count(acnum));

Upvotes: 1

Views: 801

Answers (3)

Michael Piankov
Michael Piankov

Reputation: 1997

Are you need to eliminate duplicates or its 

SELECT a.acnum, a.title, a.famname, a.givename, count(au.panum)
FROM ACADEMIC a 
JOIN AUTHOR au ON a.acnum = au.acnum 
GROUP BY a.ACNUM, a.title, a.famname, a.givename 
HAVING COUNT(au.panum) < 20;

and 

SELECT deptnum, deptname, instname, count_ac
FROM (
    SELECT d.deptnum, d.deptname, d.instname, COUNT(a.acnum) as COUNT_AC 
    FROM department d
    JOIN academic a d.deptnum = a.deptnum
    GROUP BY d.deptnum, d.deptname, d.instname
    ORDER BY COUNT(a.acnum)
) WHERE ROWNUM < 2;

or

SELECT deptnum, deptname, instname, count_ac
FROM (
    SELECT d.deptnum, d.deptname, d.instname, dense_rank() over (order by COUNT(a.acnum) desc) cur_rank, COUNT(a.acnum) as count_ac
    FROM department d
    JOIN academic a d.deptnum = a.deptnum
    GROUP BY d.deptnum, d.deptname, d.instname
) WHERE cur_rank = 1;

Upvotes: 0

user5683823
user5683823

Reputation:

Assuming a pair (panum, acnum) doesn't appear in author more than once (there are no duplicates in that table), you don't need to join to paper in the first query. This should suffice:

SELECT a.acnum, a.title, a.famname, a.givename, count(au.panum) as ct
FROM ACADEMIC a 
   INNER JOIN AUTHOR au ON a.acnum = au.acnum
GROUP BY a.ACNUM, a.title, a.famname, a.givename 
HAVING COUNT(au.panum) < 20;

The error in your original attempt was the order of the on conditions; the first inner join must be followed IMMEDIATELY by its corresponding on condition, you can't leave it for later. However, as I just showed, you only need one join.

For the second query, you need to find the department(s) with the max number of academics first, using the academic table. Then you can join the result to the department table.

select d.deptnum, d.deptname, d.instname, m.ct
from   department d inner join
       ( select   deptnum, count(acnum) as ct
         from     academic
         group by deptnum
         having   count(acnum) = (select max(acnum) from academic group by deptnum)
       ) m
           on d.deptnum = m.deptnum
;

Upvotes: 2

Matt
Matt

Reputation: 15061

Your joins need to be in order that they load.

SELECT a.acnum, a.title, a.famname, a.givename, COUNT(p.panum)
FROM ACADEMIC a
INNER JOIN AUTHOR au ON a.acnum = au.acnum
INNER JOIN PAPER p ON au.panum = p.panum 
GROUP BY a.ACNUM, a.title, a.famname, a.givename 
HAVING COUNT(p.panum) < 20;

Use an ORDER BY to give you the top department.

SELECT d.deptnum, d.deptname, d.instname, COUNT(a.acnum) 
FROM department d
INNER JOIN academic a d.deptnum = a.deptnum
GROUP BY d.deptnum, d.deptname, d.instname
ORDER BY COUNT(a.acnum);

Upvotes: 0

Related Questions