Home » SQL & PL/SQL » SQL & PL/SQL » plsql writen test questions (oracle 18)
| plsql writen test questions [message #689451] |
Sun, 07 January 2024 07:21  |
 |
bammidi
Messages: 3
Registered: January 2024
|
Junior Member |
|
|
Hi Team,
Attend plsql written test and most of the question are confusing, Could you please answer below questions.
May be it will be multipul answers.
Question : What are all the privileges required to create the materialized view as "CREATOR" when the creator owns the materialized view
1. CREATE MATERIALZIED VIEW, CREATE TABLE, CREATE VIEW
2. CREATE ANY MATERIALIZED VIEW, CREATE ANY TABLE, CREATE ANY VIEW
3. SELECT privilege on the master
4. SELECT ANY TABLE on the master
5. CREATE PROCEDURE
Question : Which of these is not a benefit of Triggers?
1. Enforce Complex business rules
2. Prevent invalid Transactions
3. Provide transparent event logging
4. Maintain Non replicated Tables Only
Question : Which of the following subtype of NUMBER can hold Floating-point with maximum precision of 63 Binary Digits?
1. INTEGER
2. REAL
3. DECIMAL
4. DOUBLE PRECISION
Question : Which of the following is true about temporary LOBS
1.Temporary LOBS provide an interface for creating and deleting the LOBs that act like local variables
2.Temporary LOB can be created using the DBMS_LOB.CREATETEMPORARY procedure
3.Temporary LOBs do not generate any redo or rollback information since they are faster than persistent LOBS
4.Temporary LOBs don't support EMPTY_B/CLOB() functions, since the temporary LOB is empty when created
Question: Which of these is a Restrictions on Cursor Expressions?
A. You cannot use a cursor expression with a SQL cursor
B. Cursor expressions can appear only in the outermost SELECT list of the query specification
C. You cannot perform BIND and EXECUTE operations on cursor expressions
Question: Which of the following is True about Database Roles
1. A database role is traditionally thought of as a named set of database privileges.
2. They are granted privileges, just as database users can be granted privileges.
3. They serve as intermediaries for mapping database privileges to database users
4. None of the above
Question : Which of the following is true about the WHERE CURRENT OF clause in CURSORS.
1. If a cursor is declared FOR UPDATE and includes UPDATE and DELETE statements then we can make use of WHERE CURRENT OF clause.
2. This allows updates or deletes to be applied on the single row, which is currently being addressed, without the need to explicitly reference ROWID.
3. This clause evaluates to the row that was just retrieved by the cursor
4. None of the above
Question: What is a Materialized View
1. Like a view
2. Consists both definition of the view and the data related to the query result
3. Consists definition of the view, data related to the query result and the MV logs.
4. Consists only the data related to the query result
5. A materialized view is a replica of a target master from a single point in time
Question: How to create READ ONLY materialized view?
1.After creating the materialized view, run the command "ALTER materialized_view_name READ ONLY;
2. Before creating the materialized view, set the session to READ ONLY using ALTER SESSION CURRENT READ ONLY:"
3. While creating, the materialized view do not use "FOR WRITE "clause
4.While creating, the materialized view do not use "FOR UPDATE "clause
5. Disabling the equivalent "FOR UPDATE" clause in replication management tool
Question: When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity? +4
1. Link the relations by a common field
2. Remove any functional dependencies from both relations
3. Assign both relations the same primary key field(s)
4. Create a primary key(s) for the new relation
5.None of the above
Question: Which of the following is true about functions when called from SQL statements +4
1. When called from SELECT statement or parallelized INSERT, UPDATE, or DELETE statement, function cannot modify database tables
2. When called from INSERT, UPDATE, or DELETE statement, function cannot query or modify any database tables modified by that statement
3. When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM
4. It cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit
5. None of the above
Question: What is Mutating in the trigger?Please select all the correct answers.
1. Same table is referred in more than one triggers
2. Same trigger is referred in more than one tables
3. Same table is referred more than once in the same trigger
4. A trigger is called by another trigger with same table name
5. A trigger is called by another trigger with different table name
Question: Which of the following is True about principals in Real Application Security
1. Principals are users and roles
2. principal is an application user or an application role
3. principal is a database user or a database role
4. None of the above
Question: Which of the following defines a Security Class in Orace Database Real Application Security
1. A security class is a scope for a set of application privileges
2. A security class includes application privileges that it inherits from her security classes, and it can include application privileges that it defines.
3. A security class is typically associated with an access control list (ACL),
4. None of the above
Question: When trying to show inheritance in a table with a parent-child relationship, three main steps are needed. Which of the following is NOT one of the three main steps
1. graphically represent database design
2. normalize the data
3. bottom-up hierarchical query
4. find root in hierarchical queries
Question: You are working on an interface which is prone to SQL Injections.
Which of these techniques can be used to avoid this threat?
A. Using Bind Arguments
B. Using Validation Checks
C. Using Explicit Format Models
Question: Which of the following is false about parameterized cursors
1. Parameters allow values to be passed to a cursor when it is opened and used within the query when it executes
2. These can be opened more than once in a block, by passing different sets of values each time
3. The datatype of the parameters are the same as those for scale variables, but are not given sizes/scales
4. "The parameters are treated like PL/SQL variables within the cursor's query expression."
5. None of the above
|
|
|
|
|
|
| Re: plsql writen test questions [message #689455 is a reply to message #689453] |
Mon, 08 January 2024 06:35  |
|
bammidi
Messages: 3
Registered: January 2024
|
Junior Member |
|
|
Question : What are all the privileges required to create the materialized view as "CREATOR" when the creator owns the materialized view
1. CREATE MATERIALZIED VIEW, CREATE TABLE, CREATE VIEW
2. CREATE ANY MATERIALIZED VIEW, CREATE ANY TABLE, CREATE ANY VIEW
3. SELECT privilege on the master
4. SELECT ANY TABLE on the master
5. CREATE PROCEDURE
Ans : 2
Question : Which of these is not a benefit of Triggers?
1. Enforce Complex business rules
2. Prevent invalid Transactions
3. Provide transparent event logging
4. Maintain Non replicated Tables Only
Ans : 2,3
Question : Which of the following subtype of NUMBER can hold Floating-point with maximum precision of 63 Binary Digits?
1. INTEGER
2. REAL
3. DECIMAL
4. DOUBLE PRECISION
Ans : 1
Question : Which of the following is true about temporary LOBS
1.Temporary LOBS provide an interface for creating and deleting the LOBs that act like local variables
2.Temporary LOB can be created using the DBMS_LOB.CREATETEMPORARY procedure
3.Temporary LOBs do not generate any redo or rollback information since they are faster than persistent LOBS
4.Temporary LOBs don't support EMPTY_B/CLOB() functions, since the temporary LOB is empty when created
Ans : 3,4
Question: Which of these is a Restrictions on Cursor Expressions?
A. You cannot use a cursor expression with a SQL cursor
B. Cursor expressions can appear only in the outermost SELECT list of the query specification
C. You cannot perform BIND and EXECUTE operations on cursor expressions
ANS: A,B,C
Question: Which of the following is True about Database Roles
1. A database role is traditionally thought of as a named set of database privileges.
2. They are granted privileges, just as database users can be granted privileges.
3. They serve as intermediaries for mapping database privileges to database users
4. None of the above
Ans : 1,2,3
Question : Which of the following is true about the WHERE CURRENT OF clause in CURSORS.
1. If a cursor is declared FOR UPDATE and includes UPDATE and DELETE statements then we can make use of WHERE CURRENT OF clause.
2. This allows updates or deletes to be applied on the single row, which is currently being addressed, without the need to explicitly reference ROWID.
3. This clause evaluates to the row that was just retrieved by the cursor
4. None of the above
Ans : 2
Question: What is a Materialized View
1. Like a view
2. Consists both definition of the view and the data related to the query result
3. Consists definition of the view, data related to the query result and the MV logs.
4. Consists only the data related to the query result
5. A materialized view is a replica of a target master from a single point in time
Ans : 2
Question: How to create READ ONLY materialized view?
1.After creating the materialized view, run the command "ALTER materialized_view_name READ ONLY;
2. Before creating the materialized view, set the session to READ ONLY using ALTER SESSION CURRENT READ ONLY:"
3. While creating, the materialized view do not use "FOR WRITE "clause
4.While creating, the materialized view do not use "FOR UPDATE "clause
5. Disabling the equivalent "FOR UPDATE" clause in replication management tool
Ans : 3,4
Question: When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity? +4
1. Link the relations by a common field
2. Remove any functional dependencies from both relations
3. Assign both relations the same primary key field(s)
4. Create a primary key(s) for the new relation
5.None of the above
Ans : 1,3
Question: Which of the following is true about functions when called from SQL statements
1. When called from SELECT statement or parallelized INSERT, UPDATE, or DELETE statement, function cannot modify database tables
2. When called from INSERT, UPDATE, or DELETE statement, function cannot query or modify any database tables modified by that statement
3. When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM
4. It cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit
5. None of the above
Ans : 3
Question: What is Mutating in the trigger?Please select all the correct answers.
1. Same table is referred in more than one triggers
2. Same trigger is referred in more than one tables
3. Same table is referred more than once in the same trigger
4. A trigger is called by another trigger with same table name
5. A trigger is called by another trigger with different table name
Ans : 3 ,4
Question: Which of the following is True about principals in Real Application Security
1. Principals are users and roles
2. principal is an application user or an application role
3. principal is a database user or a database role
4. None of the above
Ans : 1
Question: Which of the following defines a Security Class in Orace Database Real Application Security
1. A security class is a scope for a set of application privileges
2. A security class includes application privileges that it inherits from her security classes, and it can include application privileges that it defines.
3. A security class is typically associated with an access control list (ACL),
4. None of the above
Ans : 2
Question: When trying to show inheritance in a table with a parent-child relationship, three main steps are needed. Which of the following is NOT one of the three main steps
1. graphically represent database design
2. normalize the data
3. bottom-up hierarchical query
4. find root in hierarchical queries
Ans : 4
Question: You are working on an interface which is prone to SQL Injections.
Which of these techniques can be used to avoid this threat?
A. Using Bind Arguments
B. Using Validation Checks
C. Using Explicit Format Models
Ans : A
Question: Which of the following is false about parameterized cursors
1. Parameters allow values to be passed to a cursor when it is opened and used within the query when it executes
2. These can be opened more than once in a block, by passing different sets of values each time
3. The datatype of the parameters are the same as those for scale variables, but are not given sizes/scales
4. "The parameters are treated like PL/SQL variables within the cursor's query expression."
5. None of the above
Ans : 4
Please check the answers
[Updated on: Mon, 08 January 2024 06:36] Report message to a moderator |
|
|
|
| Re: plsql writen test questions [message #689456 is a reply to message #689455] |
Mon, 08 January 2024 07:10 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'll try to offer some guidance. With questions like these, you need to work out your answer before looking at the suggested answers. Consider the first question. It is about creating a materialized view in your own schema. What is a materialized view? It is a table structure created from a SELECT against one or more tables. So you will need the ability to create it, plus the ability to run the SELECT. What privileges are those? You decide! Then look at the answers.
You have chosen answer 2, which has some CREATE ANY privileges. No SELECT privilege(s). Why did you choose it? Why did you reject the other answers?
To get the most out of the exercise, do a few tests. Create a user and give him the privileges you think are needed. Can he then create a materialized view, in his own schema using tables in another schema? You may well find that the question itself is faulty. For example, none of the answers include the UNLIMITED TABLESPACE privilege or any mention of QUOTA.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 10:04:24 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
