Some Queries like 15 mints delay

SELECT arr.flight_no,ROUND(TO_NUMBER((TO_DATE(arr.arr_date || ' ' || '0010',
                                'DD-mon-yyyy HH24MI') -
                       TO_DATE(arr.arr_date || ' ' || '0520',
                                'DD-mon-yyyy HH24MI')) * 1440)) DELAY
  FROM tc_arrival_log arr
 where arr.arr_date = '18/May/2011'



-------------------------------------------------
select arr.flight_no,arr.arr_date,arr.delay,
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''')|| '-'||
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''') "Sector" 
from tc_arrival_log arr
where arr.arr_date between '01/Jan/1999' and '05/May/2011' and arr.delay>15 
and arr.delay in(select count(*) from tc_arrival_log tal  
where tal.arr_date between '01/Jan/1999' and '05/May/2011' and tal.delay>15 )
-------------------------------------------------
 SELECT DISTINCT ROUND(TO_NUMBER((TO_DATE(SYSDATE || ' ' || '0440', 'DD-mon-yyyy HH24MI') -
                                  TO_DATE(SYSDATE || ' ' || '0359', 'DD-mon-yyyy HH24MI'))
                                  * 1440)) DELAY
 FROM DUAL

---------------------------------------------------------------------------------------------------
select arr.flight_no,arr.arr_date,arr.delay,
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''')|| '-'||
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''') "Sector", ( select count(delay),arr.ata,arr.sta, "Delay Count" from tc_arrival_log where flight_no=arr.flight_no and arr_date between '01/Jan/1999' and '05/may/2011' and arr.ata between '0000' and '2400' group by flight_no  having count(delay)>15) 
from tc_arrival_log arr where arr.arr_date between '01/Jan/1999' and '05/May/2011' and arr.ata between '0000' and '2400' and arr.delay>15 order by arr.flight_no

select dep.flight_no,dep.dep_date,dep.delay,
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''')|| '-'||
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''') "Sector",(select count(delay) "Delay Count" from tc_departure_log where flight_no=dep.flight_no and dep_date between '01/Jan/1999' and '05/may/2011' group by flight_no  having count(delay)>=15) 
from tc_departure_log dep where dep.dep_date between '01/Jan/1999' and '05/May/2011' and dep.delay>='15'

select arr.flight_no,arr.arr_date,arr.sta,arr.ata,
ROUND(TO_NUMBER((TO_DATE(arr.arr_date || ' ' || '','DD-mon-yyyy HH24MI') - TO_DATE(arr.arr_date || ' ' || '','DD-mon-yyyy HH24MI')) * 1440))delay
from tc_arrival_log arr where arr.arr_date between '01/May/2000' and '30/May/2011'
and arr.flight_no = 'KFR2476' order by arr.arr_date;

select dep.dep_date,dep.dtd,dep.atd,dep.startuptime,
ROUND(TO_NUMBER((TO_DATE(dep.dep_date || ' ' || '','DD-mon-yyyy HH24MI') - TO_DATE(dep.dep_date || ' ' || '','DD-mon-yyyy HH24MI')) * 1440))delay
from tc_departure_log dep where dep.dep_date between '01/May/2000' and '30/May/2011'
and dep.flight_no = 'AIC517' order by dep.dep_date
  
 ------------------------------------------------------------------------------------------------------
  
SELECT arr.flight_no FROM TC_ARRIVAL_LOG arr WHERE arr.delay in (select count(*) from tc_arrival_log  where delay>15)

select * from tc_departure_log dl where  dl.flight_no='AIC543' and dl.dep_date='19/may/2011'
=================================================================================================

select arr.flight_no,get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''')|| '-'||
get_description ('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''') "Sector",(select count(delay) "Delay Count" from tc_arrival_log where flight_no=arr.flight_no and arr_date between '01/Jan/1999' and '05/may/2011' and arr.ata between '0000' and '2400' group by flight_no  having count(delay)>15),
(select sum(delay) "Sum" from tc_arrival_log where flight_no=arr.flight_no and delay>=15 and arr_date between '01/May/1200' and '01/May/2019' group by flight_no)
  from tc_arrival_log arr where arr.arr_date between '01/Jan/1999' and '05/May/2011' and arr.ata between '0000' and '2400' and arr.delay>15 order by arr.flight_no

select dep.flight_no,dep.dep_date,dep.delay,
get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''')|| '-'||
get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''') "Sector",(select count(delay) "Delay Count" from tc_departure_log where flight_no=dep.flight_no and dep_date between '01/Jan/1999' and '05/may/2011' and dep.atd between '0000' and '2400' group by flight_no  having count(delay)>=15) 
from tc_departure_log dep where dep.dep_date between '01/Jan/1999' and '05/May/2011' and dep.atd between '0000' and '2400' and dep.delay=''

