SQL面试题

查询出三门成绩都大于80 的学生

解析：

第一种 sql嵌套

select "name" from "Chenji" where "subject"='英语' and "score" >=80 and "name" in

(  select "name" from "Chenji" where "subject"='数学' and "score" >=80 and "name" in 
(SELECT "name" FROM "Chenji" where "subject"='语文' and "score" >=80))


第二种intersect 取交集
select "name" from "Chenji" where "subject"='英语' and "score" >=80
intersect 
select "name" from "Chenji"  where "subject"='数学' and "score" >=80
intersect
select "name" from "Chenji"  where "subject"='语文' and "score" >=80

第三种取差集
select distinct "name" from "Chenji"
where "name" not in
(select "name" from "Chenji" where "score"<80)