个人技术日记

UMR

发表于 更新于 分类于 Waline： 阅读次数：

题目

编写一个 SQL 查询，获取 Employee 表中第n高的薪水（Salary） 。

Id Salary
1 100
2 200
3 300

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

getNthHighestSalary(2)
200
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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.

Id Score
1 3.50
2 3.65
3 4.00
4 3.85
5 4.00
6 3.65

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

Score Rank
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

Write a SQL query to find all numbers that appear at least three times consecutively.

Id Num
1 1
2 1
3 1
4 2
5 1
6 2
7 2

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

ConsecutiveNums
1

题解

阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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.

Customers.
Id Name
1 Joe
2 Henry
3 Sam
4 Max
Orders.
Id CustomerId
1 3
2 1

Using the above tables as example, return the following:

阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

Id Name Salary DepartmentId
1 Joe 70000 1
2 Jim 90000 1
3 Henry 80000 2
4 Sam 60000 2
5 Max 90000 1

The Department table holds all departments of the company.

Id Name
1 IT
2 Sales
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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

Id Name Salary DepartmentId
1 Joe 85000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1
5 Janet 69000 1
6 Randy 85000 1
7 Will 70000 1

The Department table holds all departments of the company.

Id Name
1 IT
2 Sales
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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
阅读全文 »

发表于 更新于 分类于 Waline： 阅读次数：

题目

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) | RecordDate(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
阅读全文 »
0%