每日sql 1-员工奖金

Ask for a bonus less than1000employee names and bonuses

DDL

Create table If Not Exists Employee (EmpId int, Name varchar(255), Supervisor int, Salary int);Create table If Not Exists Bonus (EmpId int, Bonus int);insert into Employee (EmpId, Name, Supervisor, Salary) values (3, 'Brad', null, 4000);insert into Employee (EmpId, Name, Supervisor, Salary) values (1, 'John', 3, 1000);insert into Employee (EmpId, Name, Supervisor, Salary) values (2, 'Dan', 3, 2000);insert into Employee (EmpId, Name, Supervisor, Salary) values (4, 'Thomas', 3, 4000);insert into Bonus (EmpId, Bonus) values (2, 500);insert into Bonus (EmpId, Bonus) values (4, 2000);

sql

select Employee.Name,Bonus.Bonus FROM Employee join Bonus on Employee.EmpId=Bonus.EmpId where Bonus.Bonus <1000 or Bonus.Bonus is null;

2-Find the question with the highest answer rate

A table records the presentation of each questionshow和回答answer状态,Use if no answerskip代替answer状态.Find the question with the highest answer rate

DDL

Create table If Not Exists survey_log (uid int, action varchar(255), question_id int, answer_id int, q_num int, timestamp int);insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 285, null, 1, 123);insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'answer', 285, 124124, 1, '124');insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 369, null, 2, 125);insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'skip', 369, null, 2, 126);

sql

select question_id 
from survey_log
group by question_id
order by sum(case when action ="answer" then 1 else 0 end )/sum(case when action = "show" then 1 else 0 end) desc limit 1;