IM FINALS Flashcards by LUIS KEINE CERTEZA

Which set operator combines the results of two or more component queries into one result, returning all distinct rows selected by either query?
a) UNION ALL
b) INTERSECT
c) UNION
d) MINUS

UNION

How well did you know this?

Not at all

Perfectly

Which set operator returns all rows selected by either query, including all duplicates?
a) UNION
b) UNION ALL
c) INTERSECT
d) MINUS

UNION ALL

How well did you know this?

Not at all

Perfectly

If you want to find all distinct rows that are selected by both the first SELECT statement AND the second SELECT statement, which operator would you use?
a) UNION
b) UNION ALL
c) INTERSECT
d) MINUS

INTERSECT

How well did you know this?

Not at all

Perfectly

Which set operator returns all distinct rows selected by the first SELECT statement but NOT selected by the second SELECT statement?
a) UNION
b) UNION ALL
c) INTERSECT
d) MINUS

MINUS

How well did you know this?

Not at all

Perfectly

What is the default evaluation order if a SQL statement contains multiple set operators without parentheses?
a) From right to left (bottom to top)
b) Based on operator precedence (INTERSECT first)
c) From left to right (top to bottom)
d) The Oracle server prompts the user for the order

From left to right (top to bottom)

How well did you know this?

Not at all

Perfectly

When using set operators, what must be identical for all SELECT statements in the compound query?
a) The column names
b) The number of columns and the data types of the columns
c) The tables being queried
d) The WHERE clause conditions

The number of columns and the data types of the columns

How well did you know this?

Not at all

Perfectly

By default, how is the output of a query using the UNION operator sorted?
a) Ascending order of the last column of the SELECT clause
b) Descending order of the first column of the SELECT clause
c) Ascending order of the first column of the SELECT clause
d) The output is not sorted by default

Ascending order of the first column of the SELECT clause

How well did you know this?

Not at all

Perfectly

Which of the following is TRUE for the UNION ALL operator compared to the UNION operator?
a) UNION ALL eliminates duplicate rows, and UNION does not.
b) UNION ALL does not eliminate duplicate rows, and the output is not sorted by default.
c) UNION ALL requires column names to be identical, while UNION does not.
d) UNION ALL has higher precedence than UNION.

UNION ALL does not eliminate duplicate rows, and the output is not sorted by default.

How well did you know this?

Not at all

Perfectly

If the order of tables in an INTERSECT operation is reversed, how does it affect the result?
a) The result will be the inverse.
b) The result will not be altered.
c) It will result in an error.
d) The result will only contain rows from the new first table.

The result will not be altered.

How well did you know this?

Not at all

Perfectly

Where can the ORDER BY clause appear in a SQL statement using set operators?
a) After each SELECT statement
b) Only at the very end of the entire statement
c) Only after the first SELECT statement
d) It cannot be used with set operators

Only at the very end of the entire statement

How well did you know this?

Not at all

Perfectly

If column names differ in SELECT statements combined by a set operator, which column names appear in the result?
a) Column names from the last SELECT statement
b) Column names from the first SELECT statement
c) Concatenated column names from all SELECT statements
d) The user is prompted to specify alias names

Column names from the first SELECT statement

How well did you know this?

Not at all

Perfectly

To match expressions in select lists when using set operators, if data types differ, what can be used?
a) The IGNORE_DATATYPE_MISMATCH hint
b) The Oracle server automatically converts them
c) Dummy columns and data type conversion functions (e.g., TO_NUMBER, TO_DATE)
d) Set operators cannot be used if data types don’t match exactly

Dummy columns and data type conversion functions (e.g., TO_NUMBER, TO_DATE)

How well did you know this?

Not at all

Perfectly

Which of the following is NOT a Data Manipulation Language (DML) statement?
a) INSERT
b) UPDATE
c) DELETE
d) CREATE

CREATE

How well did you know this?

Not at all

Perfectly

A collection of DML statements that form a logical unit of work is called a:
a) Schema
b) Transaction
c) Subquery
d) View

Transaction

How well did you know this?

Not at all

