B.Straat - 5 months ago 66

SQL Question

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:

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

- 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.
- 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;
```