---
# System prepended metadata

title: SQL

---

# SQL

## [leetcode] Combine Two Tables
```sql=
select FirstName, LastName, City, State
From Person left join Address
on Person.PersonId = Address.PersonId
```

## [leetcode] Second Highest Salary
```sql=
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
```
或是LIMIT[1,1]
LIMIT 後面接[index, count]有些SQL語言不支援


## [leetcode] Nth Highest Salary
```sql=
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
      IFNULL((SELECT DISTINCT SALARY 
      FROM EMPLOYEE
      ORDER BY SALARY DESC
      LIMIT 1 OFFSET N), NULL)
  );
END

```

## [leetcode] Employees Earning More Than Their Managers
```sql=
select a.Name as 'Employee'
FROM Employee AS a ,Employee as b
WHERE a.ManagerId = b.ID AND a.Salary > b.Salary
```