2014去哪儿网校园招聘（沈阳）

2013-09-10

a) 
SELECT sno, cno, grade
FROM scg
WHERE grade < 60;

b)
SELECT sno, COUNT(cno)
FROM scg
WHERE grade>=90
GROUP BY sno HAVING COUNT(cno)>2
ORDER BY COUNT(cno) DESC;

c)

SELECT TOP 3 sno, cno, grade

FROM scg

GROUP BY sno

ORDER BY sno, grade;

d)

SELECT cno, first.sno, second.sno, first.grade, second.grade, first.grade-second.gradeFROM ( SELECT TOP 1 * FROM scg GROUP BY cno ORDER BY grade) first,      (SELECT TOP 1 *FROM( SELECT TOP 2 * FROM scgGROUP BY cnoORDER BY grade DESC) twoGROUP BY two.cnoORDER BY two.grade) second;