-- echo Building demo tables. Please wait. DROP TABLE EMP CASCADE CONSTRAINTS; D

Question

-- echo Building demo tables. Please wait.

DROP TABLE EMP CASCADE CONSTRAINTS;

DROP TABLE DEPT;

DROP TABLE SALGRADE;

DROP TABLE Prod CASCADE CONSTRAINTS;

DROP TABLE Vend;

CREATE TABLE DEPT (

DEPTNO NUMBER(2) NOT NULL,

DNAME CHAR(14),

LOC CHAR(13),

CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));

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');

CREATE TABLE EMP (

EMPNO NUMBER(4) NOT NULL,

ENAME CHAR(10),

JOB CHAR(9),

MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY

REFERENCES EMP (EMPNO),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2) NOT NULL,

CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),

CONSTRAINT EMP_PRIM_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);

CREATE TABLE SALGRADE (

GRADE NUMBER,

LOSAL NUMBER,

HISAL NUMBER);

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);

CREATE TABLE Prod

( ProdNo NUMBER(4) NOT NULL,

PName CHAR(10),

Type CHAR(4),

Family NUMBER(4),

Price NUMBER(7,2),

Disc NUMBER(3,1),

IntroDate DATE,

VendNo NUMBER(4),

Inv NUMBER(3),

PRIMARY KEY (ProdNo));

INSERT INTO Prod VALUES (

4186, 'Lotus 123', 'SPSH', 2215, 399.95, 25, '08-MAY-1990' ,26, 35);

INSERT INTO Prod VALUES (

2215, 'Windows', 'OS', 7224, 129, 40, '15-JUN-1990' ,12, 123);

INSERT INTO Prod VALUES (

6240, 'AmiPro', 'WP', 2215, 295.5, 33.3, '01-JUN-1990' ,26, 17);

INSERT INTO Prod VALUES (

7224, 'MS-DOS', 'OS', NULL, 99.95, 30, '03-MAR-1991' ,12, 88);

INSERT INTO Prod VALUES (

3055, 'Lotus 123', 'SPSH', 3088, 399.95, 0, '18-OCT-1990' ,26, 12);

INSERT INTO Prod VALUES (

3088, 'Macintosh', 'OS', NULL, 149.95, NULL, '12-DEC-1989' ,41, 142);

INSERT INTO Prod VALUES (

1108, 'Finance', 'BUS', 4186, 99.95, NULL, '22-APR-1991' ,82, 16);

INSERT INTO Prod VALUES (

9167, 'Lotus 123', 'SPSH', 7224, 399.95, 35, '26-FEB-1989' ,26, 71);

INSERT INTO Prod VALUES (

4925, 'Paradox', 'DBMS', 7224, 345, 25, '21-SEP-1989' ,55, 64);

INSERT INTO Prod VALUES (

1067, 'Finance', 'BUS', 9167, 99.95, NULL, '07-MAR-1989' ,82, 0);

INSERT INTO Prod VALUES (

6482, 'BusPlan', 'BUS', 4186, 54.5, 10, '05-JAN-1991' ,82, 41);

INSERT INTO Prod VALUES (

7190, 'BusPlan', 'BUS', 9167, 54.5, 10, '14-FEB-1989' ,82, NULL);

INSERT INTO Prod VALUES (

6888, 'BusPlan', 'BUS', 3055, 54.5, 0, '14-FEB-1989' ,82, 26);

INSERT INTO Prod VALUES (

3981, 'SQL*Report', 'DBMS', 5476, 149.5, 0, '22-SEP-1990' ,58, 12);

INSERT INTO Prod VALUES (

9482, 'Quattro', 'SPSH', 7224, 199.95, 30, '24-AUG-1990' ,55, 53);

INSERT INTO Prod VALUES (

5476, 'Oracle', 'DBMS', 7224, 895, 5, '12-SEP-1990' ,58, 38);

INSERT INTO Prod VALUES (

3007, 'Finance', 'BUS', 9482, 99.95, NULL, '06-NOV-1990' ,82, 17);

INSERT INTO Prod VALUES (

8120, 'Inventory', 'BUS', 9482, 199.5, 10, '06-NOV-1990' ,82, 0);

INSERT INTO Prod VALUES (

1830, 'SQL*Plus', 'DBMS', 5476, 199.5, 5, '06-OCT-1990' ,58, 19);

CREATE TABLE Vend

( VName CHAR(10),

VState CHAR(2),

VendNo NUMBER(2) NOT NULL,

Acct CHAR(5),

PRIMARY KEY (VendNo));

INSERT INTO Vend VALUES (

'Apple', 'CA', 41, 'COD');

INSERT INTO Vend VALUES (

'Oracle', 'CA', 58, '30');

INSERT INTO Vend VALUES (

'Lotus', 'UT', 26, '30');

INSERT INTO Vend VALUES (

'Microsoft', 'WA', 12, '10');

INSERT INTO Vend VALUES (

'Acme', 'UT', 82, 'COD');

INSERT INTO Vend VALUES (

'Borland', 'CA', 55, '30');

INSERT INTO Vend VALUES (

'Ace', 'OR', 67, '30');

COMMIT;

Run the following script to setup the database and write queries for the following questions.

6. Display employee number, employee name, department number, job for an employee who is not a manager and is not a clerk in department number 10.

Explanation / Answer

Below is the requested query:

Select EMPNO NUMBER, ENAME , JOB, DEPTNO  from EMP, DEPT where job not in(select distinct(EMP.job) from EMP where EMP.JOB=’MANAGER’ or EMP.JOB=’CLERK’ AND DEPT. DEPTNO  =10;

Thank you.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.