Tables: CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT Pk_Dept PRIMARY KEY, DNA
ID: 3541719 • Letter: T
Question
Tables:
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT Pk_Dept PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
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');
COMMIT;
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT Pk_Emp PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT EMP_FOREIGN_KEY REFERENCES DEPT (DEPTNO))
DISABLE CONSTRAINT EMP_SELF_KEY;
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'02-APR-1981',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-1981',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-1981',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-1981',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'03-DEC-1981',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'03-DEC-1981',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);
COMMIT;
Tasks:
44.Use subquery to find employees in Department 10 of the same positions as in
department 30
ENAME JOB
---------- ---------
MILLER CLERK
CLARK MANAGER
Use subquery to find employees in the department 10 that not have same positions as them in
department 30
ENAME JOB
---------- ---------
KING PRESIDENT
46. Use subquery to find employees with the same position and salary as 'FORD'
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
FORD ANALYST 3000
47.
Use subquery and show employees that have either the same position as JONES
or salary greater than or equal 'FORD' 's salary sorted by position and salary
ENAME JOB DEPTNO SAL
---------- --------- ---------- ----------
SCOTT ANALYST 20 3000
FORD ANALYST 20 3000
CLARK MANAGER 10 2450
BLAKE MANAGER 30 2850
JONES MANAGER 20 2975
KING PRESIDENT 10 5000
48.
Use subqueries and show employees in department 10 with the same position as employees of
department SALES
ENAME JOB
---------- ---------
MILLER CLERK
CLARK MANAGER
49.Use UNION to show employees that have the same salary as and SCOTT and WARD
ENAME JOB SAL
---------- --------- ----------
WARD SALESMAN 1250
MARTIN SALESMAN 1250
SCOTT ANALYST 3000
FORD ANALYST 3000
50.
Use subquery to find employees with the same position as employees in CHICAGO
ENAME JOB
---------- ---------
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SMITH CLERK
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
ALLEN SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
WARD SALESMAN
51.Using inline view to list the percentage of salary for each employee
relative to the total salary
Name Salary %
------------------------------ ---------- ------
SMITH 800 2.76
ALLEN 1600 5.51
WARD 1250 4.31
JONES 2975 10.25
MARTIN 1250 4.31
BLAKE 2850 9.82
CLARK 2450 8.44
SCOTT 3000 10.34
KING 5000 17.23
TURNER 1500 5.17
ADAMS 1100 3.79
JAMES 950 3.27
FORD 3000 10.34
MILLER 1300 4.48
52.
Use inline view to list the percentage of salary for employees per.department
relative to total wages sorted by ascending percent.
Department Sum %
-------------- ---------- ------
ACCOUNTING 8750 30.15
SALES 9400 32.39
RESEARCH 10875 37.47
Explanation / Answer
When is this assignment due mate ?
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.