?？途W(wǎng)SQL實戰(zhàn)練習——21~25

21.查找所有員工自入職以來的薪水漲幅情況，給出員工編號emp_no以及其對應(yīng)的薪水漲幅growth，并按照growth進行升序

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

分析： 對于每個員工，自入職以來的薪水漲幅是 ：當前的薪水-入職時的薪水。先取得當前時間的工資：把employees 表 e與 salaries 表通過 e.to_date="9999-01-01" 用 inner join 進行連接。接下來，取得入職時的工資，把employees 表 e與 salaries 表 s 通過 e.hire_date=s. from_date 用 inner join 進行連接。
答案：

select e.emp_no, (a.salary-b.salary) as growth
from employees as e
inner join salaries as a
on e.emp_no=a.emp_no and a.to_date='9999-01-01'
inner join salaries as b
on e.emp_no=b.emp_no and b.from_date=e.hire_date
order by growth asc

22.統(tǒng)計各個部門的工資記錄數(shù)，給出部門編碼dept_no、部門名稱dept_name以及次數(shù)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`));

分析：本題中涉及三個表，首先用dept_no把dept_emp和departments兩個表鏈接起來，用emp_no把dept_emp和salary兩個表鏈接起來。其中用count(s.salary)記錄工資次數(shù)，按各個部門區(qū)分即group by d.dept_no。
答案：

select d.dept_no,d.dept_name,count(s.salary) as sum
from departments as d,dept_emp as de,salaries as s 
where d.dept_no=de.dept_no and de.emp_no=s.emp_no
group by d.dept_no

23.對所有員工的當前(to_date='9999-01-01')薪水按照salary進行按照1-N的排名，相同salary并列且按照emp_no升序排列

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

分析：兩張相同的salaries表（分別為s1與s2）進行對比分析，先將兩表限定條件設(shè)為to_date = '9999-01-01'，挑選出當前所有員工的薪水情況。使用count,大于等于該條salary的數(shù)據(jù)條數(shù)，又因為數(shù)據(jù)有重復(fù)，所以distinct，此處必須使用表的重復(fù)使用功能。按s1.emp_no 分組，group by s1.emp_no ，最后先以 s1.salary 逆序排列，再以 s1.emp_no 順序排列輸出結(jié)果。
答案：

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries as s1,salaries as s2
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
and s1.salary<=s2.salary
group by s1.emp_no
order by s1.salary desc,s1.emp_no asc

24.獲取所有非manager員工當前的薪水情況，給出dept_no、emp_no以及salary ，當前表示to_date='9999-01-01'

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

分析：先用INNER JOIN連接employees和salaries，找出當前所有員工的工資情況,再用INNER JOIN連接dept_emp表，找到所有員工所在的部門,最后用限制條件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')選出當前所有非manager員工，再依次輸出dept_no、emp_no、salary.
答案：

select de.dept_no,s.emp_no,s.salary
from dept_emp as de inner join salaries as s
on de.emp_no=s.emp_no
where s.to_date='9999-01-01'
and de.emp_no not in(
select emp_no
    from dept_manager
    where to_date='9999-01-01'
)

25.獲取員工其當前的薪水比其manager當前薪水還高的相關(guān)信息，當前表示to_date='9999-01-01',
結(jié)果第一列給出員工的emp_no，
第二列給出其manager的manager_no，
第三列給出該員工當前的薪水emp_salary,
第四列給該員工對應(yīng)的manager當前的薪水manager_salary

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

分析：復(fù)用salaries表，獲取員工（不是manager）的當前薪水（表一），獲取manager的當前薪水（表二），.連接表一和表二，篩選出員工當前工資>manager當前工資。
答案：

select de.emp_no,dm.emp_no,s1.salary,s2.salary
from dept_emp as de ,dept_manager as dm,salaries as s1,salaries as s2
where de.dept_no=dm.dept_no
and de.emp_no = s1.emp_no
and dm.emp_no = s2.emp_no
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'
and s1.salary > s2.salary

歡迎關(guān)注微信公眾號：蛋炒番茄
同步更新?。?！