一题: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.
为报告编写SQL查询,该报告为Person表中的每个人提供以下信息,无论这个人是否有一个地址:
FirstName, LastName, City, State
英文:
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为左表,进行左关联。
# 创建Person表
Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255));
# 创建Address表
Create table Address (AddressId int, PersonId int, City varchar(255), State varchar(255));
# 删除Person表中的所有数据,类似于delete from Person
Truncate table Person;
# 往Person表中插入一条数据
insert into Person (PersonId, LastName, FirstName) values ('1', 'Wang', 'Allen');
# 删除Address表中的所有数据
Truncate table Address;
# 往Address表中插入一条数据
insert into Address (AddressId, PersonId, City, State) values ('1', '2', 'New York City', 'New York');
# Date:2019.02.15
# num:195
# 解题答案:
# ==============================================================================
# 使用左关联查询表中的数据
SELECT p.FirstName,p.LastName,a.City,a.State from Person p left join Address a on p.PersonId = a.PersonId;
二题:Employees Earning More Than Their Managers
中文:
Employee表包含所有员工,包括他们的经理。每个员工都有一个Id,并且还有一个经理Id列。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定Employee表,编写一个SQL查询,查找收入高于其经理的员工。对于上表,Joe是唯一一位收入超过其经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
英文:
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进行关联,前半部分为自身信息,后半部分为经理信息,查询自身薪水大于经理薪水。
并将查询到的name重命名为Employee。
# 创建员工表,有id,名字,薪水,经理Id
CREATE TABLE
IF
NOT EXISTS Employee ( Id INT, NAME VARCHAR ( 255 ), Salary INT, ManagerId INT );# 清空员工表中所有数据
TRUNCATE TABLE Employee;# 向员工表中插入4条数据
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
( '1', 'Joe', '70000', '3' );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
( '2', 'Henry', '80000', '4' );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
( '3', 'Sam', '60000', '0' );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
( '4', 'Max', '90000', '0' );
DROP TABLE Employee;# date:2019.02.15
# num:181
# 解题答案:
# =====================================
# 将表自身通过ManagerId进行关联,前半部分为自身信息,后半部分为经理信息,查询自身薪水大于经理薪水
# 并将查询到的name重命名为Employee
SELECT
A.`Name` AS Employee
FROM
Employee AS A,
Employee AS B
WHERE
A.ManagerId = B.Id
AND A.Salary > B.Salary;
三题:Customers Who Never Order
中文:
假设一个网站包含两个表,Customers表和Orders表。编写SQL查询以查找从未订购任何内容的所有客户。
Table: Customers
.
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Table: Orders
.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
使用上面的表作为示例,返回以下内容:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
英文:
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 |
+-----------+
解题思路:
第一种:
使用子查询。
查询在订单表中有的customerId作为查询条件,查询Customer表中Id不在其中的用户名称。
第二种:
使用左关联,将Customers和Orders表使用customerId进行关联,如果该用户不存在订单表中,则customerId为空。
# 创建客户表
CREATE TABLE
IF
NOT EXISTS Customers ( Id INT, NAME VARCHAR ( 255 ) );
# 创建订单表
CREATE TABLE
IF
NOT EXISTS Orders ( Id INT, CustomerId INT );
# 清空客户表原有数据,并插入数据
TRUNCATE TABLE Customers;
INSERT INTO Customers ( Id, NAME )
VALUES
( '1', 'Joe' );
INSERT INTO Customers ( Id, NAME )
VALUES
( '2', 'Henry' );
INSERT INTO Customers ( Id, NAME )
VALUES
( '3', 'Sam' );
INSERT INTO Customers ( Id, NAME )
VALUES
( '4', 'Max' );
# 清空订单表原有数据,并插入数据
TRUNCATE TABLE Orders;
INSERT INTO Orders ( Id, CustomerId )
VALUES
( '1', '3' );
INSERT INTO Orders ( Id, CustomerId )
VALUES
( '2', '1' );
# date: 2039.02.15
# num: 183
# 解题答案:
# =====================================
# 方法一:使用子查询。查询在订单表中有的customerId作为查询条件,查询Customer表中Id不在其中的用户名称。
SELECT NAME AS
Customers
FROM
Customers
WHERE
Id NOT IN ( SELECT CustomerId FROM Orders );
# 方法二:使用左关联,将Customers和Orders表使用customerId进行关联,如果该用户不存在订单表中,则customerId为空。
SELECT
*
FROM
Customers C
LEFT JOIN Orders O ON C.Id = O.CustomerId
WHERE
O.CustomerId IS NULL;