Query in Oracle to select with subquery

Question

I have the following table on my Oracle database:

CREATE TABLE test
(
 flight NUMBER(4),
 date   DATE,
 action VARCHAR2(50),
 CONSTRAINT pk PRIMARY KEY (flight,date)
);

and the following registers:

BEGIN
 INSERT INTO test VALUES ('1234', '2020-02-29 18:00', 'Departure');
 INSERT INTO test VALUES ('1234', '2020-02-29 19:00', 'Arrival');

 INSERT INTO test VALUES ('4321', '2020-02-20 22:00', 'Departure');
 INSERT INTO test VALUES ('4321', '2020-02-21 04:30', 'Arrival');
END;
/

My problem is that I want to make a SELECT that returns me the flight number only when it has departed and arrived at the same day. For example, flight '1234' has departed on day 29 and arrived on day 29, but in the other hand, the flight '4321' has departed on day 20 and arrived on day 21. I only would like to select the flight '1234', because it is the only one who meets the requeriments.

I have been thinking about the following select, but it doesn't work because the subquery returns more than just one value:

SELECT flight
  FROM test
 WHERE action = 'Departure'
   AND TO_CHAR(date, 'YYYY-MM-DD') = (SELECT TO_CHAR(date, 'YYYY-MM-DD')
                                        FROM test
                                       WHERE action = 'Arrival');

Thank you so much.

Answer 1

You just need distinct action within a day. So, use HAVING count distinctly of two individual actions GROUPed BY flight and day :

SELECT flight
  FROM test
 WHERE action in ('Departure','Arrival')
 GROUP BY flight, trunc("date")
HAVING COUNT(distinct action)=2

where there should be distinct clause, need is observed if the sample data set is extended

Demo

• date is a reserved keyword and cannot be used as a column name for Oracle database. So, I preferred "date"
• Prefix the time literals with timestamp keyword and add portion :00 at the end to represent second such as timestamp'2020-02-21 04:30:00' during the insertion