【LeetCode-SQL专项突破】-第1天:选择
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
前言
SQL每个人都要用，但是用来衡量产出的并不是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 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
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 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'
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 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
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 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
体系化学习SQL，请关注CSDN博客
https://blog.csdn.net/weixin_41645135/category_11653817.html
直播视频：https://www.bilibili.com/video/BV1xa411J7ft/