select dep.flight_no,get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''')|| '-'||
get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD = ''' || 'VOHY' || '''') "Sector",(select count(delay) "Delay Count" from tc_departure_log where flight_no=dep.flight_no and dep_date between '01/Jan/1999' and '05/may/2011' and dep.atd between '0000' and '2400' group by flight_no  having count(delay)>=15),dep.dep_date,dep.delay
from tc_departure_log dep where dep.dep_date between '01/Jan/1999' and '05/May/2011' and dep.atd between '0000' and '2400' and dep.delay=''


///Average finding functions

select avg(select sum(delay) from tc_arrival_log arr where flight_no='AIC517' and arr_date between '01/May/2011' and '30/may/2011')/
(select count(*) from tc_arrival_log arr where arr.delay>=15) from dual

select distinct avg(sum(arr.delay)) from tc_arrival_log arr where arr.flight_no='AIC517' and arr.arr_date between '01/May/2000' and '30/may/2011' group by arr.delay
select distinct count(*) from tc_arrival_log arr where flight_no='AIC517' and arr_date between '01/May/2000' and '30/May/2011'

select distinct arr.flight_no,get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''VIDP''')|| '-'||get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''' || 'VIDP' || '''') "Sector",arr.delay,(select count(delay)  from  tc_arrival_log  where flight_no=arr.flight_no and arr_date between  '05/MAY/1980' and '30/MAY/2011' group by flight_no having count(delay)>=15) from tc_arrival_log arr where arr.arr_date between '05/MAY/1980' and '30/MAY/2011' and arr.delay>15 order by arr.flight_no


select arr.flight_no,
       get_description('SECTOR_CODE',
                       'gf_sector_mt',
                       'LOCATION_CD=''VOHY''') || '-' ||
       get_description('SECTOR_CODE',
                       'gf_sector_mt',
                       'LOCATION_CD=''' || 'VOHY' || '''') "Sector",
       arr.delay,(select avg(sum(delay)) from tc_arrival_log arr where flight_no=arr.flight_no and arr_date between '01/JAN/1900' and '01/JAN/2012' group by arr.flight_no) "Average",
       (select count(delay)
          from tc_arrival_log
         where flight_no = arr.flight_no
           and arr_date between '01/JAN/1900' and '01/JAN/2012'
         group by flight_no
        having count(delay) >= 15) "Delay Count"
  from tc_arrival_log arr
 where arr.arr_date between '01/JAN/1900' and '01/JAN/2012'
   and arr.delay > 15
 order by arr.flight_no

 SELECT * FROM TC_ARRIVAL_LOG T WHERE T.FLIGHT_NO='IAC839' AND T.DELAY>15 AND T.ARR_DATE BETWEEN '01/May/2000' AND '01/May/2019'

select dep.dep_date,dep.dtd,dep.atd,dep.startuptime,ROUND(TO_NUMBER((TO_DATE(dep.dep_date || ' ' || '','DD-mon-yyyy HH24MI') - TO_DATE(dep.dep_date || ' ' || '','DD-mon-yyyy HH24MI')) * 1440))delay from tc_departure_log dep where dep.dep_date between '01/JAN/1900' and '01/JAN/2014' and dep.flight_no = 'AIC515' order by dep.dep_date
select arr.flight_no,get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''VOHY''')|| '-'||get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''' || 'VOHY' || '''') "Sector",
(select count(delay) from tc_arrival_log  where flight_no like '%IAC839%' and arr_date between  '01/Jan/1200' and '01/May/2014' having count(delay)>15)
from tc_arrival_log arr where arr.arr_date between '01/Jan/1900' and '01/May/2014' and arr.delay>15 order by arr.flight_no

select arr.flight_no,get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''VOHY''')|| '-'||get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''' || 'VOHY' || '''') "Sector",arr.delay,(select count(delay)  from  tc_arrival_log  where flight_no LIKE arr.flight_no and arr_date between '01/JAN/1200' and '01/JAN/2014' and delay>=15 )  from tc_arrival_log arr where arr.arr_date between '01/JAN/1200' and '01/JAN/2014' and arr.delay>15 order by arr.flight_no

select arr.flight_no,get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''VOHY''')|| '-'||get_description('SECTOR_CODE','gf_sector_mt','LOCATION_CD=''' || 'VOHY' || '''') "Sector",(select count(delay) "Delay Count" from  tc_arrival_log  where flight_no=arr.flight_no and arr_date between '01/JAN/1200' and '01/JAN/2014' and delay>=15) from tc_arrival_log arr where arr.arr_date between '01/JAN/1200' and '01/JAN/2014' and arr.delay>15 order by arr.flight_no


SELECT count(delay) from  TC_ARRIVAL_LOG T WHERE T.FLIGHT_NO='IAC839' AND T.DELAY>15 AND T.ARR_DATE BETWEEN '01/May/2000' AND '01/May/2019'


SELECT COUNT(DELAY)
FROM VOHY.TC_ARRIVAL_LOG T
Where
ARR_DATE BETWEEN '01/May/1900' AND '01/May/2014'
 AND DELAY >= 15
 AND FLIGHT_NO LIKE 'IAC839'


 select sum(tasum.delay) from tc_arrival_log tasum where tasum.flight_no='5YSIR' and tasum.delay>=15 and tasum.arr_date between '01/May/1200' and '01/May/2019' group by tasum.flight_no
 select count(tac.delay) from tc_arrival_log tac where tac.flight_no='5YSIR' and tac.delay>=15 and tac.arr_date between '01/May/1200' and '01/May/2019' group by tac.flight_no

 select sum(ta.delay)) from tc_arrival_log ta where ta.flight_no='5YSIR' and ta.delay>=15 and ta.arr_date between '01/May/1200' and '01/May/2019' group by ta.flight_no