Database work

deadline 04/18/2021

Database Design & Tools
• Use the start script posted on Canvas to create, populate, and add foreign key constraints

the tables provided below.

Database Description

Part 1: Primary Keys and Indexes

When creating the PARENT table, you realized that you did not include any primary key

specification. Since Mother_ID and Child_ID are all needed to fully define each row, create a

primary key that uses both of these attributes.

As you keep adding more households to your database, you’ll want to be able to query the

HOUSEHOLD table and have results displayed as fast as possible. Since you’ll be doing most of

your queries on the city, create an index on the city and state attributes.

Part 2: Auto-increment

Adding new rows to a table can sometimes be a tedious task, especially if you have to constantly

remember which primary key value needs to be inputted next. To let Oracle do the work of

autoincrementing, create a sequence for the Pet_ID.

Once the new sequence is made, add in the Freeman family’s new dog, Balto. He was born in

2021, has long fur, and lives in the same household as Nelson and Jessica Freeman.

Part 3: Views

Suppose that some of your family members want to get data about other households you keep in

your database. However, you don’t want to reveal the entire database, so you decide to create a

view to supplement this request. Create a view named VHOUSEHOLD that shows the

household’s House_ID, city, state, and zip code.

Now that you have this view created, query the view for every household in Cincinnati.

Part 4: SQL

1. Select all of the unique states that you have family living in.

2. Select the Member_IDs, names, birthdays, and cell phone numbers of everyone who lives

with

the potbelly pig, Jingles.

3. Select the address_num, street, city, and state of the first four households listed when

ordered by city.

4. Select the names of all the members and pets that live at 321 High St.

5. Select everyone who lives in a house without any pets.

Deliverables:

Your work for Parts 1, 2, 3, and 4 organized in a notepad file showing the changes made to the

Oracle database and the proper entries made.

2 | P a g e

3 | P a g e

The initial database:

MEMBER

Member_ID Name Phone_Num Birthday Gender Lives_In

1 Nelson Freeman 614-601-5147 1-Jun-1922 M 1

2 Samuel Hunter 513-395-7045 26-Aug-1922 M 4

3 William Mack 330-949-2522 12-Aug-1923 M 2

4 Mary Burbank 330-512-9725 15-Feb-1922 F 2

5 Samuel Freeman 15-Mar-1998 M 6

6 Jessica Freeman 614-719-9213 22-Jul-1952 F 1

7 Enoch Whitman 216 -848-9837 16-Dec-1918 M 3

8 Rebecca Gardner 25-May-1923 F 3

9 Jane Hunter 216-596-6321 29-Jan-1948 F 3

10 Lucerna Harlow 513-786-2650 3-Sep-1918 F 4

11 David Freeman 513-545-8812 28-Nov-1948 M 5

12 Desiah Mack 330-802-5776 14-Oct-1947 F 5

13 Nathanial Whitman 216-596-6123 17-Jun-1943 M 3

14 Joseph Freeman 312-893-2254 27-Dec-1972 M 6

15 Abigale Whitman 773-254-8919 3-Feb-1968 F 6

16 Mary Mader 614-624-6628 12-Jan-1918 F 1

17 Alex Whitman 216-596-6240 23-Jan-1948 F 3

PARENT

PET

Pet_ID PName Type Fur Type Year_born House_In

1 Jack Dog Long Hair 2008 1

2 Crookshanks Cat Long Hair 2015 1

3 Whiskers Dog Short Hair 2010 2

4 Jingles Potbelly Pig Short Hair 2004 3

Mother_ID Father_ID Child_ID

16 2 11

16 2 6

4 3 12

8 7 17

8 7 13

7 8 9

11 12 14

9 13 15

15 14 5

4 | P a g e

5 Tweet Canary Feathers 2018 5

HOUSEHOLD

House_ID Address_Num Street City State Zip

1 321 High St. Columbus OH 43050

2 123 2nd Ave. Akron OH 44223

3 444 Main Dr. Cleveland OH 44101

4 999 Freeland St. Cincinnati OH 45220

5 265 Iota Ave. Cincinnati OH 45220

6 102 Michigan Ave. Chicago IL 60632

