CREATE TABLE Query3Ans(
dname char(20)
);
CREATE VIEW deptNonPhDs(dept, numNonPhDs) AS
SELECT dcode AS dept, count(*) AS numNonPhDs
FROM instructor
WHERE idegree != 'PhD'
GROUP BY dcode;
INSERT INTO Query3Ans(
SELECT d.dname
FROM deptNonPhDs dp1, department d
WHERE dp1.numNonPhDs >= (select max(dp2.numNonPhDs)
FROM deptNonPhDs dp2) AND
dp1.dept=d.dcode
);
select * from Query3Ans;
Q1. I wish to know whether the select part in the View deptNonPhDs can be merged into the insert query???!!!i.e I want to avoid creating a view!!
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
Q2. How can I get the privileges to create a VIEW?
I have logged in as Scott/Tiger.
I tried the following steps:
conn scott;
CONNECTED
select * from session_privs;
PRIVILEGE
------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
grant create any view to scott;
ORA-101031: Insufficient privilieges
How do I solve this problem?
Thanx in advance! :)