【SQL解惑】谜题6：预订旅馆房间（待完善）

解法二：

--1、

create   table   hotel

(

       room_nbr   integer   not   null,

       arrival_date   date   not   null,

       departure_date   date   not   null,

       guest_name   char ( 30 )   not   null,

        primary   key   ( room_nbr , arrival_date ),

        check   ( departure_date   >=   arrival_date )

)

--2、

create   view   hotelStays ( room_nbr , arrival_date , departure_date , guest_name )

as   select   room_nbr , arrival_date , departure_date , guest_name

from   hotel    as   h1

where   not   exists( select   *   from   hotel   as   h2

                            where   h1 . room_nbr   =   h2 . room_nbr

                                   and   h2 . arrival_date   <   h1 . arrival_date

                                   and   h1 . arrival_date   <   h2 . departure_date )

                                   with   check   option

--3、

insert   into   hotelStays   values ( 1 , '2017-11-9' , '2017-11-10' , 'Coe' )

insert   into   hotelStays   values ( 1 , '2017-11-10' , '2017-11-11' , 'Doe' )

insert   into   hotelStays   values ( 1 , '2017-11-9' , '2017-11-9' , 'Roe' )    --插入失败