SQL LeetCode Database Algorithm Problems
Problem 1: Second Highest Salary
The problem states:
“Write the SQL query from the Employee Table to compute the second highest salary”
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+In the case above we should get 200 as the second highest salary. The result would look like:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+“If there are no second highest salary available, then the query should return null.”
My Approach (First Attempt):
I first used SELECT Salary in order to grab the data from the Salary column and then used as SecondHighestSalary to name the table we will be creating. After I stated the column I wanted to grab the data from, I have to state the tables name and so I wrote FROM Employee to state the table I will be grabbing the data from. Last I used WHERE id = 2 since we are trying to find the second highest salary from the chart and that should always be the amount under the id 2.
SELECT Salary as SecondHighestSalary
FROM Employee
WHERE id = 2
Although I past the first testcase, I failed the second because I did not answer for the case when there is no second highest salary thus making my response [] when it should be [null].
Proper Approach:
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
) AS SecondHighestSalary
Here we have the sub-query and limit clause approach in which we use SELECT DISTINCT in order to not populate duplicates on our chart and while still grabbing the data from the Salary column and from the Employee table. Next we would want to ORDER BY Salary in descending order (DESC) and then we LIMIT the number of results to 1 and OFFSET where we want to start grabbing the data. Since we want the second highest salary, we want to start grabbing the data after 1 so we state OFFSET 1 so that it will skip the first row and grab just the second row. After that we wrap our response with SELECT (…) AS SecondHighestSalary so that we can name our response table as SecondHighestSalary.
Problem 2: Combine Two Tables
We have two tables:
Person Table
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.Address Table
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.Proper Approach:
We want to make one table where we have the person’s first and last name as well as the city and state they reside in. We don’t need the address of the person at all. Since both tables share the PersonId in there column name, we will approach this using the outer join method. To do so we must select the column names we want to grab the data from, in this case it will be FirstName, LastName, City, and State. Then we will detail the two tables we would like to join. We use left join because we want all the records from the left table (Person table) and the matching records from the right table (Address table). And since we don’t need all the records from the address table such as AddressId, we will not get an error. So after we state the two table names, we can now set it to a variable by simply writing the variable name following the table name (p & a). Now when we write our join condition, we have to state the matching record which is the PersonId from both tables.
SELECT FirstName, LastName, City, State
FROM Person p left join Address a
ON p.PersonId = a.PersonId
