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:

SELECT with Subqueries Questions

1.    Display the names and job titles of all employees with the same job as Jones.

SELECT ename, job FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'JONES');

2.    Display the names and department name of all employees working in the same city as Jones.

SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno
WHERE loc = (SELECT loc FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno
                     WHERE ename = 'JONES');
                “OR”
SELECT ename, dname FROM emp INNER JOIN dept USING (deptno)
WHERE loc = (SELECT loc FROM emp INNER JOIN dept USING (deptno)
                     WHERE ename = 'JONES');

3.    Display the name of the employee whose salary is the lowest.

SELECT ename FROM emp WHERE sal = (SELECT min(sal) FROM emp);
“OR”
SELECT ename FROM emp, (SELECT min(sal) as sal  FROM emp) minsal
WHERE emp.sal = minsal.sal

4.    Display the names of all employees except the lowest paid.

SELECT ename FROM emp WHERE sal> (SELECT min(sal) FROM emp);

5.    Display the names of all employees whose job title is the same as anyone in the sales dept.

SELECT ename FROM emp
WHERE job IN (SELECT DISTINCT job FROM emp INNER JOIN USING(deptno)
WHERE dname = 'SALES');

6.    Display the names of all employees who work in a department that employs an analyst.

SELECT ename FROM emp WHERE deptno IN (SELECT DISTINCT emp.deptno
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = 'ANALYST');

7.    Display the names of all employees with their job title, their current salary and their salary following a 10% pay rise for clerks and a 7% pay rise for all other employees.

SELECT ename, job, sal, 1.1 * sal AS "newsal" FROM emp WHERE job = 'CLERK'
UNION
SELECT ename, job, sal, 1.07 * sal AS "newsal" FROM emp WHERE job <> 'CLERK';

8.    Display the names of all employees with their salary and commission earned. Employees with a null commission _eld should have 0 in the commission column.

SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal, 0 FROM emp WHERE comm IS NULL;
                “OR”
SELECT ename, sal, NVL(comm,0) as comm FROM emp


9.    Display the names of ALL employees with the total they have earned (ie. salary plus commission).

SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal FROM emp WHERE comm IS NULL;
“OR”
SELECT ename, NVL2(comm,sal + comm,sal) AS "earnings" FROM emp

10.    Repeat the display for the last question but this time display in descending order of earnings.

SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal FROM emp WHERE comm IS NULL ORDER BY earnings DESC;
“OR”
SELECT ename, NVL2(comm,sal + comm,sal) AS "earnings" FROM emp ORDER BY earnings DESC;

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: