CREATE TABLE EMPLOYEES(

EMPLOYEE_ID NUMBER(6) PRIMARY KEY NOT NULL,

FIRSTNAME VARCHAR(10),

LASTNAME VARCHAR(10)NOT NULL,

JOB_ID VARCHAR(10),

SALARY NUMBER(8,2),

MANAGER_ID NUMBER(6),

DEPARTMENT_ID NUMBER(4));

INSERT INTO EMPLOYEES VALUES (100,'STEVEN','KING','AD_PRES',24000,NULL,90);

INSERT INTO EMPLOYEES VALUES (101,'NENA','KOCHAR','AD_VP',17000,100,90);

INSERT INTO EMPLOYEES VALUES (102,'LEX','DE HAAN','AD_VP',17000,100,90);

INSERT INTO EMPLOYEES VALUES (103,'ALEXANDER','HUNOLD','IT_PROG',NULL,101,60);

INSERT INTO EMPLOYEES VALUES (104,'BRUCE','ERNST','IT_PROG',6000,102,60);

INSERT INTO EMPLOYEES VALUES (107,'DIANA','LORENTZ','IT_PROG',4200,103,60);

INSERT INTO EMPLOYEES VALUES (124,'KEVIN','MOURGOS','ST_MAN',5800,100,50);

INSERT INTO EMPLOYEES VALUES (141,'TRINA','RAJS','ST_CLERK',3500,124,50);

INSERT INTO EMPLOYEES VALUES (142,'CURTIS','DAVIES','ST_CLERK',3100,124,50);

INSERT INTO EMPLOYEES VALUES (143,'RANDALL','MATOS','ST_CLERK',2600,124,50);

INSERT INTO EMPLOYEES VALUES (144,'PETER','VARGAS','ST_CLERK',2500,124,50);

INSERT INTO EMPLOYEES VALUES (149,'ELENI','ZLOTKEY','SA_MAN',10500,100,80);

INSERT INTO EMPLOYEES VALUES (174,'ELLEN','ABEL','SA_REP',11000,149,50);

INSERT INTO EMPLOYEES VALUES (176,'JONATHAN','TAYLOR','SA_REP',8600,149,80);

INSERT INTO EMPLOYEES VALUES (178,'KIMBERLEY','GRANT','SA_REP',7000,149,NULL);

INSERT INTO EMPLOYEES VALUES (200, 'JENNIFER','WHALEN','AD_ASST',4400,101,10);

INSERT INTO EMPLOYEES VALUES (201,'MICHAEL','HARTSTEIN','MK_MAN',13000,100,20);

INSERT INTO EMPLOYEES VALUES (202,'PAT','FAY','MK_REP',6000,201,20);

INSERT INTO EMPLOYEES VALUES (205,'SHELLEY','HIGGINS','AC_MGR',12000,101,110);

INSERT INTO EMPLOYEES VALUES (206,'WILLIAM','GIETZ','AC_ACCOUNT',8300,205,110);

1.Create a view and name as EMPVIEWSAL get the column EMPLOYEE_ID, LASTNAME and SALARY from employees table and put a condition where SALARY is between 5000 and 10000

Who are the employees include in the EMPVIEWSAL view?

2.Add a new data in EMPVIEWSAL as shown below:

103 DE GUZMAN 4000

Is inserting Mr. De Guzman successful? To what table were you able to insert the record of Mr. De Guzman? Explain the reason why?

3.Since EMPVIEWSAL allow the insertion of data, modify the view and add a with check option. With check option should only accept 5000 and above for EMPVIEWSAL view.

4.From Employees table update the salary of all ST_CLERK job_id to 5000. Update you update select the EMPVIEWSAL.

Is there any changes in the EMPVIEWSAL view? If yes what causes this changes.

5.Add additional column to EMPVIEWSAL by adding the column JOB_ID, plus for the column SALARY add 10% and rename the column as BONUS lastly for column LASTNAME add the FIRSTNAME (note you should concatenate the LASTNAME and FIRSTNAME) then put a literal character string , in between the 2 columns and rename the column you concatenated as COMPLETE NAME.

How many rows now are there in EMPVIEWSAL? Why and why not?

6.Modify EMPVIEWSAL to not allow any DML operations.

7.Using the EMPVIEWSAL try to insert a new employee record is shown below:

Is adding Ms. Cruz successful? If no, what causes the database in not accepting this record?

8.Create a new view name as LEFTVIEW get the column, EMPLOYEE_ID, LASTNAME concatenated to FIRSTNAME (with , in between) rename the column as NAME, DEPARTMENT_ID (either from employees or departments table), DEPARTMENT_NAME, MANAGER_ID (either from employees or departments table). Make sure to join the table using LEFT OUTER join.

Who is/are the employee/s that is considered as left outer join of employees and departments table?

9.Try to delete all records in LEFTVIEW? Is it really possible to perform any DML in a view with join condition? Why and why not?

10.Create a new view name as FULLVIEW. Get the following column: MIN(LASTNAME) name as NAME, DISTINCT (MANAGER_ID) rename as Available Manager_id, COUNT (MANAGER_ID) rename as No. of Manager_id, and the SUM(SALARY) per job_id. Apply Full Outer Join

Do all possible way in order to create the FULLVIEW view, swapping the column is allowed.