1、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 query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
选出第二高的薪水
limit offset组合用法:
语句1:select * from student limit 9,4
语句2:slect * from student limit 4 offset 9// 语句1和2均返回表student的第10、11、12、13行
2、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
题目大意:
有两个数据表:Person表和Address表。Person(人员)表主键为PersonId,Address(地址)表主键是AddressId,通过PersonId与Person表关联。
编写一个SQL查询,对于Person表中的每一个人,取出FirstName, LastName, City, State属性,无论其地址信息是否存在。
3、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 | +----------+
找出雇员薪水比雇主高的雇员姓名,注意输出列的名称
4、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.
5、 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 | +----+------------+
找出不在b表中的人名