/*
表1：stu_info (学生基本信息表)：
学号 姓名 班级 年级 专业 出生日期
041010545 杨柳 软件041 04 软件技术 1989-9-9
041010427 杨柳 电商041班 04 电子商务 1988-5-6
041010604 张龙辉 电脑042班 04
041010605 林晓婷 电脑042班 04
041010718 陈小玉 旅游042班 04 旅游
*/
--使用create语句创建上面的两张表，要求如下：
--1. stu_info表“学号”字段，字符型，长度为9，设置主键约束、核查约束（check，要求学号的值长度必须为9，且每位都为0-9的数字）。
--2. stu_info表“姓名”字段不能为空。
--3. stu_info表“出生日期”字段为日期时间类型，且值必须小于当前日期。
--4. stu_JY表“就业日期”字段为日期时间类型。
--其余字段不做要求。
create table stu_info
(
学号 char(9) primary key check(学号 like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
姓名 char(6) not null,
班级 char(20),
年级 char(2),
专业 char(20),
出生日期 datetime check(出生日期 <getdate())
)
go
/*
表2：stu_JY (学生就业信息表)：
学号 姓名 班级 就业单位 岗位 就业日期
NULL 杨柳 软件041 一职 文员 2008-5-5
NULL 杨柳 电商041班 二中 会计 2008-7-30
041010604 张龙辉 电脑042班
041010605 林晓婷 电脑042班
*/
create table stu_jy
(
学号 char(9),
姓名 char(6),
班级 char(20),
就业单位 varchar(50),
岗位 char(20),
就业日期 datetime
)
go
--1. 使用insert语句为两表插入如上所示的数据。
insert into stu_info(学号,姓名, 班级,年级,专业,出生日期)
values
('041010545','杨柳','软件041','04','软件技术','1989-9-9'),
('041010427','杨柳',' 电商041班','04','电子商务','1988-5-6')
insert into stu_info(学号,姓名, 班级,年级)
values
('041010604','张龙辉',' 电脑042班','04'),
('041010605','林晓婷',' 电脑042班','04')
insert into stu_info(学号,姓名, 班级,年级,专业)
values
('041010718','陈小玉',' 旅游042班','04','旅游')
--2. 对照stu_info表将stu_JY表中的学号字段补充完整。
update stu_jy set 学号='041010545' where 姓名='杨柳' and 班级='软件041'
update stu_jy set 学号='041010427' where 姓名='杨柳' and 班级='电商041班'
--3. 将stu_info表班级字段中没有以“班”字结尾的数据，修改成以“班”结尾。
update stu_info
set 班级=rtrim(班级)+'班'
where 班级 not like '%班'
--4. 使用select 语句将stu_JY表中整体复制到stu_JY_BAK中。
select * into stu_JY_BAK
from stu_jy
--5. 删除stu_JY表中的“姓名”、“班级”字段。
alter table stu_jy
drop column 姓名,班级
--6. 修改stu_JY表结构：为学号字段增加外键约束，关联stu_info表中的学号字段。
alter table stu_jy
add constraint fk_1 foreign key(学号) references stu_info(学号)
go
--7. 创建视图v_JY，该视图返回所有学生的基本信息及就业信息。
create view v_jy
as
select stu_info.*,stu_jy.岗位,stu_jy.就业单位,stu_jy.就业日期 from stu_info, stu_jy
where stu_info.学号=stu_jy.学号
go
select * from v_jy
--三、 数据查询（共40分）
--1. 查询stu_info表中的全部数据，按学号的升序排序。
select * from stu_info order by 学号 asc
--2. 查询stu_JY表中没有就业单位的学生的学号。
select 学号 from stu_jy where 就业单位 is null
--3. 查询仍没有就业单位的学生的学号、姓名、班级。
select 学号,姓名,班级
from stu_info
where 学号 in
(
select 学号 from stu_jy where 就业单位 is null
)
--4. 查询在stu_JY表中没有相关记录的学生的学号、姓名、班级、年级、专业、出生日期等信息。
select * from stu_info
where 学号 not in
(
select 学号 from stu_jy
)
--5. 查询所有姓杨的学生的就业信息。
select * from stu_jy
where 学号 in
(
select 学号 from stu_info
where 姓名 like '杨%'
)
--6. 按班级分组，统计每班的学生人数。
select 班级,COUNT(*)
from stu_info
group by 班级
--7. 查询stu_info表中学生来自于哪些班级，不能有重复。
select distinct 班级 from stu_info
--8. 查询视图v_JY中的数据。
select * from v_jy
随时随地看视频
