0

databases Online Quiz - 178

Description: databases Online Quiz - 178
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

SELECT department, COUNT(1) FROM employees WHERE salary > 25000 GROUP BY department; What about this query :-) ?

  1. True

  2. False


Correct Option: A

where would you find automatic_ipc parameter ?

  1. listener.ora

  2. sqlnet.ora server file

  3. sqlnet.ora client file

  4. tnsnames.ora server file


Correct Option: B

Which of the following statements creates an Oracle account, but lets the operating system authenticate logons?

  1. create user ops$admin identified by os;

  2. create user ops$admin identified externally;

  3. create user ops$admin nopassword;

  4. create user ops$admin authenticated by os;


Correct Option: B
Explanation:

To solve this question, the user needs to have knowledge about Oracle database user authentication methods.

Option A: This option creates an Oracle user account with the name 'ops$admin' and the password 'os'. However, it uses a password file for authentication, not the operating system. Therefore, this option is incorrect.

Option B: This option creates an Oracle user account with the name 'ops$admin' and identifies it as an external user, which means that authentication is done by the operating system. Therefore, this option is correct.

Option C: This option creates an Oracle user account with the name 'ops$admin' and no password. This is not a secure method of authentication and is not related to operating system authentication. Therefore, this option is incorrect.

Option D: This option creates an Oracle user account with the name 'ops$admin' and specifies that it should be authenticated by the operating system. However, it does not create an operating system account for the user. Therefore, this option is incorrect.

The Answer is: B

Which of the following types of statements can use a temporary tablespace?

  1. An index creation

  2. SQL statements with a GROUP BY clause

  3. A hash join operation

  4. All of the above


Correct Option: D

Which of the following statements gives user desmond the ability to alter table gl.accounts?

  1. grant alter on gl.accounts to desmond;

  2. grant alter to desmond on gl.accounts;

  3. grant alter table to desmond;

  4. allow desmond to alter table gl.accounts;


Correct Option: A

Which of the following statements gives user desmond the ability to alter table gl.accounts as well as give this ability to other accounts?

  1. grant alter any table with grant option to desmond;

  2. grant alter on gl.accounts to desmond with admin option;

  3. grant alter any table to desmond with grant option;

  4. grant alter any table to desmond with admin option;


Correct Option: D

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) grant alter any table with grant option to desmond; This option grants Desmond the ability to alter any table, but it does not give him the ability to grant this permission to other accounts. Therefore, this option is incorrect.

Option B) grant alter on gl.accounts to desmond with admin option; This option grants Desmond the ability to alter the table "gl.accounts" specifically, but it does not give him the ability to grant this permission to other accounts. Therefore, this option is incorrect.

Option C) grant alter any table to desmond with grant option; This option grants Desmond the ability to alter any table, but it does not give him the ability to grant this permission to other accounts. Therefore, this option is incorrect.

Option D) grant alter any table to desmond with admin option; This option grants Desmond the ability to alter any table and also gives him the ability to grant this permission to other accounts. Therefore, this option is correct.

The correct answer is Option D. This option gives Desmond the ability to alter the table "gl.accounts" as well as grant this ability to other accounts.

The following SQL statement will allow user regina to perform which operations on sequence oe.orders_seq? GRANT ALL ON oe.orders_seq TO regina;

  1. Select the next value from oe.orders_seq.

  2. Alter sequence oe.orders_seq to change the next value.

  3. Change the number of sequence numbers that will be cached in memory.

  4. Both A and C.

  5. All of the above.


Correct Option: A,C,D

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) Select the next value from oe.orders_seq - This option is correct. When the user regina is granted ALL privileges on the sequence oe.orders_seq, it includes the ability to select the next value from the sequence.

Option B) Alter sequence oe.orders_seq to change the next value - This option is incorrect. Granting ALL privileges on a sequence does not allow the user to alter the sequence to change the next value.

Option C) Change the number of sequence numbers that will be cached in memory - This option is correct. Granting ALL privileges on a sequence includes the ability to change the number of sequence numbers that will be cached in memory.

Option D) Both A and C - This option is correct. As explained above, the user regina will be able to select the next value from oe.orders_seq and change the number of sequence numbers that will be cached in memory.

Option E) All of the above - This option is incorrect. Granting ALL privileges on a sequence does not include the ability to alter the sequence to change the next value.

The correct answer is D. This option is correct because the user regina will be able to select the next value from oe.orders_seq, change the number of sequence numbers that will be cached in memory, but not alter the sequence to change the next value.

