Lab1 - Basi

SELECT * FROM DELIVERERS;
SELECT DISTINCT companyid FROM companydel;
SELECT delivererid,name FROM deliverers WHERE name LIKE 'B%';
SELECT name,sex,DELIVERERID FROM deliverers WHERE phoneno<>8467 OR phoneno IS NULL;
SELECT DISTINCT name,town FROM DELIVERERS D, PENALTIES P WHERE P.DELIVERERID=D.DELIVERERID;
SELECT DISTINCT D.name,D.initials FROM DELIVERERS D,PENALTIES P,COMPANies C WHERE D.DELIVERERID=P.DELIVERERID AND C.DELIVERERID=D.DELIVERERID AND P.DATA>to_date('31/12/1980','DD/MM/YYYY') ORDER BY D.NAME;
SELECT DISTINCT C.COMPANYID,C.DELIVERERID FROM companydel C,Deliverers D WHERE C.DELIVERERID=D.delivererid AND D.town='Stratford' AND C.NUMCOLLECTIONS>=2 AND C.NUMDELIVERIES>=1;
SELECT D.DELIVERERID FROM COMPANYDEL CD, COMPANIES C, DELIVERERS D WHERE D.YEAR_OF_BIRTH>1962 AND C.MANDATE='first' AND CD.DELIVERERID=D.DELIVERERID AND c.COMPANYID=CD.COMPANYID;
SELECT DISTINCT D.name FROM companydel C1,companydel C2, deliverers D WHERE C1.DELIVERERID=C2.DELIVERERID AND C1.COMPANYID<>C2.COMPANYID AND C1.DELIVERERID=D.DELIVERERID AND (D.town='Inglewood' OR D.town='Stratford');
SELECT D.DELIVERERID,SUM(DISTINCT P1.AMOUNT)
FROM PENALTIES P1, PENALTIES P2,DELIVERERS D
WHERE P1.PAYMENTID<>P2.PAYMENTID AND P1.DELIVERERID=P2.DELIVERERID AND P1.DELIVERERID=D.DELIVERERID AND D.town='Inglewood'
GROUP BY D.DELIVERERID;