1、题目描述
查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
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 hire_date =
(select max(hire_date) from employees);
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 order by hire_date desc limit 2,1;
3、题目描述
查找各个部门当前(deptmanager.todate='9999-01-01')领导当前(salaries.todate='9999-01-01')薪水详情以及其对应部门编号deptno
(注:请以salaries表为主表进行查询，输出结果以salaries.empno升序排序，并且请注意输出结果里面deptno列是最后一列)
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));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL, -- '部门编号'
emp_no int(11) NOT NULL, -- '员工编号'
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
解题：
SELECT s.*, d.dept_no FROM salaries s , dept_manager d
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'
AND s.emp_no = d.emp_no;
4、题目描述
查找所有已经分配部门的员工的lastname和firstname以及deptno(请注意输出描述里各个列的前后顺序)
CREATE TABLE `deptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEemployees(empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (empno`));
解题
select e.last_name, e.first_name, d.dept_no
from employees as e
inner join dept_emp as d
on e.emp_no=d.emp_no;
5、题目描述
查找所有员工的lastname和firstname以及对应部门编号deptno，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)
CREATE TABLE `deptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEemployees(empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (empno`));
解题
select e.last_name, e.first_name, d.dept_no
from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no;
6、题目描述
查找所有员工入职时候的薪水情况，给出empno以及salary， 并按照empno进行逆序(请注意，一个员工可能有多次涨薪的情况)
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.salary
from employees e,salaries s
where e.emp_no=s.emp_no
and e.hire_date=s.from_date
order by e.emp_no desc;
7、题目描述
查找薪水变动超过15次的员工号empno以及其对应的变动次数t
CREATE TABLE salaries (
`empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
解题
select a.* from(
select emp_no, count(*) t
from salaries
group by emp_no) a
where t>15;
8、题目描述
找出所有员工当前(todate='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
CREATE TABLE salaries (
`empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
解题
select salary from salaries where to_date='9999-01-01'
group by salary
order by salary desc;
9、题目描述
获取所有部门当前(deptmanager.todate='9999-01-01')manager的当前(salaries.todate='9999-01-01')薪水情况，给出deptno, empno以及salary(请注意，同一个人可能有多条薪水情况记录)
CREATE TABLE `deptmanager(deptnochar(4) NOT NULL,empnoint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEsalaries(empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,from_date`));
解题
select dept_manager.dept_no,dept_manager.emp_no,salaries.salary
from dept_manager,salaries
where dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
and dept_manager.emp_no = salaries.emp_no;
10、题目描述
获取所有非manager的员工empno
CREATE TABLE `deptmanager(deptnochar(4) NOT NULL,empnoint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEemployees(empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (empno`));
如插入为:
INSERT INTO deptmanager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO deptmanager VALUES('d002',10006,'1990-08-05','9999-01-01');
INSERT INTO deptmanager VALUES('d003',10005,'1989-09-12','9999-01-01');
INSERT INTO deptmanager VALUES('d004',10004,'1986-12-01','9999-01-01');
INSERT INTO deptmanager VALUES('d005',10010,'1996-11-24','2000-06-26');
INSERT INTO deptmanager VALUES('d006',10010,'2000-06-26','9999-01-01');
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(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
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');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
解题
select e.emp_no from employees as e left join dept_manager as d
on e.emp_no = d.emp_no
where d.emp_no is null;
11、题目描述
获取所有员工当前的(deptmanager.todate='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的empno,第二列给出其manager对应的empno。
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));
如插入：
INSERT INTO deptemp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO deptemp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO deptemp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO deptemp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO deptemp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO deptemp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO deptemp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO deptemp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO deptemp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO deptemp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');
INSERT INTO deptmanager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO deptmanager VALUES('d002',10006,'1990-08-05','9999-01-01');
INSERT INTO deptmanager VALUES('d003',10005,'1989-09-12','9999-01-01');
INSERT INTO deptmanager VALUES('d004',10004,'1986-12-01','9999-01-01');
INSERT INTO deptmanager VALUES('d005',10010,'1996-11-24','2000-06-26');
INSERT INTO deptmanager VALUES('d006',10010,'2000-06-26','9999-01-01');
解题：
select de.emp_no,dm.emp_no as manager_no
from dept_manager as dm,dept_emp as de
where de.emp_no <> dm.emp_no
and de.dept_no = dm.dept_no
and dm.to_date='9999-01-01';
12、题目描述
获取所有部门中当前(deptemp.todate = '9999-01-01')员工当前(salaries.todate='9999-01-01')薪水最高的相关信息，给出deptno, empno以及其对应的salary
CREATE TABLE `deptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEsalaries(empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
如插入：
INSERT INTO deptemp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO deptemp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO deptemp VALUES(10003,'d001','1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10001,90000,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,90000,'1996-08-03','1997-08-03');
解题：
select d.dept_no, d.emp_no, max(s.salary)
from dept_emp d
left join salaries s on d.emp_no = s.emp_no and d.to_date = s.to_date
where 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);
如插入：
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10005,'Senior Staff','1996-09-12','9999-01-01');
INSERT INTO titles VALUES(10005,'Staff','1989-09-12','1996-09-12');
INSERT INTO titles VALUES(10006,'Senior Engineer','1990-08-05','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');
INSERT INTO titles VALUES(10007,'Staff','1989-02-10','1996-02-11');
INSERT INTO titles VALUES(10008,'Assistant Engineer','1998-03-11','2000-07-31');
INSERT INTO titles VALUES(10009,'Assistant Engineer','1985-02-18','1990-02-18');
INSERT INTO titles VALUES(10009,'Engineer','1990-02-18','1995-02-18');
INSERT INTO titles VALUES(10009,'Senior Engineer','1995-02-18','9999-01-01');
INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');
INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');
解题：
主要用到了group by 分组，having条件过滤，group by后面加条件的话就用having。
select title,count(*) as t from titles group by title having t>=2;
14、题目描述
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的empno进行忽略(即empno重复的title不计算，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);
如插入：
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10005,'Senior Staff','1996-09-12','9999-01-01');
INSERT INTO titles VALUES(10005,'Staff','1989-09-12','1996-09-12');
INSERT INTO titles VALUES(10006,'Senior Engineer','1990-08-05','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');
INSERT INTO titles VALUES(10007,'Staff','1989-02-10','1996-02-11');
INSERT INTO titles VALUES(10008,'Assistant Engineer','1998-03-11','2000-07-31');
INSERT INTO titles VALUES(10009,'Assistant Engineer','1985-02-18','1990-02-18');
INSERT INTO titles VALUES(10009,'Engineer','1990-02-18','1995-02-18');
INSERT INTO titles VALUES(10009,'Senior Engineer','1995-02-18','9999-01-01');
INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');
INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');
解题：
在13题基础之上在count里面加上对emp_no去重distinct即可
select title,count(distinct emp_no) as t from titles group by title having t>=2;
15、题目描述
查找employees表所有empno为奇数，且lastname不为Mary(注意大小写)的员工信息，并按照hiredate逆序排列(题目不能使用mod函数)
CREATE TABLE employees (
`empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate 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(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
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');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
解题：
两个条件 就用and来连接即可，逆序排序就用order by desc
select * from employees where (emp_no%2) != 0 and last_name != 'Mary' order by hire_date desc;
16、题目描述
统计出当前(titles.todate='9999-01-01')各个title类型对应的员工当前(salaries.todate='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。
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));
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);
如插入：
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'1986-12-01','1995-12-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');
解题：
两表做内连接，按照条件进行查询
select title, avg(salary) as avg_salary from salaries, titles
where salaries.to_date='9999-01-01' and titles.to_date='9999-01-01'
and salaries.emp_no=titles.emp_no
group by titles.title;
17、题目描述
获取当前（todate='9999-01-01'）薪水第二多的员工的empno以及其对应的薪水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 , salary
from salaries
where salary=(select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc
limit 1,1);
子查询中：
1、限定条件 where to_date=‘9999-01-01’
2、薪水可能有多个，此处用group by去重
3、order by降序排序
4、limit 选出排名第二的薪水
18、题目描述
查找当前薪水(todate='9999-01-01')排名第二多的员工编号empno、薪水salary、lastname以及firstname，你可以不使用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));
解题：
-- 由于题目禁止使用order by 进行排序，思路就是先找出最高工资的，然后去除再找最高工资的即为排名第二的员工
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary = -- 第三步: 将第二高工资作为查询条件
(
select max(salary) -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
from salaries
where salary << span="">
(
select max(salary) -- 第一步: 查出原表最高工资
from salaries
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and s.to_date = '9999-01-01';
19、题目描述
查找所有员工的lastname和firstname以及对应的deptname，也包括暂时没有分配部门的员工
CREATE TABLE departments (
`deptnochar(4) NOT NULL,deptnamevarchar(40) NOT NULL,
PRIMARY KEY (deptno));
CREATE TABLEdeptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEemployees(empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (emp_no`));
如插入：
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO departments VALUES('d004','Production');
INSERT INTO departments VALUES('d005','Development');
INSERT INTO departments VALUES('d006','Quality Management');
INSERT INTO deptemp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO deptemp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO deptemp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO deptemp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO deptemp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO deptemp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO deptemp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO deptemp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO deptemp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO deptemp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');
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(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
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');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
解题：
-- 题目显示 包括暂时没分配部门的员工 则想到使用左连接
select employees.last_name, employees.first_name, departments.dept_name
from employees
left join dept_emp on employees.emp_no = dept_emp.emp_no
left join departments on dept_emp.dept_no = departments.dept_no;
20、题目描述
查找员工编号empno为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)
CREATE TABLE salaries (
`empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
解题：
只有涨薪没有降薪 这题就简单了 直接最大-最小就是涨幅
select (max(salary)-min(salary)) as growth from salaries
where emp_no = 10001;
但是如果要是有降薪就不行了
所以用 最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅
SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth;
21、题目描述
查找所有员工自入职以来的薪水涨幅情况，给出员工编号empno以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的todate!='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));
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, (b.salary - c.salary) as growth
from
employees as a
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc
22、题目描述
统计各个部门的工资记录数，给出部门编码deptno、部门名称deptname以及部门在salaries表里面有多少条记录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));
解题：
1、用INNER JOIN连接deptemp表和salaries表，并以deptemp.no分组，统计每个部门所有员工工资的记录总数
2、再将上表用INNER JOIN连接departments表，限制条件为两表的deptno相等，找到deptno与deptname的对应关系，最后依次输出deptno、deptname、sum
`select de.deptno, d.deptname,count(s.salary) as sumfrom (deptemp as de inner join departments as d on d.deptno = de.deptno)inner join salaries as s on de.empno = s.empnogroup by de.dept_no;`
23、题目描述
对所有员工的当前(todate='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照empno升序排列
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) rank
from salaries s1, salaries s2
where s1.salary <= s2.salary
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
order by rank;
24、题目描述
获取所有非manager员工当前的薪水情况，给出deptno、empno以及salary ，当前表示todate='9999-01-01'
CREATE TABLE `deptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEdeptmanager(deptnochar(4) NOT NULL,empnoint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEemployees(empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (empno));
CREATE TABLEsalaries(empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
解题：
-- 先看工资情况
-- 再看所在部门
-- 最后筛选不是manager
select de.dept_no,s.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no=s.emp_no and s.to_date='9999-01-01'
inner join dept_emp de on e.emp_no=de.emp_no
where de.emp_no not in
(select emp_no from dept_manager WHERE to_date = '9999-01-01')
25、题目描述
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示todate='9999-01-01',
结果第一列给出员工的empno，
第二列给出其manager的managerno，
第三列给出该员工当前的薪水empsalary,
第四列给该员工对应的manager当前的薪水managersalary
CREATE TABLE `deptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEdeptmanager(deptnochar(4) NOT NULL,empnoint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno));
CREATE TABLEsalaries(empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
解题：
-- 这种场景，最重要的是学会拆分，把复杂的查询分成一个个简单的查询，最后再将其组合在一起，这便是分合的思想。
-- 先查出员工的工号和薪水：
-- SELECT de.emp_no,sa.salary
-- FROM dept_emp de,salaries sa
-- WHERE de.emp_no=sa.emp_no
-- AND de.to_date='9999-01-01'
-- AND sa.to_date='9999-01-01'
--
-- -- 再查出经理的工号和薪水：
-- SELECT dm.emp_no manager_no,sal.salary
-- FROM dept_manager dm,salaries sal
-- WHERE dm.emp_no=sal.emp_no
-- AND dm.to_date='9999-01-01'
-- AND sal.to_date='9999-01-01'
-- 接着就是组合，看准条件，做好条件衔接：
SELECT de.emp_no,dm.emp_no manager_no,
sa.salary emp_salary,sal.salary manager_salary
FROM dept_emp de,salaries sa,dept_manager dm,salaries sal
WHERE de.emp_no=sa.emp_no
AND dm.emp_no=sal.emp_no
AND de.dept_no=dm.dept_no
AND de.to_date='9999-01-01'
AND sa.to_date='9999-01-01'
AND dm.to_date='9999-01-01'
AND sal.to_date='9999-01-01'
AND sa.salary>sal.salary
26、题目描述
汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号deptno、deptname、其部门下所有的当前(deptemp.todate = '9999-01-01')员工的当前(titles.todate = '9999-01-01')title以及该类型title对应的数目count
(注：因为员工可能有离职，所有deptemp里面todate不为'9999-01-01'就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.todate 不为 '9999-01-01'，那么这个可能是员工之前的职位信息，也不计入统计)
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 count
from
dept_emp as de
inner join titles as t on de.emp_no = t.emp_no
and t.to_date = '9999-01-01'
and de.to_date = '9999-01-01'
inner join departments as d on d.dept_no = de.dept_no
group by de.dept_no,t.title;
27、题目描述
给出每个员工每年薪水涨幅超过5000的员工编号empno、薪水变更开始日期fromdate以及薪水涨幅值salarygrowth，并按照salarygrowth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', todate)
(数据保证每个员工的每条薪水记录todate-fromdate=1年，而且同一员工的下一条薪水记录fromdata=上一条薪水记录的to_data)
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));
如：插入
INSERT INTO salaries VALUES(10001,52117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
解题：
-- 此题需要用到自连接 一个作为薪水变更前的表(s) 另一个作为薪水变更后的表(ss)
select s.emp_no, ss.from_date, ss.salary - s.salary as salary_growth
from salaries s
inner join salaries as ss on s.emp_no = ss.emp_no
and s.to_date = ss.from_date
where ss.salary - s.salary > 5000
order by salary_growth desc;
28、题目描述
film表
CREATE TABLE IF NOT EXISTS film (
filmid smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (filmid));
category表
CREATE TABLE category (
categoryid tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `lastupdate` timestamp,
PRIMARY KEY ( categoryid ));
filmcategory表
CREATE TABLE filmcategory (
filmid smallint(5) NOT NULL,
categoryid tinyint(3) NOT NULL, `lastupdate` timestamp);
查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.filmid))，而且还需要该分类包含电影总数量(count(filmcategory.category_id))>=5部
如：输入为:
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO film VALUES(4,'AFFAIR PREJUDICE','A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank');
INSERT INTO film VALUES(5,'AFRICAN EGG','A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico');
INSERT INTO film VALUES(6,'AGENT TRUMAN','A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China');
INSERT INTO film VALUES(7,'AIRPLANE SIERRA','A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat');
INSERT INTO film VALUES(8,'AIRPORT POLLOCK','A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India');
INSERT INTO film VALUES(9,'ALABAMA DEVIL','A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat');
INSERT INTO film VALUES(10,'ALADDIN CALENDAR','A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China');
INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO category VALUES(12,'Music','2006-02-14 20:46:27');
INSERT INTO category VALUES(13,'New','2006-02-14 20:46:27');
INSERT INTO category VALUES(14,'Sci-Fi','2006-02-14 20:46:27');
INSERT INTO category VALUES(15,'Sports','2006-02-14 20:46:27');
INSERT INTO category VALUES(16,'Travel','2006-02-14 20:46:27');
INSERT INTO filmcategory VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(2,11,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(3,6,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(4,11,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(5,6,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(6,6,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(7,5,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(8,6,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(9,11,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(10,15,'2006-02-14 21:07:09');
解题：
-- film_category表与film表通过字段film_id连接，film_category表与category表通过字段category_id连接
-- 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，
-- 而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
-- 第一步 查找分类名称
-- select category.name
-- from film,category,film_category
-- where film.description like '%robot%'
-- and film.film_id = film_category.film_id
-- and category.category_id = film_category.category_id;
-- -- 第二步 查找电影数目
-- select category_id
-- from film_category
-- group by category_id
-- having count(film_id)>=5
--
-- 最后
select category.name,count(film.film_id)
from film,category,film_category
where film.description like '%robot%'
and film.film_id = film_category.film_id
and category.category_id = film_category.category_id
and film_category.category_id in (
select category_id
from film_category
group by category_id
having count(film_id)>=5
)
29、题目描述
film表
CREATE TABLE IF NOT EXISTS film (
filmid smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (filmid));
category表
CREATE TABLE category (
categoryid tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `lastupdate` timestamp,
PRIMARY KEY ( categoryid ));
filmcategory表
CREATE TABLE filmcategory (
filmid smallint(5) NOT NULL,
categoryid tinyint(3) NOT NULL, `lastupdate` timestamp);
使用join查询方式找出没有分类的电影id以及名称
如输入为：
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO filmcategory VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(2,11,'2006-02-14 21:07:09');
解题：
-- 使用join查询方式找出没有分类的电影id以及名称
-- 解题思路是运用 left join 连接两表，用 is null 语句限定条件：
-- 1、用 left join 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，
-- 即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
-- 2、再用 where 来限定条件 fc.category_id is null 选出没分类的电影
select film.film_id, film.title from film left join film_category
on film.film_id = film_category.film_id
where film_category.category_id is null;
30、题目描述
film表
CREATE TABLE IF NOT EXISTS film (
filmid smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (filmid));
category表
CREATE TABLE category (
categoryid tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `lastupdate` timestamp,
PRIMARY KEY ( categoryid ));
filmcategory表
CREATE TABLE filmcategory (
filmid smallint(5) NOT NULL,
categoryid tinyint(3) NOT NULL, `lastupdate` timestamp);
你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗
输入如：
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO filmcategory VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO filmcategory VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');
解题：
-- 使用子查询的方式找出属于Action分类的所有电影对应的title,description
select film.title, film.description from film
inner join film_category on film.film_id = film_category.film_id
inner join category on category.category_id = film_category.category_id
and category.name = 'Action';
31、题目描述
获取select * from employees对应的执行计划
解题：
explain select * from employees;
32、题目描述
将employees表的所有员工的lastname和firstname拼接起来作为Name，中间以一个空格区分
(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)
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 Name from employees;
33、题目描述
创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime('now','localtime'))
解题：
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'))
)
34、题目描述
对于表actor批量插入如下数据(不能有2条insert语句哦!)
CREATE TABLE IF NOT EXISTS actor (
actorid smallint(5) NOT NULL PRIMARY KEY,
firstname varchar(45) NOT NULL,
lastname varchar(45) NOT NULL,
lastupdate timestamp NOT NULL DEFAULT (datetime('now','localtime')))
解题：
insert into actor
(actor_id,first_name,last_name,last_update)
values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
;
35、题目描述
对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
CREATE TABLE IF NOT EXISTS actor (
actorid smallint(5) NOT NULL PRIMARY KEY,
firstname varchar(45) NOT NULL,
lastname varchar(45) NOT NULL,
lastupdate timestamp NOT NULL DEFAULT (datetime('now','localtime')))
解题：
insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');
36、题目描述
解题：
-- CTAS创建表 create table ... as select
create table actor_name as
select first_name,last_name from actor;
37、题目描述
针对如下表actor结构创建索引：
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作)
CREATE TABLE IF NOT EXISTS actor (
actorid smallint(5) NOT NULL PRIMARY KEY,
firstname varchar(45) NOT NULL,
lastname varchar(45) NOT NULL,
lastupdate timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对firstname创建唯一索引uniqidxfirstname，对lastname创建普通索引idx_lastname
(请先创建唯一索引，再创建普通索引)
解题：
-- 先创建唯一索引，再创建普通索引
create unique index uniq_idx_firstname on actor (first_name);
create index idx_lastname on actor (last_name);
38、题目描述
针对actor表创建视图actornameview，只包含firstname以及lastname两列，并对这两列重新命名，firstname为firstnamev，lastname修改为lastnamev：
CREATE TABLE IF NOT EXISTS actor (
actorid smallint(5) NOT NULL PRIMARY KEY,
firstname varchar(45) NOT NULL,
lastname varchar(45) NOT NULL,
lastupdate timestamp NOT NULL DEFAULT (datetime('now','localtime')))
解题：
create view actor_name_view (first_name_v, last_name_v) as select first_name,last_name from actor;
39、题目描述
针对salaries表empno字段创建索引idxempno，查询empno为10005, 使用强制索引。
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));
create index idxempno on salaries(emp_no);
解题：
select * from salaries indexed by idx_emp_no where emp_no = 10005;
40、题目描述
存在actor表，包含如下列信息：
CREATE TABLE IF NOT EXISTS actor (
actorid smallint(5) NOT NULL PRIMARY KEY,
firstname varchar(45) NOT NULL,
lastname varchar(45) NOT NULL,
lastupdate timestamp NOT NULL DEFAULT (datetime('now','localtime')));
现在在lastupdate后面新增加一列名字为createdate, 类型为datetime, NOT NULL，默认值为'0000-00-00 00:00:00'
解题：
alter table actor add 'create_date' datetime not null default '0000-00-00 00:00:00';
41、题目描述
构造一个触发器auditlog，在向employeestest表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employeestest(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMPno INT NOT NULL,
NAME TEXT NOT NULL
);
解题：
-- 构造触发器时注意以下几点：
-- 1、用 create trigger 语句构造触发器，用 before或after 来指定在执行后面的SQL语句之前或之后来触发trigger
-- 2、触发器执行的内容写出 begin与end 之间
-- 3、可以使用 new与old 关键字访问触发后或触发前的employees_test表单记录
create trigger audit_log after insert on employees_test
begin
insert into audit values (NEW.ID, NEW.NAME);
end;
42、题目描述
删除empno重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titlestest (
id int(11) not null primary key,
empno int(11) NOT NULL,
title varchar(50) NOT NULL,
fromdate date NOT NULL,
todate date DEFAULT NULL);
insert into titlestest values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
解题：
-- 先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，
-- 然后用 DELETE FROM ... WHERE ... NOT IN ... 语句删除 “非每个分组最小id对应的所有记录”
delete from titles_test where id not in
(select min(id) from titles_test group by emp_no);
43、题目描述
将所有todate为9999-01-01的全部更新为NULL,且 fromdate更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titlestest (
id int(11) not null primary key,
empno int(11) NOT NULL,
title varchar(50) NOT NULL,
fromdate date NOT NULL,
todate date DEFAULT NULL);
insert into titlestest values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
更新后的值:
titlestest 表的值：
解题：
update titles_test set to_date=null,from_date='2001-01-01'
where to_date='9999-01-01';
44、题目描述
将id=5以及empno=10001的行数据替换成id=5以及empno=10005,其他数据保持不变，使用replace实现。
CREATE TABLE IF NOT EXISTS titlestest (
id int(11) not null primary key,
empno int(11) NOT NULL,
title varchar(50) NOT NULL,
fromdate date NOT NULL,
todate date DEFAULT NULL);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
解题：
update titles_test set emp_no=replace(emp_no,10001,10005) where id=5;
45、题目描述
将titlestest表名修改为titles2017。
CREATE TABLE IF NOT EXISTS titlestest (
id int(11) not null primary key,
empno int(11) NOT NULL,
title varchar(50) NOT NULL,
fromdate date NOT NULL,
todate date DEFAULT NULL);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
解题：
-- rename table titles_test to titles_2017; -- 在mysql中可以实现 但是sqlite不可以
alter table titles_test rename to titles_2017; -- sqlite
46、题目描述
在audit表上创建外键约束，其empno对应employeestest表的主键id。
(audit已经创建，需要先drop)
CREATE TABLE employeestest(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMPno 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)
);
47、题目描述
存在如下的视图：
create view empv as select * from employees where empno >10005;
如何获取empv和employees有相同的数据？
CREATE TABLE employees (
`empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (empno`));
(你能不用 select * from employees where empno >10005完成吗，挑战一下自己对视图的理解吧)
输出格式:
解题：
-- 由于视图 emp_v 的记录是从 employees 中导出的，所以要判断两者中相等的数据，只需要判断emp_no相等即可。
-- 方法一：用 WHERE 选取二者 emp_no 相等的记录
SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
-- 方法二：用 INTERSECT 关键字求 employees 和 emp_v 的交集
-- SELECT * FROM employees INTERSECT SELECT * FROM emp_v
--
-- 方法三：仔细一想，emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录
-- SELECT * FROM emp_v
--
-- 【错误方法：】用以下方法直接输出 *，会得到两张表中符合条件的重复记录，因此不合题意，必须在 * 前加表名作限定
-- SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no
48、题目描述
请你写出更新语句，将所有获取奖金的员工当前的(salaries.todate='9999-01-01')薪水增加10%。(empbonus里面的empno都是当前获奖的所有员工)
create table empbonus(
empno int not null,
btype smallint not null);
CREATE TABLE salaries (
`empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL, PRIMARY KEY (empno,fromdate`));
如：
INSERT INTO emp_bonus VALUES (10001,1);
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
更新后的结果:
salaries:
解题：
update salaries set salary=salary*1.1 where emp_no in (
select emp_no from emp_bonus
) and to_date = '9999-01-01';
49、题目描述
针对库中的所有表生成select count(*)对应的SQL语句，如数据库里有以下表，
(注:在 SQLite 中用 “||” 符号连接字符串，无法使用concat函数)
employees
departments
deptemp
deptmanage
salaries
titles
emp_bonus
那么就会输出以下的样子: 
解题：
-- 本题主要有以下两个关键点：
-- 1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'，详情可参考：
-- http://blog.csdn.net/xingfeng0501/article/details/7804378
-- 2、在 SQLite 中用 “||” 符号连接字符串
select "select count(*) from " || name || ";" as cnts
from sqlite_master where type = 'table';
50、
题目描述
将employees表中的所有员工的lastname和firstname通过(')连接起来。(不支持concat，请用||实现)
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 name from employees;
51、题目描述
查找字符串'10,A,B' 中逗号','出现的次数cnt。
解题：
-- 由于 SQLite 中没有直接统计字符串中子串出现次数的函数，
-- 因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，
-- 即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，
-- 最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）
select (length("10,A,B")-length(replace("10,A,B",",",""))) length(",") AS cnt
52、题目描述
获取Employees中的firstname，查询按照firstname最后两个字母，按照升序进行排列
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));
输出格式： 
解题：
-- 本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。
-- 其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，
-- 当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，
-- 取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。
SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1)
SELECT first_name FROM employees ORDER BY substr(first_name,-2)
53、题目描述
按照deptno进行汇总，属于同一个部门的empno按照逗号进行连接，结果给出deptno以及连接出的结果employees
CREATE TABLE `deptemp(empnoint(11) NOT NULL,deptnochar(4) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,deptno`));
输出格式: 
解题：
-- 本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
-- 此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。可参考：
-- http://www.sqlite.org/lang_aggfunc.html#groupconcat
-- http://blog.csdn.net/langzxz/article/details/16807859
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no
-- Mysql写法
/*
select dept_no,group_concat(emp_no SEPARATOR ',')
from dept_emp
group by dept_no
*/
54、题目描述
查找排除最大、最小salary之后的当前(todate = '9999-01-01' )员工的平均工资avgsalary。
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));
如：
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
输出格式: 
解题：
select avg(salary) as avg_salary
from salaries
where to_date='9999-01-01' and
salary < (select max(salary) from salaries where to_date='9999-01-01') and
salary > (select min(salary) from salaries where to_date='9999-01-01');
55、题目描述
分页查询employees表，每5行一页，返回第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));
解题：
-- 根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：
-- 方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。
SELECT * FROM employees LIMIT 5 OFFSET 5
-- 方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。
-- SELECT * FROM employees LIMIT 5,5
56、题目描述
获取所有员工的empno、部门编号deptno以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received
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(
empno int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);
CREATE TABLE employees (
`empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate NOT NULL,
PRIMARY KEY (emp_no`));
返回的结果格式如下: 
解题：
select t1.emp_no,t1.dept_no,t2.btype,t2.received from dept_emp t1 left join emp_bonus t2 on t1.emp_no=t2.emp_no
57、题目描述
使用含有关键字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
WHERE NOT EXISTS (SELECT emp_no FROM dept_emp
WHERE employees.emp_no = dept_emp.emp_no);
58、题目描述
存在如下的视图：
create view empv as select * from employees where empno >10005;
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));
获取employees中的行数据，且这些行也存在于empv中。注意不能使用intersect关键字。
(你能不用select * from employees where empno >10005 这条语句完成吗，挑战一下自己对视图的理解
输出格式: 
解题：
select em.* from employees as em ,emp_v as ev on em.emp_no=ev.emp_no
59、题目描述
获取有奖金的员工相关信息。
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 empbonus(
empno 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));
给出empno、firstname、lastname、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示todate='9999-01-01'
输出格式: 
解题：
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(CASE b.btype
WHEN 1 THEN s.salary * 0.1
WHEN 2 THEN s.salary * 0.2
ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
60、题目描述
按照salary的累计和runningtotal，其中runningtotal为前N个当前( todate = '9999-01-01')员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
CREATE TABLE salaries ( `empnoint(11) NOT NULL,salaryint(11) NOT NULL,fromdatedate NOT NULL,todatedate NOT NULL,
PRIMARY KEY (empno,fromdate`));
输出格式: 
解题：
select s1.emp_no, s1.salary,
(select sum(s2.salary) from salaries s2
where s2.emp_no <= s1.emp_no
and s2.to_date = '9999-01-01') as running_total
from salaries s1
where s1.to_date = '9999-01-01'
order by s1.emp_no;
61、题目描述
对于employees表中，输出firstname排名(按firstname升序排序)为奇数的firstname
CREATE TABLE employees (
`empnoint(11) NOT NULL,birthdatedate NOT NULL,firstnamevarchar(14) NOT NULL,lastnamevarchar(16) NOT NULL,genderchar(1) NOT NULL,hiredatedate 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');
输出格式: 
解题：
SELECT e1.first_name FROM
employees e1
WHERE
(SELECT COUNT(*) FROM employees e2
WHERE e1.first_name >= e2.first_name)%2=1;
