It's me again - I have a new homework and I don't get any further.

The task is:

Find out the date of flight(DATE_FLY), flight number(FLY_NR), manufacturer(MANUF), type (TYP) and serial number (SER_NR) of all flights that fly from Frankfurt to Dallas between 10.11.2013 and 20.11.2013.

This is the departure_table:

And this is the fly_table that I need to join:

I wrote the following query but it showed me "no data found":

select 
    dp.date_fly, dp.fly_nr, dp.manuf, dp.typ, dp.ser_nr
from
    departure_table dp, fly_table fl
where 
    dp.fly_nr = fl.fly_nr
    and start_fly = 'Frankfurt'
    and dest_fly = 'Dallas'
    and dp.date_fly between 10.11.2013 and 20.11.2013;

I've tried a simple join but I still only got the message "no data found":

select 
    dp.date_fly, dp.fly_nr, dp.manuf, dp.typ, dp.ser_nr
from 
    departure_table dp, fly_table fl
where 
    dp.fly_nr = fl.fly_nr;

I don't know what am I doing wrong... I would be very thankful if somebody could help to solve this problem with the join function.

I need to get this result:

Thank you!

CodePudding user response：

The column FLY_DATE has a VARCHAR2 format

This is bad practice. If you have a date then store it as a date. If you have to store it as a string (really, don't do that) then store it using ISO8601 formatting YYYY-MM-DD which can be sorted alphabetically into date-order (and not DD-MM-YYYY which cannot be easily sorted).

Since you have it in a (mostly unusable) string format, you need to convert it to a DATE using the TO_DATE function and compare it to DATE literals:

select dp.date_fly,
       dp.fly_nr,
       dp.manuf,
       dp.typ,
       dp.ser_nr
from   departure_table dp
       INNER JOIN fly_table fl
       ON dp.fly_nr = fl.fly_nr
where  start_fly = 'Frankfurt'
and    dest_fly = 'Dallas'
and    TO_DATE(dp.date_fly, 'DD.MM.YYYY') between DATE '2013-11-10' and DATE '2013-11-20';

Which, for the sample data:

CREATE TABLE departure_table (DATE_FLY, FLY_NR, MANUF, TYP, PER_NR, SER_NR, TIME_START) AS
SELECT '06.07.2013', 'LH-888', 'Boeing', 'B747', '9fg-he-ztu8', 10010071, 11.23 FROM DUAL UNION ALL
SELECT '08.10.2013', 'LH-238', 'Airbus', 'A320', 'z3et-bwe7',   10010072, 22.06 FROM DUAL UNION ALL
SELECT '13.11.2013', 'LH-341', 'Boeing', 'B737', 'ba23-0012',   10010001, 10.23 FROM DUAL UNION ALL
SELECT '14.11.2013', 'LH-358', 'Boeing', 'B737', 'ba23-0012',   10010001,  8.17 FROM DUAL UNION ALL
SELECT '13.11.2013', 'LH-553', 'Boeing', 'B777', 'xv23-0889',   10010002, 16.53 FROM DUAL UNION ALL
SELECT '15.11.2013', 'LH-421', 'Boeing', 'B777', 'xv56-3142',   10010002, 14.45 FROM DUAL UNION ALL
SELECT '17.11.2013', 'LH-789', 'Airbus', 'A330', '45-6789',     10010003,  8.11 FROM DUAL UNION ALL
SELECT '14.11.2013', 'LH-112', 'Boeing', 'B737', 'ba23-0034',   10010001,  8.14 FROM DUAL UNION ALL
SELECT '17.11.2013', 'LH-421', 'Boeing', 'B777', 'xv23-0889',   10010002, 16.26 FROM DUAL UNION ALL
SELECT '18.11.2013', 'LH-223', 'Airbus', 'A380', 'ab-45-6xf',   10010004,  9.45 FROM DUAL UNION ALL
SELECT '19.11.2013', 'LH-634', 'Airbus', 'A350', '5478-awe3',   10010005, 20.25 FROM DUAL UNION ALL
SELECT '18.02.2014', 'LH-238', 'Airbus', 'A320', 'z3et-bwe7',   10010072, 23.06 FROM DUAL;

CREATE TABLE fly_table (FLY_NR, START_FLY, DEST_FLY, TIME_FLY, KM) AS
SELECT 'LH-341', 'Saarbruecken', 'Hamburg',  1.2,  490 FROM DUAL UNION ALL
SELECT 'LH-358', 'Saarbruecken', 'Leipzig',  1.1,  430 FROM DUAL UNION ALL
SELECT 'LH-553', 'Leipzig',      'Hamburg',   .5,  290 FROM DUAL UNION ALL
SELECT 'LH-112', 'Luxemburg',    'London',   1.1,  480 FROM DUAL UNION ALL
SELECT 'LH-421', 'Luxemburg',    'Ankara',   2.5, 2300 FROM DUAL UNION ALL
SELECT 'LH-789', 'Luxemburg',    'New-York', 3.5, 8300 FROM DUAL UNION ALL
SELECT 'LH-223', 'Frankfurt',    'Dallas',   3.9, 8600 FROM DUAL UNION ALL
SELECT 'LH-634', 'Frankfurt',    'Moskau',   2.3, 2020 FROM DUAL UNION ALL
SELECT 'LH-888', 'Frankfurt',    'Peking',   9.5, 7780 FROM DUAL UNION ALL
SELECT 'LH-238', 'Muenchen',     'Berlin',   1.1,  479 FROM DUAL;

Outputs:

fiddle