关于sql的的数据操作

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 = '河北');

1. 学生成绩查询系统。

表名 字段名 全部小写

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);