0

databases Online Quiz - 55

Description: databases Online Quiz - 55
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0
  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.


Correct Option: D

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

  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


Correct Option: B,D

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

  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.


Correct Option: A,B

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?

  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;


Correct Option: E
  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;


Correct Option: A
  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));


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?

  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');


Correct Option: B
  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);


Correct Option: A
  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


Correct Option: A,B,D

The Exadata Hybrid Columnar Compression is of following types

  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


Correct Option: A
  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


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

  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


Correct Option: A,B,C,D
  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


Correct Option: B,C

CELL_OFFLOAD_PROCESSING parameter effect can be also achieved by OPT_PARAM hint

  1. True

  2. False


Correct Option: A
- Hide questions