🚀 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 belowselect name,population,area from Worldwhere area>=3000000or population >=25000000/* Write your T-SQL query statement below */select name,population,area from Worldwhere area>=3000000or population >=25000000/* Write your PL/SQL query statement below */select name "name",population "population",area "area"from Worldwhere area>=3000000or population >=25000000

🚀 表：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 belowselect product_id  from Productswhere low_fats = 'Y'and recyclable ='Y'/* Write your T-SQL query statement below */select product_id  from Productswhere low_fats = 'Y'and recyclable ='Y'/* Write your PL/SQL query statement below */select product_id "product_id" from Productswhere low_fats = 'Y'and recyclable ='Y'

🚀 给定表 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 belowselect name  from customerwhere IFNULL(referee_id,0) <> 2--mysql判断非空的函数ISNULL(expr)  如果expr为null返回值1，否则返回值为0IFNULL(expr1,expr2) 如果expr1值为null返回expr2的值，否则返回expr1的值/* Write your T-SQL query statement below */select name  from customerwhere referee_id <> 2 OR referee_id IS NULL/* Write your PL/SQL query statement below */select name "name"  from customerwhere nvl(referee_id,0) <> 2

🚀 某网站包含两个表，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 belowselect Name "Customers" from Customerswhere id not in (select CustomerId  from Orders)/* Write your T-SQL query statement below */select Name "Customers" from Customerswhere id not in (select CustomerId  from Orders)/* Write your PL/SQL query statement below */select Name "Customers" from Customers awhere not exists (select 1  from Orders b where a.Id = b.CustomerId)order by 1

🚀 表: 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 belowselect employee_id,case when mod(employee_id,2)=1 and LEFT(name,1)!='M' then salaryelse 0 end bonusfrom Employeesorder 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 salaryelse 0 end bonusfrom Employeesorder 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 salaryelse 0 end  "bonus"from Employeesorder by 1

🚀 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 belowupdate 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')

🚀 表: 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 belowDELETE p1 FROM Person p1, Person p2WHERE 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 p2WHERE 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)

🚀 表： 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 belowselect user_id,concat(upper(left(name,1)),lower(substr(name,2))) namefrom Usersorder 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)) namefrom Usersorder by user_id/* Write your PL/SQL query statement below */select user_id "user_id",initcap(name) "name"from Usersorder by 1

🚀表 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 belowselect     sell_date,    count(distinct product) as num_sold,    group_concat(distinct product) as productsfrom Activitiesgroup 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 testselect 1,1111 from dualunion allselect 1,2222 from dual--drop table testselect id, count(distinct mc) mc,ltrim(max(sys_connect_by_path(mc,',')),',') row2colfrom (select id,mc,id+(row_number() over(order by id)) node_id,row_number() over(partition by id order by id) rnfrom test)start with rn = 1connect by node_id-1 = prior node_idgroup by idorder 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--列转行selectREGEXP_SUBSTR(a.rolecode ,'[^,]+',1,l)rolecodefrom (select 'a,aa,aaa' rolecode from dual) a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) bWHERE 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 aconnect 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 Activitiesgroup by sell_date

🚀 表: 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 belowselect employee_idfrom(select employee_id from Employeesunion allselect employee_id from Salaries ) as tempgroup by employee_idhaving count(*) = 1order by employee_id/* Write your T-SQL query statement below */select employee_idfrom(select employee_id from Employeesunion allselect employee_id from Salaries ) as tempgroup by employee_idhaving count(*) = 1order 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)

🚀 表：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 belowselect product_id, 'store1' store, store1 pricefrom productswhere store1 is not null unionselect product_id, 'store2' store, store2 pricefrom products where store2 is not nullunionselect product_id, 'store3' store, store3 pricefrom products where store3 is not null/* Write your T-SQL query statement below */select product_id, 'store1' store, store1 pricefrom productswhere store1 is not null unionselect product_id, 'store2' store, store2 pricefrom products where store2 is not nullunionselect product_id, 'store3' store, store3 pricefrom 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 Productsunpivot( price for  store in(store1,store2,store3))

🚀 给定一个表 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 belowselect id,case when p_id is null then 'Root'when id in (select p_id from tree) then 'Inner'else 'Leaf' end as Typefrom 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 Typefrom 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

🚀 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 belowSELECT    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 EmployeeWhere Salary <(SELECT MAX(Salary) FROM Employee);/* Write your PL/SQL query statement below */SELECT MAX(Salary) "SecondHighestSalary" FROM Employee E1WHERE 1 =(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2WHERE E2.Salary > E1.Salary);