Database work

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