sql(3/24)

Combine Two Tables

/* Write your T-SQL query statement below */
SELECT FirstName, LastName, City, State
    FROM Person p
        LEFT JOIN Address a
            ON p.PersonId = a.PersonId

# Write your MySQL query statement below
SELECT FirstName, LastName, City, State
    FROM Person pp
        LEFT JOIN Address aa
            ON pp.PersonId = aa.PersonId

Second Highest Salary

/* Write your T-SQL query statement below */
SELECT MAX(Salary) 'SecondHighestSalary'
    FROM Employee
    WHERE Salary <> (SELECT MAX(Salary) FROM Employee)

# Write your MySQL query statement below
SELECT Max(Salary) 'SecondHighestSalary'
    FROM Employee
    WHERE Salary <> (
        SELECT MAX(Salary) FROM Employee
    )

/* Write your T-SQL query statement below */
-- 如果无第2大salary，返回为空，而不是null, -->max(Salary)
SELECT Salary 'SecondHighestSalary'
    FROM(
        SELECT *, DENSE_RANK() OVER(ORDER BY Salary DESC) rank_
            FROM Employee
    ) T
    WHERE T.rank_ = 2

# Write your MySQL query statement below
SELECT (
    SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
    ) AS 'SecondHighestSalary'

# IFNULL & NULLIF
/* Write your T-SQL query statement below */
SELECT NULLIF(
    (SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY), NULL) AS 'SecondHighestSalary'
# Write your MySQL query statement below
SELECT IFNULL(
    (SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1, 1), NULL) AS 'SecondHighestSalary';

Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        /* Write your T-SQL query statement below. */
        SELECT
            max(DISTINCT Salary)
        FROM
            (SELECT
                *, DENSE_RANK() OVER(ORDER BY Salary DESC) 'rank'
             FROM
                Employee) T
        WHERE
            T.rank=@N
    );
END


CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT
            Salary
      FROM
            Employee
      GROUP BY
            Salary
      ORDER BY
            Salary DESC
      LIMIT N,1
  );
END

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        /* Write your T-SQL query statement below. */
        SELECT
            max(Salary)
        FROM
            Employee
        GROUP BY
            Salary
        ORDER BY
            Salary DESC
        OFFSET @N-1 ROWS FETCH NEXT 1 ROWS ONLY
    );
END