Skip to content

Commit 5d6d929

Browse files
linwtlinwt
authored
Add files via upload
1 parent 450dc23 commit 5d6d929

File tree

61 files changed

+629
-0
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

61 files changed

+629
-0
lines changed

‎数据库SQL实战/01.查找最晚入职员工的所有信息.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 入职日期等于最大入职日期
2+
select *
3+
from employees
4+
where hire_date = (select max(hire_date) from employees);
5+
6+
7+
-- 按照入职日期降序排序，取第一条
8+
select *
9+
from employees
10+
order by hire_date desc
11+
limit 1;

‎数据库SQL实战/02.查找入职员工时间排名倒数第三的员工所有信息.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 入职日期降序排序取第三条
2+
select *
3+
from employees
4+
order by hire_date desc
5+
limit 2,1;

‎数据库SQL实战/03.查找当前薪水详情以及部门编号dept_no.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 内连接的条件可以转化为where条件查询
2+
-- 两表内连接关联获取信息
3+
select s.*, d.dept_no
4+
from salaries as s inner join dept_manager as d
5+
on d.emp_no=s.emp_no
6+
where d.to_date='9999-01-01' and s.to_date='9999-01-01';
7+
8+
9+
-- 按照查询条件关联两表信息，相当于内连接
10+
select s.*, d.dept_no
11+
from salaries as s, dept_manager as d
12+
where d.to_date='9999-01-01' and s.to_date='9999-01-01' and d.emp_no=s.emp_no;

‎数据库SQL实战/04.查找所有已经分配部门的员工的last_name和first_name以及dept_no.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 已分配表示部门员工表和员工表对应的员工信息共同存在，故使用内连接
2+
-- 查询的字段在两表中唯一故可省略表名
3+
select last_name, first_name, dept_no
4+
from dept_emp as d inner join employees as e
5+
on e.emp_no=d.emp_no;
6+
7+
8+
-- 按照查询条件关联两表信息，相当于内连接
9+
select e.last_name, e.first_name, d.dept_no
10+
from employees as e, dept_emp as d
11+
where e.emp_no=d.emp_no;

‎数据库SQL实战/05.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
-- 员工可能未分配部门，故使用左连接
2+
select e.last_name, e.first_name, d.dept_no
3+
from employees as e left join dept_emp as d
4+
on e.emp_no=d.emp_no;

‎数据库SQL实战/06.查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 员工表和薪水表对应的员工信息需要共同存在，故使用内连接
2+
select e.emp_no, s.salary
3+
from employees as e inner join salaries as s
4+
on e.emp_no=s.emp_no and e.hire_date=s.from_date
5+
order by e.emp_no desc;
6+
7+
8+
select e.emp_no, s.salary
9+
from employees as e, salaries as s
10+
where e.emp_no=s.emp_no and e.hire_date=s.from_date
11+
order by e.emp_no desc;

‎数据库SQL实战/07.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
-- 按照员工号分组并统计次数
2+
select emp_no, count(*)
3+
from salaries
4+
group by emp_no
5+
having count(*)>15;
6+
7+
8+
-- having在产生虚表后执行
9+
-- 查询字段使用替代名
10+
select emp_no, count(emp_no) as times
11+
from salaries
12+
group by emp_no
13+
having times > 15;
14+
15+
16+
-- 此处对员工和薪水去重
17+
select emp_no, count(emp_no) as times
18+
from (select distinct emp_no, salary from salaries)
19+
group by emp_no
20+
having times > 15;

‎数据库SQL实战/08.找出所有员工当前薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 对某字段去重可用distinct或group by
2+
select distinct salary
3+
from salaries
4+
where to_date='9999-01-01'
5+
order by salary desc;
6+
7+
8+
select salary
9+
from salaries
10+
where to_date='9999-01-01'
11+
group by salary
12+
order by salary desc;

‎数据库SQL实战/09.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 关联表信息，使用内连接
2+
select d.dept_no, d.emp_no, s.salary
3+
from dept_manager as d inner join salaries as s
4+
on d.emp_no=s.emp_no
5+
where d.to_date='9999-01-01' and s.to_date='9999-01-01';
6+
7+
8+
-- 直接在条件语句中关联表信息
9+
select d.dept_no, d.emp_no, s.salary
10+
from dept_manager as d, salaries as s
11+
where d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01';