CREATE table MEMBER
(Member_ID int constraint pk_member_id PRIMARY KEY,
Name varchar2(50) constraint nm_name NOT NULL,
Phone_Num varchar2(12),
Birthday date constraint nm_birthday NOT NULL,
Gender char (1) constraint nn_gender NOT NULL, constraint chk_gender check (Gender in (‘M’,’F’)),
Lives_In int);
CREATE table PARENT
(Mother_ID int constraint nm_mother NOT NULL,
Father_ID int constraint nm_father NOT NULL,
Child_ID int constraint nm_child NOT NULL);
CREATE table PET
(Pet_ID int constraint pk_pet_id PRIMARY KEY,
PName varchar2(50) constraint pt_name NOT NULL,
Type varchar2(50),
Fur_Type Varchar2 (30),
Year_Born int,
House_In int);
CREATE table HOUSEHOLD
(House_ID int constraint pk_house_id PRIMARY KEY,
Address_num char(10),
Street varchar2(50),
City varchar2(50),
State char(2),
zip int);
INSERT ALL
INTO MEMBER values(1, ‘Nelson Freeman’, ‘614-601-5147’, ‘1-Jun-1922’, ‘M’, 1)
INTO MEMBER values(16, ‘Mary Mader’, ‘614-624-6628′, ’12-Jan-1918’, ‘F’, 1)
INTO MEMBER values(3, ‘William Mack’, ‘330-949-2522′, ’12-Aug-1923’, ‘M’, 2)
INTO MEMBER values(4, ‘Mary Burbank’, ‘330-512-9725′, ’15-Feb-1922’, ‘F’, 2)
INTO MEMBER values(7, ‘Enoch Whitman’, ‘216-848-9837′, ’16-Dec-1918’, ‘M’, 3)
INTO MEMBER values(8, ‘Rebecca Gardner’, ”, ’25-May-1923′, ‘F’, 3)
INTO MEMBER values(2, ‘Samuel Hunter’, ‘513-395-7045′, ’26-Aug-1922’, ‘M’, 4)
INTO MEMBER values(10, ‘Lucerna Harlow’, ‘513-786-2650’, ‘3-Sep-1918’, ‘F’, 4)
INTO MEMBER values(11, ‘David Freeman’, ‘513-545-8812′, ’28-Nov-1948’, ‘M’, 5)
INTO MEMBER values(12, ‘Desiah Mack’, ‘330-802-5776′, ’14-Oct-1947’, ‘F’, 5)
INTO MEMBER values(13, ‘Nathanial Whitman’, ‘216-596-6123′, ’17-Jun-1943’, ‘M’, 3)
INTO MEMBER values(9, ‘Jane Hunter’, ‘216-596-6321′, ’29-Jan-1948’, ‘F’, 3)
INTO MEMBER values(14, ‘Joseph Freeman’, ‘312-893-2254′, ’27-Dec-1972’, ‘M’, 6)
INTO MEMBER values(15, ‘Abigale Whitman’, ‘773-254-8919’, ‘3-Feb-1968’, ‘F’, 6)
INTO MEMBER values(5, ‘Samuel Freeman’, ”, ’15-Mar-1998′, ‘M’, 6)
INTO MEMBER values(6, ‘Jessica Freeman’, ‘614-719-9213′, ’22-Jul-1952’, ‘F’, 1)
INTO MEMBER values(17, ‘Alex Whitman’, ‘216-596-6240′, ’23-Jan-1948’, ‘F’, 3)
SELECT * FROM DUAL;
COMMIT;
INSERT ALL
INTO PARENT values(16,2,11)
INTO PARENT values(16,2,6)
INTO PARENT values(4,3,12)
INTO PARENT values(8,7,12)
INTO PARENT values(8,7,17)
INTO PARENT values(7,8,9)
INTO PARENT values(11,12,14)
INTO PARENT values(9,13,15)
INTO PARENT values(15,14,5)
SELECT * FROM DUAL;
COMMIT;
INSERT ALL
INTO PET values(1,’Jack’,’Dog’, ‘Long_Hair’,2008,1)
INTO PET values(2,’Crookshanks’,’Cat’,’Long_Hair’,2015,1)
INTO PET values(3,’Whiskers’,’Dog’,’Short_Hair’,2010,2)
INTO PET values(4,’Jingles’,’Potbelly Pig’,’Short_Hair’,2004,3)
INTO PET values(5,’Tweet’,’Canary’,’Feather’,2018,5)
SELECT * FROM DUAL;
COMMIT;
INSERT ALL
INTO HOUSEHOLD values(1,’321′, ‘High St.’,’Columbus’,’OH’,43050)
INTO HOUSEHOLD values(2,’123′, ‘2nd Ave.’,’Akron’,’OH’,44223)
INTO HOUSEHOLD values(3,’444′, ‘Main Dr.’,’Cleveland’,’OH’,44101)
INTO HOUSEHOLD values(4,’999′, ‘Freeland St.’,’Cincinnati’,’OH’,45220)
INTO HOUSEHOLD values(5,’265′, ‘Iota Ave.’,’Cincinnati’,’OH’,45220)
INTO HOUSEHOLD values(6,’102′, ‘Michigan Ave.’,’Chicago’,’IL’,60632)
SELECT * FROM DUAL;
COMMIT;

