Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT delivererid,name,initials
- FROM deliverers
- WHERE delivererid NOT IN (SELECT delivererid
- FROM penalties);
- SELECT delivererid
- FROM deliverers
- WHERE delivererid IN (SELECT delivererid
- FROM penalties
- WHERE AMOUNT=25)
- AND delivererid IN(SELECT delivererid /*10*/
- FROM penalties
- WHERE amount=30);
- SELECT d.delivererid, name
- FROM penalties p1, deliverers d
- WHERE p1.delivererid=d.delivererid
- AND d.delivererid IN (SELECT delivererid
- FROM penalties p2
- WHERE p1.DATA=p2.DATA AND p1.paymentid <>p2.PAYMENTID);
- SELECT delivererid
- FROM companydel /*20*/
- GROUP BY delivererid
- HAVING COUNT(*)=(SELECT COUNT(*)
- FROM companies);
- SELECT delivererid
- FROM companydel
- WHERE companyid IN (SELECT companyid
- FROM companydel
- WHERE delivererid=57)
- AND delivererid<>57;
- /*30*/
- SELECT d.delivererid,name
- FROM penalties p, deliverers d
- WHERE p.delivererid=d.delivererid AND DATA >= TO_DATE('1980-01-01', 'YYYY-MM-DD') AND DATA <=TO_DATE('1980-12-31', 'YYYY-MM-DD')
- GROUP BY d.delivererid,name
- HAVING COUNT(*)>(SELECT COUNT(*)
- FROM penalties p2
- WHERE p2.delivererid=d.delivererid AND DATA >= TO_DATE('1981-01-01', 'YYYY-MM-DD') AND DATA <=TO_DATE('1981-12-31', 'YYYY-MM-DD'));
- /*es. 7*/
- /*Function table*/
- SELECT delivererid /*40*/
- FROM penalties
- GROUP BY delivererid
- HAVING COUNT(*)= (SELECT MAX(cont)
- FROM (SELECT COUNT(*) cont
- FROM penalties
- GROUP BY delivererid));
- /*es. 8*/
- /*Richiesta: tutti i deliverers che hanno consegnato in ALMENO le stesse aziende del delivere 57*/
- SELECT delivererid
- FROM companydel
- WHERE companyid IN( /*In questo punto sto filtrando solo le aziende in comune col deliverer 57. Potrebbero essere anche di più quelle in cui
- il deliverer in questione effettua visite.
- E' UN VINCOLO SUL DATO*/
- SELECT companyid
- FROM companydel
- WHERE delivererid=57)
- AND delivererid <>57
- GROUP BY delivererid /*La group by 'convalida' il filtro precedente mediante la COUNT(*) perchè le aziende in comune col deliverer 57 devono
- essere tante quante le sue: sto escludendo quelli che hanno consegnato in aziende comuni al 57, ma in numero minore.
- E' UN VINCOLO SUL NUMERO*/
- HAVING COUNT(*)=(SELECT COUNT(*)
- FROM companydel
- WHERE delivererid=57);
- /*es. 9*/
- /*Richiesta: tutti i deliverers che hanno consegnato in ALMENO UNA azienda del deliverer 57, ma che NON HANNO consegnato in aziende diverse*/
- SELECT delivererid
- FROM companydel
- WHERE delivererid NOT IN( /*Cerco i deliverers che hanno consegnato in aziende diverse da quelle del 57 (e li escludo)*/
- SELECT delivererid
- FROM companydel
- WHERE companyid NOT IN(
- SELECT companyid
- FROM companydel
- WHERE delivererid=57))
- AND delivererid <>57;
- /*es. 10*/
- /*Richiesta: tutti i deliverers che hanno consegnato in TUTTE le aziende del 57 e SOLO quelle*/
- SELECT delivererid
- FROM companydel
- WHERE delivererid NOT IN( /*Verifica sul 'SOLO'*/
- SELECT delivererid
- FROM companydel
- WHERE companyid NOT IN(
- SELECT companyid
- FROM companydel
- WHERE delivererid=57))
- AND delivererid <>57
- GROUP BY delivererid /*Mediante il COUNT rimuovo tutti i fattorini che hanno consegnato in meno aziende di 57, seppur comuni.
- Vincolo sul 'TUTTE'*/
- HAVING COUNT(*)=(SELECT COUNT(*)
- FROM companydel
- WHERE delivererid=57);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement