student 字段：sno sname ssex splace syxid ； yxinfo 字段：yxid yxname yxplace yxtel
要求：
1) 查出“计算机系”的所有学生信息。
select * from student where syxid =(select yxid from yxinfo where yxname = '计算机系');
2) 查出“赵和堂”所在的院系信息。
select * from yxinfo where yxid = (select syxid from student where sname = '赵和堂');
3) 查出在“行政楼”办公的院系名称。
select yxname from yxinfo where yxplace like '行政楼%';
4) 查出男生女生各多少人。
select ssex,count(*) from student group by ssex;
5) 查出人数最多的院系信息。
select * from yxinfo where yxid =(select syxid from student group by syxid order by count(*) desc limit 1);
6) 查出跟“秦奕”同籍贯的所有人。
select sname from student where splace = (select splace from student where sname = '秦奕');
7）查出有“河北”人就读的院系信息。
select * from yxinfo where yxid = (select syxid from student where splace = '河北');
- 学生成绩查询系统。
表名 字段名 全部小写
stu 字段：sno sname ssex cno ； class表字段：cno cname ； score 字段：id sno cno degree
要求：
1) 查询选修了 Oracle 的学生姓名；
1) select s.* from stu as s join score as o on s.sno=o.sno join class as c on c.cno=o.con where c.cname="Oracle";
2) 查询 姜振国 同学选修了的课程名字；
select name from coures where cnoc in (select cno from selclass where sno in (select sno from setudent where sname = '姜振国'));
3) 查询只选修了1门课程的学生学号ID和姓名；
select * from stu where sno in (select sno from score group by sno having count(*)=1);
4) 查询选修了至少3门课程的学生信息；
select * from stu where sno in (select sno from score group by sno having count(*)>=3);