+-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id 是该表的主键列。 该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。
表: Company
+-------------+---------+ | Column Name | Type | +-------------+---------+ | com_id | int | | name | varchar | | city | varchar | +-------------+---------+ com_id 是该表的主键列。 该表的每一行都表示公司的名称和 ID ，以及公司所在的城市。
表: Orders
+-------------+------+ | Column Name | Type | +-------------+------+ | order_id | int | | order_date | date | | com_id | int | | sales_id | int | | amount | int | +-------------+------+ order_id 是该表的主键列。 com_id 是 Company 表中 com_id 的外键。 sales_id 是来自销售员表 sales_id 的外键。 该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。
编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
MySQL
select name from SalesPerson where sales_id notin ( select s.sales_id from Orders o Leftjoin SalesPerson s on o.sales_id = s.sales_id Leftjoin Company c on o.com_id = c.com_id where c.name = "RED")
+-------------+---------+ |Column Name | Type | +-------------+---------+ | id |int| | email |varchar| +-------------+---------+ id 是该表的主键列。 此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。
+-------------+----------+ |Column Name | Type | +-------------+----------+ | id |int| | name |varchar| | sex | ENUM | | salary |int| +-------------+----------+ id 是这个表的主键。 sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。
+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId |int| | PersonId |int| | City |varchar| | State |varchar| +-------------+---------+ addressId 是该表的主键列。 该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如 personId 的地址不在 Address 表中，则报告为空 null 。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Person表: +----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ |1| Wang | Allen | |2| Alice | Bob | +----------+----------+-----------+ Address表: +-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ |1|2|New York City |New York | |2|3| Leetcode | California | +-----------+----------+---------------+------------+ 输出: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang |Null|Null| | Bob | Alice |New York City |New York | +-----------+----------+---------------+----------+ 解释: 地址表中没有 personId =1 的地址，所以它们的城市和州返回 null。 addressId =1 包含了 personId =2 的地址信息。
s
MySQL
# Write your MySQL query statement below select Person.firstName, Person.lastName, Address.city, Address.state from Person left join Address on Person.personId = Address.personId;
+----+-------+ | Id | Name | +----+-------+ |1| Joe | |2| Henry | |3| Sam | |4| Max | +----+-------+
Orders 表：
+----+------------+ | Id | CustomerId | +----+------------+ |1|3| |2|1| +----+------------+
例如给定上述表格，你的查询应返回：
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
MySQL
Customers as A left join Orders as B
# Write your MySQL query statement below select A.Name as Customers from Customers as A left join Orders as B on A.Id = B.CustomerId where B.Id is null;
输入： Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ |1| Joe |70000|1| |2| Jim |90000|1| |3| Henry |80000|2| |4| Sam |60000|2| |5| Max |90000|1| +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ |1| IT | |2| Sales | +----+-------+ 输出： +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim |90000| | Sales | Henry |80000| | IT | Max |90000| +------------+----------+--------+ 解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。
MySQL
# Write your MySQL query statement below select D.name as Department, E.name as Employee, E.salary as Salary from Employee E leftjoin Department D on E.departmentId = D.id where (departmentId, salary) in ( select departmentId, max(salary) from Employee groupby departmentId );
# Please write a DELETE statement and DO NOT write a SELECT statement. # Write your MySQL query statement below delete P1 from Person as P1, Person as P2 where P1.email = P2.email and P1.id > P2.id;
# Write your MySQL query statement below # select() 是为了防止出现没有第二条数据的情况 select (select distinct A.salary from Employee A order by salary desc limit 1 offset 1) as SecondHighestSalary ;
# Write your MySQL query statement below select A.score as score, (select count(distinct B.score) from Scores B where B.score >= A.score) as "rank" # rank 是mysql关键字 from Scores A order by A.score desc;
+-------------+---------+ |Column Name | Type | +-------------+---------+ | student |varchar| | class |varchar| +-------------+---------+ (student, class)是该表的主键列。 该表的每一行表示学生的名字和他们注册的班级。
编写一个SQL查询来报告 至少有5个学生 的所有班级。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+----------+ 输出: +---------+ | class | +---------+ | Math | +---------+ 解释: -数学课有6个学生，所以我们包括它。 -英语课有1名学生，所以我们不包括它。 -生物课有1名学生，所以我们不包括它。 -计算机课有1个学生，所以我们不包括它。
MySQL
select class from Courses groupby class havingCOUNT(DISTINCT student) >=5;
SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 ISNOTNULL UNION SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 ISNOTNULL UNION SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 ISNOTNULL;
