資料庫SQL實戰（牛客網）：擷取所有非manager員工目前的薪水情況

擷取所有非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

));

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