Views Questions

1.    Create a view called VIEWEMP which gives the same displays as EMP but is based on the EMP1 And JOBS tables.

CREATE VIEW viewemp
AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp1
INNER JOIN jobs
ON
emp1.jobno = jobs.jobno;

“OR”

CREATE VIEW viewemp
AS
SELECT emp1.*
FROM emp1
INNER JOIN jobs USING(jobno)

2.    Create a view called DEPTSUM with two columns called DEPARTMENT and SUMSAL containing the name of each department and the sum of the salaries for all employees in the department. Look at it using a SELECT command. Now alter one of the salaries in the EMP table using the UPDATE command is the DEPTSUM affected?

CREATE VIEW deptsum
AS
SELECT dname, SUM(sal)
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
GROUP BY dname;

SELECT * FROM deptsum;

UPDATE emp SET sal = 1000 WHERE empno = 7369;

SELECT * FROM deptsum;

DEPTSUM has been updated.

Note: We can only update any table using view if the view is simple view.

3.    Create a view called BOSS which has the name and number of each employee with the name and number of his or her manager (with blanks alongside any employee that has no manager). Give each column in the view a suitable name. Change one of the entries in the EMP table to give an employee a different manager and check this is reected in the BOSS view.

CREATE VIEW boss
AS
SELECT a.ename AS ename, a.empno AS empno,
b.ename AS bname, b.empno AS bossno
FROM emp a
LEFT OUTER JOIN emp b
ON
 a.mgr = b.empno;

SELECT * FROM boss;

UPDATE emp SET mgr=7566 WHERE empno = 7934;

SELECT * FROM boss;

CHECK (empno>= 7000 AND empno<= 8000));

4.    What happens if an attempt is made to insert Delete the DEPTSUM and BOSS views.

DROP VIEW deptsum;
DROP VIEW boss;

5.    Create a view called SALES1 which has all the columns of the EMP table except the salary and commission columns, and with only the rows corresponding to employees in department number 30.

CREATE VIEW sales1
AS
SELECT empno, ename, job, mgr, hiredate, deptno
FROM emp
WHERE deptno = 30;

6.    Create a view called SALES2 which has the same columns as SALES1 except the department name  is included instead of the department number. Do this by basing the new view on a join of the SALES1 view joined to the DEPT table.

CREATE VIEW sales2
AS
SELECT empno, ename, job, mgr, hiredate, dname
FROM sales1
INNER JOIN dept
ON
sales1.deptno = dept.deptno;

7.    Insert a new employee into the SALES1 view with the INSERT command. Look in the EMP table and SALES1 and SALES2 views to check it is there.
INSERT INTO sales1
VALUES (8101, 'BOB', 'ANALYST', 7839, '09/06/1982', 20);

Do it by your self.

8.    Insert a new employee into the SALES2 view with the INSERT command. Can this be done?
INSERT INTO sales2
VALUES (8101, 'BOB', 'ANALYST', 7839, '09/06/1982', 20);

Do it by your self.

9.    Alter the view SALES1 so that it has an additional column called INCOME with the sum of the
salary and commission for each employee. (Is it really possible to alter a view?)

DROP VIEW sales1;
DROP VIEW sales1 CASCADE;

CREATE VIEW sales1
AS
SELECT empno, ename, job, mgr, hiredate, deptno, sal + comm AS income
FROM emp WHERE comm IS NOT null AND deptno = 30
UNION
SELECT empno, ename, job, mgr, hiredate, deptno, sal AS income
FROM emp WHERE comm IS null AND deptno = 30;

“OR”

CREATE VIEW sales1 AS
SELECT empno, ename, job, mgr, hiredate, deptno, NVL2(comm,sal + comm,sal) AS income
FROM emp WHERE  deptno = 30

SELECT * FROM sales1;

It is not possible to alter a view, only to drop it and re-create it

10.    Now the SALES1 view has the extra INCOME column is it possible to insert another employee to the department using this view?

INSERT INTO sales1
VALUES (8101, 'BOB', 'ANALYST', 7839, '09/06/1982', 20);
         
Do it by your self.

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: