剑指offer SQL训练
来源:互联网 发布:网络摄像头电脑客户端 编辑:程序博客网 时间:2024/06/08 15:25
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`));
答案:
select *from employees as eorder by hire_date desclimit 0,1 --limit后参数为开始点,及长度
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`));
答案:
select *from employees as eorder by hire_date desc limit 2,1
3. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号
查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号
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 `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`));
答案:
select s.*, d.dept_nofrom salaries as s inner join dept_manager as don s.emp_no = d.emp_no and s.to_date = d.to_datewhere s.to_date = '9999-01-01'
4. 查找所有已经分配部门的员工的last_name和first_name
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 `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`));答案:
select e.last_name,e.first_name,d.dept_nofrom dept_emp as d inner join employees as eon d.emp_no = e.emp_no
5. 查找所有员工的last_name和first_name以及对应部门
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 `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`));答案:
select e.last_name,e.first_name,d.dept_nofrom employees as e left join dept_emp as don e.emp_no = d.emp_no
6. 查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_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`));答案:
select e.emp_no,s.salaryfrom employees as e inner join salaries as son e.emp_no = s.emp_no and e.hire_date = s.from_dateorder by s.emp_no desc
7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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`));答案:
select emp_no,count(emp_no) as tfrom salaries group by emp_nohaving t > 15
8. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
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`));
select distinct s.salaryfrom salaries as swhere to_date = '9999-01-01'order by s.salary desc
9. 获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
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 `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`));答案:
select d.dept_no,d.emp_no,s.salaryfrom salaries as s inner join dept_manager as don s.emp_no = d.emp_no and s.to_date = '9999-01-01' and s.to_date = d.to_date
10. 获取所有非manager的员工emp_noCREATE 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`));
答案:
select e.emp_nofrom employees as ewhere e.emp_no not in (select d.emp_no from dept_manager as d )select e.emp_nofrom employees as eexcept select d.emp_no from dept_manager as d
11. 获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 `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`));
答案:
select e.emp_no,m.emp_nofrom dept_emp as e inner join dept_manager as mon e.dept_no = m.dept_no and e.to_date = '9999-01-01' and e.to_date = m.to_date and e.emp_no != m.emp_no
12. 获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
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 `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`));
答案:
select d.dept_no,d.emp_no,max(s.salary)from dept_emp as d inner join salaries as s on d.emp_no = s.emp_no and d.to_date = s.to_date and d.to_date = '9999-01-01'group by d.dept_no
13. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t
CREATE TABLE IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL,`title` varchar(50) NOT NULL,`from_date` date NOT NULL,`to_date` date DEFAULT NULL);
答案:
select titles.title,count(title) as tfrom titlesgroup by titlehaving t >= 2
14. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略
CREATE TABLE IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL,`title` varchar(50) NOT NULL,`from_date` date NOT NULL,`to_date` date DEFAULT NULL);
答案:
select titles.title,count(distinct titles.emp_no) as tfrom titlesgroup by titlehaving t >= 2
15. 查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
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`));
答案:
select *from employees where emp_no % 2 =1 and last_name != 'Mary'order by hire_date desc
16. 统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg
REATE 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`));CREATE TABLE IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL,`title` varchar(50) NOT NULL,`from_date` date NOT NULL,`to_date` date DEFAULT NULL);
答案:
select t.title,avg(s.salary) as avgfrom titles as t inner join salaries as son t.emp_no = s.emp_no where t.to_date = '9999-01-01' and t.to_date = s.to_dategroup by t.title
17. 获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
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`));
答案:
select emp_no,salaryfrom salaries where salary = (select salary from salaries group by salary order by salary desc limit 1,1)
18. 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
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`));
答案:
select e.emp_no, max(s.salary) as salary, e.last_name, e.first_name from employees as e inner join salaries as s on e.emp_no = s.emp_no and s.to_date = '9999-01-01'where s.salary <> (select max(salary) from salaries where to_date = '9999-01-01')
19. 查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
CREATE TABLE `departments` (`dept_no` char(4) NOT NULL,`dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_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 `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`));
答案:
select e.last_name,e.first_name,d2.dept_namefrom (employees as e left join dept_emp as d on e.emp_no = d.emp_no) as d1 left join departments as d2on d1.dept_no = d2.dept_no
20. 查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth
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`));
答案:
select (max(salary) - min(salary)) as growthfrom salarieswhere emp_no = '10001'
21. 查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
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`));
答案:
select a.emp_no,(a.salary-b.salary) as growthfrom (select e.emp_no,s.salary from employees as e inner join salaries as s on e.emp_no = s.emp_no and s.to_date = '9999-01-01') as a --生成员工当前薪资表inner join(select e1.emp_no,s1.salary from employees as e1 inner join salaries as s1 on e1.emp_no = s1.emp_no and e1.hire_date = s1.from_date) as b --生成员工入职薪资表on a.emp_no = b.emp_noorder by growth
22. 统计各个部门对应员工涨幅的次数总和,给出部门编码dept_no、部门名称dept_name以及次数sum
CREATE TABLE `departments` (`dept_no` char(4) NOT NULL,`dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_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 `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`));
select a.dept_no,a.dept_name,count(s.salary) as sumfrom departments as a inner join dept_emp as b on a.dept_no = b.dept_noinner join salaries as s on b.emp_no = s.emp_nogroup by a.dept_no
23. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照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`));
答案:
select s1.emp_no,s1.salary,count(distinct s2.salary) as rankfrom salaries as s1,salaries as s2where s1.salary <= s2.salary and s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'group by s1.emp_noorder by s1.salary desc, s1.emp_no asc
24. 获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date='9999-01-01'
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`));
select d.dept_no,d.emp_no,s.salaryfrom dept_emp as d inner join salaries as son d.emp_no == s.emp_no and d.to_date = s.to_date and d.to_date='9999-01-01'where d.emp_no not in (select emp_no from dept_manager)
25. 取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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 `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`));
链接:https://www.nowcoder.com/questionTerminal/f858d74a030e48da8e0f69e21be63bef
来源:牛客网
来源:牛客网
本题主要思想是创建两张表(一张记录当前所有员工的工资,另一张只记录部门经理的工资)进行比较,具体思路如下:
1、先用INNER JOIN连接salaries和demp_emp,建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager,建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工,并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
select aa.emp_no,bb.emp_no,aa.salary,bb.salaryfrom (dept_emp as a inner join salaries as son a.emp_no = s.emp_no and s.to_date = '9999-01-01') as aa inner join (dept_manager as b inner join salaries as s1on b.emp_no = s1.emp_no and s1.to_date = '9999-01-01' ) as bbon aa.dept_no = bb.dept_nowhere aa.salary > bb.salary
26. 汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
CREATE TABLE `departments` (`dept_no` char(4) NOT NULL,`dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_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 IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL,`title` varchar(50) NOT NULL,`from_date` date NOT NULL,`to_date` date DEFAULT NULL);
select d.dept_no,d.dept_name,t.title,count(t.title) as countfrom departments d inner join dept_emp e on d.dept_no == e.dept_noinner join titles t on e.emp_no = t.emp_no and t.to_date = e.to_date and t.to_date = '9999-01-01'group by d.dept_no,t.title
27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列
提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
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`));
select s1.emp_no,s1.from_date,(s1.salary - s2.salary)as salary_growthfrom salaries as s1,salaries as s2where s1.emp_no = s2.emp_no and salary_growth > 5000 and (strftime('%Y', s1.to_date) - strftime('%Y', s2.to_date) = 1 or strftime('%Y',s1.from_date) - strftime('%Y',s2.from_date) =1)order by salary_growth desc
28. 查找描述信息中包括robot的电影对应的分类(分类原先对应电影数量>=5)名称以及电影数目
film表
字段说明film_id电影idtitle电影名称description电影描述信息
CREATE TABLE IF NOT EXISTS film (film_id smallint(5) NOT NULL DEFAULT '0',title varchar(255) NOT NULL,description text,PRIMARY KEY (film_id));
category表
字段说明category_id电影分类idname电影分类名称last_update电影分类最后更新时间
CREATE TABLE category (category_id tinyint(3) NOT NULL ,name varchar(25) NOT NULL, `last_update` timestamp,PRIMARY KEY ( category_id ));
film_category表
字段说明film_id电影idcategory_id电影分类idlast_update电影id和分类id对应关系的最后更新时间
答案:CREATE TABLE film_category (film_id smallint(5) NOT NULL,category_id tinyint(3) NOT NULL, `last_update` timestamp);
select name,count(name)from film,film_category,categorywhere film.description like '%robot%' and film.film_id= film_category.film_id and film_category.category_id= category.category_id and category.category_id in (select category_id from film_category group by category_id having count(film_id)>=5)
28. 使用join查询方式找出没有分类的电影id以及名称
film表
字段说明film_id电影idtitle电影名称description电影描述信息
CREATE TABLE IF NOT EXISTS film (film_id smallint(5) NOT NULL DEFAULT '0',title varchar(255) NOT NULL,description text,PRIMARY KEY (film_id));
category表
字段说明category_id电影分类idname电影分类名称last_update电影分类最后更新时间
CREATE TABLE category (category_id tinyint(3) NOT NULL ,name varchar(25) NOT NULL, `last_update` timestamp,PRIMARY KEY ( category_id ));
film_category表
字段说明film_id电影idcategory_id电影分类idlast_update电影id和分类id对应关系的最后更新时间
答案:CREATE TABLE film_category (film_id smallint(5) NOT NULL,category_id tinyint(3) NOT NULL, `last_update` timestamp);
select f.film_id,f.titlefrom film as f left join film_category as fcon f.film_id = fc.film_idwhere fc.category_id is NULL
29. 使用子查询的方式找出属于Action分类的所有电影对应的title,description
film表
字段说明film_id电影idtitle电影名称description电影描述信息
CREATE TABLE IF NOT EXISTS film (film_id smallint(5) NOT NULL DEFAULT '0',title varchar(255) NOT NULL,description text,PRIMARY KEY (film_id));
category表
字段说明category_id电影分类idname电影分类名称last_update电影分类最后更新时间
CREATE TABLE category (category_id tinyint(3) NOT NULL ,name varchar(25) NOT NULL, `last_update` timestamp,PRIMARY KEY ( category_id ));
film_category表
字段说明film_id电影idcategory_id电影分类idlast_update电影id和分类id对应关系的最后更新时间
答案:CREATE TABLE film_category (film_id smallint(5) NOT NULL,category_id tinyint(3) NOT NULL, `last_update` timestamp);
select title,description from film where film_id in(select film_id from film_category where category_id=(select category_id from category where name='Action'));
30. 获取select * from employees对应的执行计划
答案:explain select * from employees
31. 将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`));
select (last_name || ' ' || first_name) as Namefrom employees
32. 创建一个actor表,包含如下列信息
create table actor( actor_id smallint(5) not null primary key, first_name varchar(45) not null, last_name varchar(45) not null, last_update timestamp not null default(datetime('now','localtime')));
33. 对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
答案:
insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
34. 对于表actor批量插入如下数据,如果数据已经存在,请忽略,不使用replace操作
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id first_name last_name last_update '3''ED''CHASE''2006-02-15 12:34:33'
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
insert or ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33')
35. 创建一个actor_name表,将actor表中的所有first_name以及last_name导入改表
对于如下表actor,其对应的数据为:
actor_id first_name last_name last_update 1PENELOPEGUINESS2006-02-15 12:34:332NICKWAHLBERG2006-02-15 12:34:33
创建一个actor_name表,将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下:
列表 类型 是否为NULL 含义 first_namevarchar(45)not null名字last_namevarchar(45)not null姓氏
创建一个actor_name表,将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下:
答案:
create table actor_name( first_name varchar(45) not null, last_name varchar(45) not null);insert into actor_name select first_name,last_name from actor
36. 对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
针对如下表actor结构创建索引:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
答案:
create unique index uniq_idx_firstname on actor(first_name);create index idx_lastname on actor(last_name);
37. 针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,fist_name为first_name_v,last_name修改为last_name_v:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
create view actor_name_view as select first_name as fist_name_v,last_name as last_name_vfrom actor
`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`));
create index idx_emp_no on salaries(emp_no);
答案:
select * from salaries indexed by idx_emp_nowhere emp_no = '10005'
39. 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为'0000 00:00:00'
存在actor表,包含如下列信息:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));
答案:
alter table actor add create_date datetime not null default('0000-00-00 00:00:00')
40. 构造一个触发器audit_log,在向employees表中插入一条数据的时候,触发插入相关的数据到audit中
CREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);CREATE TABLE audit(EMP_no INT NOT NULL,NAME TEXT NOT NULL);
答案:
create trigger audit_log after insert on employees_testbegin insert into audit values(new.ID,new.NAME);end
41.删除emp_no重复的记录,只保留最小的id对应的记录
CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key,emp_no int(11) NOT NULL,title varchar(50) NOT NULL,from_date date NOT NULL,to_date date DEFAULT NULL);
答案:
delete from titles_test where id not in (select min(id) from titles_test group by emp_no)
42. 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01
CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key,emp_no int(11) NOT NULL,title varchar(50) NOT NULL,from_date date NOT NULL,to_date date DEFAULT NULL);
答案:
update titles_test set to_date = null, from_date = '2001-01-01'where to_date = '9999-01-01'
CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key,emp_no int(11) NOT NULL,title varchar(50) NOT NULL,from_date date NOT NULL,to_date date DEFAULT NULL);
replace into titles_test values(5,10005,'Senior Engineer','1986-06-26','9999-01-01')
44. 将titles_test表名修改为titles_2017
答案:
alter table titles_test rename to titles_2017
45. 在audit表上创建外键约束,其emp_no对应employees_test表的主键id
CREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);CREATE TABLE audit(EMP_no INT NOT NULL,create_date datetime NOT NULL);
DROP TABLE audit;CREATE TABLE audit( EMP_no INT NOT NULL, create_date datetime NOT NULL, FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));
存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据?
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`));
select * from emp_v
47. 将所有获取奖金的员工当前的薪水增加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`));
答案:
update salaries set salary = salary * 1.1where emp_no in(select b.emp_no from emp_bonus as b inner join salaries as s on b.emp_no = s.emp_no and s.to_date = '9999-01-01')
答案:
SELECT "select count(*) from " || name || ";" AS cntsFROM sqlite_master WHERE type = 'table'
49. 将employees表中的所有员工的last_name和first_name通过(')连接起来
答案:
SELECT last_name || "'" || first_name FROM employees
50. 查找字符串'10,A,B' 中逗号','出现的次数cnt
答案:
select length('10,A,B') - length(replace('10,A,B',",",""))
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`));
答案:
select first_namefrom employeesorder by substr(first_name,-2)
52. 按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
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`));
select dept_no,group_concat(emp_no) as employeesfrom dept_empgroup by dept_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`));
答案:
select avg(salary) avg_salaryfrom salarieswhere salary not in(select max(salary) from salaries union select min(salary) from salaries) and to_date = '9999-01-01'
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`));
select * from employeeslimit 5,5
55. 使用含有关键字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`));
答案:
select * from employees ee where not exists ( select emp_no from dept_emp de where de.emp_no = ee.emp_no)
阅读全文
0 0
- 剑指offer SQL训练
- 剑指 Offer 专项训练
- 剑指offer | 训练题44:扑克牌顺子
- 剑指offer | 训练题42:左旋转字符串
- 剑指offer | 训练题43:翻转单词顺序列
- 剑指offer | 训练题46:求1+2+3+...+n
- 剑指offer | 训练题47:不用加减乘除做加法
- 剑指offer | 训练题48:把字符串转换成整数
- 剑指offer | 训练题49:数组中重复的数字
- 剑指offer | 训练题50:构建乘积数组
- 剑指offer | 训练题51:正则表达式匹配
- 剑指offer | 训练题52:表示数值的字符串
- 剑指offer | 训练题54:链表中环的入口结点
- 剑指offer | 训练题55:删除链表中重复的结点
- 剑指offer | 训练题56:二叉树下一个结点
- 剑指offer | 训练题57:对称的二叉树
- 剑指offer | 训练题58:按之字形打印二叉树
- 剑指offer | 训练题59:把二叉树打印成多行
- Hive中日期函数总结
- 设计模式-模板方法模式 C++实现
- android OpenGL es图书和学习资料
- Linux 常用20条命令 Mark
- 【解决!!!】MyEclipse 2015 stable3.0 完美破解,解决启动闪退问题
- 剑指offer SQL训练
- 2017/9/16随笔--Jquery源码解读
- Javascript 事件,思维导图
- CCF CSP 201509-1 数列分段(Java-100分)
- HTML学习整理
- 【LeetCode 32】Longest Valid Parentheses【H】
- MySQL ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
- c#知识扩展篇
- Linux 下 lua 开发环境安装及安装 luafilesystem