Perfectly

When using the INSERT statement with the VALUES clause, how many rows are inserted at a time?
a) Only one row
b) Multiple rows, depending on the values provided
c) As many rows as exist in the target table
d) It depends on the table’s primary key

Only one row

How well did you know this?

Not at all

Perfectly

If you use the INSERT statement without a column list, what is required for the values?
a) Values must be listed in alphabetical order of column names.
b) Values must be provided only for NOT NULL columns.
c) Values must be listed according to the default order of the columns in the table, and a value must be provided for each column.
d) The Oracle server will prompt for values for each column.

Values must be listed according to the default order of the columns in the table, and a value must be provided for each column.

How well did you know this?

Not at all

Perfectly

How can you insert a null value into a column implicitly using the INSERT statement?
a) Specify the NULL keyword in the VALUES clause.
b) Omit the column from the column list in the INSERT clause.
c) Use an empty string (‘’) for numeric columns.
d) Null values cannot be inserted implicitly.

Omit the column from the column list in the INSERT clause.

How well did you know this?

Not at all

Perfectly

Which function can be used in an INSERT statement to record the current date and time?
a) CURRENT_DATE
b) NOW()
c) SYSDATE
d) TODAY()

SYSDATE

How well did you know this?

Not at all

Perfectly

To insert a date value in a format other than the default, or to include a specific time, which function should be used?
a) CONVERT_DATE
b) FORMAT_DATE
c) TO_DATE
d) SET_DATE_FORMAT

TO_DATE

How well did you know this?

Not at all

Perfectly

How can you add rows to a table where the values are derived from existing tables, without using the VALUES clause?
a) Using the INSERT INTO … CLONE FROM … statement
b) Using the INSERT INTO … SELECT … statement (a subquery)
c) Using the COPY_ROWS procedure
d) This requires a PL/SQL block

Using the INSERT INTO … SELECT … statement (a subquery)

How well did you know this?

Not at all

Perfectly

What happens if you omit the WHERE clause in an UPDATE statement?
a) The statement will fail with an error.
b) Only the first row of the table will be updated.
c) All rows in the table will be modified.
d) No rows will be updated.

All rows in the table will be modified.

How well did you know this?

Not at all

Perfectly

What happens if you omit the WHERE clause in a DELETE statement?
a) The statement will fail with an error.
b) Only the last row of the table will be deleted.
c) All rows in the table will be deleted.
d) No rows will be deleted.

All rows in the table will be deleted.

How well did you know this?

Not at all

Perfectly

Which statement removes all rows from a table, is a DDL statement, and cannot easily be undone?
a) DELETE FROM table_name;
b) REMOVE ALL ROWS FROM table_name;
c) TRUNCATE TABLE table_name;
d) DROP TABLE table_name;

TRUNCATE TABLE table_name;

How well did you know this?

Not at all

Perfectly

Which of the following is TRUE about the TRUNCATE statement?
a) It fires delete triggers on the table.
b) It generates extensive rollback information.
c) It can be easily rolled back using the ROLLBACK statement.
d) It cannot be used if the table is the parent of an enabled referential integrity constraint.

It cannot be used if the table is the parent of an enabled referential integrity constraint.

How well did you know this?

Not at all

Perfectly

When does a database transaction typically begin? a) When a COMMIT statement is issued. b) When the user logs into the database. c) When the first DML SQL statement is executed. d) When a DDL statement is executed.

When the first DML SQL statement is executed.

Which of the following events will end a transaction by automatically committing pending changes? a) Executing a SELECT statement b) Executing a DDL statement (e.g., CREATE TABLE) c) The system crashing d) Issuing a SAVEPOINT statement

Executing a DDL statement (e.g., CREATE TABLE)

Which statement makes all pending data changes permanent in the current transaction? a) SAVEPOINT b) ROLLBACK c) COMMIT d) END TRANSACTION

COMMIT

Which statement discards all pending data changes in the current transaction? a) SAVEPOINT b) ROLLBACK c) COMMIT d) DISCARD CHANGES

ROLLBACK

