Table 1 DEPT
DEPTNO DNAME LOC
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Table 2 EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
7369 SMITH at (NULL) 20 7902 1980-12-17 800
7499 ALLEN SALESMAN 30
7698 1981-02-20 1600 3007521 WARD SALESMAN 30
7698 1981-02-22 1250 5007566 JONES MANAGER (NULL) 20 7839 1981-04-02 2975
7654 MARTIN SALESMAN 30
7698 1981-09-28 1250 14007839 1981-05-01 2850 7698 BLAKE MANAGER (NULL) 30
7782 CLARK MANAGER 7839 1981-06-09 2450 (NULL) 10
7788 SCOTT ANALYST (NULL) 20 7566 1987-07-03 3000
7839 KING PRESIDENT (NULL) 1981-11-17 5000 (NULL) 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
ADAMS at 7788 1987-07-13 1100 7876 (NULL) 20
7900 JAMES CLERK 7698 1981-12-03 950 (NULL) 30
7902 FORD ANALYST (NULL) 20 7566 1981-12-03 3000
7934 MILLER at 7782 1981-01-23 1300 (NULL) 10
Table 3 SALGRADE
GRADE LOSAL HISAL
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 700 1200
2 1201 1400
3 1401 2000
4, 2001, 3000
5 3001 9999
Now the problem is with 30 employees want to return to wage level of the same name and salary employees, should how to implement the
The following is what I write, but not after the search results
SELECT
E. ` ENAME `,
E. ` SAL `,
The FROM
EMP E,
SALGRADE S
WHERE
(SELECT GRADE FROM SALGRADE WHERE (E.D EPTNO=30 AND e. ` SAL ` & gt;=s. ` LOSAL ` AND e. ` SAL ` & lt;=` DEPT ` s. ` HISAL `)) IN
(SELECT GRADE FROM SALGRADE WHERE (e. ` DEPTNO `!=30 AND e. ` SAL ` & gt;=s. ` LOSAL ` AND e. ` SAL ` & lt;=s. ` HISAL `));
, every brother please!!!!
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The following for data
Table # # department
# DROP IF the EXISTS TABLE DEPT;
The CREATE TABLE DEPT (
DEPTNO int PRIMARY KEY, # # department number
DNAME VARCHAR (14), # # department name
LOC VARCHAR (13) # # department address
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
# # the staff table
# DROP IF the EXISTS TABLE EMP;
The CREATE TABLE EMP (
EMPNO int PRIMARY KEY, # employee number
ENAME VARCHAR (10), # employee name
JOB VARCHAR (9), # employees work
MGR int, # directly under the leadership staff number
HIREDATE DATE, induction time #
SAL double, # pay
COMM double, # bonus
DEPTNO int # corresponds to the dept table's foreign key
);
# # to add the main foreign key relationships between departments and employees
The ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP (DEPTNO) REFERENCES DEPT (DEPTNO);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, "1980-12-17", 800, NULL, 20).
INSERT INTO EMP VALUES (7499, 'ALLEN, "SALESMAN", 7698,' 1981-02-20 ', 1600300, 30);
INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, '1981-02-22', 1250500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20).
INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698, '1981-09-28', 1250140, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST, 7566,' 1987-07-03 ', 3000, NULL, 20).
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT, NULL,' 1981-11-17 ', 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER, "SALESMAN", 7698,' 1981-09-08 ', 1500,0,30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK' 7788, '1987-07-13', 1100, NULL, 20).
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK' 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST, 7566,' 1981-12-03 ', 3000, NULL, 20).
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK' 7782, '1981-01-23', 1300, NULL, 10);
# wage scale
# DROP IF the EXISTS TABLE SALGRADE;
The CREATE TABLE SALGRADE (
The level of GRADE int, #
LOSAL double, # minimum wage
HISAL double); # the highest salary
INSERT INTO SALGRADE VALUES (1700120);
INSERT INTO SALGRADE VALUES (2120, 1140);
INSERT INTO SALGRADE VALUES (3140, 1200);
INSERT INTO SALGRADE VALUES (4200, 1300);
INSERT INTO SALGRADE VALUES (5300, 1999, 9);
CodePudding user response:
Demo:
If object_id (' tempdb for.. # dept ') is not null
Drop table # dept
Go
The create table # dept (
Deptno int primary key,
Dname varchar (14),
Loc varchar (13)
)
Insert into # dept (deptno, dname, loc)
Select 10, 'ACCOUNTING', 'NEW YORK' union all
Select 20, 'RESEARCH', 'DALLAS' union all
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull