select student.sname,c.cname,s_c.grade
FROM s_c join student on s_c.sno = student.sno join c on s_c.cno = c.cno
where grade>90 and c.cno in (select cno from t_c where tno in (select tno from t where tname = '王刚'))
select student.sname,college.college_name
from student join college on student.college_no = college.college_no
where not exists(
select *
from t join t_c on t.tno = t_c.tno
where t.tname = '王刚' and not exists(
select *
from s_c
where s_c.cno = t_c.cno and s_c.sno = student.sno))
select student.sno,student.sname
from student
where student.sno not in (
select sno
from s_c
where cno in (select cno from c where cname = '软件工程')
)
select student.sno
from student join s_c on student.sno = s_c.sno
group by student.sno
having count(*) >= 2
select student.sno,grade
from student join s_c on student.sno = s_c.sno
where s_c.cno in (select cno from c where cname = '经济学') and grade < 60
select sno
from student as a
where not exists(
select *
from s_c as b
where b.sno = 'T06' and not exists(
select *
from s_c
where sno = a.sno and cno = b.cno )) # 注意前面的b.sno就不能再写成s_c.sno了,要和上面保持一致
select student.sno,college.college_name
from student join college on student.college_no = college.college_no
where ('C3' in (select cno from s_c where s_c.sno = student.sno)) or ('C4' in (select cno from s_c where s_c.sno = student.sno))
select cno,cname
from c
where c.cno not in(
select cno
from s_c join student on student.sno = s_c.sno
where student.sname = '王石')
select cno
from c
where c.cno not in(
select distinct cno
from s_c)
select sname
from student
where not exists(
select *
from c
where not exists(
select *
from s_c
where student.sno = sno and c.cno =cno)) # 第九题结果不为空,则第十题结果为空。反之第九题为空则第十题不为空。
select college.college_name,avg(grade)
from s_c join student on s_c.sno = student.sno join college on student.college_no = college.college_no
where s_c.cno in (select cno from c where cname = '经济学')
group by college.college_name
order by avg(grade) desc
!!! select student.sname,college.college_name,s_c.grade
from s_c join student on s_c.sno = student.sno join college on student.college_no = college.college_no
where s_c.cno in (select cno from c where cname = '经济学')
order by college_name,grade desc
select c.cno,c.cname,t.tname
from c join t_c on c.cno = t_c.cno join t on t_c.tno = t.tno
where c.ctime >= 35 and c.ctime <= 40
SELECT student.sname
FROM student join s_c on s_c.sno = student.sno
where cno = 'C3'
order by grade desc
limit 1 # limit 获取表单的前几条记录
select c.cno,cname
from s_c join c on s_c.cno = c.cno
group by c.cno
having count(*) > 5