Write stored procedure in SQL Find all employees whose salary is larger than the salary of at least one member of employee at Department operations. solve the above query according to the schema below DATABASE BEGIN TRAN T1 CREATE TABLE DEPT (DEPTNO NUMERIC(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR(14) , LOC VARCHAR(13) ) ; CREATE TABLE EMP (EMPNO NUMERIC(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4), HIREDATE DATETIME, SAL NUMERIC(7,2), COMM NUMERIC(7,2), DEPTNO NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); 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'); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902, '12-17-1980' ,800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698, '2-20-1981' ,1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698, '2-22-1981' ,1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839, '4-2-1981' ,2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698, '9-28-1981' ,1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839, '5-1-1981' ,2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839, '6-9-1981' ,2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566, '7-13-1987',3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, '11-17-1981',5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698, '9-8-1981' ,1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788, '7-13-1987',1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698, '12-3-81' ,950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566, '12-3-1981' ,3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782, '1-23-1982' ,1300,NULL,10); CREATE TABLE BONUS ( ENAME VARCHAR(10) , JOB VARCHAR(9) , SAL NUMERIC, COMM NUMERIC ) ; CREATE TABLE SALGRADE ( GRADE NUMERIC, LOSAL NUMERIC, HISAL NUMERIC ); INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999); COMMIT TRAN T1
Write stored procedure in SQL Find all employees whose salary is larger than the salary of at least one member of employee at Department operations.
solve the above query according to the schema below
BEGIN TRAN T1
CREATE TABLE DEPT
(DEPTNO NUMERIC(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR(14) ,
LOC VARCHAR(13) ) ;
CREATE TABLE EMP
(EMPNO NUMERIC(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
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');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902, '12-17-1980' ,800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698, '2-20-1981' ,1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698, '2-22-1981' ,1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839, '4-2-1981' ,2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698, '9-28-1981' ,1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839, '5-1-1981' ,2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839, '6-9-1981' ,2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566, '7-13-1987',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL, '11-17-1981',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698, '9-8-1981' ,1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788, '7-13-1987',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698, '12-3-81' ,950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566, '12-3-1981' ,3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782, '1-23-1982' ,1300,NULL,10);
CREATE TABLE BONUS
(
ENAME VARCHAR(10) ,
JOB VARCHAR(9) ,
SAL NUMERIC,
COMM NUMERIC
) ;
CREATE TABLE SALGRADE
( GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT TRAN T1
Step by step
Solved in 4 steps with 1 images
