数据库SQL实战

实战"/>

数据库SQL实战

1. 使用含有关键字exists查找未分配具体部门的员工的所有信息

1.1 题目描述

使用含有关键字exists查找未分配具体部门的员工的所有信息。
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));
输出格式:

1.2 语句实现

select e.* 
from employees e
where not exists (select emp_no from dept_emp dwhere d.emp_no = e.emp_no);

2. 获取有奖金的员工相关信息

2.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));
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));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
输出格式:

2.2 语句实现

select eb.emp_no, e.first_name, e.last_name,eb.btype, s.salary, 
(case
when eb.btype = 1 then 0.1*s.salary
when eb.btype = 2 then 0.2*s.salary
else 0.3*s.salary
end) as bonus
from emp_bonus eb
inner join employees e on eb.emp_no = e.emp_no
inner join salaries s on eb.emp_no = s.emp_no
where s.to_date = '9999-01-01';

3. 统计salary的累计和running_total

3.1 题目描述

按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。 具体结果如下Demo展示。
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));
输出格式:

3.2 语句实现

select emp_no, salary, 
sum(salary) over (order by emp_no) as running_total
from salaries
where to_date = '9999-01-01';

4. 对于employees表中，给出奇数行的first_name

4.1 题目描述

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
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’);
输出格式:

4.2 语句实现

/*
select f.first_name
from (select emp_no, first_name, row_number() over (order by first_name) as row_numfrom employees) as f
where row_num % 2 = 1
order by emp_no;
*/
select e1.first_name
from employees e1
where (select count(*) from employees e2where e1.first_name >= e2.first_name) % 2 = 1;