1.SQL 入门
🚩595.大的国家
🚀 World 表： +-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | int | +-------------+---------+ name 是这张表的主键。 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。 🚀 需求 如果一个国家满足下述两个条件之一，则认为该国是大国 ： 面积至少为 300 平方公里（即，3000000 km2），或者人口至少为 2500 万（即 25000000） 编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。 按 任意顺序 返回结果表。 查询结果格式如下例所示。 🚀 示例： 输入： World 表： +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+-----------+---------+------------+--------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000000 | | Albania | Europe | 28748 | 2831741 | 12960000000 | | Algeria | Africa | 2381741 | 37100000 | 188681000000 | | Andorra | Europe | 468 | 78115 | 3712000000 | | Angola | Africa | 1246700 | 20609294 | 100990000000 | +-------------+-----------+---------+------------+--------------+ 输出： +-------------+------------+---------+ | name | population | area | +-------------+------------+---------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +-------------+------------+---------+ 🐴🐴 答案 # Write your MySQL query statement below select name,population,area from World where area>=3000000 or population >=25000000 /* Write your T-SQL query statement below */ select name,population,area from World where area>=3000000 or population >=25000000 /* Write your PL/SQL query statement below */ select name "name", population "population", area "area" from World where area>=3000000 or population >=25000000
🚩1757. 可回收且低脂的产品
🚀 表：Products +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | low_fats | enum | | recyclable | enum | +-------------+---------+ product_id 是这个表的主键。 low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。 recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。 🚀 需求 写出 SQL 语句，查找既是低脂又是可回收的产品编号。 返回结果 无顺序要求 。 查询结果格式如下例所示： Products 表： +-------------+----------+------------+ | product_id | low_fats | recyclable | +-------------+----------+------------+ | 0 | Y | N | | 1 | Y | Y | | 2 | N | Y | | 3 | Y | Y | | 4 | N | N | +-------------+----------+------------+ Result 表： +-------------+ | product_id | +-------------+ | 1 | | 3 | +-------------+ 只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。 🐴🐴 答案 # Write your MySQL query statement below select product_id from Products where low_fats = 'Y' and recyclable ='Y' /* Write your T-SQL query statement below */ select product_id from Products where low_fats = 'Y' and recyclable ='Y' /* Write your PL/SQL query statement below */ select product_id "product_id" from Products where low_fats = 'Y' and recyclable ='Y'
🚩584. 寻找用户推荐人
🚀 给定表 customer ，里面保存了所有客户信息和他们的推荐人。 +------+------+-----------+ | id | name | referee_id| +------+------+-----------+ | 1 | Will | NULL | | 2 | Jane | NULL | | 3 | Alex | 2 | | 4 | Bill | NULL | | 5 | Zack | 1 | | 6 | Mark | 2 | +------+------+-----------+ 🚀 需求 写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是2。 对于上面的示例数据，结果为： +------+ | name | +------+ | Will | | Jane | | Bill | | Zack | +------+ 🐴🐴 答案 # Write your MySQL query statement below select name from customer where IFNULL(referee_id,0) <> 2 --mysql判断非空的函数 ISNULL(expr) 如果expr为null返回值1，否则返回值为0 IFNULL(expr1,expr2) 如果expr1值为null返回expr2的值，否则返回expr1的值 /* Write your T-SQL query statement below */ select name from customer where referee_id <> 2 OR referee_id IS NULL /* Write your PL/SQL query statement below */ select name "name" from customer where nvl(referee_id,0) <> 2
🚩183. 从不订购的客户
🚀 某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。 Customers 表： +----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Orders 表： +----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ 🚀 需求 例如给定上述表格，你的查询应返回： +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+ 🐴🐴 答案 # Write your MySQL query statement below select Name "Customers" from Customers where id not in (select CustomerId from Orders) /* Write your T-SQL query statement below */ select Name "Customers" from Customers where id not in (select CustomerId from Orders) /* Write your PL/SQL query statement below */ select Name "Customers" from Customers a where not exists (select 1 from Orders b where a.Id = b.CustomerId) order by 1
2 排序 & 修改
🚩1873. 计算特殊奖金
🚀 表: Employees +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | employee_id | int | | name | varchar | | salary | int | +-------------+---------+ employee_id 是这个表的主键。 此表的每一行给出了雇员id ，名字和薪水。 🚀 需求 写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。 Return the result table ordered by employee_id. 返回的结果集请按照employee_id排序。 查询结果格式如下面的例子所示。 示例 1: 输入： Employees 表: +-------------+---------+--------+ | employee_id | name | salary | +-------------+---------+--------+ | 2 | Meir | 3000 | | 3 | Michael | 3800 | | 7 | Addilyn | 7400 | | 8 | Juan | 6100 | | 9 | Kannon | 7700 | +-------------+---------+--------+ 输出： +-------------+-------+ | employee_id | bonus | +-------------+-------+ | 2 | 0 | | 3 | 0 | | 7 | 7400 | | 8 | 0 | | 9 | 7700 | +-------------+-------+ 解释： 因为雇员id是偶数，所以雇员id 是2和8的两个雇员得到的奖金是0。 雇员id为3的因为他的名字以'M'开头，所以，奖金是0。 其他的雇员得到了百分之百的奖金。 🐴🐴 答案 # Write your MySQL query statement below select employee_id, case when mod(employee_id,2)=1 and LEFT(name,1)!='M' then salary else 0 end bonus from Employees order by employee_id /* Write your T-SQL query statement below */ select employee_id, case when employee_id%2=1 and SUBSTRING(name,1,1)!='M' then salary else 0 end bonus from Employees order by employee_id /* Write your PL/SQL query statement below */ select employee_id "employee_id", case when mod(employee_id,2)=1 and substr(name,1,1)!='M' then salary else 0 end "bonus" from Employees order by 1
🚩627. 变更性别
🚀 Salary 表： +-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | name | varchar | | sex | ENUM | | salary | int | +-------------+----------+ id 是这个表的主键。 sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。 🚀 需求 请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。 注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。 查询结果如下例所示。 示例 1: 输入： Salary 表： +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | +----+------+-----+--------+ 输出： +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 | +----+------+-----+--------+ 解释： (1, A) 和 (3, C) 从 'm' 变为 'f' 。 (2, B) 和 (4, D) 从 'f' 变为 'm' 。 🐴🐴 答案 # Write your MySQL query statement below update Salary set sex= case sex when 'm' then 'f' when 'f' then 'm' end /* Write your T-SQL query statement below */ update Salary set sex= case sex when 'm' then 'f' when 'f' then 'm' end /* Write your PL/SQL query statement below */ update Salary set sex=decode(sex,'m','f','f','m')
🚩196. 删除重复的电子邮箱
🚀 表: Person +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id是该表的主键列。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。 🚀 需求 编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。 以任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询） 查询结果格式如下所示。 示例 1: 输入: Person 表: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ 输出: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ 解释: john@example.com重复两次。我们保留最小的Id = 1。 🐴🐴 答案 # Please write a DELETE statement and DO NOT write a SELECT statement. # Write your MySQL query statement below DELETE p1 FROM Person p1, Person p2 WHERE p1.email = p2.email AND p1.id > p2.id /* Please write a DELETE statement and DO NOT write a SELECT statement. Write your T-SQL query statement below */ DELETE p1 FROM Person p1, Person p2 WHERE p1.email = p2.email AND p1.id > p2.id /* Please write a DELETE statement and DO NOT write a SELECT statement. Write your PL/SQL query statement below */ delete from Person where id in (select e1.id from Person e1,Person e2 where e1.email = e2.email and e1.id>e2.id) delete from Person e1 where exists (select 1 from Person e2 where e1.email = e2.email and e1.id>e2.id)
第3天 字符串处理函数/正则
🚩1667. 修复表中的名字
🚀 表： Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | name | varchar | +----------------+---------+ user_id 是该表的主键。 该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。 🚀 需求 编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。 返回按 user_id 排序的结果表。 查询结果格式示例如下。 示例 1： 输入： Users table: +---------+-------+ | user_id | name | +---------+-------+ | 1 | aLice | | 2 | bOB | +---------+-------+ 输出： +---------+-------+ | user_id | name | +---------+-------+ | 1 | Alice | | 2 | Bob | +---------+-------+ 🐴🐴 答案 # Write your MySQL query statement below select user_id, concat(upper(left(name,1)),lower(substr(name,2))) name from Users order by user_id /* Write your T-SQL query statement below */ select user_id, UPPER(substring(name,1,1))+LOWER(substring(name,2,len(name)-1)) name from Users order by user_id /* Write your PL/SQL query statement below */ select user_id "user_id", initcap(name) "name" from Users order by 1
🚩1484. 按日期分组销售产品
🚀表 Activities： +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | sell_date | date | | product | varchar | +-------------+---------+ 此表没有主键，它可能包含重复项。 此表的每一行都包含产品名称和在市场上销售的日期。 🚀 需求 编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。 每个日期的销售产品名称应按词典序排列。 返回按 sell_date 排序的结果表。 查询结果格式如下例所示。 示例 1: 输入： Activities 表： +------------+-------------+ | sell_date | product | +------------+-------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+-------------+ 输出： +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+ 解释： 对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。 对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。 对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。 🐴🐴 答案 # Write your MySQL query statement below select sell_date, count(distinct product) as num_sold, group_concat(distinct product) as products from Activities group by sell_date /* Write your T-SQL query statement below */ SELECT STUFF((SELECT ','+product FROM Activities for xml path('')),1,1,'') /* Write your T-SQL query statement below */ select sell_date, count(distinct product) as num_sold, stuff((select distinct ','+product from Activities a where a.sell_date=b.sell_date for xml path('')),1,1,'') AS products from Activities b group by sell_date --行转列 create table test (id int,mc varchar(2000)) insert into test select 1,1111 from dual union all select 1,2222 from dual --drop table test select id, count(distinct mc) mc, ltrim(max(sys_connect_by_path(mc,',')),',') row2col from (select id,mc, id+(row_number() over(order by id)) node_id, row_number() over(partition by id order by id) rn from test) start with rn = 1 connect by node_id-1 = prior node_id group by id order by id; elect name,coures,to_char(wmsys.wm_concat(xxx.score)) c from (select '小明' name,'语文' coures,90 score from dual union all select '小明' name,'语文' coures,91 score from dual union all select '小明' name,'数学' coures,90 score from dual union all select '小明' name,'数学' coures,91 score from dual) xxx group by xxx.name,coures --列转行 select REGEXP_SUBSTR(a.rolecode ,'[^,]+',1,l) rolecode from ( select 'a,aa,aaa' rolecode from dual ) a, (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b WHERE l <=LENGTH(a.rolecode) - LENGTH(REPLACE(rolecode,','))+1 with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual) select regexp_substr(id,'[^,]+',1,rownum) id from a connect by rownum <= length(regexp_replace(id,'[^,]+')) --分组加排序，数据量大时结果会比较慢 SELECT listagg(t.ename,',') WITHIN GROUP(ORDER BY t.sal) FROM scott.emp t; 查询结果为CLOB SELECT wm_concat(t.ename) FROM scott.emp t ORDER BY t.sal; /* Write your PL/SQL query statement below */ select sell_date "sell_date", count(distinct product) as "num_sold", wm_concat(distinct product) as "products" from Activities group by sell_date
第4天 组合查询 & 指定选取
🚩1965. 丢失信息的雇员
🚀 表: Employees +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id 是这个表的主键。 每一行表示雇员的id 和他的姓名。 表: Salaries +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | salary | int | +-------------+---------+ employee_id is 这个表的主键。 每一行表示雇员的id 和他的薪水。 🚀 需求 写出一个查询语句，找到所有丢失信息的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失： 雇员的 姓名 丢失了，或者 雇员的 薪水信息 丢失了 返回这些雇员的id employee_id ， 从小到大排序 。 查询结果格式如下面的例子所示。 示例 1： 输入： Employees table: +-------------+----------+ | employee_id | name | +-------------+----------+ | 2 | Crew | | 4 | Haven | | 5 | Kristian | +-------------+----------+ Salaries table: +-------------+--------+ | employee_id | salary | +-------------+--------+ | 5 | 76071 | | 1 | 22517 | | 4 | 63539 | +-------------+--------+ 输出： +-------------+ | employee_id | +-------------+ | 1 | | 2 | +-------------+ 解释： 雇员1，2，4，5 都工作在这个公司。 1号雇员的姓名丢失了。 2号雇员的薪水信息丢失了。 🐴🐴 答案 # Write your MySQL query statement below select employee_id from (select employee_id from Employees union all select employee_id from Salaries ) as temp group by employee_id having count(*) = 1 order by employee_id /* Write your T-SQL query statement below */ select employee_id from (select employee_id from Employees union all select employee_id from Salaries ) as temp group by employee_id having count(*) = 1 order by employee_id /* Write your PL/SQL query statement below */ select employee_id "employee_id" from ( select employee_id from Employees a where not exists (select 1 from Salaries where employee_id= a.employee_id) union all select employee_id from Salaries b where not exists (select 1 from Employees where employee_id= b.employee_id) order by employee_id )
🚩1795. 每个产品在不同商店的价
🚀 表：Products +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | store1 | int | | store2 | int | | store3 | int | +-------------+---------+ 这张表的主键是product_id（产品Id）。 每行存储了这一产品在不同商店store1, store2, store3的价格。 如果这一产品在商店里没有出售，则值将为null。 🚀 需求 请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。 如果这一产品在商店里没有出售，则不输出这一行。 输出结果表中的 顺序不作要求 。 查询输出格式请参考下面示例。 示例 1： 输入： Products table: +------------+--------+--------+--------+ | product_id | store1 | store2 | store3 | +------------+--------+--------+--------+ | 0 | 95 | 100 | 105 | | 1 | 70 | null | 80 | +------------+--------+--------+--------+ 输出： +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 0 | store1 | 95 | | 0 | store2 | 100 | | 0 | store3 | 105 | | 1 | store1 | 70 | | 1 | store3 | 80 | +------------+--------+-------+ 解释： 产品0在store1，store2,store3的价格分别为95,100,105。 产品1在store1，store3的价格分别为70,80。在store2无法买到。 🐴🐴 答案 # Write your MySQL query statement below select product_id, 'store1' store, store1 price from products where store1 is not null union select product_id, 'store2' store, store2 price from products where store2 is not null union select product_id, 'store3' store, store3 price from products where store3 is not null /* Write your T-SQL query statement below */ select product_id, 'store1' store, store1 price from products where store1 is not null union select product_id, 'store2' store, store2 price from products where store2 is not null union select product_id, 'store3' store, store3 price from products where store3 is not null /* Write your PL/SQL query statement below */ select product_id "product_id", lower(store) as "store", price "price" from Products unpivot ( price for store in(store1,store2,store3))
🚩608. 树节点
🚀 给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。 +----+------+ | id | p_id | +----+------+ | 1 | null | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +----+------+ 树中每个节点属于以下三种类型之一： 叶子：如果这个节点没有任何孩子节点。 根：如果这个节点是整棵树的根，即没有父节点。 内部节点：如果这个节点既不是叶子节点也不是根节点。 🚀 需求 写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为： +----+------+ | id | Type | +----+------+ | 1 | Root | | 2 | Inner| | 3 | Leaf | | 4 | Leaf | | 5 | Leaf | +----+------+ 解释 节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。 节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。 节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。 样例中树的形态如下： 1 / \ 2 3 / \ 4 5 注意 如果树中只有一个节点，你只需要输出它的根属性。 🐴🐴 答案 # Write your MySQL query statement below select id,case when p_id is null then 'Root' when id in (select p_id from tree) then 'Inner' else 'Leaf' end as Type from tree /* Write your T-SQL query statement below */ select id,case when p_id is null then 'Root' when id in (select p_id from tree) then 'Inner' else 'Leaf' end as Type from tree /* Write your PL/SQL query statement below */ select id "id",case when p_id is null then 'Root' when id in (select p_id from tree) then 'Inner' else 'Leaf' end as "Type" from tree
🚩176. 第二高的薪水
🚀 Employee 表： +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id 是这个表的主键。 表的每一行包含员工的工资信息。 🚀 需求 编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。 如果不存在第二高的薪水，查询应该返回 null 。 查询结果如下例所示。 示例 1： 输入： Employee 表： +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 输出： +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ 示例 2： 输入： Employee 表： +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ 输出： +---------------------+ | SecondHighestSalary | +---------------------+ | null | +---------------------+ 🐴🐴 答案 # Write your MySQL query statement below SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary /* Write your T-SQL query statement below */ SELECT MAX(Salary) SecondHighestSalary FROM Employee Where Salary < (SELECT MAX(Salary) FROM Employee); /* Write your PL/SQL query statement below */ SELECT MAX(Salary) "SecondHighestSalary" FROM Employee E1 WHERE 1 = (SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary);
