[HackerRank][SQL(Oracle)] Symmetric Pairs

 

※  You are given a table, Functions, containing two columns: X and Y.

 

■  [Advanced Join]  Symmetric Pairs

https://www.hackerrank.com/challenges/symmetric-pairs/problem?isFullScreen=true 

Q.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

A.

SELECT		DISTINCT F1.X, F1.Y
FROM		(SELECT ROWNUM RN, X, Y FROM FUNCTIONS) F1
			,(SELECT ROWNUM RN, X, Y FROM FUNCTIONS) F2
WHERE		F1.X = F2.Y AND F2.X = F1.Y AND F1.X <= F1.Y AND F1.RN <> F2.RN;

SELECT		DISTINCT F1.X, F1.Y
FROM		(SELECT ROWNUM RN, X, Y FROM FUNCTIONS) F1	JOIN
			(SELECT ROWNUM RN, X, Y FROM FUNCTIONS) F2	ON F1.X = F2.Y AND F2.X = F1.Y AND F1.RN <> F2.RN
WHERE		F1.X <= F1.Y;

(+)

SELECT 		DISTINCT F1.X, F1.Y
FROM 		FUNCTIONS F1, FUNCTIONS F2
WHERE		F1.X = F2.Y AND F2.X = F1.Y
GROUP BY 	F1.X, F1.Y
HAVING 		(F1.X = F1.Y AND COUNT(*) > 1) OR F1.X < F1.Y;

SELECT 		DISTINCT F1.X, F1.Y
FROM 		FUNCTIONS F1 JOIN FUNCTIONS F2	ON F1.X = F2.Y AND F2.X = F1.Y
GROUP BY 	F1.X, F1.Y
HAVING 		(F1.X = F1.Y AND COUNT(*) > 1) OR F1.X < F1.Y;

SELECT 		X, Y
FROM 		FUNCTIONS
GROUP 		BY X, Y
HAVING 		X = Y AND COUNT(*) > 1
UNION
SELECT 		DISTINCT F1.X, F1.Y
FROM 		FUNCTIONS F1 JOIN FUNCTIONS F2	ON F1.X = F2.Y AND F2.X = F1.Y
GROUP BY 	F1.X, F1.Y
HAVING 		F1.X < F1.Y;

SELECT	X, Y
FROM	(
			SELECT 		F1.X, F1.Y, COUNT(*) AS CNT
			FROM		FUNCTIONS F1 JOIN FUNCTIONS F2	ON F1.X = F2.Y AND F2.X = F1.Y
			GROUP BY	F1.X, F1.Y
		)
WHERE	(X = Y AND CNT > 1) OR X < Y;