Manage Employment Data -2
practice window function and case when.
Link
Create Table
# question 1
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
# Question 2
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
# Question 3
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
#Question 4
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
如，输入为：
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');Questions
使用含有关键字exists查找未分配具体部门的员工的所有信息。
select *
from employees as e
where not exists (
select d.emp_no
from dept_emp as d
where d.emp_no = e.emp_no
);
# without using exists keyword
select *
from employees as e
where e.emp_no not in (
select d.emp_no
from dept_emp as d
);
2. SQL59 获取有奖金的员工相关信息
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
select e.emp_no, e.first_name,
e.last_name, b.btype,
s.salary, case when b.btype=1 then s.salary*0.1
when b.btype=2 then s.salary*0.2
else s.salary * 0.3 end bonus
from emp_bonus as b
left join employees as e on b.emp_no = e.emp_no
left join salaries as s on b.emp_no = s.emp_no
where s.to_date = "9999-01-01";
3. 按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。 具体结果如下Demo展示。
Note: 在 Window function 里面如果 func(column) over (...), 里面 over () 括号里面没有partition by 对row进行分区操作， 那么 window func在某一行row的操作是把当前这个row（包括当前的row）的前面所有row的值进行操作。 比如 window function是sum，那么在第10行的window function的输出就把1~10行的值给加起来
select s.emp_no, s.salary, sum(s.salary) over (order by s.emp_no) as running_total
from salaries as s
where s.to_date = "9999-01-01"
;4. 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
select a.first_name
from employees e left join
(
select e.first_name, ROW_NUMBER() over (order by e.first_name ) as r_number
from employees as e
) as a
on e.first_name = a.first_name
where a.r_number % 2 = 1
;
Conclusion
这里回顾了Window Function用法
在window function里面 func(..) over (..) 如果over()里面没有指定partition by进行分组， 那么window function 会对当前的row之前的所有row(包括当前的row) 进行window function的操作。 比如下面 sum salary 里面没有用partition by， 那么它就会第 i 行的running——total的值等于前面 1~ i行的salary的sum
select s.emp_no, s.salary, sum(s.salary) over (order by s.emp_no) as running_total from salaries as s where s.to_date = "9999-01-01" ;
Case when的用法回顾
select case when condition_a then do_a case when condition_b then do_b ... else default_value end column_alias_name from ...;
Last updated
Was this helpful?