‎数据库SQL实战/10.获取所有非manager的员工emp_no.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 直接条件查询员工号不在manager中
2+
select emp_no
3+
from employees
4+
where emp_no not in
5+
(select emp_no from dept_manager);
6+
7+
8+
-- 使用左连接，非manager的员工关联不到其他信息，没有dept_no
9+
select e.emp_no
10+
from employees as e left join dept_manager as d
11+
on e.emp_no=d.emp_no
12+
where d.dept_no is null;

‎数据库SQL实战/11.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 内连接关联信息
2+
select e.emp_no, m.emp_no
3+
from dept_emp as e inner join dept_manager as m
4+
on e.dept_no=m.dept_no
5+
where e.emp_no!=m.emp_no and e.to_date='9999-01-01' and m.to_date='9999-01-01';
6+
7+
8+
-- 不等于号使用 “！=” 或 “<>”
9+
-- 内连接条件可以转化为where条件
10+
select e.emp_no, m.emp_no
11+
from dept_emp as e, dept_manager as m
12+
where e.dept_no=m.dept_no and e.emp_no<>m.emp_no and e.to_date='9999-01-01' and m.to_date='9999-01-01';

‎数据库SQL实战/12.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 内连接关联信息，按照部门分组，max()函数获取每个部门的最高薪水
2+
select d.dept_no, d.emp_no, max(s.salary) as salary
3+
from dept_emp as d inner join salaries as s
4+
on d.emp_no=s.emp_no
5+
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
6+
group by d.dept_no;
7+
8+
9+
select d.dept_no, d.emp_no, max(s.salary) as salary
10+
from dept_emp as d, salaries as s
11+
where d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
12+
group by d.dept_no;

‎数据库SQL实战/13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 按title分组，用count()函数统计个数
2+
select title, count(title) as t
3+
from titles
4+
group by title
5+
having t>=2;

‎数据库SQL实战/14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t，注意对于重复的emp_no进行忽略.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 员工号去重
2+
select title, count(distinct emp_no) as t
3+
from titles
4+
group by title
5+
having t>=2;
6+
7+
8+
select title, count(title) as t
9+
from (select distinct * from titles)
10+
group by title
11+
having t>=2;

‎数据库SQL实战/15.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 查询条件加入计算
2+
select *
3+
from employees
4+
where emp_no%2!=0 and last_name!='Mary'
5+
order by hire_date desc;

‎数据库SQL实战/16.统计出当前各个title类型对应的员工当前薪水对应的平均工资，结果给出title以及平均工资avg.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 内连接关联表信息，按照title分组，用avg()函数计算平均工资
2+
select title, avg(salary) as avg
3+
from titles as t inner join salaries as s
4+
on s.emp_no=t.emp_no
5+
where s.to_date='9999-01-01' and t.to_date='9999-01-01'
6+
group by title;
7+
8+
9+
select title, avg(salary) as avg
10+
from salaries as s, titles as t
11+
where s.emp_no=t.emp_no and s.to_date='9999-01-01' and t.to_date='9999-01-01'
12+
group by title;

‎数据库SQL实战/17.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
-- 按薪水分组用于去重，取第二多
2+
select emp_no, salary
3+
from salaries
4+
group by salary
5+
order by salary desc
6+
limit 1,1;
7+
8+
9+
-- 子查询直接对薪水去重，取第二多的薪水，父查询再获取要查询的信息
10+
select emp_no, salary
11+
from salaries
12+
where salary=(
13+
select distinct salary from salaries
14+
order by salary desc
15+
limit 1,1
16+
);

‎数据库SQL实战/18.查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 在薪水小于第一多的数据中获取最多，即为第二多
2+
select e.emp_no, max(s.salary) as salary, e.last_name, e.first_name
3+
from employees as e, salaries as s
4+
where s.salary < (select max(salary) from salaries)
5+
and e.emp_no=s.emp_no and s.to_date='9999-01-01';

