/* Question 1*/
select givename, famname
from academic natural join department
where acnum in (select author.acnum
from paper natural join author
where title like '%Data%'
or title like '%data%')
and descrip = 'RMIT CS';

/* Question 2 Workout */
select *
from author;

select *
from paper
where title like '%system%'
or title like '%System%';

/* Question 2 */
select distinct(acnum)
from author
where not exists (select *
  from paper
  where panum = author.panum
  and title like '%system%'
  and title like '%System%');

/* Question 3 */
select distinct (acnum)
from interest
where fieldnum in (select fieldnum
from field
where rtrim(title) like 'Logic Programming');

/* Question 4 - Uses parts of 3.1 and 3.3. */
select acnum
from author
where acnum in ( select interest.acnum
  from interest natural join field
  where interest.acnum in ( select author.acnum
    from paper natural join author
    where title like '%Data%'
    or title like '%data%')
  and rtrim(title) like 'Logic Programming');

/* Question 5 workout */
select distinct(deptnum), count(panum)
from author natural join academic
group by deptnum
having count(panum) = (select max(count(panum))
from author
group by acnum); 
/* For this question, join two tables together
 Academic and Author using common acnum key, then search for deptnum
 in the new table.*/

/* Question 6

The queries are not logically equivalent. With regards to c)
and d) since state has some values which are null, the count 
function does not count nulls unless specifically programmed to. 
With regards to a) and b) it is programmed to count when state 
is not 'VIC'. However, a) calculates the count of state whereas
b) counts the amount of department numbers.

*/