mysql 查询实战3-题目

看了mysql 查询实战2-解答，里面有个新知识，异或的解决交换座位的处理，接着看实战3的题目

11、查询每⽉产品交易与退款情况

目标：查询每⽉产品交易（交易总额，交易数）与退款情况（退款总额，退款数）

1，建表

create table transactions(id int not null auto_increment,product_id int,state enum('success','proceding'),amount int default 0,trans_date date,primary key(id)
);create table refund(trans_id int,trans_date date);

2，插入数据

insert into transactions values
(1,1001,'success',1000,'2020-01-02'),
(2,1002,'proceding',2000,'2020-01-03'),
(3,1001,'success',3000,'2020-01-10'),
(4,1001,'success',4000,'2020-02-13'),
(5,1002,'success',5000,'2020-02-15');insert into refund values
(2,'2020-02-29'),
(1,'2020-02-10'),
(5,'2020-03-30');

12、查询活动产品的平均价格

1，建表

create table activity(id int not null auto_increment,product_id int,price int default 0,start_date date,end_date date,primary key(id)
);create table sold(product_id int,num int,purchase_date date
);

2，插入数据

insert into activity values
(1,1001,100,'2020-01-15','2020-01-20'),
(2,1001,200,'2020-02-02','2020-02-15'),
(3,1002,300,'2020-01-15','2020-01-20'),
(4,1002,400,'2020-02-02','2020-02-15');insert into sold values
(1001,30,'2020-01-19'),
(1001,10,'2020-01-19'),
(1001,20,'2020-02-10'),
(1002,40,'2020-01-20'),
(1002,60,'2020-02-11');

13、统计部⻔每⽉收⼊概况

每月，就是1到12月的收入情况

1，建表

create table department_income(department_id int,income int,month char(3)
);

2，插入数据

insert into department_income(department_id,income,month)values
(1,20,'Jan'),
(2,20,'Jan'),
(2,20,'Feb'),
(1,20,'Mar'),
(3,20,'Apr'),
(1,20,'May'),
(2,20,'Jun'),
(1,20,'Jul'),
(2,20,'Aug'),
(3,20,'Sep'),
(1,20,'Oct'),
(2,20,'Nov'),
(1,20,'Dec'),
(1,30,'Jan'),
(2,40,'Feb'),
(1,50,'Mar'),
(3,60,'Apr'),
(1,70,'May'),
(2,80,'Jun'),
(1,90,'Jul'),
(2,80,'Aug'),
(3,70,'Sep'),
(1,60,'Oct'),
(2,50,'Nov'),
(1,30,'Dec');

14、统计课程新学员数量

统计最近指定天数，比如180天内容，每天课程的新学员的数量

1，建表

DROP TABLE IF EXISTS learning_records;
create table learning_records(user_id int,course_id int,created_at date
);

2，插入数据

insert into learning_records values
(1, 3, '2020-02-01'),
(1, 3, '2020-02-03'),
(1, 3, '2020-02-05'),
(2, 3, '2020-04-04'),
(2, 3, '2020-04-11'),
(2, 3, '2020-05-20'),
(3, 3, '2020-02-02'),
(3, 3, '2020-03-01'),
(4, 3, '2020-03-01'),
(4, 3, '2019-03-15'),
(5, 3, '2020-04-04'),
(5, 3, '2020-04-06'),
(1, 10, '2019-11-01'),
(2, 10, '2020-03-01'),
(3, 10, '2020-05-01');

15、平均销售额：部⻔与公司对⽐

目标：计算每月部门和公司的收入比较

1，建表

create table income(id int not null auto_increment,sale_id int,income int,created_at date,primary key(id)
);create table sales_person(id int not null auto_increment,department_id int,primary key(id)
);

2，插入数据

insert into sales_person values
(1,1),
(2,2),
(3,2);insert into income values
(1, 1, 9000, '2020-04-03'),
(2, 2, 8000, '2020-04-02'),
(3, 3, 5000, '2020-04-01'),
(4, 1, 10000, '2020-05-03'),
(5, 2, 9000, '2020-05-02'),
(6, 3, 4000, '2020-05-01'),
(7, 1, 11000, '2020-03-03'),
(8, 2, 600, '2020-03-02'),
(9, 3, 15000, '2020-03-01'),
(10, 1, 17000, '2020-04-06'),
(11, 2, 5000, '2020-04-08'),
(12, 3, 7000, '2020-04-09');