Какие (может быть несколько или ни одного!) из ниже перечисленных операторов SELECT дают следующую выборку:
Выборка: s_no sname sum_p
s1 Smith 1300
s4 Clark 900
Варианты:
А) SELECT s.s_no,sname,SUM(qty) AS sum_p FROM s,sp WHERE s.s_no=sp.s_no
GROUP BY s.s_no,sname HAVING COUNT(*)>2
Б) SELECT s.s_no,sname,SUM(qty) AS sum_p FROM s,sp WHERE s.s_no=sp.s_no
GROUP BY s.s_no,sname HAVING COUNT(*)>1
В) SELECT s.s_no,sname,SUM(qty) AS sum_p FROM s,sp WHERE s.s_no=sp.s_no AND
EXISTS(SELECT * FROM sp z WHERE sp.s_no=z.s_no AND sp.p_no<>z.p_no)
GROUP BY s.s_no,qty
Г) SELECT s.s_no,sname,SUM(qty) AS sum_p FROM s,sp WHERE s.s_no=sp.s_no
AND EXISTS(SELECT * FROM sp z WHERE sp.s_no=z.s_no AND sp.p_no<>z.p_no)
GROUP BY s.s_no,sname
Д) SELECT s.s_no,sname,SUM(qty) AS sum_p FROM s,sp WHERE s.s_no=sp.s_no
GROUP BY s.s_no,sname HAVING COUNT(*)>3
