早晨和陳John一起來實驗室的路上聽他說起leetcode上也有數據庫和shell的練習。于是拿來練練手，發現數據庫的題只有幾道而且做得也很快AC率也蠻高，權當復習了一下數據庫的基本語法了吧。

1：Employees Earning More Than Their Managers

?

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 | +----------+
剛開始沒看清楚，后來研究了一下第一張表才發現原來ManagerId是屬于Id的，所以用到兩張表，這道題很簡單

SELECT a.NAME FROM Employee a, Employee b WHERE a.ManagerId = b.Id AND a.Salary > b.Salary;

2：Duplicate Emails

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 | +---------+

Note: All emails are in lowercase.

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

具體關于group by 和having 的用法參考了別人的一篇博客

http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

3：Combine Two Tables

Table:?Person

+-------------+---------+ | Column Name | Type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId is the primary key column for this table.

Table:?Address

+-------------+---------+ | Column Name | Type | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId is the primary key column for this table.

?

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:

FirstName, LastName, City, State

SELECT p.FirstName, p.LastName, a.City, a.State FROM Person p LEFT JOIN Address a USING (PersonId)

主要是兩個表的連接，參考鏈接如下：

http://www.bkjia.com/Mysql/777046.html

http://www.cnblogs.com/devilmsg/archive/2009/03/24/1420543.html

4：Customers Who Never Order

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 | +-----------+

SELECT name FROM Customers c LEFT JOIN Orders o on c.Id = o.CustomerId WHERE o.Id IS NULL

網址http://www.tuicool.com/articles/miAfii給出了三種方法，可供參考

5:Rising Temperature

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是哪一張表的ID，w1.Id.

其次有計算date天數的函數，課參考http://blog.chinaunix.net/uid-26921272-id-3385920.html

SELECT w1.Id FROM Weather w1 INNER JOIN Weather w2 ON TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1 AND w1.Temperature > w2.Temperature

6:Second Highest Salary

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

+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

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

SELECT Max(Salary) FROM Employee WHERE Salary < (SELECT Max(Salary) FROM Employee)

7:明天繼續

?

轉載于:https://www.cnblogs.com/gracyandjohn/p/4562747.html

總結

以上是生活随笔為你收集整理的Leetcode之Database篇的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。