5. Add additional column to EMPVIEWSAL by adding the column JOB_ID, plus for the

ID: 3914686 • Letter: 5

Question

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

ALTER TABLE EMPVIEWSAL

ADD JOB_ID ,SALARY+0.1 AS BONUS,CONCAT(LASTNAME,FIRSTNAME) AS COMPLETENAME

In the above query

SALARY+0.1 COLUM NAME is going to replace by BONUS

CONCAT(LASTNAME,FIRSTNAME) is going to replace by COMPLETENAME

Number of rows depends on how many records you are going to insert .and you can find it using select * from empviewsal additional colums based on previous query is JOB_ID,BONUS,COMPLETENAME

Oracle and SQL Server by default locks by row - so new rows being inserted are locked - but not the whole table.

This will change if you insert more than 5000 rows in a single transaction. In that case, keeping that many individual locks just becomes too much and SQL Server will do a lock escalation and lock the entire table instead.

It depends on the size of the table and settings on the clustered index if one exists. If the CI has page locks and row locks allowed, then those are the defaults. You will see lock escalation if multiple pages are locked, potentially up to a table lock.

If you want to explicitly lock the table, you can use the TABLOCK hint on the insert (INSERT INTO EMPVIEWSAL WITH (TABLOCK)...

Using the EMPVIEWSAL try to insert a new employee record is shown below: 105 CRUZ,ANNA 80000 AD_VP

In this case if data is lock then you cannot insert the record ,otherwise it will check the value type which is matching to the corresponding column type of table/view or not if it is then it is going to insert other it will not

In this case it will not because value type is not matching

SELECT

E.EMPLOYEE_ID, CONCAT(E.LASTNAME,E.FIRSTNAME) as ‘NAME’ , E.DEPARTMENT_ID, D.DEPARTMENT_NAME,D.MANAGER_ID From

EMPLOYEE E

LEFT JOIN DEPARTMENT D ON E. DEPARTMENT_ID = D. DEPARTMENT_ID;