• databases Online Quiz - 55
Back
0

databases Online Quiz - 55

Description: databases Online Quiz - 55
Number of Questions: 20
Created by:
Tags: databases
Start the Quiz
Attempted 0/20 Correct 0 Score 0
‹
›

In which scenario would an index be most useful?

technology databases

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.

Show answer
Correct Option: D

When should you create a role? (Choose two.)

technology databases

  1. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement

  2. to grant a group of related privileges to a user

  3. when the number of people using the database is very high

  4. to simplify the process of granting and revoking privileges

  5. to simplify profile maintenance for a user who is constantly traveling

Show answer
Correct Option: B,D

Which two statements about sequences are true? (Choose two.)

technology databases

  1. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

  2. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

  3. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

  4. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

  5. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

  6. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

Show answer
Correct Option: A,B

Which operator can be used with a multiple-row subquery?

technology databases

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. IS

  6. <>

Show answer
Correct Option: D

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES table. How can you accomplish this task?

technology databases

  1. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

  2. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

  3. ALTER VIEW emp_dept_vu AS SELECT employee_id,employee_name, department_name,manager_id FROM employees e,departments d WHERE e.department_id = d.department_id;

  4. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

  5. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id=d.department_id;

Show answer
Correct Option: E

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?

technology databases

  1. UPDATE new_employees SET name = (SELECT last_name|| first_ name FROM employees WHERE employee_id =180) WHERE employee_id =180;

  2. UPDATE new_employees SET name = (SELECT last_name ||first_name FROM employees ) WHERE employee_ id =180;

  3. UPDATE new_employees SET name = (SELECT last_name || first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);

  4. UPDATE new_employees SET name = (SELECT last name || first_name FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;

Show answer
Correct Option: A

Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?

technology databases

  1. "&"

  2. ACCEPT

  3. PROMPT

  4. "&&"

Show answer
Correct Option: D

Click the Exhibit button and examine the data in the EMPLOYEES table. Which three sub queries work? (Choose three.)

technology databases

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

  3. SELECT distinct department_id FROM employees WHERE salary > ANY(SELECT AVG(salary) FROM employees GROUP BY department_id);

  4. SELECT department_id FROM employees WHERE salary > ALL(SELECT AVG(salary) FROM employees GROUP BY department_id);

  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

Show answer
Correct Option: C,D,E

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin placed his orders?

technology databases

  1. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000','21-JUL-2000');

  2. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));

  3. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cust_name = 'Martin');

  4. SELECT ord_ id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin');

Show answer
Correct Option: B
Explanation:

To solve this question, the user needs to know SQL syntax and how to retrieve data from a database table. The user must select the appropriate columns from the orders table where the order date is the same as the order date for Martin's orders.

Option A: This option is incorrect because it joins the orders and customers tables but does not specify a relationship between them. Also, it selects orders on two specific dates (18-JUL-2000 and 21-JUL-2000) which may not be the dates Martin placed his orders.

Option B: This option is correct. It selects the order ID, customer ID, and order total from the orders table where the order date is the same as the order date for Martin's orders. It uses subqueries to find the order date and customer ID for Martin and then selects orders with the same order date.

Option C: This option joins the orders and customers tables but does not specify a relationship between them, so it is incorrect. Also, it selects orders on the same dates as Martin's orders, which may not be the correct dates.

Option D: This option selects orders where the customer ID is the same as Martin's customer ID, but it does not specify a relationship between the orders and customers tables. Therefore, it may not retrieve the correct orders.

The Answer is: B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));

Examine the structure of the EMPLOYEES and NEW EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which MERGE statement is valid?
technology databases

  1. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  2. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  3. MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHES THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  4. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES(e.employee_id, e.first_name ||'.'||e.last_name);

Show answer
Correct Option: A
Explanation:

To determine which MERGE statement is valid, we need to examine the syntax of each option and compare it to the requirements of the question.

The question provides two tables: EMPLOYEES and NEW EMPLOYEES. Both tables have an EMPLOYEE_ID column as the primary key. The goal of the MERGE statement is to update the NAME column in the NEW EMPLOYEES table with the concatenated FIRST_NAME and LAST_NAME values from the EMPLOYEES table. If an employee exists in the NEW EMPLOYEES table, the NAME should be updated. If the employee does not exist, a new row should be inserted.

Now let's examine each option:

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

This option is valid. It uses the correct syntax for a MERGE statement and joins the two tables on the EMPLOYEE_ID column. It updates the NAME column in the NEW EMPLOYEES table with the concatenated FIRST_NAME and LAST_NAME values from the EMPLOYEES table. If a match is not found, a new row is inserted into the NEW EMPLOYEES table.

B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

This option is invalid. It uses the correct syntax for a MERGE statement, but the WHEN EXISTS clause is not valid. It should be WHEN MATCHED instead. Additionally, the syntax for the UPDATE clause is incorrect.

C. MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHES THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

This option is invalid. It has a syntax error in the WHEN NOT MATCHES clause, which should be WHEN NOT MATCHED. Additionally, the UPDATE clause syntax is incorrect.

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES(e.employee_id, e.first_name ||'.'||e.last_name);

