mysql面試練習題

查詢沒有報名的學生

方法1

SELECT s.* FROM student s

LEFT JOIN baoming bm ON s.sid = bm.sid

WHERE bm.sid IS NULL;

方法2

SELECT* FROM student

WHERE sid NOT IN (SELECT DISTINCT sid FROM baoming);

查出哪個老師的學生最多

方法1

SELECT t. NAME,COUNT(s.sid) 學生數

FROM student s,teacher t,baoming bm,ke

WHERE s.sid = bm.sid

AND t.tid = ke.tid

AND bm.kid = ke.kid

GROUP BY t. NAME

ORDER BY COUNT(s.sid) DESC

LIMIT 0,

 1;

 

方法2 

SELECT t. NAME,COUNT(bm.sid) AS 學生數

FROM teacher t

JOIN ke ON ke.tid = t.tid

JOIN baoming bm ON bm.kid = ke.kid

GROUP BY t.name

ORDER BY COUNT(bm.sid) DESC

LIMIT 1;

理題思路

原始表是student表和class表，查詢結果是不是'班級1'的結果，'班級4'有null值，所以需要用right join

第一種

SELECT*FROM student AS s

RIGHT JOIN class  AS c ON s.cid = c.cid

WHERE

c.name!='班級1';

第二種

SELECT*FROM student AS s

RIGHT JOIN class  AS c ON s.cid = c.cid

WHERE

c.name <>'班級1';