Problem 175 - Combine Two Tables

Question:

Table: Person

Table: Address

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

Solution

This is a easy question that interferes with LEFT JOIN technique, FYI, please notice the word ON

Problem 176 - Second Highest Salary

Question:

Write a SQL query to get the second highest salary from the Employee table.

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

Solution:

recommended way.

Runtime: faster than 92%

Memory: Less than 100%

other solution which might be slower for using the DISTINCT

Runtime: faster than 39%

Memory: Less than 100%

Problem 178 - Rank Scores

Question:

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

For example, given the above Scores table, your query should generate the following report (order by highest score):

Problem 181 - Employees Earning More Than Their Managers

Question:

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

Solution:

Runtime: faster than 93.96%

Memory: Less than 100%