I have problem with my homework - join shows no data

DonDon 0
Viewed 55

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:

DATE_FLY FLY_NR MANUF TYP PER_NR SER_NR TIME_START
06.07.13 LH-888 Boeing B747 9fg-he-ztu8 10010071 11.23
08.10.13 LH-238 Airbus A320 z3et-bwe7 10010072 22.06
13.11.13 LH-341 Boeing B737 ba23-0012 10010001 10.23
14.11.13 LH-358 Boeing B737 ba23-0012 10010001 8.17
13.11.13 LH-553 Boeing B777 xv23-0889 10010002 16.53
15.11.13 LH-421 Boeing B777 xv56-3142 10010002 14.45
17.11.13 LH-789 Airbus A330 45-6789 10010003 8.11
14.11.13 LH-112 Boeing B737 ba23-0034 10010001 8.14
17.11.13 LH-421 Boeing B777 xv23-0889 10010002 16.26
18.11.13 LH-223 Airbus A380 ab-45-6xf 10010004 9.45
19.11.13 LH-634 Airbus A350 5478-awe3 10010005 20.25
18.02.14 LH-238 Airbus A320 z3et-bwe7 10010072 23.06

And this is the fly_table that I need to join:

FLY_NR START_FLY DEST_FLY TIME_FLY KM
LH-341 Saarbruecken Hamburg 1.2 490
LH-358 Saarbruecken Leipzig 1.1 430
LH-553 Leipzig Hamburg .5 290
LH-112 Luxemburg London 1.1 480
LH-421 Luxemburg Ankara 2.5 2300
LH-789 Luxemburg New-York 3.5 8300
LH-223 Frankfurt Dallas 3.9 8600
LH-634 Frankfurt Moskau 2.3 2020
LH-888 Frankfurt Peking 9.5 7780
LH-238 Muenchen Berlin 1.1 479

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:

DATE_FLY FLY_NR MANUF TYP SER_NR
18-NOV-13 LH-223 Airbus A380 10010004

Thank you!

oracle sql
1 Answers

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:

DATE_FLY FLY_NR MANUF TYP SER_NR
18.11.2013 LH-223 Airbus A380 10010004

fiddle

MT00
Related