What is the purpose of the SAVEPOINT name statement? a) To commit all changes up to that point. b) To mark a point within the current transaction to which one can later roll back. c) To save the current state of the database to a backup file. d) To temporarily pause the transaction.

To mark a point within the current transaction to which one can later roll back.

If a single DML statement within a transaction fails during execution, what happens? a) The entire transaction is automatically rolled back. b) Only that specific DML statement is rolled back; previous DML statements in the transaction are retained. c) The entire transaction is automatically committed up to the point of failure. d) The user is prompted to either commit or roll back the failed statement.

Only that specific DML statement is rolled back; previous DML statements in the transaction are retained.

What does read consistency in Oracle ensure? a) Writers always have precedence over readers. b) Readers see data as it existed at the last commit, not data currently being changed by other users. c) All DML operations are immediately visible to all users. d) Only one user can read from a table at any given time.

Readers see data as it existed at the last commit, not data currently being changed by other users.

Before a COMMIT or ROLLBACK, who can view the results of DML operations made by the current user? a) All users b) Only the current user c) Only users with DBA privileges d) No one, until the transaction is committed

Only the current user

What happens to locks on affected rows after a COMMIT statement is issued? a) Locks are upgraded to exclusive locks. b) Locks remain in place until the session ends. c) Locks are released, and rows become available for other users. d) Locks are transferred to the DBA.

Locks are released, and rows become available for other users.

Which of the following is NOT a database object mentioned in the notes for the DDL introduction? (Referring to the initial list provided in section 5.3 for database objects context) a) Table b) View c) Trigger d) Sequence

Trigger

Table names and column names in Oracle must: a) Begin with a number. b) Be 1–64 characters long. c) Contain only A-Z, a-z, 0-9, _, $, and #. d) Be case-sensitive.

Contain only A-Z, a-z, 0-9, _, $, and #.

To create a table, a user must have the CREATE TABLE privilege and: a) DBA role b) A storage area c) At least one existing table in their schema d) SYSDBA connection

A storage area

What is the purpose of the DEFAULT option when defining a column in a CREATE TABLE statement? a) To specify the primary key for the table. b) To ensure the column only accepts a specific set of values. c) To specify a value to be used if a value is omitted during an INSERT. d) To set a default sorting order for queries on that column.

To specify a value to be used if a value is omitted during an INSERT.

Which data type is used for variable-length character data where a maximum size must be specified (e.g., up to 4000 bytes)? a) CHAR(size) b) VARCHAR2(size) c) TEXT(size) d) STRING(size)

VARCHAR2(size)

What does the NUMBER(p,s) data type define, where p is precision and s is scale? a) p is total digits, s is digits to the left of the decimal. b) p is digits to the right of decimal, s is total digits. c) p is total digits, s is digits to the right of the decimal. d) p is digits to the left of decimal, s is digits to the right of decimal.

p is total digits, s is digits to the right of the decimal.

Which of the following is a limitation of the LONG data type? a) It can be included in a GROUP BY clause. b) Multiple LONG columns can be used per table. c) A LONG column is not copied when a table is created using a subquery. d) Constraints can be defined on a LONG column.

A LONG column is not copied when a table is created using a subquery.

The TIMESTAMP data type is an extension of the DATE data type and stores: a) Only year, month, and day. b) Year, month, day, hour, minute, second, and fractional second values. c) Only hour, minute, and second values. d) A period of time in years and months.

Year, month, day, hour, minute, second, and fractional second values.

Which constraint type ensures that a column cannot contain null values? a) UNIQUE b) PRIMARY KEY c) NOT NULL d) CHECK

NOT NULL

Which constraint uniquely identifies each row in a table and implies both UNIQUE and NOT NULL? a) FOREIGN KEY b) PRIMARY KEY c) CHECK d) INDEX

PRIMARY KEY

A UNIQUE constraint: a) Does not allow null values. b) Requires every value in a column or set of columns to be unique, but allows nulls (unless NOT NULL is also specified). c) Must be defined at the table level. d) Automatically creates a non-unique index.

