52 lines
1.5 KiB
Plaintext
52 lines
1.5 KiB
Plaintext
/* 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
|