sql 选了全部课程的学生(not exists双重否定)

 

-- 上过王鲁的课
SELECT sname
FROM s
WHERE sno in (
    SELECT DISTINCT sno
    FROM sc
    join c
    on sc.cno = c.cno
    JOIN t 
    on c.tno = t.tno
    WHERE t.tname = '王鲁'
)


-- 选了全部的课程
SELECT sname
FROM s
WHERE not EXISTS(  
	SELECT *
	FROM c
	WHERE not EXISTS(
				SELECT *
				FROM sc
				WHERE sc.sno = s.sno and sc.cno = c.cno
	)
)


-- 选了费玉奎 全部的课
SELECT sname
FROM s
WHERE not EXISTS(
        SELECT *
        FROM c,t
        WHERE c.tno = t.tno and t.tname = '费玉奎' and not EXISTS(
                SELECT * 
                FROM sc
                WHERE sc.sno = s.sno  and sc.cno = c.cno 
        )
)

-- 选了 1号老师 全部的课程
SELECT sname
FROM s
WHERE not EXISTS(
        SELECT *
        FROM c
        WHERE c.tno = 1 and not EXISTS(
                SELECT * 
                FROM sc
                WHERE sc.sno = s.sno  and sc.cno = c.cno 
        )
)