User system granted SELECT on sh.products to user A using WITH GRANT OPTION. A then granted SELECT on sh.products to user B. A has left the company, and his account is dropped. What happens to B’s privileges on sh.products?

  1. B loses his SELECT privilege on sh.products.

  2. B retains his SELECT privilege on sh.products.

  3. B loses his SELECT privilege if A was dropped with the CASCADE REVOKE option.

  4. B retains his SELECT privilege if A was dropped with the NOCASCADE REVOKE option


Correct Option: A

AI Explanation

To answer this question, we need to understand how privileges and the "WITH GRANT OPTION" work in Oracle.

When user A is granted SELECT privilege on sh.products with the "WITH GRANT OPTION," it means that A can also grant this privilege to other users. In this case, A grants SELECT privilege on sh.products to user B.

However, when user A's account is dropped, all the privileges granted by A are also revoked. This means that user B will lose the SELECT privilege on sh.products because it was originally granted by user A. Therefore, the correct answer is A: B loses his SELECT privilege on sh.products.

User system granted SELECT ANY TABLE to user A using WITH ADMIN OPTION. A then granted SELECT ANY TABLE to user B. Ian A left the company, and his account is dropped. What happens to B's privileges?

  1. B loses his privileges.

  2. B retains his privileges.

  3. B loses his privileges if A was dropped with the CASCADE REVOKE option.

  4. B retains his privileges if A was dropped with the NOCASCADE REVOKE option.


Correct Option: B

AI Explanation

To answer this question, we need to understand how privilege inheritance works in Oracle databases.

When user A is granted the SELECT ANY TABLE privilege with the WITH ADMIN OPTION, it means that A can not only select from any table but also grant the same privilege to other users.

When A grants the SELECT ANY TABLE privilege to user B, B inherits the privilege from A. This means that B retains the privilege independently of A's account status.

Therefore, even if A's account is dropped or revoked, user B will still retain the SELECT ANY TABLE privilege.

The correct answer is B. B retains his privileges.

Which of the following system privileges can allow the grantee to masquerade as another user and therefore should be granted judiciously?

  1. CREATE ANY JOB

  2. ALTER USER

  3. CREATE ANY PROCEDURE

  4. All of the above


Correct Option: D

Which of the following statements enables the role user_admin in the current session?

  1. alter session enable role user_admin;

  2. alter session set role user_admin;

  3. alter role user_admin enable;

  4. set role user_admin;


Correct Option: D

AI Explanation

To enable the role user_admin in the current session, you would use the SET ROLE statement.

Let's go through each option to understand why it is correct or incorrect:

Option A) alter session enable role user_admin; - This option is incorrect because the ENABLE ROLE statement is used to enable a role for a specific user, not for the current session.

Option B) alter session set role user_admin; - This option is incorrect because the SET ROLE statement is missing. The correct syntax for enabling a role using the SET ROLE statement would be ALTER SESSION SET ROLE user_admin;.

Option C) alter role user_admin enable; - This option is incorrect because the ALTER ROLE statement is used to modify the properties of a role, not to enable it for the current session.

Option D) set role user_admin; - This option is correct because the SET ROLE statement is used to enable a role for the current session. Therefore, the correct answer is option D.

The correct answer is option D.

Which of the following SQL statements allows user augustin to use the privileges associated with the password-protected role info_czar, which has been granted to him?

  1. set role all;

  2. alter user augustin default role all;

  3. alter session enable role info_czar;

  4. alter session enable info_czar identified by brozo


Correct Option: B

Which things should be considered while deciding the size of undo tablespace in your database ( choose three)

  1. The size of an undo block

  2. The size of the redo log files.

  3. The size of the database buffer cache.

  4. The value of the UNDO_RETENTION parameter.

  5. Undo blocks generated per second.


Correct Option: A,D,E

You suspect unauthorized data manipulation language (DML) operations on a particular table. You want to track users who performing the transactions and the values used in the transactions. Also you plan to transfer these values to another table for analysis. How would you achieve this?

  1. By auditing the all DML operations on table.

  2. By using external table.

  3. By using triggers.

  4. By using anonymous PL/SQL blocks


Correct Option: C

Which of the following is invalid db2 component :

  1. System Services

  2. Locking Services

  3. SQL Services

  4. Distributed DATA Facilities


Correct Option: C

AI Explanation

To answer this question, we need to understand the components of IBM DB2, a relational database management system. Let's go through each option to understand why it is correct or incorrect:

Option A) System Services - This option is a valid component of DB2. System Services provide the basic functions and utilities required for the operation of the DB2 system.

