-- echo Building demo tables. Please wait. DROP TABLE EMP CASCADE CONSTRAINTS; DROP TABLE DEPT; DROP...
-- 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.
Homework Answers
SQL> select *from DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select *from EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ----------
----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ----------
----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ----------
----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7900 JAMES CLERK 7698 03-DEC-81 950
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ----------
----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7369 SMITH CLERK 7902 17-DEC-80 800
20
7788 SCOTT ANALYST 7566 09-DEC-82 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ----------
----------
DEPTNO
----------
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> CREATE TABLE SALGRADE (
2 GRADE NUMBER,
3 LOSAL NUMBER,
4 HISAL NUMBER);
Table created.
SQL> INSERT INTO SALGRADE VALUES (1,700,1200);
1 row created.
SQL> INSERT INTO SALGRADE VALUES (2,1201,1400);
1 row created.
SQL> INSERT INTO SALGRADE VALUES (3,1401,2000);
1 row created.
SQL> INSERT INTO SALGRADE VALUES (4,2001,3000);
1 row created.
SQL> INSERT INTO SALGRADE VALUES (5,3001,9999);
1 row created.
SQL> CREATE TABLE Prod
2 ( ProdNo NUMBER(4) NOT NULL,
3 PName CHAR(10),
4 Type CHAR(4),
5 Family NUMBER(4),
6 Price NUMBER(7,2),
7 Disc NUMBER(3,1),
8 IntroDate DATE,
9 VendNo NUMBER(4),
10 Inv NUMBER(3),
11 PRIMARY KEY (ProdNo));
Table created.
SQL> INSERT INTO Prod VALUES (
2 4186, 'Lotus 123', 'SPSH', 2215, 399.95, 25, '08-MAY-1990' ,26,
35);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 2215, 'Windows', 'OS', 7224, 129, 40, '15-JUN-1990' ,12,
123);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 6240, 'AmiPro', 'WP', 2215, 295.5, 33.3, '01-JUN-1990' ,26,
17);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 7224, 'MS-DOS', 'OS', NULL, 99.95, 30, '03-MAR-1991' ,12,
88);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 3055, 'Lotus 123', 'SPSH', 3088, 399.95, 0, '18-OCT-1990' ,26,
12);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 3088, 'Macintosh', 'OS', NULL, 149.95, NULL, '12-DEC-1989' ,41,
142);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 1108, 'Finance', 'BUS', 4186, 99.95, NULL, '22-APR-1991' ,82,
16);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 9167, 'Lotus 123', 'SPSH', 7224, 399.95, 35, '26-FEB-1989' ,26,
71);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 4925, 'Paradox', 'DBMS', 7224, 345, 25, '21-SEP-1989' ,55,
64);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 1067, 'Finance', 'BUS', 9167, 99.95, NULL, '07-MAR-1989' ,82,
0);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 6482, 'BusPlan', 'BUS', 4186, 54.5, 10, '05-JAN-1991' ,82,
41);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 7190, 'BusPlan', 'BUS', 9167, 54.5, 10, '14-FEB-1989' ,82,
NULL);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 6888, 'BusPlan', 'BUS', 3055, 54.5, 0, '14-FEB-1989' ,82,
26);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 3981, 'SQL*Report', 'DBMS', 5476, 149.5, 0, '22-SEP-1990' ,58,
12);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 9482, 'Quattro', 'SPSH', 7224, 199.95, 30, '24-AUG-1990' ,55,
53);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 5476, 'Oracle', 'DBMS', 7224, 895, 5, '12-SEP-1990' ,58, 38);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 3007, 'Finance', 'BUS', 9482, 99.95, NULL, '06-NOV-1990' ,82,
17);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 8120, 'Inventory', 'BUS', 9482, 199.5, 10, '06-NOV-1990' ,82,
0);
1 row created.
SQL> INSERT INTO Prod VALUES (
2 1830, 'SQL*Plus', 'DBMS', 5476, 199.5, 5, '06-OCT-1990' ,58,
19);
1 row created.
SQL> CREATE TABLE Vend
2 ( VName CHAR(10),
3 VState CHAR(2),
4 VendNo NUMBER(2) NOT NULL,
5 Acct CHAR(5),
6 PRIMARY KEY (VendNo));
Table created.
SQL> INSERT INTO Vend VALUES (
2 'Apple', 'CA', 41, 'COD');
1 row created.
SQL> INSERT INTO Vend VALUES (
2 'Oracle', 'CA', 58, '30');
1 row created.
SQL> INSERT INTO Vend VALUES (
2 'Lotus', 'UT', 26, '30');
1 row created.
SQL> INSERT INTO Vend VALUES (
2 'Microsoft', 'WA', 12, '10');
1 row created.
SQL> INSERT INTO Vend VALUES (
2 'Acme', 'UT', 82, 'COD');
1 row created.
SQL> INSERT INTO Vend VALUES (
2 'Borland', 'CA', 55, '30');
1 row created.
SQL> INSERT INTO Vend VALUES (
2 'Ace', 'OR', 67, '30');
1 row created.
SQL> select *from DEPT where LOC='BOSTON';
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Add Answer to:
-- echo Building demo tables. Please wait.
DROP TABLE EMP CASCADE CONSTRAINTS;
DROP TABLE DEPT;
DROP...
Create an XML document for the Oracle Starter Database Tables (i.e., emp, dept, ord, product, price)....
Create an XML document for the Oracle Starter Database Tables (i.e., emp, dept, ord, product, price). Each XML document must be in the format as described in the "Mapping tables to XML" section on pg. 1161 (any column that does not have a value can be displayed as empty, e.g., <DEGREE></DEGREE> ). The name for each document will be the name of the table followed by ".xml" as the file extension (e.g., "emp.xml"). Data for dept. table CREATE TABLE DEPT...
CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT...
CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13),
PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK');
INSERT INTO DEPT VALUES (20,'HOME','DALLAS');
INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO');
INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON');
CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
SAL FLOAT,
COMM FLOAT,
DEPTNO INTEGER NOT NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
FOREIGN KEY (MGR) REFERENCES EMP(EMPNO),
PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,
5000,NULL,10);
INSERT INTO...
drop table department cascade constraints; create table department ( Dname varchar2(15) not null, Dnumber int not...
drop table department cascade constraints;
create table department (
Dname varchar2(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
mgr_start_date Date,
primary key (Dnumber),
Unique (Dname));
insert into DEPARTMENT values ('Research', '5', '333445555',
'22-May-1988');
insert into DEPARTMENT values ('Administration', '4', '987654321',
'01-Jan-1995');
insert into DEPARTMENT values ('Headquarters', '1', '888665555',
'19-Jun-1981');
drop table employee cascade constraints;
create table employee (
Fname varchar2(15) not null,
Minit char(1),
Lname varchar2(15) not null,
Ssn char(9),
Bdate date,
Address varchar2(30),
Sex char(1),...
Here is the P4 university database ---Drop tables --Drop Table FACULTY Cascade Constraints; --Drop Table OFFERING...
Here is the P4 university database ---Drop tables --Drop Table FACULTY Cascade Constraints; --Drop Table OFFERING Cascade Constraints; --Drop Table STUDENT Cascade Constraints; --Drop Table COURSE Cascade Constraints; --Drop Table ENROLLMENT Cascade Constraints; Create Table STUDENT( Std_ID NUMBER(3) Constraint Student_STD_ID_FK PRIMARY KEY, Std_FN Varchar2 (9), Std_LN Varchar2 (8) , Std_City Varchar2 (10), Std_State Varchar2 (4), Std_Zip Number (5), Std_Major Varchar2 (6), Std_Class Varchar2 (3), Std_GPA NUMBER (3,2)); ---Insert records into Student Insert Into STUDENT Values( 101, 'Joe', 'Smith','Eau Clare','WI',18121,'IS','FR',3.8); Insert...
Q2. Retrieve the names of all employees from the employee table to produce output on CSV...
Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...
CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13),
PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK');
INSERT INTO DEPT VALUES (20,'HOME','DALLAS');
INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO');
INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON');
CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
SAL FLOAT,
COMM FLOAT,
DEPTNO INTEGER NOT NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
FOREIGN KEY (MGR) REFERENCES EMP(EMPNO),
PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,
5000,NULL,10);
INSERT INTO...
drop table department cascade constraints;
create table department (
Dname varchar2(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
mgr_start_date Date,
primary key (Dnumber),
Unique (Dname));
insert into DEPARTMENT values ('Research', '5', '333445555',
'22-May-1988');
insert into DEPARTMENT values ('Administration', '4', '987654321',
'01-Jan-1995');
insert into DEPARTMENT values ('Headquarters', '1', '888665555',
'19-Jun-1981');
drop table employee cascade constraints;
create table employee (
Fname varchar2(15) not null,
Minit char(1),
Lname varchar2(15) not null,
Ssn char(9),
Bdate date,
Address varchar2(30),
Sex char(1),...
Most questions answered within 3 hours.
-
McCracken Aerial,
Inc., produces and sells a unique type of TV antenna. The company
has just...
asked 3 seconds from now -
Write a program to prompt the user for hours and rate per hour
using input to...
asked 24 minutes ago -
Hi, please help
1)Draw two molecules that could hydrogen bond with each other.
Draw and indicate...
asked 28 minutes ago -
Using Java, I created 3 files, Pizza,PizzaOrder, and
PizzaOrder_Demo that I attached down below. But I...
asked 27 minutes ago -
a. An 8-bit successive approximation ADC has a reference voltage
of 10 V. Calculate the resolution...
asked 37 minutes ago -
The distance between the two hydrogen nuclei in the H2 molecule
is 110 pm (picometer). How...
asked 39 minutes ago -
write an article about drone effect on
construction safety.
asked 42 minutes ago -
Consider the following data for a dependent variable y and two
independent variables, x1 and x2....
asked 1 hour ago -
what is the most likely shape for a distribution with a mean of
40 and a...
asked 53 minutes ago -
The work function, Φ0 (or W0), for cesium is 3.43×10−19 J. The
frequency of a photon...
asked 59 minutes ago -
18.54----At the surface of Venus the average temperature is a
balmy 460 ∘C due to the...
asked 58 minutes ago -
#1. A food has been ingested. Identify all of the anatomic
structures and substructures it travels...
asked 51 minutes ago