ALTER table MEMBER
add constraint fk_lives_in foreign key
(Lives_In) references HOUSEHOLD (House_ID);

ALTER table PARENT
add constraint fk_mother_id foreign key
(Mother_ID) references MEMBER (Member_ID);
ALTER table PARENT
add constraint fk_father_id foreign key
(Father_ID) references MEMBER (Member_ID);
ALTER table PARENT
add constraint fk_child_id foreign key
(Child_ID) references MEMBER (Member_ID);

ALTER table PET
add constraint fk_house_in foreign key
(House_In) references HOUSEHOLD (House_ID);

2

2-I

S

3

0

30

Database Design &

T

ools

1

Lab

A

ssignment

Part 1A (

10

points):

To begin, you’ll need to drop all tables that are currently in your Oracle account
besides the tables from Lab Assignment

4

.

·

V

iew all tables currently in your account using the statement S

E

LECT TA

B

LE_NA

M

E

F

ROM USER_TABLES; Then, delete each table using a drop statement: DROP TABLE

CASCADE CONSTRAINTS;

· Use the start script posted on Canvas to create, populate, and add foreign key constraints the tables provided below.

M

5

222556666

5

666884444

B

F

222556666

5

M

F

777211234

987987987

M

4

F

64000

4

Employee

Ssno

Last_name

Mi

First_name

Gender

Bdate

Address

Salary

Supssno

Dno

1234 5 6789

MCCARTNEY

B

paul

M

12/25/1978

731, Fondren, WACO , TX

30000

666884444

5

222556666

Wong

T

Frank

11/25/1978

638, Voss, CLEVELAND , OH

55000

987987987

453453453

English

A

J oyce

F

10/25/1978

5631, Rice, ORLANDO , FL

50000

GOMEZ

Jane

9/25/1978

975, FireOak, Humble, TX

45000

777211234

RUTHERFORD

E

James

8/25/1978

3321, Castle, Spring, TX

6 40 00

1

987654321

SMITH

S

Jeniffer

7/25/1978

2912, BERRY, CINCINNATI , OH

60000

4

Jabbar

V

Ahmad

6/25/1978

980, CALHOUN, CINCINNATI, OH

35 000

123123123

KEYS

J

Alicia

5/25/1978

3321, Castle, CINCINNATI, OH

Department

Dept_name

Dept_no

Mgr_start_date

Mgrssno

Headquarters

1

6/19/91

777211234

Administration

4

1/1/89

987654321

Production

5

5/22/93

222556666

Location

LoCATION

LOC_SIZE

STATE

WACO

20

0

tx

CINCINNATI

200

OH

ORLANDO

300

fl

CLEVELAND

400

OH

123456789

1

123456789

222556666

2

222556666

10

222556666

10

10

222556666

10

453453453

1

453453453

2

20

666884444

3

777211234

20

987654321

20

987654321

30

20

987987987

10

987987987

30

5

123123123

10

10

123123123

30

Works_on
	Essno	Pno	Work_hrs
32.5
			2	7.5
								10
		3
				30
					20
40
0
15
35
25.5

Essno

123456789

F

123456789

F

123456789

M

222556666

F

Daughter

222556666

M

Spouse

123123123

M

Son

987654321

M

Spouse

DependEnt
DependEnt_nm	Dep_gender	Dep_bdate	Related_how
Alice	6/7/92		Daughter
Elizabeth	5/5/67			Spouse
Michael	1/10/89		Son
sARAH	4/5/86
Joy	5/3/58
Theodore	11/12/96
BILL	2/29/60

1

WACO

4

CINCINNATI

5

CLEVELAND

