Some useful operations in MySQL--Easy level

I am practicing some SQL question to prepare for 2020 summer intership interviews. I learned some useful operations beyond the basic SELECT, FROM, WHERE, GROUP BY and HAVING.

Some useful DATE operations

The date record has data type as DATE in MySQL. It has some special operations.

EX1:

Q: Given a Weather table, find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

1
2
3
4
5
6

SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1 and w2.Temperature > w1.Temperature

### another option ### 
WHERE w1.RecordDate = DATE_SUB(w2.RecordDate, INTERVAL 1 DAY) and  w2.Temperature > w1.Temperature

1

WHERE RecordDate BETWEEN '2015-01-01' and '2015-01-10'

DISTINCT result

About DISTINCT in MySQL, it filters out the replicated selected rows, not just one variale.

Table: friend_request

Table: request_accepted

1
2

SELECT DISTINCT requester_id, accepter_id
FROM request_accepted

The result is

1

[[1, 2], [1, 3], [2, 3], [3, 4]]

Q: Write a query to find the overall acceptance rate of requests rounded to 2 decimals, which is the number of acceptance divide the number of requests.

The highlight of this question is to use IFNULL and ROUND.

1
2

SELECT ROUND(IFNULL(COUNT(DISTINCT requester_id, accepter_id)/COUNT(DISTINCT sender_id, send_to_id), 0), 2) AS accept_rate
FROM friend_request, request_accepted

DELETE deplicate rows

Table Person

1
2
3
4
5
6
7
8

DELETE FROM Person
WHERE Id NOT IN (
    SELECT id from (
        SELECT MIN(Id) as id
        FROM Person
        GROUP BY Email
    ) AS Minp
)

LIMIT and OFFSET

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

1
2
3
4
5
6
7

SELECT (
    SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
) AS SecondHighestSalary