Requires every value in a column or set of columns to be unique, but allows nulls (unless NOT NULL is also specified).

A FOREIGN KEY constraint establishes a relationship with which type of key in the referenced (parent) table? a) Any indexed column b) A CHECK constraint c) A PRIMARY KEY or a UNIQUE key d) A NOT NULL column

A PRIMARY KEY or a UNIQUE key

In a FOREIGN KEY constraint definition, what does ON DELETE CASCADE specify? a) Deletion of the parent row is prevented if child rows exist. b) Child row foreign key values are set to NULL when the parent row is deleted. c) Dependent rows in the child table are also deleted when a row in the parent table is deleted. d) An error is raised, and the transaction is rolled back.

Dependent rows in the child table are also deleted when a row in the parent table is deleted.

Which of the following is NOT allowed in a CHECK constraint's condition? a) Comparison operators (e.g., salary > 0) b) Logical operators (e.g., AND, OR) c) References to pseudocolumns like CURRVAL or ROWNUM d) References to other columns within the same row

References to pseudocolumns like CURRVAL or ROWNUM

If you do not name a constraint, how does the Oracle server name it? a) CONSTRAINT_TABLE_COLUMN b) SYS_Cn (where n is an integer) c) USER_CONSTRAINT_n d) It requires the user to provide a name.

SYS_Cn (where n is an integer)

When creating a table using a subquery (CREATE TABLE ... AS SELECT ...), which of the following is TRUE? a) All constraints (PRIMARY KEY, FOREIGN KEY) are passed to the new table. b) Only the column names and data types are passed; NOT NULL constraints are not. c) Column data type definitions and the NOT NULL constraint are passed; other constraints are not. d) The new table inherits all indexes from the source table.

Column data type definitions and the NOT NULL constraint are passed; other constraints are not.

The ALTER TABLE statement can be used to: a) Change the name of the table. (Note: RENAME is used for this, ALTER TABLE modifies structure) b) Add a new column or modify an existing column's data type (with limitations). c) Roll back DML changes made to the table. d) Query data from the table.

Add a new column or modify an existing column's data type (with limitations).

What happens when a DROP TABLE statement is executed? a) Only the data is deleted; the table structure remains. b) The table definition is removed, data is lost, and associated indexes are dropped; it cannot be easily rolled back. c) A backup of the table is automatically created before dropping. d) The user is prompted for confirmation multiple times.

The table definition is removed, data is lost, and associated indexes are dropped; it cannot be easily rolled back.

What is a view in an Oracle database? a) A physical copy of a table's data, stored separately. b) A logical table based on a table or another view, stored as a SELECT statement. c) A special type of index used for fast data retrieval. d) A PL/SQL procedure that returns a dataset.

A logical table based on a table or another view, stored as a SELECT statement.

Which of the following is an advantage of using views? a) They always improve DML performance on base tables. b) They can restrict access to data by displaying only selected columns or rows. c) They store their own data, making queries faster than on base tables. d) They prevent any DML operations on the underlying base tables.

They can restrict access to data by displaying only selected columns or rows.

A simple view typically: a) Derives data from multiple tables and contains group functions. b) Derives data from only one table, contains no functions or groups, and allows DML. c) Does not allow any DML operations. d) Requires special privileges to create compared to complex views.

Derives data from only one table, contains no functions or groups, and allows DML.

In the CREATE VIEW statement, what does the OR REPLACE option do? a) Forces the view creation even if base tables don't exist. b) Re-creates the view if it already exists, without dropping and re-granting privileges. c) Replaces the underlying base table with the view definition. d) Creates a read-only version of an existing updatable view.

Re-creates the view if it already exists, without dropping and re-granting privileges.

When performing DML operations on a view, you CANNOT remove a row if the view contains: a) A WHERE clause b) The DISTINCT keyword or group functions c) Only columns from a single base table d) An ORDER BY clause

The DISTINCT keyword or group functions

The WITH CHECK OPTION clause in a CREATE VIEW statement: a) Ensures that no DML operations can be performed on the view. b) Specifies that only rows accessible to the view (matching its WHERE clause) can be inserted or updated through the view. c) Checks the syntax of the view's subquery for errors during creation. d) Allows DML operations to bypass constraints on the base table.

