The database article of Leetcode

On the way to the lab with John Chen in the morning, listen to him. Leetcode also has a database and shell practice. So take to practice practiced hand, found the database of the problem only a few and do also quickly AC rate is quite high, right when review the basic grammar of the database.

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 Employee The table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe was the only employee of the WHO earns more than his manager.

+----------+| Employee |+----------+| Joe      |+----------+
At first did not see clearly, then studied the first table to find that the original ManagerID is the ID, so used to two tables , the problem is very simple

SELECT  from  WHERE=and> b.salary;

2:duplicate Emails

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

+----+---------+| Id | Email   |+----+---------+| 1  | [Email protected] | | 2  | [Email protected] | | 3  | [Email protected] |+----+---------+

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

+---------+| Email   |+---------+| [Email protected] |+---------+

Note:all emails is in lowercase.

SELECT  from GROUP  by  having COUNT (*) > 1

Specific about group by and having the use of a reference to someone else's blog

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

3:combine 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 are the primary key column for this table.

Write a SQL query for a report this provides the following information for each person in the person table, regardless if There is a address for each of those people:

FirstName, LastName, city, state

SELECT  from  Left JOIN Address a USING (PersonId)

The main is the connection of two tables, the reference link is as follows:

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 the table and the table tables Customers Orders . Write a SQL query to find all customers The 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  from  Left JOIN  on = WHERE  is NULL

URL http://www.tuicool.com/articles/miAfii gives three ways to reference

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 |               Ten | |       2 | 2015-01-02 |               | |       3 | 2015-01-03 |               | |       4 | 2015-01-04 |               |+---------+------------+------------------+

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

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

The first thing to do is inline, and its secondary note is the id,w1.id of which table is the Id of the query.

Next there is a function to calculate date days, class reference http://blog.chinaunix.net/uid-26921272-id-3385920.html

SELECT  from INNER JOIN  on =+1and> w2. Temperature

6:second Highest Salary

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

+----+--------+| Id | Salary |+----+--------+| 1 | |    | 2 | | |    3  |    |+----+--------+

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

SELECT Max  from Employee WHERE < (SELECTMax from Employee)

7: Continue Tomorrow

The database article of Leetcode