# Query 1
select distinct term_type
from term;

# Query 2
select term_type, count(name) 
from term 
group by term_type;

# Query 3
select count(*) 
from term_synonym s join term t on t.id=s.term_id 
where t.name='GTPase activity';

# Query 4
select d.term_definition, t.term_type  
from term_definition d join term t on d.term_id=t.id 
where t.name like 'phosphorylase kinase activity';

# Query 5
select distinct d.xref_dbname  
from dbxref d, seq_dbxref x, seq s  
where s.id=x.seq_id and d.id=x.dbxref_id and s.display_id='Q9XYZ9';

# Query 6
select distinct t.name, g.symbol 
from association a, gene_product g, term t 
where t.id=a.term_id and g.id=a.gene_product_id 
and g.symbol like 'cAMP%';

# Query 7
select s.genus, s.species, s.common_name, count(g.id)  
from gene_product g join species s on s.id=g.species_id 
where s.genus='Felis' 
group by s.genus, s.species;

# Query 8
select name,count(distinct term_type) as c 
from term 
group by name 
having c>1 
order by c asc;

# Query 9
select id, name 
from term 
where id in (select term2_id 
             from term2term 
             where term1_id=(select id 
                             from term 
                             where name='apoptosis'));

# Query 10 - Non-correlated
select count(id)
from term
where id NOT IN (select distinct term_id
                 from association a)

# Query 11 - Correlated: inner query references outer query (t.id)
select count(t.id)
from term t
where NOT EXISTS (select distinct a.term_id
                  from association a
                  where a.term_id=t.id)

# Query 12
select g.symbol,  count(distinct t.name) as c
from association a, term t, gene_product g
where g.symbol like "ATP%"
  and t.id=a.term_id
  and a.gene_product_id=g.id
group by g.symbol
having c >= ALL (select count(distinct t2.name) as c2
                 from association a2, term t2, gene_product g2
                 where g2.symbol like "ATP%"
                   and t2.id=a2.term_id 
                   and a2.gene_product_id=g2.id
                 group by g2.symbol);