第二题表
#新建学生表
drop table if exists setudent;
create table setudent(
sno int(10) not null primary key comment '学号',
sname varchar(20) not null comment '姓名',
ssex varchar(10) not null comment '性别'
);
#给学生表添加数据
insert into setudent values(1,'姜振国','男'),(2,'赵书文','男'),(3,'芮思涵','女'),(4,'余浩然','男');
#新建课程表
drop table if exists coures;
create table coures(
cnoc int(10) not null primary key comment '课程ID',
name varchar(30) not null comment '课程名称'
);
#给课程表添加数据
insert into coures values(1,'java'),(2,'oracle'),(3,'js'),(4,'jquery');
#新建选课表
drop table if exists selclass;
create table selclass(
selno int(10) not null primary key auto_increment comment '选课ID',
sno int(10) not null comment '学生ID',
cno int(10) not null comment '课程ID',
count int(10) not null comment '成绩'
);
#给选课表添加数据
insert into selclass values(1,1,1,88),(2,1,2,77),(3,2,1,78),(4,2,2,91),(5,3,1,55),(6,3,2,65),(7,3,3,75),(10,4,3,74),(9,4,4,64);
第一题表
drop table if exists student;
create table student(
sno int not null primary key comment'学生ID',
sname varchar(20) not null comment'姓名',
ssex varchar(20) not null comment'性别',
splace varchar(20) not null comment'籍贯',
syxid varchar(20) not null comment'院系ID'
);
drop table if exists yxinfo;
create table yxinfo(
yxid int not null primary key comment'院系ID',
yxname varchar(20) not null comment'院系名称',
yxplace varchar(20) not null comment'地址',
yxphone varchar(20) not null comment'联系电话'
);
insert into student values
('1','温惠青','女','江苏','1'),
('2','赵和堂','男','重庆市','2'),
('3','赵修平','男','河北','1'),
('4','秦奕','男','福建','3'),
('5','何灵泉','女','福建','3'),
('6','周海龙','男','山东','1');
insert into yxinfo values
('1','计算机系','科研楼608','0533-2168068'),
('2','数学系','行政楼203','0533-2157068'),
('3','物理系','科研楼607','0533-3153606');
第一题
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 ssex,count(*) from student where syxid = (select syxid from student group by syxid order by count(*) desc limit 1) group by ssex;
7.查出跟'秦奕'同籍贯的所有人
select sname from student where splace = (select splace from student where sname = '秦奕');
8.查出'河北'人就读的院系信息
select * from yxinfo where yxid = (select syxid from student where splace = '河北');
9.查出跟'福建女生' 同院系的所有学生信息；
select * from student where syxid = (select syxid from student where splace = '福建' and ssex = '女');
第二题
1.查询选修了'oracle'的学生姓名
select sname from setudent where sno in(select sno from selclass where cno in (select cnoc from coures where name = 'oracle'));
2.查询 姜振国 同学选修了的课程名称
select name from coures where cnoc in (select cno from selclass where sno in (select sno from setudent where sname = '姜振国'));
3.查询只选修了一门课的学生学号和姓名；
select sno,sname from setudent where setudent.sno in (select selclass.sno from selclass group by selclass.sno having count(*) = 1);
4.查询选修了至少3门课程的学生信息
select * from setudent where setudent.sno in (select selclass.sno from selclass group by selclass.sno having count(*) >= 3);
5.查询选修了所有课程的学生
select * from setudent where setudent.sno in (select selclass.sno from selclass group by selclass.sno having count(*) =(select count(*) from coures));
6.查询选修课程的学生人数
select cno,count(*) from selclass group by selclass.cno;
7.查询所学课程至少有一门跟姜振国所学课程相同的学生信息
select setudent.sno,sname,ssex from setudent join selclass on setudent.sno = selclass.sno where selclass.cno in (select selclass.cno from selclass join setudent on setudent.sno = selclass.sno where sname = '姜振国');
8.查询两门及两门以上不及格同学的平均分
select sname,avg(count) from selclass a join setudent on setudent.sno = a.sno where a.sno = (select b.sno from selclass b where count < 60 group by b.sno having count(*) >=2) group by a.sno;