5

5

ORLANDO

department_Location
Dnum	Dept_location
Detroit

1

5

2

5

3

5

10

4

20

1

30

4

Project
Proj_name	Proj_no	Dnumb
Product a
Product b
Product c
Computerization
Reorganization
BenefitS PLAN

Part 1B (10 points):

You are tasked with updating the extensive company database in Oracle to incorporate the following changes. It is industry standard to save all your work in notepad or by equivalent means in order to reference later.
You will need to make changes to the database before it will allow you to enter data, so decide what needs to be done and in what order. Changes made directly to the original code provided (database structure start script) will NOT receive credit.

1. On January 23 of this year, the company hired a new employee: Samuel G. Patel with a salary of $120,000 and an SSNO 234-01-5678. He was born April 25, 1986 and his manager is Joyce English. Samuel is the Manager of the newly created department, Operations (#18), which runs out the company’s office just opened in Miami, Florida. With this location still in its infancy stage, it only staffs 100 employees.

2. Currently, Samuel is working on a brand-new project called Productivity (#41) which the Operations Department is responsible for. He has already dedicated 89.0 hours of his time to this project and is hoping senior management will realize its potential and provide him with more employees.

3. Even though you work in database management, you have strong ties to HR and so you ask for more details regarding Samuel. Besides enjoying long walks on the beach and murder mystery novels, he has two children, Marisa and Bensen. Marisa was born May 29, 2014 while Ben turned 3 years old the day Samuel was hired. He has a caring wife, Micah, born January 14, 1981. The Patel family lives at 1270, Creekside, Miami, FL.

Part 2 (20 points):

Using the “Company database” that you have created in Oracle, construct queries in
SQL (ANSI 1991)
to answer the following questions and execute the queries on the Oracle platform (4 points each).

DML:

1. Retrieve the First name, Middle initial, Last name, Birthdate, Gender, and Salary of the employees along with their dependent names. The list should include ALL employees. (Use Outer join)

2. List the SSNO of employees who don’t have dependents.

a. Demonstrate the use of the MINUS operator in this query.

b. Write the query without using the MINUS operator.

3. List the SSNO of employees who are department managers.

a. Demonstrate the use of INTERSECT operator in this query.

b. Write the query without using the INTERSECT operator.

4. Retrieve the name and birthday of all employees; if the employee is a manager, include the name of the department managed. If the employee has a dependent(s) include the name of the dependents(s)

a. Demonstrate the use of outer join operation in this query.

-if an employee has multiple dependents it is okay for their name to be in multiple records

5. List the names of the dependents whose related employee works on the “PRODUCT B” Project.

Deliverables:

· Your SQL work for Part 1 in a notepad text file showing the changes made to the database and the proper data entries made.

· Either using the spool function, spool each query for Part 2 and its results immediately following the query, or
copy your query results in a notepad text file after each query.

create table EMPLOYEE
(Ssno char (9) constraint pk_emp primary key,
Last_name varchar2 (20) constraint nn_LastName not null,
Mi char (1),
First_name varchar2 (20) constraint nn_FirstName not null,
Gender char (1) constraint nn_gen not null, constraint chk_gender check (Gender in (‘M’,’F’)),
Bdate date,
Address varchar2 (40),
Salary number (7) constraint ck_sal check (Salary between 30000 and 70000),
Supssno char (9) DEFAULT ‘123456789’,
Dno number (2),
constraint ck_empnm unique (Last_name, Mi, First_name));
create table DEPARTMENT
(Dept_name varchar (15) constraint nn_depname not null, constraint ck_depname check (Dept_name in (‘ADMINISTRATION’,’HEADQUARTERS’,’PRODUCTION’)), constraint ck_depname2 unique (Dept_name),
Dept_no number (2) constraint pk_dep primary key, constraint ck_depno check (Dept_no between 1 and 10),
Mgr_Start_date date,
Mgrssno char (9));
create table LOCATION
(
Location varchar2 (12) constraint ck_deploc check (Location in (‘WACO’, ‘ORLANDO’, ‘CLEVELAND’, ‘CINCINNATI’ )) CONSTRAINT pk_loc PRIMARY KEY,
Loc_size number(3),
State char(2)
);
create table Department_Location
(
Dnum number(2),
Dep_Location varchar2(12),
CONSTRAINT fk_DL_dept FOREIGN KEY (Dnum) REFERENCES Department(Dept_no),
CONSTRAINT fk_Loc_Location FOREIGN KEY (Dep_Location) REFERENCES Location(Location),
CONSTRAINT pk_Dep_Loc PRIMARY KEY(Dnum,Dep_Location)
);
create table PROJECT
(Proj_name varchar2 (15), constraint un_Projname unique (Proj_name),
Proj_no number (2) constraint pk_project primary key, constraint ck_proj_no check (Proj_no between 1 and 30),
Dnumb number (2));
create table Dependent
(Essno char (9),
Dependent_nm varchar2 (15),
Dep_gender char (1) constraint nn_depgender not null, constraint ck_depgender check (Dep_gender in (‘M’,’F’)),
Dep_bdate date,
Related_how varchar2 (8) constraint ck_relhow check (Related_how in (‘DAUGHTER’, ‘SPOUSE’, ‘SON’)),
constraint pk_depd primary key (Essno, Dependent_nm),
constraint ck_gendrel check((Dep_gender in (‘M’) and Related_how in (‘SPOUSE’, ‘SON’)) or (Dep_gender in (‘F’) and Related_how in (‘SPOUSE’, ‘DAUGHTER’))));
create table WORKS_ON
(Essno char (9),
Pno number (2) ,
Work_hrs number (3,1) constraint ck_workhrs check (Work_hrs <= 80), constraint pk_work primary key (Essno, Pno)); INSERT ALL into EMPLOYEE values('123456789', 'MCCARTNEY', 'B', 'PAUL', 'M', '25-Dec-78', '731, FONDREN, WACO, TX', 30000, '666884444', 5) into EMPLOYEE values('222556666', 'WONG', 'T', 'FRANK', 'M', '25-Nov-78', '638, VOSS, CLEVELAND, OH', 55000, '987987987', 5) into EMPLOYEE values('453453453', 'ENGLISH', 'A', 'JOYCE', 'F', '25-Oct-78', '5631, RICE, ORLANDO, FL', 50000, '222556666', 5) into EMPLOYEE values('666884444', 'GOMEZ', 'B', 'JANE', 'F', '25-Sep-78', '975, FIREOAK, HUMBLE, TX', 45000, '222556666', 5) into EMPLOYEE values('777211234', 'RUTHERFORD', 'E', 'JAMES', 'M', '25-Aug-78', '3321, CASTLE, SPRING, TX', 64000, '123456789', 1) into EMPLOYEE values('987654321', 'SMITH', 'S', 'JENNIFER', 'F', '25-Jul-78', '2912, BERRY, CINCINNATI, OH', 60000, '777211234', 4) into EMPLOYEE values('987987987', 'JABBAR', 'V', 'AHMAD', 'M', '25-Jun-78', '980, CALHOUN, CINCINNATI, OH', 35000, '123456789', 4) into EMPLOYEE values('123123123', 'KEYS', 'J', 'ALICIA', 'F', '25-May-78', '3321, CASTLE, CINCINNATI, OH', 64000, '123456789', 4) select * from dual; INSERT ALL into DEPARTMENT values('HEADQUARTERS', 1, '19-Jun-91', '777211234') into DEPARTMENT values('ADMINISTRATION', 4, '01-Jan-89', '987654321') into DEPARTMENT values('PRODUCTION', 5, '22-May-93', '222556666') select * from dual; INSERT ALL into LOCATION values('WACO', 200, 'TX') into LOCATION values('CINCINNATI', 200, 'OH') into LOCATION values('ORLANDO', 300, 'FL') into LOCATION values('CLEVELAND', 400, 'OH') select * from dual; INSERT ALL into Department_Location values(1, 'WACO') into Department_Location values(4, 'CINCINNATI') into Department_Location values(5, 'CLEVELAND') into Department_Location values(5, 'WACO') into Department_Location values(5, 'ORLANDO') select * from dual; INSERT ALL into PROJECT values('PRODUCT A', 1, 5) into PROJECT values('PRODUCT B', 2, 5) into PROJECT values('PRODUCT C', 3, 5) into PROJECT values('COMPUTERIZATION', 10, 4) into PROJECT values('REORGANIZATION', 20, 1) into PROJECT values('BENEFITS PLAN', 30, 4) select * from dual; INSERT ALL into Dependent values('123456789', 'ALICE', 'F', '07-Jun-92', 'DAUGHTER') into Dependent values('123456789', 'ELIZABETH', 'F', '05-May-67', 'SPOUSE') into Dependent values('123456789', 'MICHAEL', 'M', '10-Jan-89', 'SON') into Dependent values('222556666', 'SARAH', 'F', '05-Apr-86', 'DAUGHTER') into Dependent values('222556666', 'JOY', 'M', '03-May-58', 'SPOUSE') into Dependent values('123123123', 'THEODORE', 'M', '12-Nov-96', 'SON') into Dependent values('987654321', 'BILL', 'M', '29-Feb-60', 'SPOUSE') select * from dual; INSERT ALL into WORKS_ON values('123456789', 1, 32.5) into WORKS_ON values('123456789', 2, 7.5) into WORKS_ON values('222556666', 2, 10) into WORKS_ON values('222556666', 3, 10) into WORKS_ON values('222556666', 10, 10) into WORKS_ON values('222556666', 30, 10) into WORKS_ON values('453453453', 1, 20) into WORKS_ON values('453453453', 2, 20) into WORKS_ON values('666884444', 3, 40) into WORKS_ON values('777211234', 20, 0) into WORKS_ON values('987654321', 20, 15) into WORKS_ON values('987654321', 30, 20) into WORKS_ON values('987987987', 10, 35) into WORKS_ON values('987987987', 30, 5) into WORKS_ON values('123123123', 10, 10) into WORKS_ON values('123123123', 30, 25.5) select * from dual; commit; alter table EMPLOYEE add constraint fk_emp foreign key (Supssno) references EMPLOYEE (Ssno); alter table EMPLOYEE add constraint fk_dept foreign key (Dno) references DEPARTMENT (Dept_no); alter table EMPLOYEE add constraint ck_ssno check (Ssno <> Supssno);
alter table DEPARTMENT
add constraint fk_emp2 foreign key
(Mgrssno) references EMPLOYEE
(Ssno);
alter table PROJECT
add constraint fk_dept3 foreign key
(Dnumb) references DEPARTMENT
(Dept_no) ON DELETE CASCADE;
alter table DEPENDENT
add constraint fk_emp3 foreign key
(Essno) references EMPLOYEE
(Ssno) ON DELETE CASCADE;
alter table WORKS_ON
add constraint fk_emp4 foreign key
(Essno) references EMPLOYEE
(Ssno);
Alter table WORKS_ON
add constraint fk_proj foreign key
(Pno) references PROJECT
(Proj_no);

insert into department
values (‘OPERATIONS’, 18, ’23-JAN-2021’, ‘234015678’);
alter table employee
enable constraint fk_dept;

Alter table department
ADD CONSTRAINT ck_depno check (Dept_no between 1 and 20);

alter table employee
drop constraint ck_sal;
alter table employee
add constraint ck_sal check (Salary between 30000 and 140000);

alter table department
drop constraint ck_depname;
alter table department
add constraint ck_depname check (Dept_name in (‘ADMINISTRATION’,’HEADQUARTERS’,’PRODUCTION’,’OPERATIONS’));
alter table location
drop constraint ck_deploc;
alter table location
add constraint ck_deploc check (Location in (‘WACO’, ‘ORLANDO’, ‘CLEVELAND’, ‘CINCINNATI’, ‘MIAMI’));
alter table works_on
drop constraint ck_workhrs;
alter table works_on
add constraint ck_workhrs check (Work_hrs <= 90); alter table project drop constraint ck_proj_no; alter table project add constraint ck_proj_no check (Proj_no between 1 and 50); alter table employee disable constraint fk_dept; insert into EMPLOYEE values ('234015678', 'PATEL’, 'G', 'SAMUEL', 'M', '25-APR-86', '1270, Creekside, MIAMI, FL', 120000, '453453453', 18); insert into department values ('OPERATIONS', 18, '23-JAN-2021’, '234015678'); insert all into dependent values ('234015678', 'Marisa', 'F', '29-MAY-2014', 'DAUGHTER') into dependent values ('234015678', 'Micah', 'F', '29-MAY-1981', 'SPOUSE') into dependent values ('234015678', 'Bensen’, 'M', '23-JAN-2018', 'SON') select * from dual; insert into location values ('MIAMI', 100, 'FL'); insert into department_location values (18, 'MIAMI'); insert into project values ('PRODUCTIVITY', 41, 18); insert into works_on values ('234015678', 41, 89.0); SELECT first_name, last_name, BDate, Gender, salary, dependent_nm FROM employee e, dependent dep WHERE E.Ssno = dep.essno(+); FIRST_NAME LAST_NAME BDATE G SALARY DEPENDENT_NM -------------------- -------------------- --------- - ---------- --------------- ALICIA KEYS 25-MAY-78 F 64000 THEODORE PAUL MCCARTNEY 25-DEC-78 M 30000 ALICE PAUL MCCARTNEY 25-DEC-78 M 30000 ELIZABETH PAUL MCCARTNEY 25-DEC-78 M 30000 MICHAEL FRANK WONG 25-NOV-78 M 55000 JOY FRANK WONG 25-NOV-78 M 55000 SARAH SAMUEL PATEL 25-APR-86 M 120000 Bensen SAMUEL PATEL 25-APR-86 M 120000 Marisa SAMUEL PATEL 25-APR-86 M 120000 Micah JOYCE ENGLISH 25-OCT-78 F 50000 JANE GOMEZ 25-SEP-78 F 45000 FIRST_NAME LAST_NAME BDATE G SALARY DEPENDENT_NM -------------------- -------------------- --------- - ---------- --------------- JAMES RUTHERFORD 25-AUG-78 M 64000 JENNIFER SMITH 25-JUL-78 F 60000 BILL AHMAD JABBAR 25-JUN-78 M 35000 2 A SELECT Employee.ssno FROM EMPLOYEE MINUS SELECT Essno Dependent FROM Dependent; 453453453 666884444 777211234 987987987 B SELECT Ssno FROM Employee Where ssno NOT IN (Select essno From Dependent); 987987987 777211234 453453453 666884444 3 SELECT ssno From Employee, Department Where Employee.ssno= Department.Mgrssno INTERSECT SELECT employee.ssno From Employee, Department Where Employee.ssno= Department.Mgrssno; 222556666 234015678 777211234 987654321 B. Select Distinct employee.ssno From Employee, Department Where Employee.ssno= Department.Mgrssno; 234015678 987654321 777211234 222556666 4. SELECT employee.last_name, employee.first_name,employee.Bdate,department.dept_name,Dependent.Dependent_nm FROM Employee, Department, Dependent Where Employee.ssno = Department.Mgrssno(+) And employee.ssno = Dependent.essno(+); LAST_NAME FIRST_NAME BDATE DEPT_NAME -------------------- -------------------- --------- --------------- DEPENDENT_NM --------------- KEYS ALICIA 25-MAY-78 THEODORE MCCARTNEY PAUL 25-DEC-78 ALICE MCCARTNEY PAUL 25-DEC-78 ELIZABETH LAST_NAME FIRST_NAME BDATE DEPT_NAME -------------------- -------------------- --------- --------------- DEPENDENT_NM --------------- MCCARTNEY PAUL 25-DEC-78 MICHAEL WONG FRANK 25-NOV-78 PRODUCTION JOY WONG FRANK 25-NOV-78 PRODUCTION SARAH LAST_NAME FIRST_NAME BDATE DEPT_NAME -------------------- -------------------- --------- --------------- DEPENDENT_NM --------------- PATEL SAMUEL 25-APR-86 OPERATIONS Bensen PATEL SAMUEL 25-APR-86 OPERATIONS Marisa PATEL SAMUEL 25-APR-86 OPERATIONS Micah LAST_NAME FIRST_NAME BDATE DEPT_NAME -------------------- -------------------- --------- --------------- DEPENDENT_NM --------------- SMITH JENNIFER 25-JUL-78 ADMINISTRATION BILL JABBAR AHMAD 25-JUN-78 RUTHERFORD JAMES 25-AUG-78 HEADQUARTERS LAST_NAME FIRST_NAME BDATE DEPT_NAME -------------------- -------------------- --------- --------------- DEPENDENT_NM --------------- ENGLISH JOYCE 25-OCT-78 GOMEZ JANE 25-SEP-78 5. SELECT Dependent.Dependent_nm FROM Dependent, Project, Works_on WHERE Works_on.pno = Project.Proj_no AND Dependent.Essno = Works_on.Essno AND Project.Proj_name = 'PRODUCT B'; DEPENDENT_NM --------------- ALICE ELIZABETH MICHAEL JOY SARAH