Option B) Locking Services - This option is a valid component of DB2. Locking Services manage the concurrent access to data by multiple users, ensuring data integrity and preventing conflicts.

Option C) SQL Services - This option is invalid. There is no such component called "SQL Services" in DB2.

Option D) Distributed DATA Facilities - This option is a valid component of DB2. Distributed Data Facilities enable the integration and management of data across multiple DB2 systems.

The correct answer is Option C) SQL Services. This option is incorrect because there is no such component in DB2 called "SQL Services".

Package is a

  1. Compiled form of application plan

  2. Compiled form of Database Requests

  3. Collection of Sql statements

  4. Collection of Application plansCollection of Application plans


Correct Option: B

AI Explanation

To answer this question, we need to understand what a package is in the context of computer programming.

Option A) Compiled form of application plan - This option is incorrect. A package is not specifically a compiled form of an application plan. An application plan is a high-level overview of the steps and logic involved in executing an application.

Option B) Compiled form of Database Requests - This option is correct. A package in this context refers to a compiled form of database requests or SQL statements. It is a collection of SQL statements that are pre-compiled and stored in a database for efficient execution.

Option C) Collection of SQL statements - This option is partially correct. A package does contain a collection of SQL statements, but it is more specifically the compiled form of those statements.

Option D) Collection of Application plans - This option is incorrect. A package is not a collection of application plans. As mentioned earlier, an application plan is a high-level overview of the steps and logic involved in executing an application.

Therefore, the correct answer is B) Compiled form of Database Requests.

Responsibility of OPTIMIZER is :

  1. Compiles the SQL statements

  2. Complies the list of packages into application plan

  3. Stored the application plan and packages into Directory

  4. Chooses the best access path from catalog tables


Correct Option: D
Explanation:

To answer this question, the user needs to know about the role of an optimizer in database management systems.

The optimizer is a component of a database management system (DBMS) that is responsible for generating the most efficient execution plan for a given SQL query.

Now let's go through each option and explain why it is right or wrong:

A. Compiles the SQL statements: This option is partially correct. The optimizer does not compile SQL statements, but it does analyze the query and generates an execution plan to execute the SQL statement.

B. Complies the list of packages into application plan: This option is incorrect. The job of the precompiler is to compile the list of packages into an application plan, not the optimizer.

C. Stored the application plan and packages into Directory: This option is incorrect. The job of the precompiler is to store the application plan and packages into a directory, not the optimizer.

D. Chooses the best access path from catalog tables: This option is correct. The optimizer analyzes the query and chooses the best access path from a set of possible access paths based on the metadata stored in the catalog tables.

Therefore, the correct answer is:

The Answer is: D

If an object is dropped , all Application plans that were using the object are marked by DB2 as invalid ( True or False)

  1. True

  2. False


Correct Option: A
Explanation:

To solve this question, the user needs to understand the concept of object dropping in DB2 and how it affects application plans.

When an object is dropped in DB2, all application plans that use that object are invalidated by DB2. This is because the object no longer exists and any references to it in the application plans are no longer valid. Therefore, the correct answer is:

The Answer is: A. True

  1. True

  2. False


Correct Option: B
Explanation:

To solve this question, the user needs to know how DB2 handles new objects.

The option A is incorrect because it's false. DB2 does not automatically start using a new object immediately after creation. The object must be activated before it can be used.

The option B is correct because DB2 does not automatically start using a new object. The object must be activated using the ACTIVATE DATABASE command or the ACTIVATE OBJECT command before it can be used.

Therefore, the correct answer is:

The Answer is: B

Which utility is used to retrieve data from a Image copy

  1. LOAD

  2. UNLOAD

  3. REORG

  4. RUNSTATS


Correct Option: B
Explanation:

To answer this question, the user needs to have an understanding of database management and utilities used to manipulate data in a database.

The utility used to retrieve data from an Image copy is the UNLOAD utility. This utility is used to extract data from a table or set of tables and write it to an output data set. The output data set can then be used to restore the data to a database using the LOAD utility.

Option A, LOAD, is incorrect because it is used to load data into a database from an input data set. It is the opposite of the UNLOAD utility.

Option C, REORG, is incorrect because it is used to reorganize a table or set of tables in a database, not to retrieve data from an Image copy.

Option D, RUNSTATS, is incorrect because it is used to collect statistics about the data in a table or set of tables. It is not used to retrieve data from an Image copy.

Therefore, the correct answer is:

The Answer is: B. UNLOAD

- Hide questions