1.Create a new view name as LEFTVIEW get the column, EMPLOYEE_ID, LASTNAME conca
ID: 3908431 • Letter: 1
Question
1.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?
2.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?
3.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
1.
Create view LEFTVIEW as select
EMPLOYEES.EMPLOYEE_ID,
DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENTS.DEPARTMENT_NAME,
EMPLOYEES.MANAGER_ID, FROM EMPLOYEES LEFT JOIN DEPARTMENTS ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ALL THE RECORDS FROM EMPLOYEES TABLE WILL BE PRESENT IN RESULTANT JOINED TABLE.
2. The DROP command cannot be used. The DML commands can be used in view which is having JOIN condition when the view contains all NOT NULL columns of the the table it references. in this case the Department ID in Departments Table contains NULL value. so the DML commands cant be used.
3.CREATE VIEW FULLVIEW AS SELECT MIN(EMPLOYEES.LASTNAME) AS NAME,
DISTINCT(EMPLOYEES.MANAGER_ID) AS AVAILABLE MANAGER_ID,
COUNT(EMPLOYEES.MANAGER_ID) AS NO. OF MANAGER_ID,
SUM(EMPLOYEES.SALARY) AS JOB_ID
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS
ON EMPLYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.