数据库查询

select * from student,dorm;

select * from student s,dorm d
where s.dormno=d.dormno;

select * from student s left join dorm d
on s.dormno=d.dormno;

select * from student s right join dorm d
on s.dormno=d.dormno;

select * from student s full join dorm d
on s.dormno=d.dormno;

select sno,sname,d.dormno from
(select * from student where dormno is null) s,
(select dormno,null as sex from dorm where dormno not in
(select dormno from student where dormno is not null)
union
select  dormno,sex from student 
where  dormno is not null and sex in(select sex from student where dormno is null)
group by dormno,sex
having count(*)<5) d
where s.sex=d.sex or d.sex is null;

select * from student 
where dno=(select dno from student where sname='原野')
and sname!='原野';

select d.dormno,count(sno) from student s
left join dorm d
on s.dormno = d.dormno
group by d.dormno
having count(sno)>1