SQL

Es

4

DB: S(_SNum_, SName, Status, City) P(_PNum_, PName, Color, Weight, City) SP(SNum, PNum, QTY)

.1
a:
select
  SName, sum(QTY) as TotPartiRosse
from
  SP join P on (SP.PNum == P.PNum) join
  S on (S.SNum == SP.SNum)
where
  Color == 'Red'
group by SP.SNum, SName;

b:
select
  SName, coalesce(sum(QTY),0) as TotPartiRosse
from
  SP left join P on (SP.PNum == P.PNum)
  left join S on (S.SNum == SP.SNum)
where
  Color == 'rosso' or Color is null
group by SP.SNum, SName;

c:
select
  SName, coalesce(sum(QTY),0) as TotPartiRosse
from
  S left join (SP join P on (SP.PNum=P.PNum))
  on (S.SNum = SP.SNum and Color = 'Red')
group by SP.SNum, SName;

.2
a:
select
  City, sum(QTY)
from
  S join SP on (S.SNum = SP.SNum)
where
  QTY > 100
group by City, SName

b:
select distinct(S.City)
from
  S join
  SP sp1 join SP sp2 (sp1.PNum = sn2.PNum)
  join P p1 on (p1.PNum = sn1.PNum)
  join P p2 on (p2.PNum = sn2.PNum)
where
  p1.color < p2.color
group by S.City
having count(distinct S.SNum) >= 2;

5

DB: S(_SNum_, SName, Status, City) P(_PNum_, PName, Color, Weight, City) SP(SNum, PNum, QTY)

.1
a:
select distinct SNum
from
  SP
where PNum in (
  select PNum
  from P
  where City = 'London'
);

b:
select distinct SNum
from
  SP
where
  PNum = any (
    select PNum
    from P
    where City = 'London'
  );

.2
a:
select distinct City
from S
where SNum not in (
  select SNum
  from S
  where Status <
  (select avg(Status)
   from S)
);
b:
select distinct City
from S
where
  City <> all (
    select City
    from S
    where Status < (
      select avg(Status)
      from S
    )
  );

6

DB: S(_SNum_, SName, Status, City) P(_PNum_, PName, Color, Weight, City) SP(SNum, PNum, QTY)

.1
a:
select p1.PNum
from P p1
where p1.Weight >= all (
  select p2.Weight
  from P p2
  where p1.PNum <> p2.PNum
);

b:
select p1.PNum
from P p1
where not exists (
  select *
  from P p2
  where p2.weight > p1.weight
);

.2
select distinct SName
from S
where
  not exists(
    select *
    from P
    where PNum not in (
      select SP.PNum
      from SP
      where SP.PNum = S.PNum
    )
  );

.3
select PNum
from SP sp1
where
  sp1.SNum = 'S2' and
  PNum not in (
    select sp2.PNum
    from SP sp2
    where sp2.SNum = 'S3'
  )

select SName
from S
where not exists (
  select *
  from SP sp1
  where
    sp1.SNum = 'S2'
    sp1.PNum not in (
      select sp2.PNum
      from SP sp2
      where sp2.SNum = S.SNum));