数据库课外实验作业

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