The article directories

      • 175. Combine two tables
      • 176. Second highest salary

175. Combine two tables

SQL schema Table 1: Person

+ — — — — — — — — — — — — – — — — — — — — — + | column type | | + — — — — — — — — — — — — – — — — — — — — — + | PersonId | int | | FirstName | varchar | | LastName | Varchar | + — — — — — — — — — — — — – — — — — — — — — + PersonId is the table primary key table 2: the Address

+ — — — — — — — — — — — — – — — — — — — — — + | column type | | + — — — — — — — — — — — — – — — — — — — — — + | AddressId | int | | PersonId | int | | City | varchar | | The State | varchar | + — — — — — — — — — — — — – — — — — — — — — + AddressId is above the primary key

Write an SQL query that provides the following information for person based on the above two tables, regardless of whether person has address information: FirstName, LastName, City, and State

select FirstName, LastName, City, State
 from Person left join Address on Person.PersonId = Address.PersonId
Copy the code

176. Second highest salary

Write an SQL query to get the second highest Salary in the Employee table.

– – – – – — — — — — + | | Id Salary | + — – — — — — — — — + | 1 | 100 | | | 200 | 2 | 3 | | + 300 – + — — — — — — — + for example the Employee table above, The SQL query should return 200 as the second highest salary. If no second highest salary exists, the query should return NULL.

+ — — — — — — — — — — — — — — — — — — — — + | SecondHighestSalary | + — — — — — — — — — — — — — — — — — — — — + | 200 | + — — — — — — — — — — — — — — — — — — — — +

select  (select distinct salary    from Employee 
  order by salary desc
  limit 1 offset 1  )    as SecondHighestSalary
Copy the code