create database if not exists exam;
use exam;

# 题目1
create table Class(
                      C_ID varchar(12) primary key not null comment '序号',
                      C_NAME varchar(12) comment '班级'
)comment '班级表';

create table Student(
                        S_ID varchar(12) primary key not null comment '序号',
                        C_ID varchar(12) comment '班级序号',
                        S_NAME varchar(10) comment '学生'
)comment '学生表';

insert into class
values (1,'一班'),
       (2,'二班'),
       (3,'三班');
insert into Student
values (1,1,'学生1'),
       (2,1,'学生2'),
       (3,2,'学生3'),
       (4,2,'学生4'),
       (5,2,'学生5');
#题目2
create table t_order(
                        orderID int auto_increment primary key comment 'id',
                        region char(2)  comment '地区',
                        sales varchar(10) not null comment '销售人员',
                        total int unsigned comment '合同金额'
);
insert into t_order
values (null,'A','张三',1000000),
       (null,'A','李四',500000),
       (null,'C','赵五',2000000),
       (null,'A','张三',1500000),
       (null,'C','赵五',1500000),
       (null,'A','张三',500000),
       (null,'B','王六',1000000),
       (null,'B','钱七',800000),
       (null,'B','王六',900000);

#题目3：
create table t_student_class(
                                class_id int primary key AUTO_INCREMENT comment '班级ID',
                                class_name varchar(10) not null comment '班级名称',
                                is_good boolean default false comment '是否优秀班级'
)comment '班级表';
insert into t_student_class
values (null,'101班',true),(null,'102班',true),(null,'103班',false);

create table t_student_info(
                               student_id int primary key AUTO_INCREMENT comment '学生ID',
                               student_name varchar(12) not null comment '学生姓名',
                               student_score tinyint unsigned comment '学生成绩',
                               class_id int comment '所在班级',
                               constraint t_student_info foreign key (class_id)references t_student_class(class_id)
)comment '学生表';

insert into t_student_info
values (null,'A同学',80,1),(null,'B同学',90,1),(null,'C同学',70,1),
       (null,'D同学',90,2),(null,'E同学',80,2),(null,'F同学',60,2),
       (null,'X同学',80,3),(null,'Y同学',90,3),(null,'Z同学',70,3);

# 题目4：
create table USER(
                     NAME varchar(12)  not null comment '姓名',
                     JOB varchar(12) comment '职务',
                     SAL tinyint comment '收入'
)comment '员工数据表';
# 添加数据
insert into USER(NAME,JOB,SAL)
values('张三','教师',100),
      ('张四','教师',101),
      ('张五','厨师',102),
      ('张六','厨师',103),
      ('张七','医生',104);