This option is invalid. It does not use the correct syntax for a MERGE statement. Additionally, the INSERT INTO clause syntax is incorrect.

Thus, the valid MERGE statement is:

The Answer is: A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

Which of the following is applicable for Exadata Hybrid Columnar Compression?
technology databases

  1. a. The database stores the same column for a group of rows together

  2. b. The data block does not store the rows in the row-major format

  3. c. The database stores the identical rows together with a reference for each row

  4. d. Database operations works transparently against compressed objects without the need of changing application code

Show answer
Correct Option: A,B,D

The Exadata Hybrid Columnar Compression is of following types
technology databases

  1. a. Warehouse Compression & Online Archival Compression

  2. b. Basic Compression, Warehouse Compression & Online Archival Compression

  3. c. Basic Compression, OLTP Compression, Warehouse Compression & Online Archival Compression

  4. d. OLTP Compression, Warehouse Compression & Online Archival Compression

Show answer
Correct Option: A

The database stores a set of rows in an internal structure called a compression unit in case of Exadata Hybrid Columnar Compression.
technology databases

  1. a. Compression unit can span across multiple data blocks

  2. b. The value of particular column can span across multiple blocks

  3. c. If a column in a compression unit is updated, the database will take a lock only on the updated rows

  4. d. If a column in a compression unit is updated, the database will take a lock on the entire compression unit

Show answer
Correct Option: A,B,D

When tables use Exadata Hybrid Columnar Compression, Oracle DML locks larger blocks of data (compression unit) which might reduce concurrency
technology databases

  1. a. For basic and online archival compression inserted and updated rows are uncompressed

  2. b. Higher compression levels of Exadata Hybrid Columnar Compression are achieved only with data that is direct-path inserted

  3. c. High CPU overhead in case Warehouse compression

  4. d. Conventional inserts and updates are supported in Exadata Hybrid Columnar Compression but the extent of compression is low

Show answer
Correct Option: A,B,C,D

Which of the following statement(s) is (are) true?
technology databases

  1. a. COMPRESS FOR QUERY HIGH should be used where load performance is critical

  2. b. COMPRESS FOR QUERY LOW should be used where load performance is critical

  3. c. COMPRESS FOR ARCHIVE HIGH should be used for rarely accessed application

  4. d. COMPRESS FOR ARCHIVE HIGH should be used for heavily accessed application

Show answer
Correct Option: B,C

DBMS_COMPRESSION package helps to determine the expected compression level. COMPRESSION and COMPRESS_FOR column of user_tables provides whether compression is enabled or disabled and compression method respectively
technology databases

  1. a. CELL_OFFLOAD_PLAN_DISPLAY

  2. OPT_PARAM

  3. c. CELL_OFFLOAD_PROCESSING

  4. d. CELL_PARTITION_LARGE_EXTENTS

Show answer
Correct Option: C

CELL_OFFLOAD_PROCESSING parameter can be changed at system or session level
technology databases

  1. True

  2. False

Show answer
Correct Option: A

CELL_OFFLOAD_PROCESSING parameter effect can be also achieved by OPT_PARAM hint
technology databases

  1. True

  2. False

Show answer
Correct Option: A
  1. CELL_OFFLOAD_PROCESSING parameter can be used to compare the performance of Exadata Cell with conventional storage
technology databases

  1. True

  2. False

Show answer
Correct Option: B
  1. CELL_OFFLOAD_PLAN_DISPLAY parameter determines whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Exadata Cell as STORAGE predicates for a given SQL command
technology databases

  1. True

  2. False

Show answer
Correct Option: A

AI Explanation

To answer this question, we need to understand the purpose of the CELL_OFFLOAD_PLAN_DISPLAY parameter and its effect on the SQL EXPLAIN PLAN command.

The CELL_OFFLOAD_PLAN_DISPLAY parameter determines whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Exadata Cell as STORAGE predicates for a given SQL command.

Option A) True - This option is correct. The CELL_OFFLOAD_PLAN_DISPLAY parameter does determine whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Exadata Cell as STORAGE predicates for a given SQL command. If the parameter is set to true, the predicates that can be offloaded to the Exadata storage cells are displayed in the EXPLAIN PLAN output.

Option B) False - This option is incorrect. The CELL_OFFLOAD_PLAN_DISPLAY parameter does determine whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Exadata Cell as STORAGE predicates for a given SQL command.

The correct answer is A) True. This option is correct because the CELL_OFFLOAD_PLAN_DISPLAY parameter does have an effect on whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Exadata Cell as STORAGE predicates for a given SQL command.

- Hide questions
ADVERTISEMENT

Find more quizzes from top tags

general knowledge
3615 Quizzes
119858 Questions
 technology
307 Quizzes
36705 Questions
 sports
963 Quizzes
19148 Questions
 history
1187 Quizzes
13475 Questions
 physics
598 Quizzes
13441 Questions
 biology
1160 Quizzes
12174 Questions
 science & technology
551 Quizzes
11015 Questions
 chemistry
490 Quizzes
9892 Questions
 maths
518 Quizzes
9324 Questions
 softskills
0 Quizzes
7131 Questions