赞
踩
答案:select product_id from Products where low_fats='Y' and recyclable='Y'
较为简单入门。
答案:select name from customer where referee_id <> 2 or referee_id is null
这里面需要注意的是id!=2的情况中,null也需要算进去,<>这种一般的逻辑符号是会忽略null的,所以需要需要加一个or来补充。
答案:select name,population,area
from World where area >=3000000 or population >=25000000
较为简单
答案:select distinct author_id as id from Views
where author_id=viewer_id
order by author_id
这里的逻辑是要找author_id=viewer_id的所有行,但是可以看到44 44 有两个,所以需要去重 就需要引入distinct这类语句。
答案:select·tweet_id from·Tweets
where·length(content)>15
这里需要注意的是 length() 函数的使用,()里面是属性 也是列,表示这列中的某个元素的字符长度超过15。
在 SQL 中,employee_id 是这个表的主键。 这个表包含了员工,他们的薪水和上级经理的id。 有一些员工没有上级经理(其 manager_id 是空值)。
查找这些员工的id,他们的薪水严格少于$30000
并且他们的上级经理已离职。当一个经理离开公司时,他们的信息需要从员工表中删除掉,但是表中的员工的manager_id
这一列还是设置的离职经理的id 。
返回的结果按照employee_id
从小到大排序。
查询结果如下所示:
解答:
select employee_id from Employees
where salary <30000 and manager_id not in(select employee_id from Employees)
order by employee_id
这个题的思路是先选出工资低于30000的员工id,然后对比 被选出的表当中的employee_id与employee_id,如果相等,则说明这个人的领导没有辞职,如果不在这个表中 那就说明他的领导辞职了。
select employee_id from Employees
: 这行代码是查询语句的开始,它指定了要从Employees
表中选择employee_id
字段。employee_id
是员工的唯一标识符。
where salary < 30000
: WHERE
子句用于过滤结果,只选择那些salary
字段值小于30000的记录。
and manager_id not in (select employee_id from Employees)
: 这个AND
条件进一步过滤了上一步的结果。它指定manager_id
字段的值不能是Employees
表中任何记录的employee_id
。换句话说,它排除了那些有直属上司的员工,只选择了那些没有直属上司的员工(即可能是部门领导或公司高层管理人员)。
order by employee_id
: ORDER BY
子句用于对结果进行排序。这里指定了按照employee_id
字段的值进行升序排序。
综上所述,这条SQL语句的作用是:从Employees
表中选择那些工资低于30000元且没有直属上司的员工的员工ID,并按员工ID进行排序。这通常用于找出公司中高层管理人员或独立工作的员工。
思路:因为这个表是奇数的,所以可以先 判断 id%2=0,是否是偶数,是偶数可以直接id-1,那就相当于变换了,如果id是最后一个的话 那么id不变,if id=select count(*)from Seat then id
,else否则 id+1 end 囊括了所有情况。
select(
case when id%2=0 then id-1
when id=(select count(*) from Seat) then id
else id+1 end
) as id,student from Seat order by id
请你编写一个解决方案:
February 2020
平均评分最高 的电影名称。如果出现平局,返回字典序较小的电影名称。字典序 ,即按字母在字典中出现顺序对字符串排序,字典序较小则意味着排序靠前。
返回结果格式如下例所示。
答案:(select name as results from movierating join users using(user_id) group by user_id order by count(movie_id) desc,name limit 1 ) union all (select title as results from movierating join movies using(movie_id) where DATE_FORMAT(created_at,'%Y-%m')='2020-02' group by movie_id order by avg(rating) desc,title limit 1 )
有点难了 ,需要细化 拆开来看,
这条SQL查询语句使用了`UNION ALL`操作符来合并两个查询的结果集。每个查询都从`movierating`表中获取数据,并与另一个表(`users`或`movies`)进行连接,然后进行分组、排序和限制结果数量。下面是对这条语句的逐行解释:
1. `(select name as results from movierating join users using(user_id) group by user_id order by count(movie_id) desc,name limit 1 )`:
- 这是一个子查询,它首先通过`JOIN`操作将`movierating`表和`users`表连接起来,使用`user_id`作为连接条件。
- `GROUP BY user_id`表示按用户ID进行分组。
- `ORDER BY count(movie_id) desc, name`表示首先按照每个用户的电影数量降序排序,如果电影数量相同,则按照用户的名字升序排序。
- `LIMIT 1`表示只选择排序后的第一条记录。
- `name as results`表示将`users`表中的`name`字段重命名为`results`。
2. `union all`:
- `UNION ALL`操作符用于合并两个`SELECT`语句的结果集,它不会去除重复的行。
3. `(select title as results from movierating join movies using(movie_id) where DATE_FORMAT(created_at,'%Y-%m')='2020-02' group by movie_id order by avg(rating) desc,title limit 1 )`:
- 这是第二个子查询,它通过`JOIN`操作将`movierating`表和`movies`表连接起来,使用`movie_id`作为连接条件。
- `WHERE DATE_FORMAT(created_at,'%Y-%m')='2020-02'`是一个过滤条件,它只选择`created_at`字段在2020年2月创建的电影。
- `GROUP BY movie_id`表示按电影ID进行分组。
- `ORDER BY avg(rating) desc, title`表示首先按照电影的平均评分降序排序,如果平均评分相同,则按照电影标题升序排序。
- `LIMIT 1`表示只选择排序后的第一条记录。
- `title as results`表示将`movies`表中的`title`字段重命名为`results`。
这条查询语句的目的是:
- 从第一个子查询中选择一个用户,该用户在`movierating`表中拥有最多的电影评分,且如果评分数量相同,则选择名字在字典序上排在最前的用户。
- 从第二个子查询中选择一个在2020年2月创建的电影,该电影在`movierating`表中拥有最高的平均评分,且如果平均评分相同,则选择标题在字典序上排在最前的电影。
- 最后,使用`UNION ALL`将这两个结果合并为一个结果集。
在学习SQL语法之前有必要解释一下什么是关系型数据库(Relational databases)?数据库由若干张表
(Table)组成,这里说的数据Table很像Excel里的表; 正如Excel里的表格,Table也是由 行(rows)
和列(columns)
组成
一个Table存储一个类别的数据,每一行是一条数据,每一列是这种数据的一个属性; Table就像一个二维的表格,列(columns)
是有限固定的,行(rows)
是无限不固定的
举个例子, 比如我们数据库里有一张表(Table)
. 是用来存储世界上所有的狗,每一行(rows)
是一条狗的信息。那么你会在这个狗Table里存储 哪些狗的属性列(columns)
呢?比如 编号,名字,体重,身高,品种,年龄,毛发颜色?。让我们来看一下这张表,暂且就叫表 Dog吧!
Table(表): Dog
Id | # Name(名字) | # Weight(体重) | # Height (身高) | # Type (品种) | # Age (月龄) | # Color (肤色) |
1 | Mike | 3 | 28 | 吉娃娃 | 10 | 白 |
2 | Sala | 6.5 | 40 | 柴犬 | 15 | 黄 |
3 | 黑狮 | 21.5 | 45 | 藏獒 | 26 | 黑 |
4 | 大圣 | 15 | 42 | 牧羊犬 | 20 | 黄 |
5 | Boy | 5.5 | 24 | 蝴蝶犬 | 6 | 白 |
... | ... | ... | ... | ... | ... | ... |
上面就是一个Dog表的简单展示,像这样一个狗数据库里,除了这张狗(Dog)表,还可能存在其他的表,比如有一张表是存狗品种信息, 另外有一张表是存狗主人信息的 ...
我们学习SQL, 其实最后目的就是在类似这张Dog表数据基础上, 可以通过SQL来回答诸如以下的一类问题: "现在全世界名字叫 'Mike'的狗有几条?", 或者 "有多少条狗的毛发是 红色的?", 等等。 通过SQL我们可以方便的去获取,统计这些信息,在接下来的更多章节中你会有更深的体会.
Id | Title | Director | Year | Length_minutes |
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug's Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Finding Nemo | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
12 | Cars 2 | John Lasseter | 2011 | 120 |
13 | Brave | Brenda Chapman | 2012 | 102 |
14 | Monsters University | Dan Scanlon | 2013 | 110 |
title
SELECT Title FROM movies;
id
为6的电影
year
上映的电影
year
上映的电影
现在有一个工厂网站由几台机器运行,每台机器上运行着 相同数量的进程 。编写解决方案,计算每台机器各自完成一个进程任务的平均耗时。
完成一个进程任务的时间指进程的'end' 时间戳
减去 'start' 时间戳
。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。
结果表必须包含machine_id(机器ID)
和对应的 average time(平均耗时) 别名 processing_time
,且四舍五入保留3位小数。
以 任意顺序 返回表。
这里需要知道的知识:
- select machine_id ,
-
- round(
-
- sum(if(activity_type ='end',timestamp,-timestamp))/count(distinct process_id )
-
- ,3) as processing_time
-
- from Activity
- group by machine_id
编写解决方案,找出与之前(昨天的)日期相比温度更高的所有日期的 id
。
返回结果 无顺序要求 。
需要知道的知识:
DATEDIFF()语句,DATEDIFF(datepart, date1, date2)
datepart
: 指定你想要比较的时间单位,例如天(day)、小时(hour)、分钟(minute)等。date1
: 第一个日期值。date2
: 第二个日期值。
SELECT DATEDIFF(day, '2024-01-01', '2024-05-24') AS DaysDifference;
- 编写解决方案,找出与之前(昨天的)日期相比温度更高的所有日期的 id
-
- 返回结果 无顺序要求
-
- select a.id from Weather a, Weather b
-
- where a.Temperature > b.Temperature and datediff (a.recordDate,b.recordDate) = 1
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案,来查找这些顾客的 ID ,以及他们只光顾不交易的次数。
返回以 任何顺序 排序的结果表。
需要知道的知识:
左连接 from 左表 left join 右表 on 条件
- SELECT customer_id, count(customer_id) count_no_trans
- FROM Visits v
- LEFT JOIN transactions t
- ON v.visit_id = t.visit_id
- WHERE transaction_id IS NULL
- GROUP BY customer_id;
count(*)
count(具体内容)
总结来说,COUNT(*)
用于获取总行数,而COUNT(具体内容)
用于获取非NULL值的数量。在编写SQL查询时,应根据实际需求选择合适的计数方式。
编写解决方案,报告每个奖金 少于 1000
的员工的姓名和奖金数额。
以 任意顺序 返回结果表。
- # Write your MySQL query statement below
- select name ,bonus
- from Employee a left join Bonus b
- on a.empId=b.empId
- where bonus < 1000 or bonus is null
查询出每个学生参加每一门科目测试的次数,结果按 student_id
和 subject_name
排序。
- select a.student_id ,a.student_name ,a.subject_name ,count(b.subject_name) as attended_exams
- from
- (select student_id ,student_name ,subject_name from Students cross join Subjects) as a
- left join Examinations b on a.student_id=b.student_id and a.subject_name=b.subject_name
- group by a.student_id,a.subject_name
- order by student_id
编写解决方案,找出所有影片描述为 非 boring
(不无聊) 的并且 id 为奇数 的影片。
返回结果按 rating
降序排列。
- select *
- from cinema
- where id%2=1 and description != 'boring'
- order by rating desc
需要知道 %2可以作为判断是否是奇偶数的标准。
编写解决方案以查找每种产品的平均售价。average_price
应该 四舍五入到小数点后两位。
- select p.product_id, ifnull(round(sum(price * units) / sum(units), 2), 0) as average_price
- from Prices p
- left join UnitsSold u on u.product_id = p.product_id
- and u.purchase_date between p.start_date and p.end_date
- group by p.product_id
在SQL中,IFNULL
是一个函数,它接受两个参数:第一个参数是可能为NULL
的表达式,第二个参数是当第一个参数为NULL
时的替代值。如果第一个参数不是NULL
,IFNULL
函数返回第一个参数的值;如果第一个参数是NULL
,则返回第二个参数的值。
这个题需要考虑空值的情况,也就是第一个表有可能是空的。
比如product_id=3 ,也有price但是销售量为0,那么此时它的平均售价需要为0才行。
请写一个 SQL 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
- select project_id ,round(avg(e.experience_years ),2)average_years
- from Project p left join Employee e
- on p.employee_id =e.employee_id
- group by p.project_id
这里就一个avg round 语句需要注意,左连接两个表,通过group by一筛选就完事 比较简单
编写解决方案统计出各赛事的用户注册百分率,保留两位小数。
返回的结果表按 percentage
的 降序 排序,若相同则按 contest_id
的 升序 排序。
- SELECT b.contest_id, ROUND(COUNT(b.user_id) * 100.0 / t.total, 2) AS percentage
- FROM Register b
- CROSS JOIN (SELECT COUNT(user_id) AS total FROM Users) AS t
- GROUP BY b.contest_id
- ORDER BY percentage DESC, b.contest_id;
思路:统计所有的users有多少可以作为一个表 和后面的表连接在一起,然后计数相除。
编写一个解决方案,找出至少有五个直接下属的经理。
以 任意顺序 返回结果表。
- select name from Employee where id in
- (
- select managerId from Employee group by managerId having count(1) >= 5
- )
思路 找出所在managerId中出现次数超过5的id,然后在原表中输出这些id所对应的name就行了。太绝了。
where xxxx in (一个表 select所写的条件表,这个表中 managerId出现的次数需要大于等于5)
group by XX按照XX分组,并且having count(1)大于等于5 就是说要求XX出现的次数大于5
用户的 确认率 是 'confirmed'
消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0
。确认率四舍五入到 小数点后两位 。
编写一个SQL查询来查找每个用户的 确认率 。
- select s.user_id,round(avg(if(c.action = 'confirmed',1,0)),2) confirmation_rate
- from Signups s
- left join
- Confirmations c
- on s.user_id=c.user_id
- group by s.user_id;
avg if 函数联合使用 太妙了。
编写解决方案,以获取 Sales
表中所有 sale_id
对应的 product_name
以及该产品的所有 year
和 price
。
返回结果表 无顺序要求 。
结果格式示例如下。
- SELECT activity_date AS day , COUNT(DISTINCT user_id) AS active_users
- FROM activity
- WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
- GROUP BY activity_date
展示每位用户的 唯一标识码(unique ID );如果某位员工没有唯一标识码,使用 null 填充即可。
你可以以 任意 顺序返回结果表。
select unique_ID,name from Employees left join EmployeeUNI on Employees.id = EmployeeUNI.id
查询每位老师在大学里教授的科目种类的数量。
以 任意顺序 返回结果表。
- select DISTINCT teacher_id, count(DISTINCT subject_id) as cnt
- from Teacher
- group by teacher_id
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。