‎数据库SQL实战/19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
-- 两次左连接关联表信息
2+
select e.last_name, e.first_name, dm.dept_name
3+
from (employees as e left join dept_emp as de on e.emp_no=de.emp_no)
4+
left join departments as dm on de.dept_no=dm.dept_no;
5+
6+
7+
select e.last_name, e.first_name, dm.dept_name
8+
from employees as e left join
9+
(dept_emp as de left join departments as dm on de.dept_no=dm.dept_no)
10+
on e.emp_no=de.emp_no;
11+
12+
13+
select e.last_name, e.first_name, d.dept_name
14+
from employees as e left join
15+
(select de.emp_no, dm.dept_name
16+
from dept_emp as de, departments as dm
17+
where de.dept_no=dm.dept_no) as d
18+
on e.emp_no=d.emp_no;

‎数据库SQL实战/20.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 直接取最大和最小工资作差，但存在最后一次工资是降薪的情况
2+
select (max(salary)-min(salary)) as growth
3+
from salaries
4+
where emp_no=10001;
5+
6+
7+
-- 按照日期排序，取最大和最小日期对应的工资作差
8+
select (
9+
(select salary from salaries where emp_no=10001 order by to_date desc limit 1) -
10+
(select salary from salaries where emp_no=10001 order by to_date asc limit 1)
11+
) as growth;

‎数据库SQL实战/21.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
-- 员工表两次内连接薪水表，关联获取当前和入职时的薪水，作差得到涨幅
2+
select e.emp_no, (a.salary-b.salary) as growth
3+
from employees as e
4+
inner join salaries as a
5+
on e.emp_no=a.emp_no and a.to_date='9999-01-01'
6+
inner join salaries as b
7+
on e.emp_no=b.emp_no and b.from_date=e.hire_date
8+
order by growth asc;
9+
10+
11+
-- 建立两张表，当前和入职时的薪水表，再关联信息作差得到涨幅
12+
select a.emp_no, (a.salary-b.salary) as growth
13+
from (select s.emp_no, s.salary from employees as e, salaries as s where e.emp_no=s.emp_no and s.to_date='9999-01-01') as a,
14+
(select s.emp_no, s.salary from employees as e, salaries as s where e.emp_no=s.emp_no and s.from_date=e.hire_date) as b
15+
where a.emp_no=b.emp_no
16+
order by growth asc;

‎数据库SQL实战/22.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
-- 两次内连接关联表信息，按照部门号分组，用count()函数统计个数
2+
select dm.dept_no, dm.dept_name, count(s.salary) as sum
3+
from salaries as s
4+
inner join dept_emp as de
5+
on s.emp_no=de.emp_no
6+
inner join departments as dm
7+
on de.dept_no=dm.dept_no
8+
group by dm.dept_no;
9+
10+
11+
-- 内连接可以转化为where查询
12+
select dm.dept_no, dm.dept_name, count(s.salary) as sum
13+
from salaries as s, dept_emp as de, departments as dm
14+
where s.emp_no=de.emp_no and de.dept_no=dm.dept_no
15+
group by dm.dept_no;

‎数据库SQL实战/23.对所有员工的当前薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
-- 以表a当前薪水小于等于表b当前薪水为条件进行内连接，可以关联获得比表a中当前薪水大的记录数，去重统计个数即可得到排名
2+
select a.emp_no, a.salary, count(distinct b.salary) as rank
3+
from salaries as a inner join salaries as b
4+
on a.salary<=b.salary
5+
where a.to_date='9999-01-01' and b.to_date='9999-01-01'
6+
group by a.emp_no
7+
order by a.salary desc, a.emp_no asc;
8+
9+
10+
select a.emp_no, a.salary, count(distinct b.salary) as rank
11+
from salaries as a, salaries as b
12+
where a.salary<=b.salary and a.to_date='9999-01-01' and b.to_date='9999-01-01'
13+
group by a.emp_no
14+
order by a.salary desc, a.emp_no asc;

