学生信息、班级信息与所在系信息的相关查询与编辑

项目背景介绍：本次的数据库设计主要涉及到学生信息、班级信息与所在系信息的相关查询与编辑。知识点：关联表中的列必须是主键。外键约束语法如下：语法： foreign key(外键列名称)  references  关联表(关联表的主键); 

任务概述：

具体关系数据库如下：

现要为学校建立一个数据库：学生信息库，学校院系机构设置如下：

一个系有若干个专业，每个专业一年只招生一个班，

每个班有若干个学生，要求建立一个关于系，学生，班级的数据库，

关系模式如下：

学生STUDENT（学号，姓名，年龄，班号）

班CLASS（班号，专业号，系号，入学年份）

系DEPARTMENT（系号，系名）

请用SQL语句完成以下功能： 

(1)、用查询分析器建表，要求声明：

<1> 每个表的主外码。

<2> 每个班级的人数不能超过20人。

<3> 学生的年龄介于16到30之间。

<4> 学生姓名不能为空。

(2)、插入如下数据：

CLASS（

101，软件，002，1995;      

102，微电子，002，1996;

111，无机化学，003，1995；

112　高分子化学，003，1996；

121，统计数学，001，1995；

131，现代语言，004，1996；

141，国际贸易，005，1997；

142，国际金融，005，1996；

）

Student(

  8101，张三，18,101；　8102，钱四，16,121；

　8103，王玲，17,131；　8105，李飞，19,102；

　8109，赵四，18,141；　8110，李可，20,142；

　8201，张飞，18,111；　8302，周瑜，16,112；

　8203，王亮，17,111；　8305，董林，19,102；

　8409，赵龙，18,101；　8510，李丽，20,142）

DEPARTMENT（001，数学；

　002，计算机；

　003，化学；

　004，中文；

　005，经济）

(3)、查询出所有姓李的学生，并按其年龄由小到大排序。

(4)、查询出所有开设超过两个专业的系的名字。

(5)、查询出学生人数大于等于2的系的编号和名称。

(6)、将学生张三转到化学系111班，请更新相关的表。

具体操作如下所示：

特别注意：

创建表

先父表，后子表。

特别注意：
创建表
先父表，后子表。

#系表
create table department
(
  dept_id int(3) not null primary key,
  dept_name varchar(12)
);
#班级表
create table class
(
  class_id int(3) not null primary key,
  spe_name varchar(12) ,
  dept_id int(3),
  stu_date int(4),
  index(dept_id),
  foreign key (dept_id) references department(dept_id)
  on delete cascade
  on update cascade
);
#学生表
create table student
(
  stu_id integer not null primary key,
  name varchar(12) not null,
  age int(2),
  class_id int(3),
  index(class_id),
  foreign key (class_id) references class(class_id)
  on delete no action,
  check(age>15 and age<40)
);

#插入数据先系 班 学生

#插入数据先系 班 学生
insert into department (dept_id,dept_name) values(001,'数学');
insert into department (dept_id,dept_name) values(002,'计算机');
insert into department (dept_id,dept_name) values(003,'化学');
insert into department (dept_id,dept_name) values(004,'中文');
insert into department (dept_id,dept_name) values(005,'经济');

insert into class (class_id,spe_name,dept_id,stu_date) values(101,'软件',002,1995);
insert into class (class_id,spe_name,dept_id,stu_date) values(111,'无机化学',003,1995);
insert into class (class_id,spe_name,dept_id,stu_date) values(121,'数理统计',001,1995);
insert into class (class_id,spe_name,dept_id,stu_date) values(141,'国际贸易',005,1997);
insert into class (class_id,spe_name,dept_id,stu_date) values(102,'微电子',002,1996);
insert into class (class_id,spe_name,dept_id,stu_date) values(112,'高分子化学',003,1996);
insert into class (class_id,spe_name,dept_id,stu_date) values(131,'现代语言',004,1996);
insert into class (class_id,spe_name,dept_id,stu_date) values(142,'国际金融',005,1996);

insert into student (stu_id,name,age,class_id) values(8101,'张三',18,101);
insert into student (stu_id,name,age,class_id) values(8102,'钱四',16,121);
insert into student (stu_id,name,age,class_id) values(8103,'王玲',17,131);
insert into student (stu_id,name,age,class_id) values(8105,'李飞',19,102);
insert into student (stu_id,name,age,class_id) values(8109,'赵四',18,141);
insert into student (stu_id,name,age,class_id) values(8110,'李可',20,142);
insert into student (stu_id,name,age,class_id) values(8201,'张飞',18,111);
insert into student (stu_id,name,age,class_id) values(8302,'周瑜',16,112);
insert into student (stu_id,name,age,class_id) values(8203,'王亮',17,111);
insert into student (stu_id,name,age,class_id) values(8305,'董庆',19,102);
insert into student (stu_id,name,age,class_id) values(8409,'赵龙',18,101);
insert into student (stu_id,name,age,class_id) values(8510,'李丽',20,142);

预览：

(3)、查询出所有姓李的学生，并按其年龄由小到大排序。

 select * from student where name like '%李%' order by age ;

查询结果：

(4)、查询出所有开设超过两个专业的系的名字。

select dept_name from department
 where dept_id in(
                     select dept_id from class group by dept_id  having(count(*)>=2)
                 );

查询结果：

(5)、查询出学生人数大于等于2的系的编号和名称。

select * from department where
 dept_id in(
     select dept_id from class
      where class_id in
      (
          select class_id from student group by class_id having(count(*)>=2)
      )
 );

查询结果：

(6)、将学生张三转到化学系111班，请更新相关的表。

update student set class_id=111 where name='张三';

(7)、学校又新增加了一个物理系，编号为006。

insert into department (dept_id,dept_name) values(006,'物理');