Database Indexing and Tables

Play an AI-generated podcast conversation about this lesson

What is the primary difference between the TRUNCATE and DELETE commands in SQL?

TRUNCATE is faster and does not log transactions, while DELETE is slower and logs transactions. (correct)
TRUNCATE is used for temporary tables, while DELETE is used for permanent tables.
TRUNCATE is used for DML operations, while DELETE is used for DDL operations.
TRUNCATE is used for inserting data, while DELETE is used for updating data.

What is the purpose of the ROWID pseudo column in SQL?

To retrieve the highest salary of an employee.
To retrieve the row number of a table.
To retrieve the average salary of a department.
To retrieve the unique identifier of a row. (correct)

What is the effect of the DENSE_RANK() function in SQL?

It assigns a null rank to each row.
It assigns a random rank to each row.
It assigns a unique rank to each row without gaps. (correct)
It assigns a rank to each row with gaps.

What is the purpose of the CASE statement in the UPDATE command?

To swap values between two columns. (A) Signup and view all the answers

What is the purpose of the correlated subquery in the SELECT command?

To retrieve the employees who earn more than the average salary of their department. (C) Signup and view all the answers

What is the purpose of the CURRVAL pseudo column in SQL?

To retrieve the current value of a sequence. (D) Signup and view all the answers

What is the primary purpose of a global temporary table?

To hold session-specific records (C) Signup and view all the answers

What is the effect of using the ON COMMIT DELETE ROWS clause in a global temporary table?

Rows are deleted at the end of the transaction (B) Signup and view all the answers

What is the minimum privilege required to execute the MERGE statement?

INSERT and UPDATE privileges on the target table (C) Signup and view all the answers

What is the purpose of the MERGE statement?

To merge data from a source result set to a target table (C) Signup and view all the answers

What type of index is suitable for data with a high degree of repetition?

Bitmap index (A) Signup and view all the answers

What is the syntax to create a global temporary table?

CREATE GLOBAL TEMPORARY TABLE my_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT DELETE ROWS; (D) Signup and view all the answers

What is the function of the REGEXP_REPLACE function in the given SQL query?

To replace a pattern in a string with another string (D) Signup and view all the answers

What is the purpose of the dual table in Oracle?

To provide a dummy table for calculation and queries (D) Signup and view all the answers

What is the purpose of the REGEXP_LIKE function in the given SQL query?

To search for a pattern in a string (B) Signup and view all the answers

What is the main difference between an inner join and an outer join?

Inner join returns only matching rows, outer join returns all rows from both tables (D) Signup and view all the answers

What is the purpose of the TRANSLATE function in the given SQL query?

To remove unwanted characters from a string (A) Signup and view all the answers

What is an equi join, also known as an equality join?

A type of inner join that uses the equality operator (=) (A) Signup and view all the answers