<\/span><\/h2>\n\n\n\nYou are given a table, Functions<\/em>, containing two columns: X <\/em>and Y<\/em>.<\/p>\n\n\n\nColumn<\/th> | Type<\/th><\/tr><\/thead> |
---|
X<\/em><\/td>Integer<\/em><\/td><\/tr>Y<\/em><\/td>Integer<\/em><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Two pairs (X1<\/sub>, Y1<\/sub>)<\/em> and (X2<\/sub>, Y2<\/sub>)<\/em> are said to be symmetric<\/em> pairs<\/em> if X1<\/sub> = Y2<\/sub><\/em> and X2<\/sub> = Y1<\/sub><\/em>.<\/p>\n\n\n\nWrite a query to output all such symmetric<\/em> pairs<\/em> in ascending order by the value of X<\/em>. List the rows such that X1<\/sub> \u2264 Y1<\/sub><\/em>.<\/p>\n\n\n\nSample Input<\/strong><\/p>\n\n\n\nX<\/th> | Y<\/th><\/tr><\/thead> |
---|
20<\/em><\/td>20<\/em><\/td><\/tr>20<\/em><\/td>20<\/em><\/td><\/tr>20<\/em><\/td>21<\/em><\/td><\/tr>23<\/em><\/td>22<\/em><\/td><\/tr>22<\/em><\/td>23<\/em><\/td><\/tr>21<\/em><\/td>20<\/em><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Sample Output<\/strong><\/p>\n\n\n\n20 20\n20 21\n22 23<\/code><\/pre>\n\n\n\n<\/span>Solution – Symmetric Pairs in SQL<\/strong><\/span><\/h2>\n\n\n\n<\/span>MySQL<\/strong><\/span><\/h3>\n\n\n\nSELECT X,\n Y\nFROM FUNCTIONS F1\nWHERE EXISTS\n (SELECT *\n FROM FUNCTIONS F2\n WHERE F2.Y = F1.X\n AND F2.X = F1.Y\n AND F2.X > F1.X)\n AND (X != Y)\nUNION\nSELECT X,\n Y\nFROM FUNCTIONS F1\nWHERE X = Y\n AND (\n (SELECT COUNT(*)\n FROM FUNCTIONS\n WHERE X = F1.X\n AND Y = F1.X) > 1)\nORDER BY X;<\/pre>\n\n\n\n
| | | | | | | | | | | |
| | | |