SQL Query Syntax Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

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

They are created and maintained by the database user

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

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

Views with different prefixes like CDB, DBA, ALL and USER reference different base tables

How is the data dictionary maintained?

It is maintained by the application user

It is maintained by the database administrator (correct)

It is automatically maintained by the database system

It is maintained by the SYSTEM user

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

They reference user-defined tables

They reference DBA-defined tables

They reference different base tables from the data dictionary

They reference the same base tables from the data dictionary (correct)

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

They can be queried directly Signup and view all the answers

Who owns the data dictionary?

It is owned by the SYSTEM user Signup and view all the answers

Which of the following is stored in the data dictionary?

Usernames of all users including database administrators Signup and view all the answers

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_%'; Signup and view all the answers

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. Signup and view all the answers

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

SELECT &col1, &col2FROM &&tableWHERE &condition; Signup and view all the answers

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

Data modification is restricted, but queries are allowed on the tablespaces. Signup and view all the answers

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. Signup and view all the answers

What information does the V$TABLESPACE view display?

All of the above Signup and view all the answers

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; Signup and view all the answers

What is the size of the UNDOTBS1 undo tablespace?

100 MB Signup and view all the answers

What is the UNDO_RETENTION setting for the UNDOTBS1 undo tablespace?

15 minutes Signup and view all the answers

What is the AUTOEXTEND setting for the UNDOTBS1 undo tablespace?

Off Signup and view all the answers

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'. Signup and view all the answers

Which statement about single-row functions is false?

They can be used only in the WHERE clause of a SELECT statement. Signup and view all the answers

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. Signup and view all the answers

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. Signup and view all the answers

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. Signup and view all the answers

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. Signup and view all the answers

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. Signup and view all the answers

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

It frees resources held by abnormally terminated processes Signup and view all the answers

In which situation does a new transaction always start?

When issuing the first Data Manipulation Language (DML) statement after a <code>COMMIT</code> or <code>ROLLBACK</code> statement in the same session Signup and view all the answers

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

A new transaction starts when issuing a <code>SELECT FOR UPDATE</code> statement after a <code>CREATE TABLE AS SELECT</code> statement in the same session Signup and view all the answers

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

When issuing a <code>SELECT</code> statement after a <code>CREATE TABLE</code> statement in the same session Signup and view all the answers

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.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

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.