【SQL解惑】谜题14：电话

解惑一：

1、创建表和插入数据

createtable Personnel

(emp_idinteger primary key,

first_namechar(20)not null,

last_namechar(20)not null)

createtable Phones

(emp_idinteger not null,

phone_typechar(3)not null

           check(phone_typein ('hom','fax')),

phone_nbrchar(12)not null,

primarykey (emp_id,phone_type),

foreignkey (emp_id)references Personnel(emp_id))

insertinto Personnel(emp_id,first_name,last_name)

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

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

(3,'Tim','Gour')

insertinto Phones(emp_id,phone_type,phone_nbr)

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

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

(3,'hom','8845123')

go

2、创建视图

createview FaxPhones (last_name,first_name,emp_id,fax_phone)

asselect e1.last_name,e1.first_name,e1.emp_id,f1.phone_nbr

      from (Personnelas e1

              left outer join

              Phones as f1

              on e1.emp_id= f1.emp_id

              and f1.phone_type= 'fax')

go    

createview HomePhones (last_name,first_name,emp_id,home_phone)

asselect e1.last_name,e1.first_name,e1.emp_id,f1.phone_nbr

      from (Personnelas e1

              left outer join

              Phones as f1

              on e1.emp_id= f1.emp_id

              and f1.phone_type= 'hom')   

go

selecth1.last_name,h1.first_name,home_phone,fax_phone

fromHomePhones as h1,FaxPhonesas f1

whereh1.emp_id= f1.emp_id

go

解惑二：

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

selecte1.last_name,e1.first_name,

        h1.phone_nbras Home,

        f1.phone_nbras Fax

from(Personnelas e1

       left outer join

       Phones as h1

       on e1.emp_id= h1.emp_id

            andh1.phone_type= 'hom')

       left outer join

       Phones as f1

       on e1.emp_id= f1.emp_id

            andf1.phone_type= 'fax'

解惑三：

selecte1.emp_id,e1.first_name,e1.last_name,

        max(casewhen p1.phone_type= 'hom'

                        thenp1.phone_nbr

                        elsenull end)as home_phone,

        max(casewhen p1.phone_type= 'fax'

                        thenp1.phone_nbr

                        elsenull end)as fax_phone

fromPersonnel as e1

leftouter join

Phonesas p1

onp1.emp_id= e1.emp_id

groupby e1.emp_id,e1.first_name,e1.last_name

解惑四：

selectp1.last_name,p1.first_name,

        (selectt1.phone_nbr

             from Phones as t1

            where t1.emp_id= p1.emp_id

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

        (selectt2.phone_nbr

             from Phones as t2

            where t2.emp_id= p1.emp_id

              and t2.phone_type= 'fax')as fax_phone

fromPersonnel as p1