--  Show all supplier names with their status doubled.
select distinct SName, Status * 2 AS "Doubled Status"
from Supplier

--  Show all supplies in ascending PNum and descending quantity.
select distinct PNum, Quantity, SNum
from Supplies
order by PNum ASC, Quantity DESC

--  Show the names and status of the suppliers with the
--  highest status.
select SName, Status
from Suppliers
where Status >= all(select distinct status from Suppliers)

--  Show all part inventory on those part with total quantity
--  of 200 or above.
select PNum AS "Part Number", Sum(Quantity) AS "QUANTITY"
from Supplies
group by PNum
having Sum(Quantity) > 200

--  Show all suppliers with names containing the string "Toy".
select distinct SName, SCity
from Suppliers
where SName like '%Toy%'
--  A better solution:
select distinct SName, SCity
from Suppliers
where LOWER(SName) like '%toy%'

--  List all part inventory.
--  Note that for MS Access, "Quantity" must be "Number"
--  to avoid 'circular references'.
(select PNum as "Part Number", Sum(Quantity) as "Quantity"
 from Supplies
 group by PNum)
union
(select PNum as "Part Number", 0 as "Quantity"
 from parts
 where PNum not in (select distinct PNum from Supplies))

--  Show all supplier names that supply all parts.
select distinct SName
from Suppliers
where SNum not in
   (select SNum
    from (select distinct SNum from Suppliers),
         (select distinct PNum from Parts)
    where (SNum, PNum) not in 
         (select distinct SNum, PNum from Supplies))

--  or
select distinct SNum
from Supplies S1
where ((select distinct PNum
        from Supplies S2
        where S1.SNum = S2.SNum)
       contains
       (select distinct PNum from Parts))
          
--  or
select distinct SName
from Suppliers S
where not exists (select distinct PNum from Parts P
                  where not exists (select *
                                    from Supplies SP
                                    where S.SNum = SP.SNum
                                    and P.PNum = SP.PNum))

--  Show all SNum that supplies all parts supplied by S1.
select distinct SNum from Supplies
where SNum not in 
(select distinct SNum from 
 (select SNum, PNum from
    (select distinct PNum from Supplies where SNum = 'S1'),
    (select distinct SNum from Supplies)
  where (SNum, PNum) not in (select distinct SNum, PNum from Supplies)))