CREATE TABLE EMPLOYEES( EMPLOYEE_ID NUMBER(6) PRIMARY KEY NOT NULL, FIRSTNAME VA
ID: 3915016 • Letter: C
Question
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);
Direction: Use the Employees, Departments and Locations table. Codes for these three table are uploaded in the previous topic.
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:
105 CRUZ,ANNA 80000 AD_VP
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.
Explanation / Answer
Following is the answer:
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
CREATE VIEW EMPVIEWSAL as SELECT EMPLOYEE_ID, LASTNAME, SALARY from EMPLOYEES where SALARY BETWEEN 5000 AND 10000
Who are the employees include in the EMPVIEWSAL view?
104
ERNST
6000.00
124
MOURGOS
5800.00
176
TAYLOR
8600.00
178
GRANT
7000.00
202
FAY
6000.00
206
GIETZ
8300.00
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?
It is not inserted because EmployeeId is primary key and 103 is already available.You can not insert it in any table because table are connected with foreign keys and 103 is already available in the table.
INSERT INTO `EMPVIEWSAL` (EMPLOYEE_ID, LASTNAME, SALARY)
SELECT 1003,'DE GUZMAN',4000
Employee Id is not 103 because it is unique so we can use another.
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.
CREATE or REPLACE VIEW EMPVIEWSAL as SELECT EMPLOYEE_ID, LASTNAME, SALARY from EMPLOYEES where SALARY>= 5000 WITH CHECK OPTION
4.From Employees table update the salary of all ST_CLERK job_id to 5000. Update you update select the EMPVIEWSAL.
UPDATE EMPLOYEES set SALARY=5000 WHERE JOB_ID="ST_CLERK"
Now the result is:
100
KING
24000.00
101
KOCHAR
17000.00
102
DE HAAN
17000.00
104
ERNST
6000.00
124
MOURGOS
5800.00
141
RAJS
5000.00
142
DAVIES
5000.00
143
MATOS
5000.00
144
VARGAS
5000.00
149
ZLOTKEY
10500.00
174
ABEL
11000.00
176
TAYLOR
8600.00
178
GRANT
7000.00
201
HARTSTEIN
13000.00
202
FAY
6000.00
205
HIGGINS
12000.00
206
GIETZ
8300.00
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.
CREATE or REPLACE VIEW EMPVIEWSAL as SELECT concat(FIRSTNAME ,"," , LASTNAME) AS "COMPLETE NAME", JOB_ID, SALARY, (SALARY*10/100) AS "BONUS" from EMPLOYEES where SALARY >= 5000 WITH CHECK OPTION
How many rows now are there in EMPVIEWSAL? Why and why not?
17 rows are displayed, because we only add jobid column and change the fullname so all remaining things are same as before.
6.Modify EMPVIEWSAL to not allow any DML operations.
ALTER VIEW EMPVIEWSAL as SELECT concat(FIRSTNAME ,"," , LASTNAME) AS "COMPLETE NAME", JOB_ID, SALARY, (SALARY*10/100) AS "BONUS" from EMPLOYEES where SALARY >= 5000 UNION all SELECT 0,0,0,0
7.Using the EMPVIEWSAL try to insert a new employee record is shown below:
105 CRUZ,ANNA 80000 AD_VP
Is adding Ms. Cruz successful? If no, what causes the database in not accepting this record?
INSERT INTO `EMPVIEWSAL` (EMPLOYEE_ID, FULL_NAME, SALARY, JOB_ID)
SELECT 105,'DE GUZMAN',4000, "AD_VP"
This record is not inserted successfully, because view is now read only.
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?
CREATE VIEW LISTVIEW as SELECT e.EMPLOYEE_ID,d.DEPARTMENT_ID,d.DEPARTMENT_NAME,e.MANAGER_ID, concat(e.FIRSTNAME,",",e.LASTNAME) as NAME from EMPLOYEES e LEFT OUTER JOIN DEPARTMENTS d on e.DEPARTMENT_ID = d.DEPARTMENT_ID
Following are the employee names:
STEVEN,KING
NENA,KOCHAR
LEX,DE HAAN
ALEXANDER,HUNOLD
BRUCE,ERNST
DIANA,LORENTZ
KEVIN,MOURGOS
TRINA,RAJS
CURTIS,DAVIES
RANDALL,MATOS
PETER,VARGAS
ELENI,ZLOTKEY
ELLEN,ABEL
JONATHAN,TAYLOR
KIMBERLEY,GRANT
JENNIFER,WHALEN
MICHAEL,HARTSTEIN
SHELLEY,HIGGINS
WILLIAM,GIETZ
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?
It is not possible to delete records from view.There is no data in the view to delete, the data remains in the underlying tables.
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
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
SELECT MIN(e.LASTNAME) as "NAME",(e.MANAGER_ID) as “AvailableManager_id” , COUNT(e.MANAGER_ID) as “NoofManager_id”, SUM(e.SALARY) from DEPARTMENTS d , EMPLOYEES e where e.DEPARTMENT_ID = d.DEPARTMENT_ID ORDER BY e.JOB_ID
Do all possible way in order to create the FULLVIEW view, swapping the column is allowed.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.