Specifies that only rows accessible to the view (matching its WHERE clause) can be inserted or updated through the view.

To ensure that NO DML operations can be performed on a view, which clause should be added to its definition? a) WITH NO DML b) WITH READ ONLY c) WITH PROTECT OPTION d) DISABLE DML

WITH READ ONLY

What is a sequence in Oracle? a) A stored procedure that executes a series of SQL statements. b) A database object that generates unique integer values. c) An ordered list of columns in a table. d) A type of database trigger.

A database object that generates unique integer values.

Which pseudocolumn is used to get the next available value from a sequence? a) sequence_name.CURRENTVAL b) sequence_name.GETNEXT c) sequence_name.NEXTVAL d) sequence_name.FETCHVAL

sequence_name.NEXTVAL

Before using CURRVAL for a sequence in a session, what must have occurred? a) The sequence must have been reset. b) NEXTVAL must have been issued for that sequence in the current session. c) The sequence must be cached. d) The user must have ALTER SEQUENCE privilege.

NEXTVAL must have been issued for that sequence in the current session.

Which CREATE SEQUENCE clause specifies the first sequence number to be generated? a) INCREMENT BY n b) START WITH n c) MINVALUE n d) FIRSTVAL n

START WITH n

What happens if the CYCLE option is specified for a sequence, and it reaches its MAXVALUE (for an ascending sequence)? a) It stops generating values and returns an error. b) It starts generating values from MINVALUE again. c) It automatically increases its MAXVALUE. d) It reverses direction and starts decrementing.

It starts generating values from MINVALUE again.

Gaps in sequence values can occur if: a) The sequence uses the NOCACHE option. b) A COMMIT occurs after fetching a sequence value. c) A transaction that fetched a sequence value is rolled back, or the system crashes (especially with caching). d) The NOCYCLE option is specified.

A transaction that fetched a sequence value is rolled back, or the system crashes (especially with caching).

What is an index in Oracle primarily used for? a) Enforcing data type consistency. b) Storing backup copies of table data. c) Speeding up the retrieval of rows by using a pointer, reducing disk I/O. d) Logging all DML operations on a table.

Speeding up the retrieval of rows by using a pointer, reducing disk I/O.

When is a unique index automatically created by the Oracle server? a) When a FOREIGN KEY constraint is defined. b) When a PRIMARY KEY or UNIQUE constraint is defined. c) Whenever a table is created with more than 1000 rows. d) When a CHECK constraint is defined on a column.

When a PRIMARY KEY or UNIQUE constraint is defined.

When should you generally consider creating an index on a column? a) The column is very small and contains mostly identical values. b) The table is small, and most queries retrieve all rows. c) The column contains a wide range of values, and it's frequently used in WHERE clauses or join conditions on large tables. d) The column is of LONG data type.

The column contains a wide range of values, and it's frequently used in WHERE clauses or join conditions on large tables.

What is a synonym in Oracle? a) A reserved word that cannot be used as an object name. b) A data dictionary view that lists similar objects. c) An alternative name for a database object like a table, view, or sequence. d) A type of constraint that ensures two columns have identical values.

An alternative name for a database object like a table, view, or sequence.

Creating a PUBLIC synonym means: a) The synonym is only accessible to the user who created it. b) The synonym is accessible to all users in the database. c) The underlying object is made public and accessible to everyone. d) The synonym can only be created for public tables owned by SYS.

The synonym is accessible to all users in the database.

The Data Dictionary in Oracle is: a) A collection of user-created tables for storing application metadata. b) A set of tables and views created and maintained by the Oracle server, containing information about the database. c) An optional component that needs to be installed separately. d) A tool for defining word translations for database error messages.

A set of tables and views created and maintained by the Oracle server, containing information about the database.

Who owns all base tables and user-accessible views of the data dictionary? a) The SYSTEM user b) The SYS user c) The user who created the database d) Each user owns their respective portion of the data dictionary.

The SYS user

