1.Write an SQL script to find any passengers who flew on their birthday.2. Write an SQL script to list the passengers who took at least 4 flights, and order the
number of flights by descending order.第一个问题的表有2个,一个叫passenger,一个叫flew,然后在passenger中有birthdate,记录的是passenger的生日,格式是DD-MM-YY,然后在flew里面有个flightdate,记录的是飞的日期,格式跟birthdate是一样的,但是年份是不同,如何只查寻日期和月份而不查年份呢?第二个问题的表也是两个,passenger和flew,flew里面有flightid,passengerid,而在passenger里面有givenname,familyname,passengerid。怎么去算他总共飞了多少次呢?
number of flights by descending order.第一个问题的表有2个,一个叫passenger,一个叫flew,然后在passenger中有birthdate,记录的是passenger的生日,格式是DD-MM-YY,然后在flew里面有个flightdate,记录的是飞的日期,格式跟birthdate是一样的,但是年份是不同,如何只查寻日期和月份而不查年份呢?第二个问题的表也是两个,passenger和flew,flew里面有flightid,passengerid,而在passenger里面有givenname,familyname,passengerid。怎么去算他总共飞了多少次呢?
select a.* from passenger a where to_char(a.birthday,'MM')='12' and
to_char(a.birthday,'DD')='31'
1.Write an SQL script to find any passengers who flew on their birthday.
select p.*
from passenger p,flew f
where to_char(p.birthday,'mm')=to_char(f.filthtdate,'mm') and
to_char(p.birthday,'dd')=to_char(f.filthtdate,'dd');
with passenger as(
select 1 passengerid,'James' name,to_date('1988-10-12','yyyy-mm-dd') birthdate,'AAA' familiname from dual union all
select 2,'Honna',to_date('1963-12-01','yyyy-mm-dd'),'BBB' from dual union all
select 3,'Imma',to_date('1998-12-12','yyyy-mm-dd'),'VVV' from dual union all
select 4,'Tina',to_date('2010-01-01','yyyy-mm-dd'),'CCC' from dual union all
select 5,'Hebe',to_date('2005-05-25','yyyy-mm-dd'),'ff' from dual union all
select 6,'Oliver',to_date('2001-10-23','yyyy-mm-dd'),'tt' from dual),
flew as(
select 1015 flightid,to_date('2011-10-12','yyyy-mm-dd') flightdate,1 passengerid from dual union all
select 1105,to_date('2011-12-12','yyyy-mm-dd'),1 from dual union all
select 1145,to_date('2011-05-25','yyyy-mm-dd'),3 from dual union all
select 1215,to_date('2011-01-01','yyyy-mm-dd'),4 from dual union all
select 1125,to_date('2011-10-23','yyyy-mm-dd'),6 from dual
)
--
1.Write an SQL script to find any passengers who flew on their birthday.
select p.*
from passenger p,flew f
where p.passengerid=f.passengerid and
to_char(p.birthdate,'mm')=to_char(f.flightdate,'mm') and
to_char(p.birthdate,'dd')=to_char(f.flightdate,'dd');
PASSENGERID NAME BIRTHDATE FAMILINAME
----------- ------ ----------- ----------
1 James 1988-10-12 AAA
4 Tina 2010-1-1 CCC
6 Oliver 2001-10-23 tt
--
第二个问题的表也是两个,passenger和flew,flew里面有flightid,passengerid,
而在passenger里面有givenname,familyname,passengerid。怎么去算他总共飞了多少次呢?
select p.name,count(f.flightid)num_of_flight
from passenger p,flew f
where p.passengerid=f.passengerid
group by p.name
order by num_of_flight;
NAME NUM_OF_FLIGHT
------ -------------
Imma 1
Oliver 1
Tina 1
James 2
select p.passengerid,p.name,p.birthdate,f.flightdate
from passenger p,flew f
where p.passengerid=f.passengerid and
to_char(p.birthdate,'mm')=to_char(f.flightdate,'mm') and
to_char(p.birthdate,'dd')=to_char(f.flightdate,'dd');
PASSENGERID NAME BIRTHDATE FLIGHTDATE
----------- ------ ----------- -----------
1 James 1988-10-12 2011-10-12
4 Tina 2010-1-1 2011-1-1
6 Oliver 2001-10-23 2011-10-23