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
-- 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.
5. Display all the fields of dept table, where location is BOSTON.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started