1.查找最晚入职员工的所有信息
CREATE TABLE employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (emp_no
));
解析:这个题很简单,关键是对入职时间做以逆序(desc)排序而后选出第一条信息
select * from employees order by hire_date desc limit 1
或者判断入职时间是employees中hire_date最大的
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
));
解析:这个题可以以入职时间做以逆序(desc)排序而后选出第一条信息
select * from employees
order by hire_date desc limit 2,1
3.查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号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
));
解析:两个表连接加上对两个表的to_date进行限制,其中连接表可以用left join连接,或者基本的逗号连接都可
select salaries.*,dept_manager.dept_no from salaries, dept_manager
where salaries.emp_no = dept_manager.emp_no
and salaries.to_date = "9999-01-01"
and dept_manager.to_date = "9999-01-01";
select salaries.*,dept_manager.dept_no
from salaries left join dept_manager
on (salaries.emp_no=dept_manager.emp_no)
where dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'
4.查找所有已经分配部门的员工的last_name和first_name
CREATE TABLE dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
CREATE TABLE employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (emp_no
));
解析:这个题也是连结两个表然后加入限制进行查找
select b.last_name,b.first_name,a.dept_no
from dept_emp a,employees b
where a.emp_no=b.emp_no
其中连结表时可以用inner join
select employees.last_name, first_name, dept_emp.dept_no
from dept_emp inner join employees where
dept_emp.emp_no = employees.emp_no;
5.查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
CREATE TABLE dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
CREATE TABLE employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (emp_no
));
解析:同第四题
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left join dept_emp on employees.emp_no=dept_emp.emp_no
总结:前五道题涉及order by,limit,inner join,left join等用法,做这些题时,在基础方法解决时,可以根据w3c中的提供的手册进行尝试改进方法,这样可以更好的掌握SQL语言。