sql语句实例练习
1.最晚入职员工查询
select * from employees
where hire_date =
(select max(hire_date) from employees)
2.倒数第三名入职信息
select * from employees order by hire_date desc limit 2,1;
3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from salaries inner join dept_manager
on dept_manager.emp_no = salaries.emp_no
and dept_manager.to_date = '9999-01-01'
and salaries.to_date = '9999-01-01';
4.查找所有已经分配部门的员工的last_name和first_name
select employees.last_name, first_name, dept_emp.dept_no from employees inner join dept_emp on employees.emp_no=dept_emp.emp_no
5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工
select a.last_name,a.first_name,b.dept_no
from employees a
left join dept_emp b
on a.emp_no=b.emp_no
6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
select employees.emp_no,salaries.salary
from employees,salaries
where employees.emp_no=salaries.emp_no
and employees.hire_date=salaries.from_date
order by employees.emp_no desc;
7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数
select a.* from(
select emp_no,count(*) as t
from salaries
group by emp_no) as a
where t>15
8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
select salary from salaries where to_date='9999-01-01' group by salary order by salary DESC
SELECT dept_manager.dept_no,dept_manager.emp_no,salaries.salary
from salaries,dept_manager
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的员工emp_no
select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where d.emp_no is null;
11.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
SELECT a.dept_no,a.emp_no,max(b.salary) salary FROM
dept_emp a,salaries b
WHERE a.to_date='9999-01-01'
AND b.to_date='9999-01-01'
AND a.emp_no=b.emp_no
GROUP BY a.dept_no
;
12.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
SELECT title,COUNT(title) AS t
FROM titles
GROUP BY title
HAVING t > 1;
select title, count(distinct emp_no) as t
from titles
group by title
having t >= 2
;
14.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc
15.统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg
select title,avg(salary) as avg
from titles a
inner join salaries b
on a.emp_no=b.emp_no and a.to_date = '9999-01-01' AND b.to_date = '9999-01-01'
group by a.title
16.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no,salary from salaries s where s.to_date = '9999-01-01' order by s.salary desc limit 1,1
17.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
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
where to_date='9999-01-01'and salary not in
(select max (salary) from salaries where to_date='9999-01-01')
18.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工SELECT
e.last_name, e.first_name, dp.dept_name FROM employees AS e
LEFT JOIN dept_emp AS d ON e.emp_no = d.emp_no
LEFT JOIN departments AS dp ON d.dept_no = dp.dept_no
19.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
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 LIMIT 1)
) AS growth;
20.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS sCurrent,
(SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart
WHERE sCurrent.emp_no = sStart.emp_no
ORDER BY growth
21.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
select d.dept_no ,dept.dept_name,count(salary) from salaries s ,dept_emp d,departments dept
where s.emp_no = d.emp_no and d.dept_no=dept.dept_no group by dept.dept_no
22.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
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;
23.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
SELECT de.dept_no,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' AND de.emp_no NOT IN(
SELECT emp_no
FROM dept_manager
)
24.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
SELECT s1.emp_no AS emp_no, s2.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM
(SELECT s.emp_no, de.dept_no, s.salary FROM salaries s INNER JOIN dept_emp de ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01')
AS s1,
(SELECT s.emp_no, dm.dept_no, s.salary FROM salaries s INNER JOIN dept_manager dm ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01')
AS s2
WHERE s1.dept_no = s2.dept_no
AND s1.salary > s2.salary
25.汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS de
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp
ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title
26.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND s2.salary - s1.salary > 5000
AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
ORDER BY salary_growth DESC;
select c.name,count(fcc.film_id)as num
from film_category fc
inner join category c on c.category_id=fc.category_id
inner join (select * from film f where f.description like '%robot%') as f on f.film_id=fc.film_id
inner join (select *,count (fc.film_id)as num from film_category fc group by category_id having num>=5)as fcc on fc.category_id=fcc.category_id
28.使用join查询方式找出没有分类的电影id以及名称
select f.film_id, f.title
from film as f
left join film_category as fc
on f.film_id = fc.film_id
where fc.category_id is null
29.使用子查询的方式找出属于Action分类的所有电影对应的title,description
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，中间以一个空格区分
SELECT last_name||" "||first_name AS Name FROM employees
32.创建一个actor表，包含如下列信息
| 列表 | 类型 | 是否为NULL | 含义 |
|---|---|---|---|
| actor_id | smallint(5) | not null | 主键id |
| first_name | varchar(45) | not null | 名字 |
| last_name | varchar(45) | not null | 姓氏 |
| last_update | timestamp | not null | 最后更新时间，默认是系统的当前时间 |
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')) -- ,
-- PRIMARY KEY(actor_id)
)
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')))
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| 1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
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' |
INSERT or IGNORE INTO actor
VALUES ('3','ED','CHASE','2006-02-15 12:34:33');
35.对于如下表actor，其对应的数据为:
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| 1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
| 列表 | 类型 | 是否为NULL | 含义 |
|---|---|---|---|
| first_name | varchar(45) | not null | 名字 |
| last_name | varchar(45) | not null | 姓氏 |
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.针对如下表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')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
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两列，并对这两列重新命名，first_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')))
create view actor_name_view (first_name_v,last_name_v) as
select first_name,last_name from actor
38.针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为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 idx_emp_no on salaries(emp_no);
select * from salaries
indexed by idx_emp_no
where emp_no = '10005'
39.存在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')));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'
ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
40.构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到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_test
begin
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);
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');
delete from titles_test
where id not in (select min(id) from titles_test)
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);
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 to_date=null,from_date='2001-01-01' where to_date='9999-01-01';
43.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。
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);
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');
replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
44.将titles_test表名修改为titles_2017。
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);
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');
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`));
输出格式:
| emp_no | birth_date | first_name | last_name | gender | hire_date |
|---|---|---|---|---|---|
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
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.1
where emp_no in (select emp_no from emp_bonus)
48.将所有获取奖金的员工当前的薪水增加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.1
where emp_no in (select emp_no from emp_bonus)
49.针对库中的所有表生成select count(*)对应的SQL语句
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));
输出格式:
| cnts |
|---|
| select count(*) from employees; |
| select count(*) from departments; |
| select count(*) from dept_emp; |
| select count(*) from dept_manager; |
| select count(*) from salaries; |
| select count(*) from titles; |
| select count(*) from emp_bonus; |
SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table'
50.将employees表中的所有员工的last_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`));
输出格式:
| name |
|---|
| Facello'Georgi |
| Simmel'Bezalel |
| Bamford'Parto |
| Koblick'Chirstian |
| Maliniak'Kyoichi |
| Preusig'Anneke |
| Zielinski'Tzvetan |
| Kalloufi'Saniya |
| Peac'Sumant |
| Piveteau'Duangkaew |
| Sluis'Mary |
select last_name || "'" || first_name as 'name' from employees |
select (length('10,A,B')-length(replace('10,A,B',',',''))) as cnt
52.获取Employees中的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`));
输出格式：
| first_name |
|---|
| Chirstian |
| Tzvetan |
| Bezalel |
| Duangkaew |
| Georgi |
| Kyoichi |
| Anneke |
| Sumant |
| Mary |
| Parto |
| Saniya |
SELECT first_name FROM employees
order by substr(first_name,length(first_name)-1)
53.按照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`));
输出格式:
| dept_no | employees |
|---|---|
| d001 | 10001,10002 |
| d002 | 10006 |
| d003 | 10005 |
| d004 | 10003,10004 |
| d005 | 10007,10008,10010 |
| d006 | 10009,10010 |
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no;
54.查找排除当前最大、最小salary之后的员工的平均工资avg_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`));
输出格式:
| avg_salary |
|---|
| 69462.5555555556 |
select avg(salary) as avg_salary from salaries
where to_date = '9999-01-01'
AND salary <> (select max(salary) from salaries)
AND salary <> (select min(salary) from salaries);
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`)); select * from employees limit 5,5 56.获取所有员工的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_no | dept_no | btype | recevied |
|---|---|---|---|
| 10001 | d001 | 1 | 2010-01-01 |
| 10002 | d001 | 2 | 2010-10-01 |
| 10003 | d004 | 3 | 2011-12-03 |
| 10004 | d004 | 1 | 2010-01-01 |
| 10005 | d003 | ||
| 10006 | d002 | ||
| 10007 | d005 | ||
| 10008 | d005 | ||
| 10009 | d006 | ||
| 10010 | d005 | ||
| 10010 | d006 |
SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied FROM dept_emp AS de LEFT JOIN emp_bonus AS eb ON de.emp_no = eb.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 `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`));
输出格式:
| emp_no | birth_date | first_name | last_name | gender | hire_date |
|---|---|---|---|---|---|
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
create view emp_v as select * from employees where emp_no >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中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
输出格式:
| emp_no | birth_date | first_name | last_name | gender | hire_date |
|---|---|---|---|---|---|
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
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,
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`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
输出格式:
| emp_no | first_name | last_name | btype | salary | bonus |
|---|---|---|---|---|---|
| 10001 | Georgi | Facello | 1 | 88958 | 8895.8 |
| 10002 | Bezalel | Simmel | 2 | 72527 | 14505.4 |
| 10003 | Parto | Bamford | 3 | 43311 | 12993.3 |
| 10004 | Chirstian | Koblick | 1 | 74057 | 7405.7 |
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 e inner join emp_bonus b on e.emp_no=b.emp_no inner join salaries s on
s.emp_no=e.emp_no and s.to_date = '9999-01-01';
60.按照salary的累计和running_total，其中running_total为前两个员工的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`));
输出格式:
| emp_no | salary | running_total |
|---|---|---|
| 10001 | 88958 | 88958 |
| 10002 | 72527 | 161485 |
| 10003 | 43311 | 204796 |
| 10004 | 74057 | 278853 |
| 10005 | 94692 | 373545 |
| 10006 | 43311 | 416856 |
| 10007 | 88070 | 504926 |
| 10009 | 95409 | 600335 |
| 10010 | 94409 | 694744 |
| 10011 | 25828 | 720572 |
select a.emp_no,a.salary,(select sum(s.salary) from salaries as s where s.emp_no <=a.emp_no and s.to_date='9999-01-01')as running_total
from salaries as a where a.to_date='9999-01-01' order by a.emp_no;
61.对于employees表中，给出奇数行的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`));
输出格式:
| first_name |
|---|
| Georgi |
| Chirstian |
| Anneke |
| Tzvetan |
| Saniya |
| Mary |
SELECT E1.first_name
FROM employees E1
WHERE(SELECT COUNT(*) FROM employees E2 WHERE E1.first_name>=E2.first_name)%2=1;
更多精彩
