学习《SQL解惑》

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 )