Data dictionary views prefixed with USER_ (e.g., USER_OBJECTS) show: a) All objects in the database. b) All objects accessible to the current user. c) Objects owned by the current user. d) Only user accounts and their privileges.

Objects owned by the current user.

Which data dictionary view contains the names and short descriptions of other dictionary views accessible to the user? a) USER_CATALOG b) DBA_VIEWS_LIST c) DICTIONARY (or DICT) d) ALL_DESCRIPTIONS

DICTIONARY (or DICT)

To see the names and types of all objects you own, along with their creation date and status, you would query: a) USER_TABLES b) USER_OBJECTS c) ALL_SCHEMAS d) DBA_CATALOG

USER_OBJECTS

The USER_TABLES data dictionary view (or its synonym TABS) provides information about: a) All columns within your tables. b) The names of all your tables and detailed storage information. c) Indexes defined on your tables. d) Constraints applied to your tables.

The names of all your tables and detailed storage information.

To find detailed information about the columns in your tables (like data type, length, precision, scale, nullability), which view would you query? a) USER_COL_DEFINITIONS b) USER_TABLE_STRUCTURE c) USER_TAB_COLUMNS d) ALL_COLUMN_SPECS

USER_TAB_COLUMNS

The USER_CONSTRAINTS view provides information about constraint definitions. What does a CONSTRAINT_TYPE of 'R' signify? a) Read-Only constraint on a view b) Required (NOT NULL) constraint c) Referential integrity (FOREIGN KEY) constraint d) Rule-based (CHECK) constraint

Referential integrity (FOREIGN KEY) constraint

In USER_CONSTRAINTS, if DELETE_RULE is 'CASCADE' for a foreign key, it means: a) The parent record cannot be deleted if child records exist. b) Child records are deleted when the parent record is deleted. c) The foreign key in child records is set to NULL when the parent is deleted. d) An error is reported, and the delete operation fails.

Child records are deleted when the parent record is deleted.

To find the actual text (the SELECT statement) of a view you created, you would query the TEXT column in which data dictionary view? a) USER_VIEWS_SQL b) ALL_VIEW_DEFINITIONS c) USER_VIEWS d) DBA_SOURCE_CODE

USER_VIEWS

The USER_SEQUENCES data dictionary view shows details about sequences you own. What does the LAST_NUMBER column represent for a cached sequence? a) The most recently generated value by any user. b) The next value that will be generated. c) The last number placed in the sequence cache (likely greater than the last used value). d) The starting value of the sequence.

The last number placed in the sequence cache (likely greater than the last used value).

The USER_SYNONYMS view provides information about: a) All public synonyms in the database. b) Private synonyms owned by the current user. c) Objects that have similar names. d) Synonyms that point to data dictionary views.

Private synonyms owned by the current user.

How can you add a descriptive comment to a table or a column in Oracle? a) Using the ALTER TABLE ... ADD DESCRIPTION statement. b) By inserting a row into USER_COMMENTS view. c) Using the COMMENT ON TABLE table_name IS 'text'; or COMMENT ON COLUMN table.column IS 'text'; statement. d) Comments are automatically generated based on object names.

Using the COMMENT ON TABLE table_name IS 'text'; or COMMENT ON COLUMN table.column IS 'text'; statement.

Where can you view comments added to your tables? a) USER_DESCRIPTIONS b) ALL_NOTES c) USER_TAB_COMMENTS and USER_COL_COMMENTS d) DBA_METADATA_COMMENTS

USER_TAB_COMMENTS and USER_COL_COMMENTS

If a component query in a UNION operation selects character data of type CHAR, and another selects VARCHAR2, what will be the data type of the returned values? a) CHAR b) VARCHAR2 c) An error will occur d) LONG

VARCHAR2

Which DML statement can utilize a subquery in its VALUES clause (indirectly, by replacing the VALUES clause itself with a subquery)? a) UPDATE b) DELETE c) INSERT d) MERGE

INSERT

