====== SQL Exercises ======
We have a hospital database:

**PAT** (__PID__, PNA, PAD, PDB, PTE)

**DOC** (__DID__, DNA, SPE)

**VIS** (__PID, DID, DVI__, DIA, TYP, PRI)
 
Here is the [[https://users.fit.cvut.cz/~valenta/bie-dbs/seminars/doctor_cre.sql|script which creates doctor database and initialize it by some data]].
   
And here are the queries:
1. List of all doctor's specializations
<code sql> 
select distinct spe from doc;
</code>
 
2. Names of all orthopeds.
<code sql> 
 select dna 
 from doc
 where upper(spe) = 'ORTHOPED';
</code>
 
3. Names and addresses of all patients borned before 1920.
<code sql>  
 select pna
 from pat
 where to_char(pdb, 'YYYY') < 1920;
</code>
 
4. DIDs of doctors visited by patient Scott.
<code sql>  
 select distinct did
 from vis join pat using (pid)
 where upper(pna) = 'SCOTT';
</code>
 
5. Names of internist visited by patient Scott.
<code sql>  
 select distinct dna
 from (doc join vis using (did)) join pat using (pid)
 where upper (spe) = 'INTERNIST' and upper (pna) = 'SCOTT';
</code>
 
6. Names and addresses of patients  visited by doctor Jones.
<code sql>  
 select pna, pad
 from (pat join vis using (pid)) join doc using (did)
 where upper(dna) = 'JONES';
</code>
 
7. Names and address of patients visited only by doctor Jones (i.e. by no other doctor).
<code sql>  
 select pna, pad
 from (pat join vis using (pid)) join doc using (did)
 where upper(dna) = 'JONES'
 minus
 select pna, pad
 from (pat join vis using (pid)) join doc using (did)
 where upper(dna) <> 'JONES'
</code>
 
8. Two-tuples of names of patients living in the same address.
<code sql>  
 select p1.pna, p2.pna
 from pat p1 join pat p2 on (p1.pad = p2.pad and p1.pid < p2.pid);
</code>
 
9. The doctors who already had patients.
<code sql>  
 select distinct d.*
 from doc d join vis v on (d.did = v.did);
</code>
 
10. Names of doctors who have no patients yet.
<code sql>  
 select * from doc
 minus
 select distinct d.*
 from doc d join vis v on (d.did = v.did);
</code>

11. Which specialization did not work yet.
<code sql>  
 select * from doc
 minus
 select distinct d.*
 from doc d join vis v on (d.did = v.did);
</code>
 
12. Names and addresses of patients with diagnose D1 (do not use a natural join for this query).
<code sql>  
 select pna, pad
 from pat join vis on (pat.pid = vis.pid and dia = 'D1');
</code>
 
13. The patients who visited any doctor.
<code sql>  
 select distinct pat.*
 from pat join vis on (pat.pid = vis.pid)
</code>
 
14. The patients who did not visited each doctor.
<code sql>  
 select *
 from pat 
 where pid in 
  (select distinct pid from
   (select p.pid, v.did
   from ((select pid from pat) p cross join 
        (select distinct did from vis) v )
     minus
    (select pid, did from vis)));
</code>
 
15. The patients who visited all doctors who had any patient.
<code sql>  
 select * from pat
 minus
 select *
 from pat 
 where pid in 
  (select distinct pid from
   (select p.pid, v.did
   from ((select pid from pat) p cross join 
        (select distinct did from vis) v )
     minus
    (select pid, did from vis)));
</code>
 
16. The patients who visited at least one othoped.
<code sql>  
 select distinct pid, pna, pad, pdb, pte
 from pat join vis using (pid) join doc using (did)
 where upper(spe) = 'ORTHOPED';
</code>
 
17. Compute the sum of money spent by all the patients together for all visits.
 
18. What is maximal price of the visit.
 
19. Patients who paid maximal price for their visit.
 
20. For each patient display the average of money spent for their visits. Order the output by the amount of visits.
 
21. As 20, but include also patients which were not on visit.
 
22. as 20, but include just patients who were at lest on three visits.