Topic Description Find all Departments current (to_date= ' 9999-01-01 ') leadership current salary details and their corresponding department number Dept_no
CREATE TABLE ' Dept_manager ' (
' Dept_no ' char (4) NOT NULL,
' emp_no ' int (one) not null,
' from_date ' date not NULL ,
' to_date ' date not NULL,
PRIMARY KEY (' emp_no ', ' dept_no '));
CREATE TABLE ' salaries ' (
' emp_no ' int (one) not null,
' salary ' int (one) not null,
' from_date ' date not NULL,
' to_date ' date not NULL,
PRIMARY KEY (' emp_no ', ' from_date '));
Enter a description:
No
Output Description:
Emp_no |
Salary |
from_date |
to_date |
Dept_no |
10002 |
72527 |
2001-08-02 |
9999-01-01 |
d001 |
10004 |
74057 |
2001-11-27 |
9999-01-01 |
d004 |
10005 |
94692 |
2001-09-09 |
9999-01-01 |
d003 |
10006 |
43311 |
2001-08-02 |
9999-01-01 |
d002 |
10010 |
94409 |
2001-11-23 |
9999-01-01 |
d006 |
Example 1
input
No
Output
No
Solution:
This problem needs to be examined carefully, with the following considerations. The table is a two-table association table, but the elements cannot be repeated, and you need to salaries the table's field order, all the fields in the table, and the department number the employee number of the table-emp_no is incremented sequentially, so you need to place the salaries table in the front
Select S.*,d.dept_no from
salaries as s,dept_manager as D
where s.to_date= "9999-01-01" and
d.to_date= " 9999-01-01 "and
d.emp_no = S.emp_no