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 )