Insertion Deletion Predicate Reads - DBMS Questions and Answers

1.

A non-correlated subquery can be defined as________

   A.) A set of sequential queries, all of which must always return a single value
   B.) A set of sequential queries, all of which must return values from the same table
   C.) A SELECT statement that can be embedded in a clause of another SELECT statement only
   D.) A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

Answer: Option 'D'

A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

2.

OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence:

CREATE ROLE r1;
GRANT SELECT, INSERT ON oe. orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe. orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;

What would be the outcome after executing the statements?

   A.) SCOTT would be able to query the OE.ORDERS table
   B.) SCOTT would not be able to query the OE.ORDERS table
   C.) The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1
   D.) The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1

Answer: Option 'A'

The REVOKE statement is used to remove privileges from a specific user or role, or from all users, to perform actions on database objects.

3.

EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

   A.)
UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;
   B.)
DELETE FROM empdet
WHERE ename LIKE 'J%';
   C.)
CREATE VIEW empvu
AS
SELECT* FROM empdept;
   D.)
CREATE INDEX empdet_idx
ON empdet(empno);

Answer: Option 'C'

External tables are created using the SQL CREATE TABLE…ORGANIZATION EXTERNAL statement. When an external table is created, you specify type ,default directory, access parameters and location.

4.

SCOTT is a user in the database.

Evaluate the commands issued BY the DBA:
1 - CREATE ROLE mgr;
2 - GRANT CREATE TABLE, SELECT
ON oe. orders TO mgr;
3 - GRANT mgr, CREATE TABLE TO SCOTT;

Which statement is true regarding the execution of the above commands?

   A.) Statement 1 would not execute because the WITH GRANT option is missing
   B.) Statement 1 would not execute because the IDENTIFIED BY clause is missing
   C.) Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement
   D.) Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command

Answer: Option 'D'

The GRANT statement is used to give privileges to a specific user or role, or to all users, to perform actions on database objects.

5.

Which statements are correct regarding indexes?

   A.) When a table is dropped, the corresponding indexes are automatically dropped
   B.) For each DML operation performed, the corresponding indexes are automatically updated
   C.) A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index
   D.) All of the mentioned

Answer: Option 'D'

All of the mentioned

Insertion Deletion Predicate Reads - DBMS Questions and Answers Download Pdf