B.Straat B.Straat - 1 month ago 25
SQL Question

Oracle SQL Programming Query

Apologies, but I have little experience with SQL programming and I need to figure out why my SQL query isn't working...I have been trying to no avail to work this out!

Database SQL file located here

Database schema as follows:

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)


Essentially I am trying to find out the following, and having some real issues:


  1. Need to find the academics that have more than 1 research interest. I need to list the acnum, famname and givename of these academics, sorted by famname and then by givename.

    select A.acnum, A.givename, A.famname, INTEREST.FIELDNUM
    from ACADEMIC A, INTEREST
    where A.ACNUM = INTEREST.ACNUM
    having count (Interest.acnum) > 1;



Something like this?


  1. I need to find if there are any research fields where no academics have no interest in? I need to print the total number of research fields like this. I believe the query uses a SET operator.

  2. I need to find the research fields that have the largest number of interested academics. I need to output the fieldnum and number of interested academics.


Answer

The schema is not quite clear to me, but here is a starting point:

1) First one:

SELECT a.acnum, a.famname ,a.givename, count(i.fieldnum) 
FROM ACADEMIC a INNER JOIN INTEREST i ON a.acnum = i.acnum
GROUP BY a.acnum, a.famname ,a.givename
HAVING COUNT(i.fieldnum) > 1
ORDER BY a.famname ,a.givename;

2) If you just need the number of them:

SELECT COUNT(1) 
FROM FIELD f 
WHERE NOT EXISTS(SELECT 1 
                 FROM ACADEMIC a 
                 INNER JOIN INTEREST i ON a.acnum = i.acnum 
                 WHERE f.fieldnum = i.fieldnum);

3) It's a bit fuzzy, because I don't know what largest actually means, but here is the sorted list:

SELECT f.fieldnum, count(a.acnum) as number_of_interested_academics
FROM FIELD f 
    INNER JOIN INTEREST i ON f.fieldnum = i.fieldnum
    INNER JOIN ACADEMIC a ON i.acnum = a.acnum
GROUP BY f.fieldnum
ORDER BY count(a.acnum) DESC;