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! :)

Member Avatar for hfx642

A1. Yes! You can. You are making it way more complicated than it has to be.
(I am not giving you the answer because this sounds like a homework assignment.
We don't do that here.)

A2. Only someone with DBA priviledges can grant priviledges to other accounts.
Why are you using Scott/Tiger anyways? This account was created for demonstration purposes.
Your DBA should have given you either; a personal account, or the system development account, to use.

A1. Yes! You can. You are making it way more complicated than it has to be.
(I am not giving you the answer because this sounds like a homework assignment.
We don't do that here.)

A2. Only someone with DBA priviledges can grant priviledges to other accounts.
Why are you using Scott/Tiger anyways? This account was created for demonstration purposes.
Your DBA should have given you either; a personal account, or the system development account, to use.

Its true it is a HW question but I have given an answer in the first code snippet which is correct .... I just asked that out of curiosity .... ! :O
I would really appreciate if you could help me with the first question!

Member Avatar for hfx642

Okay... Let's look at what you've got.

Your Select, for your View, gives you a list of Departments (I assume "Code" is a Department Code or Department "Number").
Because of the "Group by", it gives you a Distinct (unique) list of Department Codes.

The Select, for your Insert, is to get the Department Names of those Departments in your first Select.
Your "Where" clause should reflect this.
ie. No need to join the Department Table to the View.
Use a Sub-Query in your "Where" clause, to get the Department Code, for your Select.
I can't really hint any more than that.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.