Question

1. Create a new table called EmpDB based on emp table but includes only fields: emp mgr, copy data of employees. 2. Create a new table called device that has the following Field name No Name ESSN Data type size Note Number 3 Varchar2 10 Number4 PK 3. Make sure that ID in device table is between 100 and 999. 4. Make the field ESSN in table device as foreign key to the empno in table EmpDB. 5. Create stored function calsal that calculate and return the average salary, and call the fun

Answer 1

Answer

1.

Create table based on another table by copying data from selected columns

Syntax

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

SQL code for the given question based on the above syntax is

CREATE TABLE EmpDB
  AS (SELECT empno, ename, mgr
      FROM emp);

2.

SQL code to create device table is

CREATE TABLE device
( No number(3) PRIMARY KEY,
  Name varchar2(10) NOT NULL,
  ESSN number(4)
);

3.

SQL code to make sure ID is in between 100 and 999 is

ALTER TABLE device
  ADD CHECK (No BETWEEN 100 AND 999);

4.

SQL code to make ESSN as foreign key to the empno in EmpDB is

ALTER TABLE device
  ADD FOREIGN KEY(ESSN) REFERENCES EmpDB(empno);