/* select the snumbers and the number of parts suppled and the average qty of parts supplied for those suppliers who have a qty greater than the average qty of all suppliers. */
select avg(qty)
from spj
group by snumber;

/* average of parts supplied */
select avg(qty)
from spj;

/* select the snumbers and the number of parts suppled and the average qty of parts supplied for those suppliers who have a qty greater than the average qty of all suppliers. */
select snumber, avg(qty)
from spj
group by snumber
having avg(qty) > (
   
	(select avg(qty) from spj));

/*
	s1 30
	s2 20
	s3 40
*/

/* select number and the avg qty for suppliers whose range is greater tham the sum of all qty */
select snumber, avg(qty), (select avg(qty) as AVERAGE_QTY_OF_ALL)
from spj
group by snumber
having avg(qty) >
 (select avg(qty) 
  from spj);

/* list snumber, snames, and total number of parts supplied for suppliers who live in london, athens or paris, Use nested selects. 
1. Use WHERE clause only without nested selects.
2. Use nested selects .
*/

select supplier.snumber, sname, pnumber, city
from supplier, spj
where supplier.snumber = spj.snumber and
(city = 'paris' or city = 'london');

select supplier.snumber, sname, count(pnumber) as NUM_PARTS_SUPPLIED
from supplier, spj
where supplier.snumber = spj.snumber and
(city = 'paris' or city = 'london')
group by spj.snumber;

select supplier.snumber, count(pnumber) as NUM_PARTS_SUPPLIED 
(select sname from supplier where snumber = spj.snumber)
(select city from supplier where snumber = spj.snumber)
from spj