‎数据库SQL实战/24.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
-- 两次内连接关联表信息
2+
select de.dept_no, de.emp_no, s.salary
3+
from employees as e inner join dept_emp as de
4+
on e.emp_no=de.emp_no
5+
inner join salaries as s
6+
on e.emp_no=s.emp_no
7+
where s.to_date='9999-01-01' and e.emp_no not in (select emp_no from dept_manager);
8+
9+
10+
select de.dept_no, de.emp_no, s.salary
11+
from employees as e, dept_emp as de, salaries as s
12+
where e.emp_no=s.emp_no and e.emp_no=de.emp_no and s.to_date='9999-01-01' and
13+
e.emp_no not in (select emp_no from dept_manager);
14+
15+
16+
-- 省略employees
17+
select de.dept_no, de.emp_no, s.salary
18+
from dept_emp as de, salaries as s
19+
where de.emp_no=s.emp_no and s.to_date='9999-01-01' and de.emp_no not in (select emp_no from dept_manager);

‎数据库SQL实战/25.获取员工其当前的薪水比其manager当前薪水还高的相关信息，给出emp_no、manager_no、emp_salary、manager_salary.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
-- 建立员工工资表和领导工资表，进行关联和比较
2+
select a.emp_no as emp_no, b.emp_no as manager_no, a.salary as emp_salary, b.salary as manager_salary
3+
from (select s.emp_no, s.salary, de.dept_no
4+
from dept_emp as de, salaries as s
5+
where de.emp_no=s.emp_no and s.to_date='9999-01-01')
6+
as a,
7+
(select s.emp_no, s.salary, dm.dept_no
8+
from dept_manager as dm, salaries as s
9+
where dm.emp_no=s.emp_no and s.to_date='9999-01-01')
10+
as b
11+
where a.dept_no=b.dept_no and a.salary>b.salary;
12+
13+
14+
-- 四表直接关联信息
15+
select de.emp_no as emp_no, dm.emp_no as manager_no, s1.salary as emp_salary, s2.salary as manager_salary
16+
from dept_emp as de, dept_manager as dm, salaries as s1, salaries as s2
17+
where de.emp_no=s1.emp_no and dm.emp_no=s2.emp_no and de.dept_no=dm.dept_no and
18+
s1.salary>s2.salary and s1.to_date='9999-01-01' and s2.to_date='9999-01-01';

‎数据库SQL实战/26.汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 三表信息关联，按部门号和头衔分组，统计个数
2+
select dm.dept_no, dm.dept_name, t.title, count(*) as count
3+
from departments as dm, dept_emp as de, titles as t
4+
where dm.dept_no=de.dept_no and de.emp_no=t.emp_no and de.to_date='9999-01-01' and t.to_date='9999-01-01'
5+
group by dm.dept_no, t.title;

‎数据库SQL实战/27.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 复用表，关联涨薪日期相差一年的信息，薪水作差计算涨薪幅度
2+
select a.emp_no, a.from_date, (a.salary-b.salary) as salary_growth
3+
from salaries as a, salaries as b
4+
where a.emp_no=b.emp_no and strftime('%Y', a.to_date)-strftime('%Y', b.to_date)=1 and salary_growth>5000
5+
order by salary_growth desc;

‎数据库SQL实战/28.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量≥5部.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 根据条件获得虚表，实表与虚表进行多表关联获取信息
2+
select c.name, count(fc.film_id)
3+
from film as f, film_category as fc, category as c,
4+
(select category_id, count(film_id) as num
5+
from film_category
6+
group by category_id
7+
having num>=5) as cn
8+
where f.description like '%robot%'
9+
and f.film_id=fc.film_id
10+
and fc.category_id=c.category_id
11+
and c.category_id=cn.category_id;

‎数据库SQL实战/29.使用join查询方式找出没有分类的电影id以及名称.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 左连接，没有关联到信息说明没有分类
2+
select f.film_id, f.title
3+
from film as f left join film_category as fc
4+
on f.film_id=fc.film_id
5+
where fc.category_id is null;

‎数据库SQL实战/30.使用子查询的方式找出属于Action分类的所有电影对应的title,description.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
-- 表信息关联获取符合条件的film_id，再根据这个条件去获取其他信息
2+
select title, description
3+
from film
4+
where film_id in
5+
(select fc.film_id
6+
from category as c, film_category as fc
7+
where c.name='Action' and c.category_id=fc.category_id)

‎数据库SQL实战/31.获取select x from employees对应的执行计划.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
explain select * from employees;

0 commit comments

Comments
 (0)