SQL Query Syntax Quiz

27 Questions

Which of the following statements is true regarding data dictionary views?

They are created by joins of dictionary base tables and DBA-defined tables

How is the data dictionary maintained?

It is maintained by the database administrator

Which of the following statements is true about views with different prefixes like CDB, DBA, ALL and USER?

They reference the same base tables from the data dictionary

Which of the following statements is true regarding the base tables in the data dictionary?

They can be queried directly

Who owns the data dictionary?

It is owned by the SYSTEM user

Which of the following is stored in the data dictionary?

Usernames of all users including database administrators

What is the correct SQL query to display details of customers residing in cities starting with the letter D followed by at least two characters?

SELECT * FROM customers WHERE city LIKE 'D_%';

In Oracle, what happens when an unused column is dropped from a database table?

The column metadata is removed, and the physical storage space is released immediately.

Which SQL query correctly retrieves data from a table in Oracle?

SELECT &col1, &col2FROM &&tableWHERE &condition;

When altering a tablespace to READ ONLY in Oracle, what occurs?

Data modification is restricted, but queries are allowed on the tablespaces.

Which action must be taken if using Oracle Managed Files (OMF) when renaming a file?

The file is renamed but moved to a predefined destination set by DB_CREATE_FILE_DEST.

What information does the V$TABLESPACE view display?

All of the above

Which query can be used to display the unique promotion costs in each promotion category?

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

What is the size of the UNDOTBS1 undo tablespace?

100 MB

What is the UNDO_RETENTION setting for the UNDOTBS1 undo tablespace?

15 minutes

What is the AUTOEXTEND setting for the UNDOTBS1 undo tablespace?

Off

What will happen when the next update is attempted by any transaction after the UNDOTBS1 undo tablespace fills with uncommitted undo 10 minutes after the database opens?

The update will fail and return the error message 'ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'.

Which statement about single-row functions is false?

They can be used only in the WHERE clause of a SELECT statement.

In the absence of an ORDER BY clause, how is the output sorted in a compound query with the UNION operator?

It is sorted based on the first column in the first SELECT statement.

Which of the following account management capabilities cannot be configured using Oracle profiles?

The maximum amount of CPU time allowed for a user's sessions before their account is locked.

Which statement is true regarding the ORDER BY clause in a compound query with the UNION operator?

Each SELECT statement in the compound query must have its own ORDER BY clause.

Which of the following account management capabilities can be configured using Oracle profiles?

The maximum number of sessions permitted for a user before the account is locked.

Which of the following statements about single-row functions is true?

The data type returned can be different from the data type of the argument.

Which of the following tasks is performed by the Process Monitor (PMON) in Oracle?

It frees resources held by abnormally terminated processes

In which situation does a new transaction always start?

When issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement in the same session

Which of the following statements is true about transactions in Oracle?

A new transaction starts when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement in the same session

Which of the following statements correctly describes a situation where a new transaction does NOT start?

When issuing a SELECT statement after a CREATE TABLE statement in the same session

Study Notes

Data Dictionary Views

Data dictionary views are maintained by Oracle automatically.
Views with different prefixes like CDB, DBA, ALL, and USER provide different levels of access to the data dictionary.
The data dictionary views are based on the base tables in the data dictionary.

Data Dictionary Maintenance

The data dictionary is maintained by Oracle.
SYS owns the data dictionary.

Data Dictionary Content

The data dictionary stores information about the database, including schema objects, users, and storage.

SQL Queries

The correct SQL query to display details of customers residing in cities starting with the letter D followed by at least two characters is SELECT * FROM customers WHERE city LIKE 'D_%';.
The query SELECT * FROM table_name; correctly retrieves data from a table in Oracle.

Table and Tablespace Management

When an unused column is dropped from a database table, the space is not released back to the OS.
Altering a tablespace to READ ONLY in Oracle makes it unavailable for write operations.
When using Oracle Managed Files (OMF) and renaming a file, the corresponding database file must be updated.

V$TABLESPACE View

The V$TABLESPACE view displays information about the tablespace.

Promotion Costs

The query SELECT DISTINCT promotion_cost FROM promotions; can be used to display the unique promotion costs in each promotion category.

Undo Tablespace

The size of the UNDOTBS1 undo tablespace is 100M.
The UNDO_RETENTION setting for the UNDOTBS1 undo tablespace is 10 minutes.
The AUTOEXTEND setting for the UNDOTBS1 undo tablespace is ON.
When the UNDOTBS1 undo tablespace fills with uncommitted undo 10 minutes after the database opens, the next update will be blocked by any transaction.

Single-Row Functions

Not all single-row functions can be used in the SELECT, WHERE, and HAVING clauses of a query.
Single-row functions can be used to perform calculations on a single row.

Compound Queries

In the absence of an ORDER BY clause, the output of a compound query with the UNION operator is sorted by the first column.
The ORDER BY clause can be used in a compound query with the UNION operator to specify the sort order.

Account Management

Oracle profiles can be used to configure account management capabilities, including password policies and resource limits.
Password expiration and account locking can be configured using Oracle profiles.

Transaction Management

The Process Monitor (PMON) in Oracle is responsible for rolling back aborted transactions.
A new transaction always starts when a COMMIT or ROLLBACK statement is executed.
A new transaction does not start when a DDL statement is executed.

Test your knowledge of SQL query syntax with this quiz. Determine the correct way to select columns from a table based on certain conditions. Choose the right query format to display specific customer details.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.