赞
踩
表1:Person
表2:Address
问题:编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息: FirstName, LastName, City, State
示例
- SELECT a.FirstName, a.LastName, b.City, b.State
- FROM Person AS a
- LEFT JOIN Address AS b
- ON a.PersonId = b.PersonId;
表:World
问题:如果一个国家满足下述两个条件之一,则认为该国是大国:
1.面积至少为 300 平方公里(
3000000平方公里)
2.人口至少为 2500 万(
25000000)
编写一个 SQL 查询以报告大国的国家名称、人口和面积,按任意顺序返回结果表
示例
- SELECT name, population, area
- FROM World
- WHERE area >= 3000000 OR population >= 25000000;
表:Courses
问题:编写一个SQL查询来报告至少有5个学生的所有课程,以任意顺序返回结果表
示例
- SELECT class
- FROM Courses
- GROUP BY class
- HAVING COUNT(student) >= 5;
表: cinema
问题:某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非boring(不无聊) 的并且id 为奇数的影片,结果请按等级rating排列
对于上面的例子,则正确的输出是为:
- SELECT id, movie, description, rating
- FROM cinema
- WHERE description != 'boring'
- AND (id % 2 != 0)
- ORDER BY rating DESC;
表: Tweets
问题:写一条SQL语句,查询所有无效推文的编号(ID)。当推文内容中的字符数严格大于
15
时,该推文是无效的,以任意顺序返回结果表
示例
- SELECT tweet_id
- FROM Tweets
- WHERE length(content) > 15;
表:Orders
问题:编写一个SQL查询,为下了最多订单的客户查找
customer_number
示例
- SELECT a.customer_number
- FROM Orders AS a
- INNER JOIN (SELECT customer_number, COUNT(order_number) AS num
- FROM Orders
- GROUP BY customer_number) AS b
- ON a.customer_number = b.customer_number
- ORDER BY b.num DESC
- LIMIT 1;
表:MyNumbers
问题:单一数字是在
MyNumbers
表中只出现一次的数字请你编写一个SQL查询来报告最大的单一数字 。如果不存在单一数字 ,查询需报告
null
示例1
示例2
- SELECT IFNULL((SELECT num
- FROM MyNumbers
- GROUP BY num
- HAVING COUNT(num) = 1
- ORDER BY num DESC
- LIMIT 1),null) AS num;
表: Employee
问题:编写一个SQL查询来查找收入比经理高的员工,以任意顺序返回结果表
示例
- -- 解法1
- SELECT a.name AS Employee
- FROM Employee AS a,Employee AS b
- WHERE a.ManagerId = b.Id
- AND a.Salary > b.Salary;
-
- -- 解法2
- SELECT a.name AS Employee
- FROM Employee AS a
- INNER JOIN Employee AS b
- ON a.managerId = b.id
- WHERE a.salary > b.salary;
表: Person
问题:根据以上输入,你的查询应返回以下结果:
说明:所有电子邮箱都是小写字母
- -- 解法1
- SELECT Email
- FROM (SELECT Email, COUNT(*) AS nums
- FROM Person
- GROUP BY Email
- HAVING nums > 1) AS new_person;
-
- -- 解法2
- SELECT Email
- FROM Person
- GROUP BY Email
- HAVING COUNT(Id) > 1;
表: Customers
表: Orders
问题:某网站包含两个表
,Customers
表和Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户,查询应返回:
- SELECT Name AS Customers
- FROM Customers
- WHERE Id NOT IN(SELECT CustomerId FROM Orders);
表: Customer
问题:给定表
customer
,里面保存了所有客户信息和他们的推荐人写一个查询语句,返回一个客户列表,列表中客户的推荐人的编号都不是2
对于上面的示例数据,结果为:
- SELECT name
- FROM customer
- WHERE referee_id != 2 OR referee_id IS Null;
表:Employee
表:Bonus
问题:选出所有bonus < 1000的员工的name及其bonus,输出示例:
- SELECT a.name, b.bonus
- FROM Employee AS a
- LEFT JOIN Bonus AS b
- ON a.empId = b.empId
- WHERE b.bonus < 1000 OR b.bonus IS null;
表:Activity
问题:写一条 SQL查询语句获取每位玩家第一次登陆平台的日期,查询结果的格式如下所示:
- SELECT player_id, MIN(event_date) AS first_login
- FROM Activity
- GROUP BY player_id;
表:Activity
问题:请编写一个 SQL 查询,描述每一个玩家首次登陆的设备名称,查询结果格式在以下示例中:
- SELECT player_id AS 'player_id', device_id AS 'device_id'
- FROM Activity
- WHERE (player_id, event_date) IN (SELECT player_id, min(event_date)
- FROM Activity
- GROUP BY player_id);
表:Problems
问题:写一个SQL查询低质量问题的ID集合。如果一个力扣问题的喜欢率(喜欢数除以总投票数)严格低于
60%
,则该问题为低质量问题,按problem_id
升序排列返回结果表,查询结果的格式如下所示:
- -- 解法1
- SELECT problem_id
- FROM Problems
- GROUP BY problem_id
- HAVING (SUM(likes) / (SUM(likes) + SUM(dislikes))) * 100 < 60
- ORDER BY problem_id ASC;
-
- -- 解法2
- SELECT problem_id
- FROM Problems
- WHERE (likes / (likes + dislikes)) < 0.6
- ORDER BY problem_id;
表: Customers
问题:写一个SQL查询来查询2021年具有正收入的客户,可以按任意顺序返回结果表,查询结果格式如下例:
- SELECT customer_id
- FROM Customers
- WHERE year = 2021 AND revenue > 0;
表:Products
问题:写出SQL语句,查找既是低脂又是可回收的产品编号,返回结果无顺序要求,查询结果格式如下例所示:
- SELECT product_id
- FROM Products
- WHERE low_fats = 'Y' AND recyclable = 'Y';
表: Employees
问题:编写一个SQL查询以计算每位员工每天在办公室花费的总时间(以分钟为单位),请注意,在一天之内,同一员工是可以多次进入和离开办公室的。在办公室里一次进出所花费的时间为out_time 减去 in_time,返回结果表单的顺序无要求,查询结果的格式如下:
- SELECT event_day AS day, emp_id, SUM(out_time - in_time) AS total_time
- FROM Employees
- GROUP BY day, emp_id
表:DailySales
问题:写一条 SQL 语句,使得对于每一个
date_id
和make_name,
返回不同的lead_id
以及不同的partner_id
的数量,按任意顺序返回结果表,查询结果格式如下示例所示:
- SELECT date_id, make_name,
- COUNT(DISTINCT lead_id) AS unique_leads,
- COUNT(DISTINCT partner_id) AS unique_partners
- FROM DailySales
- GROUP BY date_id, make_name;
表:Activities
问题:编写一个SQL查询来查找每个日期、销售的不同产品的数量及其名称,每个日期的销售产品名称应按词典序排列,返回按
sell_date
排序的结果表,查询结果格式如下例所示:
- SELECT sell_date, COUNT(DISTINCT product) AS num_sold,
- GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
- FROM Activities
- GROUP BY sell_date
- ORDER BY sell_date ASC;
表:Employees
表:Salaries
问题:写出一个查询语句,找到所有丢失信息的雇员id。当满足下面其中一个条件时,就被认为是雇员的信息丢失:
1.雇员的姓名丢失了
2.雇员的薪水信息丢失了
返回这些雇员的id(employee_id
),
从小到大排序,查询结果格式如下面的例子所示:
- SELECT a.employee_id
- FROM Employees AS a
- LEFT JOIN Salaries AS b
- ON a.employee_id = b.employee_id
- WHERE b.salary IS Null
- UNION
- SELECT b.employee_id
- FROM Employees AS a
- RIGHT JOIN Salaries AS b
- ON a.employee_id = b.employee_id
- WHERE a.name IS NULL
- ORDER BY employee_id ASC;
表:Sales
表:Product
问题:写一条SQL查询语句获取
Sales
表中所有产品对应的产品名称product_name 以及该产品的所有售卖年份year和价格price,查询结果中的顺序无特定要求,查询结果格式示例如下:
- SELECT b.product_name, a.year, a.price
- FROM Sales AS a
- INNER JOIN Product AS b
- ON a.product_id = b.product_id
- ORDER BY a.year ASC;
表:Sales
表:Product
问题:编写一个SQL查询,按产品id(product_id)来统计每个产品的销售总量,查询结果格式如下面例子所示:
- SELECT a.product_id, SUM(b.quantity) AS total_quantity
- FROM Product AS a
- INNER JOIN Sales AS b
- ON a.product_id = b.product_id
- GROUP BY b.product_id;
表:Weather
问题:编写一个SQL查询,来查找与之前(昨天的)日期相比温度更高的所有日期的id,返回结果不要求顺序,查询结果格式如下例:
- -- DATEDIFF函数,可以计算两者的日期差
- -- DATEDIFF('2007-12-31','2007-12-30');
- -- DATEDIFF('2010-12-30','2010-12-31');
-
- SELECT b.Id
- FROM Weather as a,Weather as b
- WHERE a.Temperature < b.Temperature and DATEDIFF(a.recordDate,b.recordDate) = -1;
表:Person
问题:编写一个SQL查询来删除所有重复的电子邮件,只保留一个id最小的唯一电子邮件,以任意顺序返回结果表,查询结果格式如下所示:
- -- 解法1
- DELETE P1
- FROM Person AS P1, Person AS P2
- WHERE P1.Email = P2.Email -- 利用where进行自连接
- AND P1.Id > P2.Id; -- 选择Id较大的行
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。