Problem

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

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

即找到与前一日相比温度升高的条目Id。

MySQL

# Write your MySQL query statement below
SELECT w1.Id FROM Weather w1, Weather w2 WHERE w1.Temperature > w2.Temperature AND datediff(w1.date,w2.date) =1;

分析

主要是掌握MySQL的日期函数。

Problem

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

删除邮件重复的记录，保留Id最小的

MySQL

DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

分析

无

Problem

How would you print just the 10th line of a file?

For example, assume that file.txt has the following content:

Line 1
Line 2
Line 3
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10

Your script should output the tenth line, which is:

Line 10

即输出第十行，又是bash题。

Bash

sed -n 10p file.txt

分析

没什么可说的。题目要求3种以上解法。不过对于bash题，暂时以学习了解为主。

Problem

Given a text file file.txt that contains list of phone numbers (one per line), write a one liner bash script to print all valid phone numbers.

You may assume that a valid phone number must appear in one of the following two formats: (xxx) xxx-xxxx or xxx-xxx-xxxx. (x means a digit)

You may also assume each line in the text file must not contain leading or trailing white spaces.

For example, assume that file.txt has the following content:

987-123-4567
123 456 7890
(123) 456-7890

Your script should output the following valid phone numbers:

987-123-4567
(123) 456-7890

即从文件中找到合法的电话号码。又进入bash题区了。

Bash

grep -P '^(\d{3}-|\(\d{3}\) )\d{3}-\d{4}$' file.txt

分析

没啥可说的，正则题而已。唯一需要注意的是，这里用的是\d ，所以需要-P支持。

Problem

Write a function that takes an unsigned integer and returns the number of ’1’ bits it has (also known as the Hamming weight).

For example, the 32-bit integer ’11’ has binary representation 00000000000000000000000000001011, so the function should return 3.

求整数转化为二进制数中，1的个数

Python3

# Write a function that takes an unsigned integer and returns the number of ’1' bits
# it has (also known as the Hamming weight).
#
# For example, the 32-bit integer ’11' has binary representation 00000000000000000000000000001011,
# so the function should return 3.

# author li.hzh
class Solution(object):
    def hammingWeight(self, n):
        """
        :type n: int
        :rtype: int
        """
        result = 0
        while n:
            n = n & n - 1
            result += 1
        return  result

print(Solution().hammingWeight(11))

分析

其实完全可以通过Reverst Bit中的方法，通过 & 1，右移把1统计出来。不过效率会略低。因为每一位都要检查。

这里通过一个技巧 n & (n-1)，想象一下，每次都会消掉一个1，就加快了效率。

Problem

Reverse bits of a given 32 bits unsigned integer.

For example, given input 43261596 (represented in binary as 00000010100101000001111010011100), return 964176192 (represented in binary as 00111001011110000010100101000000).

Follow up: If this function is called many times, how would you optimize it?

即将整数的32个bit，逆序后，输出对应的整数。

Python3

# Reverse bits of a given 32 bits unsigned integer.
#
# For example, given input 43261596 (represented in binary as 00000010100101000001111010011100), return 964176192 (represented in binary as 00111001011110000010100101000000).
#
# Follow up:
# If this function is called many times, how would you optimize it?

# author li.hzh
class Solution:
    # @param n, an integer
    # @return an integer
    def reverseBits(self, n):
        result = 0
        digits = 0
        while digits < 32:
            temp = n & 1
            result <<= 1
            result |= temp
            n >>= 1
            digits += 1
        return result

solution = Solution()
print(solution.reverseBits(43261596))

分析

通过位运算取整移位。不得不说，算是自己的一个进步吧，能应用位运算了。

Problem

Rotate an array of n elements to the right by k steps.

For example, with n = 7 and k = 3, the array [1,2,3,4,5,6,7] is rotated to [5,6,7,1,2,3,4].

Note: Try to come up as many solutions as you can, there are at least 3 different ways to solve this problem.

[show hint]

Hint: Could you do it in-place with O(1) extra space?

即按照要求移位数组。最好不利用额外空间。

Python3

# Rotate an array of n elements to the right by k steps.
#
# For example, with n = 7 and k = 3, the array [1,2,3,4,5,6,7] is rotated to [5,6,7,1,2,3,4].
#
# Note:
# Try to come up as many solutions as you can, there are at least 3 different ways to solve this problem.
#
# [show hint]
#
# Hint:
# Could you do it in-place with O(1) extra space?
# Related problem: Reverse Words in a String II

# author li.hzh
class Solution:
    def rotate(self, nums, k):
        """
        :type nums: List[int]
        :type k: int
        :rtype: void Do not return anything, modify nums in-place instead.
        """
        length = len(nums)
        k = k % length
        if length <= 1 or k == 0:
            return
        end_index = length - 1
        for i in range(length):
            temp = nums[i]
            current_end = end_index - i
            if i < current_end:
                nums[i] = nums[current_end]
                nums[current_end] = temp
            else:
                break
        for i in range(k):
            temp = nums[i]
            current_end = k - i - 1
            if i < current_end:
                nums[i] = nums[current_end]
                nums[current_end] = temp
            else:
                break
        for i in range(k, length):
            temp = nums[i]
            current_end = end_index - i + k
            if i < current_end:
                nums[i] = nums[current_end]
                nums[current_end] = temp
            else:
                return


solution = Solution()
nums = [1, 2, 3]
solution.rotate(nums, 1)
print(nums)

分析

题目说用3种以上不同的解法。这里只给出了一种，O(n)时间，O(1) Space的，比较好理解的解法。就是 [1,2,3,4,5] ，先反转[5,4,3,2,1]，再按照k分割前后反转就是答案。比如k=2,[4,5,1,2,3]。代码很容易写。

如果使用额外空间，那么O(n)的解法也很直接。这里不再赘述。

Problem

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

即找到没有订单的客户

MySQL

解法一

SELECT Name AS Customers FROM Customers c WHERE c.Id NOT IN (SELECT CustomerId FROM Orders);

解法二

SELECT Customers FROM (SELECT c.Name AS Customers, o.Id AS oid FROM Customers c LEFT JOIN Orders o ON c.Id = o.CustomerId) AS r WHERE r.oid IS NULL;

分析

无

Problem

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

找到重复的Email。

MySQL

SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > 1;

分析

无

Problem

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

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

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.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

找到比Manager薪水高的雇员

MySQL

SELECT e1.Name AS Employee FROM Employee e1 INNER JOIN Employee e2 ON e1.ManagerId = e2.Id WHERE e1.Salary > e2.Salary;

分析

通过Join解决问题