MYSqL测试题2_mysql练习题2

本文结合猴子老师(知乎：'猴子聊数据分析')的常见sql问题，写出了全部解答过程，欢迎大家批评指正。猴子：常见的SQL笔试题和面试题(上)：经典50题​zhuanlan.zhihu.com猴子：常见的SQL笔试题和面试题(下)​zhuanlan.zhihu.com

14创建两张表employees和department如下：

drop table if exists employees;

create table employees(

employee_id int,

first_name char(20),

department_id int,

salary bigint);

drop table if exists departments;

create table departments(

department_id int,

department_name char(30),

manager_id int,

location_id int);

insert into employees values(100,'Steven',90,24000);

insert into employees values(101,'Neena',90,17000);

insert into employees values(102,'Lex',90,17000);

insert into employees values(103,'Alexander',60,9000);

insert into employees values(104,'Bruce',60,6000);

insert into employees values(105,'David',60,4800);

insert into employees values(106,'Valli',60,4800);

insert into employees values(107,'Diana',60,4200);

insert into employees values(108,'Nancy',100,12000);

insert into employees values(109,'Danicel',100,9000);

insert into employees values(110,'John',100,82000);

insert into employees values(111,'Ismael',100,77000);

insert into employees values(112,'Jose Manuel',100,78000);

insert into employees values(113,'Luis',100,69000);

insert into employees values(114,'Den',30,11000);

insert into employees values(115,'Alexander',30,31000);

insert into employees values(116,'Shelli',30,29000);

insert into employees values(117,'Sigal',30,28000);

insert into departments values(30,'Purchasing',114,1700);

insert into departments values(60,'Purchasing',103,1400);

insert into departments values(90,'Purchasing',100,1700);

insert into departments values(100,'Purchasing',108,1700);

(1)写出SQL得出每个部门的平均工资。

select department_id

,avg(salary) as avg_salary

from employees

group by department_id

;

(2)查询量表得出如下结果：

select e.first_name

,d.department_name

,e.salary

from departments d,employees e

where d.department_id=e.department_id

group by e.first_name

,d.department_name

,e.salary

;

15.如有有一张表player，有id、name两个列，我想知道这张表有多少行数据，id最大的前10位的name是什么，请问这两个SQL语句如何写？

--行数：

select count(id) from player;

--id最大的前10位的name是什么?

drop table if exists player;

create table player(

id int,

name char(20));

insert into player values(1,'Stven');

insert into player values(2,'Steen');

insert into player values(3,'Steven');

insert into player values(4,'Steen');

insert into player values(5,'Stevn');

insert into player values(6,'Steve');

insert into player values(7,'Seven');

insert into player values(8,'Stevyen');

insert into player values(9,'Steven');

insert into player values(10,'Steven');

insert into player values(11,'Sterven');

select name from player

order by id desc

limit 10

;

16.查询一张数据表(tb)，基本字段：日期，订单

要求用SQL实现：

周次(week)，订单总和，日均订单，极大值订单，极小值订单

--计算两个日期之间相差多少周

select timestampdiff(week,'2017-9-30','2018-11-02'); --56周

--计算两个日期之间相差多少天

select timestampdiff(day,'2017-9-30','2018-11-02');--398天

select timestampdiff(week,'2017-9-30','2018-11-02') as week

,sum(order)

,sum(order)/timestampdiff(day,'2017-9-30','2018-11-02') as avg_order

,max(order)

,min(order)

from tb

group by 1;

---行转列---

17.使用SQL实现以下数据表及总分，平均分(数据表：table)

select ifnull(s_id,'total') as s_id

,sum(if(c_id=01,s_score,0)) as '语文'

,sum(if(c_id=02,s_score,0)) as '数学'

,sum(if(c_id=03,s_score,0)) as '外语'

,sum(if (c_id=04,s_score,0)) as '体育'

,sum(if (c_id=05,s_score,0)) as '物理'

,sum(if (c_id=06,s_score,0)) as '化学'

,sum(s_score) as total

from score

group by s_id with rollup;

18.表group有四个字段，表结构如下：

drop table if exists group;

create table group (

id bigint comment'群号',

name varchar(50) comment'群名',

class varchar(20) comment'群类别'

num int comment'群成员数量');

insert into group values(1,'一起打球','篮球',10);

insert into group values(2,'来玩球吧','篮球',15);

insert into group values(3,'滨江一霸','篮球',5);

insert into group values(4,'足球小将','足球',20);

insert into group values(5,'绝代双骄','足球',30);

insert into group values(6,'玩个球啊','乒乓',19);

PK为id

--求群数量少于1000的群类别?

select class from group1

where num<1000;

19区间划分问题，使用case语句

drop table if exists deptsales;

create table deptsales(

dept_id int,

submonth char(30),

sales int,

deptname varchar(20));

insert into deptsales values(1,1,55,'NULL');

insert into deptsales values(2,1,66,'NULL');

insert into deptsales values(3,1,77,'NULL');

insert into deptsales values(2,2,34,'NULL');

insert into deptsales values(4,2,56,'NULL');

insert into deptsales values(3,3,78,'NULL');

---列转行--

select dept_id as '部门'

,sum(case when submonth=1 then sales else 0 end ) as '一月销售额'

,sum(case when submonth=2 then sales else 0 end) as '二月销售额'

,sum(case when submonth=3 then sales else 0 end) as '三月销售额'

,sum(case when submonth=4 then sales else 0 end) as '四月销售额'

from deptsales

group by dept_id

;

---计算每个部门每个月销售额及销售额总额--

select ifnull(dept_id,'总销售额') as dept_id

,sum(case when submonth=1 then sales else 0 end ) as '一月销售额'

,sum(case when submonth=2 then sales else 0 end) as '二月销售额'

,sum(case when submonth=3 then sales else 0 end) as '三月销售额'

,sum(case when submonth=4 then sales else 0 end) as '四月销售额'

,sum(sales) as '总销售额'

from deptsales

group by dept_id with rollup;