SQLs From MySQL To Oracle

GROUP BY + LIMIT

SELECT COUNT(*) AS TOTAL, GENDER FROM MEMBERS_MERGE GROUP BY GENDER HAVING TOTAL > 0 ORDER BY GENDER DESC LIMIT 1, 1;

SELECT SUBQUERY2.* FROM (
  SELECT SUBQUERY1.*, ROWNUM AS SUBQUERY1_ROWNUM FROM (
    SELECT GENDER, COUNT(*) AS TOTAL FROM MEMBERS_MERGE GROUP BY GENDER HAVING
    TOTAL > 0 ORDER BY GENDER ASC
  ) SUBQUERY1 WHERE ROWNUM <= 2
) SUBQUERY2 WHERE SUBQUERY1_ROWNUM >= 2;

ORDER BY + LIMIT

SELECT * FROM MEMBERS_MERGE WHERE DEPARTMENT > 0 ORDER BY ID DESC LIMIT 1, 5;

SELECT SUBQUERY2.* FROM (
  SELECT SUBQUERY1.*, ROWNUM AS SUBQUERY1_ROWNUM FROM (
    SELECT * FROM MEMBERS_MERGE WHERE DEPARTMENT > 0 ORDER BY ID DESC
  ) SUBQUERY1 WHERE ROWNUM <= 6
) SUBQUERY2 WHERE SUBQUERY1_ROWNUM >= 2;

JOIN

SELECT A.NAME AS ANAME, B.ID AS BID FROM TABLE1 AS A INNER JOIN TABLE2 AS B ON A.ID = B.ID;

SELECT A.NAME AS ANAME, B.ID AS BID FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID;

INSERT IGNORE INTO

INSERT IGNORE INTO TEMP (MEMBER_ID, TYPE) VALUES (6, 3);

MERGE INTO TEMP T
  USING (SELECT 6 AS MEMBER_ID, 3 AS TYPE FROM DUAL) D
  ON (T.MEMBER_ID = D.MEMBER_ID)
WHEN NOT MATCHED THEN
  INSERT (MEMBER_ID, TYPE) VALUES (D.MEMBER_ID, D.TYPE);

INSERT INTO... ON DUPLICATE KEY UPDATE

INSERT INTO TEMP (ID, FULL_NAME, AGE) VALUES (5, 'Vera Farmiga', 33) ON DUPLICATE KEY UPDATE AGE = AGE + 1;

MERGE INTO TEMP T
  USING (SELECT 5 AS ID, 'Vera Farmiga' AS FULL_NAME, 33 AS AGE) D
  ON (T.ID = D.ID)
WHEN MATCHED THEN
  UPDATE SET T.AGE = T.AGE + 1
WHEN NOT MATCHED THEN
  INSERT (ID, FULL_NAME, AGE) VALUES (5, 'Vera Farmiga', 33);