【SQL解惑】谜题14：电话

解惑一：

1、创建表和插入数据

create table Personnel

(emp_id integer primary key,

first_name char(20) not null,

last_name char(20) not null)

create table Phones

(emp_id integer not null,

phone_type char(3) not null

           check(phone_type in ('hom','fax')),

phone_nbr char(12) not null,

primary key (emp_id,phone_type),

foreign key (emp_id) references Personnel(emp_id))

insert into Personnel(emp_id,first_name,last_name)

values(1,'La','Done'),

(2,'Guo','Yin'),

(3,'Tim','Gour')

insert into Phones(emp_id,phone_type,phone_nbr)

values(1,'hom','8845123'),

(2,'hom','8845123'),

(3,'hom','8845123')

go

2、创建视图

create view FaxPhones (last_name,first_name,emp_id,fax_phone)

as select e1.last_name,e1.first_name,e1.emp_id,f1.phone_nbr

       from (Personnel as e1

               left outer join

               Phones as f1

               on e1.emp_id = f1.emp_id

               and f1.phone_type = 'fax')

go    

create view HomePhones (last_name,first_name,emp_id,home_phone)

as select e1.last_name,e1.first_name,e1.emp_id,f1.phone_nbr

       from (Personnel as e1

               left outer join

               Phones as f1

               on e1.emp_id = f1.emp_id

               and f1.phone_type = 'hom')   

go

select h1.last_name,h1.first_name,home_phone,fax_phone

from HomePhones as h1,FaxPhones as f1

where h1.emp_id = f1.emp_id

go

解惑二：

1、from子句中，从Personel表中，左外连接Phones表，匹配emp_id和phone_type标识

select e1.last_name,e1.first_name,

         h1.phone_nbr as Home,

         f1.phone_nbr as Fax

from (Personnel as e1

        left outer join

        Phones as h1

        on e1.emp_id = h1.emp_id

            and h1.phone_type = 'hom')

        left outer join

        Phones as f1

        on e1.emp_id = f1.emp_id

            and f1.phone_type = 'fax'

解惑三：

select e1.emp_id,e1.first_name,e1.last_name,

         max(case when p1.phone_type = 'hom'

                        then p1.phone_nbr

                        else null end) as home_phone,

         max(case when p1.phone_type = 'fax'

                        then p1.phone_nbr

                        else null end) as fax_phone

from Personnel as e1

left outer join

Phones as p1

on p1.emp_id = e1.emp_id

group by e1.emp_id,e1.first_name,e1.last_name

解惑四：

select p1.last_name,p1.first_name,

         (select t1.phone_nbr

              from Phones as t1

             where t1.emp_id = p1.emp_id

               and t1.phone_type = 'hom') as home_phone,

         (select t2.phone_nbr

              from Phones as t2

             where t2.emp_id = p1.emp_id

               and t2.phone_type = 'fax') as fax_phone

from Personnel as p1