Oracle Database SQL Certified Associate (1Z0-071)
Which three statements are true about external tables? A. DML statements can modify them B. They can be temporary tables C. They can be used in queries containing joins D. They can be indexed E. They can be used in queries containing sorts F. Their metadata is stored in the database
C. They can be used in queries containing joins E. They can be used in queries containing sorts F. Their metadata is stored in the database
FLASHBACK TABLE
Can be used to restore a dropped table and its data as of a specific time in the database. Cannot be run if a PURGE command has been executed.
What does the UNION ALL set operator do?
Combines row sets. Does not remove duplicates.
What does the UNION set operator do?
Combines row sets. Removes duplicate rows.
C. D. Object privileges granted on a table auto apply to synonyms for that table E. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users F. Schema owners can grant any object privilege on any schema to any other user or role
D. Object privileges granted on a table auto apply to synonyms for that table E. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users F. Schema owners can grant any object privilege on any schema to any other user or role
USER_OBJECTS
Data Dictionary view. Contains information about all objects owned by the current user. Synonym is OBJ.
ALL_TABLES
Data Dictionary view. Contains information about tables to which the current user has privileges, regardless of who owns the table.
USER_CATALOG
Data Dictionary view. Contains information about tables, views, synonyms, and sequences owned by the current user. Two columns: TABLE_TYPE and TABLE_NAME. Synonym is CAT.
INCREMENT BY
Each requested sequence number will increase by the number provided. A negative number will cause the sequence number to descend. Default value is 1.
TRUE or FALSE CURRVAL and NEXTVAL can be combined with UNION, INTERSECT, and MINUS set operators.
FALSE
TRUE or FALSE CURRVAL and NEXTVAL can be used in a CHECK constraint.
FALSE
TRUE or FALSE CURRVAL and NEXTVAL can be combined with a DISTINCT operator.
FALSE
TRUE or FALSE CURRVAL and NEXTVAL can be invoked as the DEFAULT for CREATE TABLE and ALTER TABLE statements.
FALSE CURRVAL and NEXTVAL cannot be invoked as the default clause of CREATE TABLE or ALTER TABLE statements.
TRUE or FALSE BLOBs and CLOBS can be used in SET operators.
FALSE Large data types, such as BLOB and CLOB, cannot be used in SET operators.
TRUE or FALSE Columns set to unused appear when a user runs a describe command on the corresponding table.
False Columns set to unused do not appear when a user runs a describe command. Unused columns are essentially dropped and are never available again.
NEXTVAL
Increments to the next number in the sequence. The sequence is incremented to the next number even if the statement that invoked it fails.
NOMAXVALUE
No maximum value specified for the sequence.
NOMINVALUE
No minimum value specified for the sequence.
What does the INTERSECT set operator do?
Only includes rows that are present in both queries.
CYCLE
Restarts the sequence when it reaches the end of its range. An ascending sequences cycles when it hits MAXVALUE. The following number will be the MINVALUE. A descending sequences cycles when it hits MINVALUE. The following number will be the MAXVALUE.
DBA_TABLES
Shows table information for every table in the database, regardless of who owns the table or has privilege to the table.
What does the MINUS operator do?
Subtracts the the rows in the second result set from the first result set
TRUE or FALSE A dropped table can be recovered via a FLASHBACK statement.
TRUE Dropped objects are moved into the recycle bin. Objects in the recycle bin can be recovered with a FLASHBACK command so long as a PURGE command has not been executed.
What are the four SET operators?
UNION UNION ALL INTERSECT MINUS
Four rules to ensure SELECT statements work with SET operators
1. The number of expressions in each SELECT statement must be the same. 2. The data types for each expression must match the data type of its corresponding expression in the other SELECT statement. Or the data types must be automatically convertible. 3. BLOBs and CLOBS cannot be used. 4. The ORDER BY clause can only be included in the last SELECT statement.
Which DML statement(s) trigger an automatic update on the queried table's index? A. INSERT B. UPDATE C. DELETE D. All of the above
D. All of the above When DML operations are performed on an indexed table, the index is automatically updated by the Oracle Database, which adds to the workload of DML statements.
In which of the following locations can CURRVAL and NEXTVAL be called? Select all that apply. A. The subquery of a CREATE VIEW statement B. The subquery of a SELECT statement C. The subquery of an UPDATE statement D. The subquery of a DELETE statement E. None of the above
D. None of the above
USER_TAB_COLUMNS
Data Dictionary view. Contains information about the tables and views owned by the current user. Synonym is COLS
USER_CONSTRAINTS
Data Dictionary view. Contains information on table constraints. Four columns. CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, STATUS Constraint Types are: P - Primary Key R - Foreign Key (R stands for Referential Integrity) U - Unique C - CHECK or NOT NULL
USER_TABLES
Data Dictionary view. Contains information regarding tables owned by the current user. Every account has a USER_TABLES view that displays information about tables owned by the account. Does not have an OWNER column since the view only shows tables owned by the current user. Synonym is TABS.
DICTIONARY
Data Dictionary view. Has two columns: TABLE_NAME and COMMENTS
ALL_COL_COMMENTS
Displays comments for all tables and views in the database.
ALL_TAB_COMMENTS
Displays comments for every object in the database.
TRUE or FALSE A database administrator manages updates to the data dictionary.
FALSE The Oracle Database automatically updates the data dictionary every time a DDL statement is issued. Updates are applied to the base tables that form the foundation of the data dictionary. Users, including the DBA, DO NOT explicitly manage any information in the data dictionary.
TRUE or FALSE The columns and rows in each SELECT statement of a SET operator must have the same names.
FALSE The SELECT statements must have the same amount of expressions that are the same data types, But the names are irrelevant.
CURRVAL
The current number the sequence is holding. Only valid if the NEXTVAL has been invoked.
START WITH
The first number in the sequence. Defaults to MINVALUE for ascending sequences and MAXVALUE for descending sequences. Sequences with NOMINVALUE or NOMAXVALUE default to 1.
NOMAXVALUE
The maximum number allowed in the sequence. Default is NOMAXVALUE
MINVALUE
The minimum number for a sequence. Default is NOMINVALUE unless required by a cycle, wherein which the default is 1.
Visible Index
The optimizer can see and use a visible index while processing SQL queries. An index is visible by default.
Invisible Index
The optimizer does not consider the invisible index while preparing an execution plan for a SQL query. An invisible index must be explicitly declared.
NOCYCLE
The sequence stops generating numbers when it reaches the end of its range. The default cycle setting for sequences. Has no effect if the sequence does not have a specified range.