如下为数据库的两张表,请根据各小题的输出结果,写出各小题的sql语句。
1)输出第一个名字(First_name)包含’o’的所有雇员信息。
输出结果如下:
输出结果如下:
SQL:
2)输出每月的总支出工资大于1500000的部门和对应的支出,按高到低排序。
输出结果如下:
SQL:
3)输出有奖金(Incentive)和没奖金的人数。
输出结果如下:
SQL:
select * from Employee where First_name like '%o%';
selectdept,sum(total_salary) as total_salaryfrom (selectb.dept,month(date) as mon,sum(salary) as total_salaryfrom incentive ajoin employees b on a.emp_id=b.emp_idgroup byb.dept,month(date)having avg(salary) > 15000000) tgroup by dept;
select sum(case when Incentive_amount is not null then 1 else 0 end) as 有奖金, sum(case when Incentive_amount is null then 1 else 0 end) as 没奖金 from Employees left join (select distinct Employee_id, Incentive_amount from Incentive) temp on Employees.Employee_id=temp.Employee_id;
1. 输出第一个名字(First_name)包含’o’的所有雇员信息。 SELECT * FROM employees WHERE first_name LIKE '%o%'; 或者 SELECT * FROM employees WHERE locate('o', first_name) > 0; 2. 输出每月的总支出工资大于1500000的部门和对应的支出,按高到低排序。 SELECT dept, SUM(total_amt) AS total_amt FROM ( -- 每月支出都要求大于1500000 SELECT b.dept, month(date) AS mon, SUM(amount) AS total_amt FROM incentive a JOIN employees b ON a.emp_id = b.emp_id GROUP BY b.dept, month(date) HAVING AVG(amount) > 15000000 ) t GROUP BY dept ; 3. 输出有奖金(Incentive)和没奖金的人数。 SELECT SUM(IF(b.emp_id IS NOT NULL, 1, 0)) AS `有奖金`, SUM(IF(b.emp_id IS NOT NULL, 0, 1)) AS `没奖金` FROM employees a LEFT JOIN ( SELECT emp_id FROM incentive GROUP BY emp_id ) b ON a.emp_id = b.emp_id ;
SELECT have "有", nohave1 "没有" FROM ( SELECT bianhao, CASE bianhao WHEN 1 THEN num ELSE 0 END have, CASE bianhao WHEN 0 THEN num ELSE 0 END nohave FROM ( SELECT CASE ids WHEN 1 THEN 1 ELSE 0 END as bianhao, num FROM ( SELECT cishu > 0 ids , COUNT(1) num fROM employee e LEFT JOIN ( SELECT employee_id, count(1) as cishu FROM incentive GROUP BY employee_id ) as i ON e.employee_id = i.employee_id GROUP BY cishu > 0 ) temp ) as tmp ) tmp JOIN ( SELECT bianhao as bianhao1, CASE bianhao WHEN 1 THEN num ELSE 0 END have1, CASE bianhao WHEN 0 THEN num ELSE 0 END nohave1 FROM ( SELECT CASE ids WHEN 1 THEN 1 ELSE 0 END as bianhao, num FROM ( SELECT cishu > 0 ids , COUNT(1) num fROM employee e LEFT JOIN ( SELECT employee_id, count(1) as cishu FROM incentive GROUP BY employee_id ) as i ON e.employee_id = i.employee_id GROUP BY cishu > 0 ) temp ) as tmp ) as tmp1 WHERE tmp.bianhao > tmp1.bianhao1