# 1

SELECT *
FROM deliverers;

#2

SELECT companyid
FROM companies;

#3

SELECT d.name, d.delivererid
FROM deliverers d
WHERE d.name LIKE 'B%'

#4

SELECT d.name, d.sex,d.delivererid
FROM deliverers d
WHERE d.phoneno <> '8467' OR d.phoneno IS NULL;

#5

SELECT DISTINCT d.name, d.town
FROM deliverers d JOIN penalties pe
ON d.delivererid = pe.delivererid
ORDER BY d.name;

#6

SELECT DISTINCT comref.name, comref.initials
FROM (SELECT d.delivererid, d.name, d.initials
      FROM deliverers d JOIN companies com ON com.delivererid = d.delivererid) comref
JOIN penalties pen ON comref.delivererid = pen.delivererid
WHERE pen.DATA > TO_DATE('31/12/1980','DD/MM/YYY');

#7

SELECT DISTINCT cd.companyid,d.delivererid
FROM (SELECT delivererid FROM deliverers WHERE deliverers.town = 'Stratford') d
JOIN companydel cd ON cd.delivererid = d.delivererid
WHERE cd.numcollections > 2 AND cd.numdeliveries > 1;

#8

SELECT DISTINCT cd.delivererid
FROM companydel cd
NATURAL JOIN (SELECT d.delivererid FROM deliverers d WHERE d.year_of_birth > 1962) de
NATURAL JOIN (SELECT com.companyid FROM companies com WHERE com.mandate = 'first') comp;

### non esiste un deliverid = 67 ma solo uno = 57

#9

SELECT del.name
FROM deliverers del
WHERE del.delivererId IN (SELECT del.delivererid
FROM companydel com
JOIN (SELECT d.delivererid FROM deliverers d WHERE d.town = 'Inglewood' OR d.town = 'Stratford') del
ON del.delivererid = com.delivererid
GROUP BY del.delivererid
HAVING COUNT(*) >= 2)

#10

SELECT del.delivererId,SUM(pen.amount)
FROM penalties pen
NATURAL JOIN (SELECT d.delivererId FROM deliverers d WHERE d.town = 'Inglewood') del
GROUP BY del.delivererId
HAVING COUNT(*) >= 2

#11

SELECT del.name,MIN(pen.amount)
FROM penalties pen
NATURAL JOIN deliverers del
GROUP BY del.delivererId
HAVING COUNT(*) >= 2 AND COUNT(*) < 4

#12

SELECT SUM(x.numdeliveries), SUM(x.numcollections)
FROM (SELECT com.numdeliveries, com.numcollections
FROM companydel com
NATURAL JOIN (SELECT * FROM deliverers d WHERE d.town <> 'Stratford' AND d.name LIKE 'B%') del
GROUP BY com.delivererId,com.companyId) x