The TRUNCATE statement is faster than DELETE for removing all rows because: a) It processes rows one by one but with higher priority. b) It generates no rollback information and doesn't fire delete triggers. c) It only marks rows as deleted but doesn't physically remove them. d) It uses parallel processing by default.

It generates no rollback information and doesn't fire delete triggers.

When a transaction is interrupted by a system failure, what is the default behavior? a) The transaction is automatically committed up to the point of failure. b) The entire transaction is automatically rolled back. c) The user is prompted to complete the transaction upon system recovery. d) Only the last DML statement is rolled back.

The entire transaction is automatically rolled back.

A PRIMARY KEY constraint implicitly creates: a) A non-unique index. b) A unique index. c) A foreign key constraint on related tables. d) A sequence for the primary key column.

A unique index.

The INTERVAL YEAR TO MONTH data type stores: a) A specific point in time, including year and month. b) A period of time using the YEAR and MONTH datetime fields. c) Only the year and month of the current date. d) The number of months between two dates.

A period of time using the YEAR and MONTH datetime fields.

If you attempt to delete a row from a parent table that contains a primary key referenced by a foreign key in a child table (and no ON DELETE CASCADE/SET NULL is defined), what happens? a) The row is deleted, and corresponding child rows are also deleted. b) The row is deleted, and foreign keys in child rows are set to NULL. c) The deletion fails due to the "child record found" violation (e.g., ORA-02292). d) The foreign key constraint in the child table is automatically disabled.

The deletion fails due to the "child record found" violation (e.g., ORA-02292).

The STATUS column in the USER_OBJECTS view can indicate: a) Whether an object is PUBLIC or PRIVATE. b) Whether an object (like a view or procedure) is VALID or INVALID. c) The storage size of the object. d) The last user who accessed the object.

Whether an object (like a view or procedure) is VALID or INVALID.

If a SEQUENCE is created with CACHE 20 and the system crashes after 5 values from the cache have been used, what happens to the remaining 15 cached values? a) They are automatically restored to the sequence upon system startup. b) They are lost, potentially creating a gap in the sequence numbers. c) The sequence automatically adjusts its LAST_NUMBER to account for them. d) They are written back to disk before the crash.

They are lost, potentially creating a gap in the sequence numbers.

If a SELECT statement in a UNION uses an alias for a column, and the ORDER BY clause (at the very end) needs to refer to this column, can it use the alias? a) No, only the original column name from the first SELECT can be used. b) Yes, if the alias is defined in the first SELECT statement. c) No, only positional notation can be used in ORDER BY with UNION. d) Yes, aliases from any SELECT statement in the UNION can be used.

Yes, if the alias is defined in the first SELECT statement.

In the context of DML on views, what is a column defined by an expression? a) A column directly selected from a base table (e.g., SELECT salary ...). b) A column created by a calculation (e.g., SELECT salary * 12 AS annual_salary ...). c) A column with a DEFAULT value. d) A column that is part of a PRIMARY KEY.

A column created by a calculation (e.g., SELECT salary * 12 AS annual_salary ...).

The MINUS operator is sensitive to the order of the SELECT statements. a) True b) False

True

The INTERSECT operator is sensitive to the order of the SELECT statements. a) True b) False

b) False (The note says: "Reversing the order of the intersected tables does not alter the result.")

SAVEPOINT is an ANSI standard SQL command. a) True b) False

b) False (The note says: "Note: SAVEPOINT is not ANSI standard SQL.")

To restart a sequence at a different number, you can use ALTER SEQUENCE ... START WITH .... a) True b) False

b) False (The note says: "The sequence must be dropped and re-created to restart the sequence at a different number.")

Dropping a table also drops its associated views. a) True, views become invalid and are dropped. b) False, views remain but become invalid if they reference the dropped table.

b) False, views remain but become invalid if they reference the dropped table. (The note on DROP TABLE says: "Any views and synonyms remain but are invalid.")

The TRUNCATE TABLE statement can be part of a larger transaction and rolled back with other DML statements. a) True b) False

b) False (The note says: "The TRUNCATE statement is a data definition language (DDL) statement and generates no rollback information.")

IM FINALS Flashcards

(100 cards)