学习《SQL解惑》
来源:互联网 发布:九天封神翅膀进阶数据 编辑:程序博客网 时间:2024/05/16 14:14
PUZZLE ONE
1、integer , date ,extract
2、constraint ( ) check ( )
code:
create table fiscalyeartable1
(fiscal_year integer not null primary key,
start_date date not null,
constraint valid_start_date
check(
(extract(year from start_date)=fiscal_year - 1
) and
(extract(month from start_date)=10
) and
(extract(day fromstart_date)=1)
),
end_date date not nullL,
constraint valid_end_date
check(
(extract(year from start_date)=fiscal_year - 1
) and
(extract(month from start_date)=08
) and
(extract (day from start_date) = 30)
)
)
PUZZLE TWO
外来键 reference tabel_name (column_name)
create table absenteeism
(emp_id integer not null references personnel(emp_id),
absent_date date not null,
reason_code char(40) not null references excuselist(reason_code),
severity_points integer not null check(severity_points between 1 and 4);
primary key (emp_id,absent_date)
);
update absenteeism
set severity_points = 0,
reason_code = 'long term illness'
where exists
(select *
from absenteeism as a2
where absenteeism.emp_id = a2.emp_id
and absenteeism.absent_date = (a2.absent_date + interval '1' day)
);
delete from personnel
where emp_id = (select a1.emp_id
from absenteeism as a1
where a1.emp_id = personnel.emp
group by a1.emp_id
having sum (severity_points) >= 40);
(思路:select emp_id,sum(severity_points)
from absenteeism
group by emp_id;
再对sum(severity_points)进行筛选,
总结:对集合函数进行筛选可以使用having )
- 学习《SQL解惑》
- SQL解惑
- 《Java 解惑》学习笔记
- 编程学习-解惑1
- SQL解惑(第2版)
- 【SQL解惑】谜题13:教师
- 【SQL解惑】谜题14:电话
- 【解惑】关于数据结构的学习
- 【解惑】计算机学习速成法
- 【解惑】计算机学习速成法
- 【解惑】关于数据结构的学习
- java解惑学习阶段1
- 机器学习的解惑笔记
- 解惑
- 解惑
- 解惑
- 【解惑】
- 解惑
- 问题 : 打牌
- HDU6103
- 大疆无人机视频恢复成功
- 搭建spring boot集成mybatis项目入门
- flume小白入门篇一
- 学习《SQL解惑》
- ssm分布式框架电商项目(二)
- 对象1(封装)
- HDU 1039-Easier Done Than Said?(字符串)
- ViewPager的无限轮播
- 关于两个jre,三个lib讲解!!
- Unity3D Shader(10)——Fragment Shader(漫反射、phong效果、blinnPhong效果、阴影、halfLambert、渐变纹理)
- js 获取select CheckBox
- 一大波水仙花来袭! ! !