数据库操作练习9

1. 将所有获取奖金的员工当前的薪水增加10%

题目描述

将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied 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`));

sql1:

        update salaries set salary=salary+salary*0.1 where emp_no in (select emp_no from emp_bonus) and to_date='9999-01-01'

sql2:

        update salaries set salary=salary*1.1 where emp_no in
(select sa.emp_no from salaries sa inner join emp_bonus em on sa.emp_no=em.emp_no and sa.to_date='9999-01-01')

2. 获取所有员工的emp_no

题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示
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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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 `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`));
输出格式:
e.emp_nodept_nobtyperecevied10001d00112010-01-0110002d00122010-10-0110003d00432011-12-0310004d00412010-01-0110005d003 10006d002 10007d005 10008d005 10009d006 10010d005 10010d006

select e.emp_no,e.dept_no,em.btype,em.recevied from dept_emp e left join emp_bonus em on e.emp_no=em.emp_no

3. 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

题目描述

将employees表的所有员工的last_name和first_name拼接起来作为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`));

输入描述:

无

输出描述:

NameFacello GeorgiSimmel BezalelBamford PartoKoblick ChirstianMaliniak KyoichiPreusig AnnekeZielinski TzvetanKalloufi SaniyaPeac SumantPiveteau DuangkaewSluis Mary    

这题用CONCAT(last_name,' ',first_name )始终通不过，由于concat函数来只支持MySQL、SQL Server、Oracle等数据库，SQLite数据库连接字符串要用连接符号"||"

select last_name||' '||first_name as Name from employees