查询所有未选课程的学生姓名和所在系

SELECt sname,sdept
FROM Student
where not exits
(select sno
from sc
where sc.sno=student.sno);
错在哪里？

exits改成exists

SELECt sname,sdept
FROM Student
where not exists
(select sno
from sc
where sc.sno=student.sno);

SQL里没有exits这个关键字吧。 SELECT sname,sdept FROM Student，SC where sno not in (SELECt Sno FROM SC GROUP BY Sno HAVINg COUNT (Sno)>=1) and sc.sno=student.sno; 应该可以。

改为： SELECt sname,sdept FROM Student where not in (select sno from sc where sc.sno=student.sno); 或者就用not exits语句，如下： SELECt sname,sdept FROM Student where not in (select 1 from sc where sc.sno=student.sno); 也许你看不懂这个1的意思，不过在使用EXISTS的时候，通常都会使用1，它代表所查询出来的集合，等同于select sno。

思路：1. 查询出每个系有多少课程 2. 查出学生选了对应系的课程数量 3，比对前面的数量，如相等，表示选了所有的课程 select distinnct b. student_name from (select dept, count(course_no) as c_qty from course c group by dept) a, ( select s.name as student_name, s.dept, count(course_no) as c_qty from student s, c_s cs, course c where s.id=cs.student_id and s.dept=c.dept group by cs.student_name, s.dept ) b where a.c_qty=b.c_qty and a.dept=b.dept 按上面的sql试一下，应该是可行的。sql还有优化空间的...