Questions are to be implemented in the Oracle SQL-Plus environment Create an SQL

Question

Questions are to be implemented in the Oracle SQL-Plus environment

Create an SQL file that contains the answers to the following questions:

0. set linesize 120; set echo on and set serveroutput on.

1. Create a duplicate table for Employee, call it Employee2 without inserting any tuples. (Hint: use an insert into command with a query – see the SQL tutorial in the Data drive). To test: Show the table description, and the table content.

2. Write a PL/SQL procedure updateSupervisorAll for the COMPANY database that will take both old and new values of the supervisor ssn for the Employee table and change the old supervisor ssn to the new one (i.e., suppose that the supervisor with old superssn is no longer a supervisor and a new one, which must be from the existing employees, is assigned to all of the employees who have been supervised by the old supervisor.

To test: First, display employee last names and their old supervisor last name. Apply the stored procedure updateSupervisorAll to update supervisor. Then display employee last names and their new supervisor last name. Then rollback.

3. Write a PL/SQL procedure for the COMPANY database that will find the highest salaried employee (his/her last, first name, ssn#, dno) and his/her department name. Display the result by calling this procedure appropriately.

EMPLOYEE Fname Minit Lname Ssn BdateAddress Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONS numberDlocation PROJECT Pname Pnumbe Plocaion Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Dependent_name SexBdate Relationship

Explanation / Answer

SET ECHO On
SET SERVEROUTPUT OFF

To insert new data into the employee table, we use the INSERT statement:

Show the table description, and the table content.

DESCRIBE Employee

DESCRIBE Employee2

To view the content

SELECT * from Employee

For each employee, retrieve the employee’s first and last name, and the first and last name of his or her immediate supervisor.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM

EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN = S.SSN

CREATE TABLE department

(dnumber      NUMBER(1),

dname        VARCHAR2(15),

mgrssn       VARCHAR2(9),

mgrstartdate DATE

CONSTRAINT pk_department PRIMARY KEY (dnumber) );

CREATE TABLE employee

(fname           VARCHAR2(8),

minit           VARCHAR2(2),

lname           VARCHAR2(8),

ssn             VARCHAR2(9) NOT NULL,

bdate           DATE,

address         VARCHAR2(27),

sex             VARCHAR2(1),

salary          NUMBER(7) NOT NULL,

superssn        VARCHAR2(9),

dno             NUMBER(1) NOT NULL,

CONSTRAINT pk_emp PRIMARY KEY (ssn),

CONSTRAINT fk_dno FOREIGN KEY (dno)

REFERENCES department (dnumber) ON DELETE CASCADE);

SQL> CREATE TABLE WORKS_ON (

2   ESSN                CHAR(9) NOT NULL,

3   PNO                 INT NOT NULL,

4   HOURS               DECIMAL(3,1) );

3.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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