/* -----------------20第二种解法 select cname,COUNT(student.sno),AVG(grade) from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno group by sc.cno,cname */
select * ------为什么这里查询不到结果 from course -------------21 where cpno is null
--sql 查询实验三代码
--------1
select student.*,sc.cno,sc.grade ----左外连接(显性连接) from student left join sc on student.sno=sc.sno /*
select student.*,sc.cno,sc.grade
16
from student inner join sc ----内连接法（显性连接） on student.sno=sc.sno */ /*
select student.*,sc.cno,sc.grade ----------隐性连接 from student,sc
where student.sno=sc.sno */
select first.cname,first.cpno,second.cno from course first,course second ----------2 where first.cpno=second.cno
select student.*,sc.* ---------------3 from student right join sc on student.sno=sc.sno
select student.sno,sname from student,sc,(select student.sno
from student,sc -----4 导出表的使用 where sc.sno=student.sno and cno='2' group by student.sno)as result(sno)
17
where student.sno=sc.sno and cno='3' group by student.sno,student.sname
select student.sno,sname
from student,sc as x --------4法二
where student.sno=x.sno and cno='2' and exists(select student.sno from student,sc as y
where cno='3' and exists(select * from student,sc as z
where z.sno=y.sno and z.sno=x.sno))
select student.sno,sname
from student,sc as x,sc as y ------4法三
where student.sno=x.sno and x.sno=y.sno and x.cno='2' and y.cno='3' select *
from student as x,student y --------5 where x.sname='刘晨' and x.sage=y.sage
select sname,sage
18
from student,sc,course -----------6 where
student.sno=sc.sno
and
sc.cno=course.cno
course.cname='数据库'
select sname from student
where sage
from student ----------7 where sdept='is' ) and sdept< >'is' select sname from student
where sage'is'
select sname
from student ------------9 where sno in(select sno from sc
and
19
group by sno
having COUNT(*)=7)
select * --------------10 from student
where sdept='is' and ssex='男'
select distinct student.sname -------------11 from student,sc
where sc.cno='1' except(select student.sname from student,sc where sc.cno='2') select cno
from sc ---------------------12 where sc.sno not in(select sno from student
where student.sname='李丽')
select AVG(sage) --------------------------13 from student,sc
where student.sno=sc.sno and cno='3'
20