Section 1
Preview this deck
Which type of constraint specifies that the column on which the constraint is defined CANNOT contain a NULL value?
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
4 years ago
Date created
Mar 14, 2020
Cards (331)
Section 1
(50 cards)
Which type of constraint specifies that the column on which the constraint is defined CANNOT contain a NULL value?
NOT NULL
"How many digits to the right of the decimal point are allowed for a column defined as NUMBER(7,3)?"
three
Which group function computes a total for a group of rows?
SUM
"By default, how is the output of the UNION set operator sorted?"
It is sorted ascending in the order of the columns in the first SELECT list.
What is produced when a join condition is not specified in a multiple-table query?
a Cartesian product or cross join
Which type of join joins two tables by all columns that have the same name in both tables?
a natural join
A multiple-row operator expects how many values?
one or more
Which type of constraint specifies that every value in the column must be unique or have a NULL value?
UNIQUE
Which SELECT statement clause can be used with the JOIN keyword to perform a natural join but limit the columns for the join condition?
a USING clause
Which function returns the first non-null expression in a list?
the COALESCE function
Which statement explicitly makes all pending data modifications permanent?
the COMMIT statement
Multiple-column subqueries are often used as data sources in which clause of an outer SELECT statement?
the FROM clause
What datatype would be used to store binary data in an external file?
BFILE
"If a composite primary key is required, the PRIMARY KEY constraint must be defined at what level?"
the table level
Which type of join containing an equality operator joins two tables by a column that contains a matching value?
an equijoin
Is it possible for the main query to contain a greater than sign in its WHERE clause if the subquery returns only one value?
Yes
"Which statement would you use to change the name of a table, view, sequence, or synonym?"
RENAME. The syntax is RENAME old_name TO new_name;
Which character function substitutes one string for another?
"REPLACE. The syntax of the REPLACE function is REPLACE(column|expression, search_string [, 'replacement_string'])"
An UPDATE statement can update multiple columns in one table by using multiple columns in which clause?
"The SET clause. When multiple columns are included in the SET clause, they should be separated by commas."
Valid table and column names can only contain which characters?
"the characters A-Z, a-z, 0-9, _ (underscore), $, and #. (Normally table names with lower case alphabetics are treated the same as a table with upper case alphabetics. However, you can make case significant if you surround the table name by double quotes at the time it is created.) "
What type of index would be most appropriate to use in a data warehousing application on a column where the cardinality may be poor?
a bitmap index
Which clause of a MERGE statement modifies existing records?
the UPDATE clause
Which DML statement only adds rows to a table?
INSERT
Which comparison operator compares a value to each value returned by a subquery?
ANY
Which clause of the ALTER TABLE statement would you use to add a NOT NULL constraint to an existing table?
the MODIFY clause
What is the default order of precedence for set operators?
The set operators have equal precedence and thus are evaluated from left to right.
"Which datatype stores lengths of time represented in days, hours, minutes, and seconds?"
INTERVAL DAY TO SECOND
Which element would be used in a format model to return the spelled-out year?
YEAR or SYEAR
"How do you insure that values entered in the salary column of the employee table must always be greater than $30,000?"
Use a check constraint indicating salary > 30000.
Which one of the regular expression functions returns the substring value that matches a given pattern in a given string?
REGEXP_SUBSTR
"When adding a DATE datatype and a NUMBER datatype, which datatype is the result?"
a DATE datatype
Which function rounds numeric values to a specified decimal place?
"ROUND. The syntax of the ROUND number function is ROUND(column|expression, n)"
"By default, how is the output of the UNION ALL set operator sorted?"
It is not sorted.
Which set operator returns only results that are common to two queries?
INTERSECT
What is the name of the concept that guarantees that users have a consistent view of the data?
read consistency
What constraint(s) is used to create referential integrity?
the REFERENCES constraint or the FOREIGN KEY constraint
What is the function of the NOCYCLE keyword in a hierarchical query?
It prevents the query from aborting at run time due to an infinite loop
What should enclose subqueries?
parentheses
Which function removes leading and/or trailing characters from a character string?
"TRIM. The syntax of the TRIM function is TRIM(leading|trailing|both, trim_character, FROM column|expression)"
"If within a transaction, a single DML statement fails, what is rolled back?"
only the work of the offending DML statement
"When using the TO_CHAR function to format dates, which format model element will display the three-letter abbreviation for the day of the week?"
DY
Subqueries that return more than one column are referred to as what type of subqueries?
multiple-column subqueries
Which clause of a CREATESEQUENCE statement specifies the starting value for the sequence?
START WITH n
"List the values that would match the following regular expression: ['abc']{3,5}"
abcabcabc or abcabcabcabc or abcabcabcabcabc
Can a group function be included in a HAVING clause?
Yes
Which group function calculates a standard deviation for a group of values?
STDDEV
Which comparison operators can be used with multiple-row subqueries?
"IN, ANY, and ALL"
An index created on multiple columns in a table is called what?
a composite or concatenated index
"When a GROUP BY clause contains multiple columns, which grouping is the most major grouping?"
the first column listed in the GROUP BY clause
Which two older datatypes hold raw binary data?
RAW and LONG RAW
Section 2
(50 cards)
Which system privilege allows a user to connect to the database?
the CREATE SESSION system privilege
Which SELECT statement clause creates an equijoin by specifying a column name common to both tables?
a USING clause
What type of subquery returns more than one row of data?
a multiple-row subquery
What command will insert rows into a table if they don't already exist and will update rows in that same table if they do already exist?
MERGE
"Assuming you have the appropriate object privileges, how should you access a table owned by another user in a FROM clause?"
"prefix the table name with the owner/schema name, as in ownername.tablename"
"When joining more than two tables using an ON clause, in which order are the joins evaluated?"
from left to right
What is the name for a SELECT statement that is embedded in another SELECT statement?
a subquery
Which keyword can be used with the INSERT and UPDATE statement to set a column value to the value previously defined in a DEFAULT constraint?
DEFAULT
"When using Oracle proprietary join syntax, what operator would you use in a WHERE clause to produce an outer join?"
(+)
Which two categories of statements cause an autocommit to occur?
DCL and DDL statements
"When performing a self join, what must be used to qualify table names?"
table aliases
Which function returns the number of characters in a column or character string?
LENGTH. The syntax of the LENGTH function is LENGTH(column|expression)
Which statement would you use to define a default value for an existing column?
"ALTER TABLE. You would also use the ALTER TABLE statement to add, modify, or drop columns."
Which clause of a SELECT statement does Oracle server evaluate last?
the ORDER BY clause
"When using the TO_CHAR function to format numbers, what does a zero in the format model indicate?"
A zero (0) returns a zero in the specified position when there is no number for the position.
A ______ value must either be null or match an existing value in the primary key column of the parent table.
foreign key
Which statement do you use to remove previously granted privileges?
the REVOKE statement
What is the purpose of the START WITH <column name> IS NULL clause in a hierarchical query?
"Hierarchical queries that should begin at the very top of the hierarchy will often have a NULL value for <column name> since that column often will be the name of the parent, and the top row of the query does not have a parent."
"Which statement permanently removes all the rows in a table, the structure of the table, all constraints, all triggers on the table, and any associated indexes?"
the DROPTABLE statement
What is the name of the pseudocolumn which indicates the DML performed on a row?
versions_operation
"When performing a multitable INSERT, what two properties about the columns in the subquery and the columns in the outer query must match?"
they must be equal in number and in datatype
"The CREATE, ALTER, DROP, RENAME, and TRUNCATE statements are examples of which category of SQL statement?"
DDL
What is the default number of decimal positions stored in a timestamp datatype?
6
To how many levels can group functions be nested?
two
What does the IDENTIFIED BY clause of the ALTER USER statement specify?
the assignment of a password for the user
"If today is March 12, 2012, at exactly 12 o'clock noon, what will be the result of executing the statement SELECT sysdate - to_date('12-Mar-2012') FROM dual;?"
0.5
"When using an ORDER BY clause in a compound query, where should you place the clause?"
at the end of the statement
"In a SELECT statement, in which clause must you include all columns in the SELECT list that do NOT use a grouping function of some sort?"
in the GROUP BY clause
Which type of outer join includes all matched rows and all unmatched rows in the second table listed?
a right outer join
What is the syntax to return just the month portion of the values stored in column hire_date?
EXTRACT (MONTH FROM hire_date)
The ADD clause of the ALTER TABLE statement is used to do what?
add a column or constraint to a table
Which SELECT statement clause would you use with the JOIN keyword to specify a traditional join predicate?
the ON clause
Can a multitable INSERT insert rows into multiple tables and views in a single pass?
No. A multitable INSERT cannot insert rows into views.
Can a column be referenced in an ON clause before the column's table has been specified?
No
Reversing the order of the SELECT statements when using the INTERSECT operator has what effect on the results?
It will not affect the results.
Which clause in a hierarchical query specifies the root row of the hierarchy?
START WITH
What is the term for a subquery in which two or more values from the main query are matched to two or more values of the subquery?
a multicolumn subquery
Which set operator returns only the results of the first query that are not in the second query?
MINUS
Which datatype is an extension of the DATE datatype and stores a fractional second value?
TIMESTAMP
How do you recognize a correlated subquery?
The outer query makes reference to the table name in the inner query.
Which character function is functionally equivalent to using the || operator?
"CONCAT. The syntax of the CONCAT function is CONCAT(column1|expression1, column2|expression2)"
"When using the TO_CHAR function to format a date value, are the use of upper and lower case characters significant in the format mask?"
Yes
Which SELECT statement clause would you use to override the implicit sorting of a GROUP BY clause?
an ORDER BY clause
Which expression will add five days to the current date?
SYSDATE + 5
Which type of join joins two tables when the column in one table does not directly correspond to a column in the second table?
a non-equijoin
At which two levels can constraints be defined?
at the column or table level
How do you create a sequence that generates descending sequence values?
"Specify a negative value in the INCREMENT BY clause. For example, INCREMENT BY -1 would generate sequential descending values."
Valid column and table names should begin with what?
an alphabetic character
An owner of an object can grant access to all users using which keyword?
PUBLIC
Which group function calculates the average of a group of values?
AVG
Section 3
(50 cards)
Which database object is an alternate name for a table or view?
a synonym
"What keyword must be included in the equality condition in a hierarchical query, on one side or the other of the equal sign?"
PRIOR
What happens when the = operator is used with a multiple-row subquery?
An ORA-01427: single-row subquery returns more than one row error occurs.
Which character function returns an occurrence of one string of characters within a column value or expression?
"INSTR. The syntax of the INSTR function is INSTR(column|expression, 'string' [, m] [, n])"
Which option for a column in a CREATETABLE statement indicates the value that the column should be given if no value is explicitly specified on an INSERT statement?
the DEFAULT option
Which type of join joins a table to itself?
a self join
"When a character column contains data, can you decrease the width of the column?"
"Yes, but only if the existing data does not violate the new size"
Which statement would you use to add a PRIMARY KEY constraint to a table?
ALTER TABLE ADD CONSTRAINT. The full syntax is ALTER TABLE tablename ADD [CONSTRAINT constraint] type (column);
Which date function returns a DATE datatype representing the current database server date and time?
SYSDATE
What are ten criteria which indicate that a b-tree index would be appropriate to create on a given column in a table?
"1. the column contains many different values (good cardinality) 2. the table is large (or it contains many rows) 3. the value of the column is seldom NULL 4. the column is used often in a WHERE clause 5. the column is used often in a sort clause (ORDER BY) 6. the column is used often when multiple tables are joined together 7. the column is used often in a GROUP BY clause 8. the column, when used in a WHERE clause, does not have an arithmetic expression or function applied to it before the comparison operation is performed 9. updates to the values in that column are kept to a minimum 10. you know the optimizer is going to choose to use that index as opposed to performing a full table scan"
Which date function returns the ending date of the month containing a given date?
LAST_DAY. The syntax of the LAST_DAY function is LAST_DAY(date)
"What is the advantage of writing a multitable INSERT command that will insert rows from table_a into either tablex, tabley, or tablez based upon some condition, as opposed to executing three separate INSERT statements to insert rows one table at a time?"
Performance. The multitable INSERT needs to make only one pass of the data in table_a to get the job done.
What is the primary difference between an index organized table and a regular (heap) table?
"In the index organized table, the rows are physically stored in order by the primary key. "
"If the column order_date has a datatype of date, what would be the value of order_date + 7?"
"Exactly one week after the order date, at the same time of day."
What valid character does Oracle recommend you NOT use for table names?
"The $ character. Because data dictionary tables contain a $ character, using $ in a table name makes it hard to distinguish between a data dictionary object and a user-created object."
When would you use an ORDER BY clause in a subquery?
to perform Top-n Analysis
What are the first two words in the command used to disable a constraint on the column called id on the emp table?
ALTER TABLE
What are the four group functions that can only be used with numeric datatypes?
"SUM, AVG, VARIANCE, and STDDEV"
Which data dictionary view would you query to display objects you can access either by privileges explicitly granted to you or by privileges granted to PUBLIC?
ALL_COL_PRIVS_RECD
"If you have two WHEN conditions in an INSERT statement, what keyword will ensure the insertion is completed after evaluating only one WHEN condition?"
FIRST
"Which function compares two expressions, returning NULL if the expressions are equal and returning the first expression if the expressions are not equal?"
the NULLIF function
"For the statement SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE dept_name = 'ACCTG'); to execute properly, how many id values should the subquery return?"
one
Which statement do you use to assign a privilege to a user or role?
the GRANT statement
Which statement can be used to alter a view?
CREATE OR REPLACE VIEW
"What type of sort should be done in order to list the 10 highest paid employees in the table, starting with the highest paid person?"
Descending
Which statement removes rows in a table but can be rolled back?
DELETE
What would be the most common reason for an error message if you attempted to drop a primary key constraint on a table?
"The reason for the error would be that the column that had the primary key constraint on it was also acting as the parent in a parent / child relationship. Consequently there is a column in another table which is acting as the child in that relationship, and is pointing (via a foreign key constraint) to the column containing the primary key constraint. The primary key constraint ensures that the parent column in this parent/child relationship is unique, so Oracle prevents you from removing it. If duplicate values were allowed in the primary key column, there could be multiple parents for the same child, a violation of basic database design. "
When using Oracle proprietary join syntax which clause of the SELECT statement represents the join criteria?
the WHERE clause
You can update or delete rows in a table based on rows in another table by including what within your DML statements?
subqueries
What would you use to force the order of evaluation of set operators in a compound query?
parentheses
Can a row be both the child and parent of a given row?
No
"What command could a user issue such that the default format of dates, when displayed as output, are not in the typical dd-Mon-yy format?"
ALTER SESSION
Which type of outer join will include all rows from both tables being joined?
a full outer join
Which two functions can be used to pad a character string to the right or left with a specified character?
the RPAD and LPAD functions
How many ORDER BY clauses are allowed in a compound query?
only one
Which statement removes a sequence from the data dictionary?
DROP SEQUENCE sequence_name;
"When creating a table, column definitions can be omitted if what is used?"
"the AS subquery clause (CTAS, CREATE TABLE AS SELECT)"
Which statement removes an index from the data dictionary?
DROP INDEX indexname;
"What is the effect of using the GROUP BY and ROLLUP keywords in the statement GROUP BY expr1, ROLLUP(expr2, expr3)?"
The subtotals calculated will be all the various combinations of expr2 and expr3 while expr1 stays fixed.
"The NVL2 function accepts three expressions, and returns the second expression if the first expression is ______."
"NOT NULL. If the first expression is null, the third expression is returned."
Which statement would you use to create a nonunique index on a table?
the CREATE INDEX statement
"If an UPDATE statement contains a SELECT statement as a subquery, can the subquery contain both a GROUP BY and a HAVING clause?"
Yes
"When a commit occurs, what happens to existing savepoints?"
All savepoints are erased when a commit occurs.
Are the names of the columns in SELECT lists in a compound query required to be the same?
No. The names of columns can be different. The number and datatypes must be compatible.
Which statement creates a marker in the current transaction to allow you to rollback only a portion of the changes within the transaction?
SAVEPOINT. The syntax is SAVEPOINT name;
Which type of constraint enforces a relationship between the column and another column in the same or a different table?
FOREIGN KEY
"When modifying a sequence, which sequence numbers are affected?"
only future sequence numbers
Which keyword used with group functions causes the function to only consider unique values?
DISTINCT
What is another name for an outer query?
the main query
What is the relationship between the number of groups created by the ROLLUP operation and the number of grouping columns specified in the SELECT clause?
The number of groups created by the ROLLUP operation is one more than the number of grouping columns specified.
Section 4
(50 cards)
"In an INSERT statement, how can you specify that a NULL value is to be inserted into a column?"
omit the column in the column list OR explicitly include the NULL keyword in the INSERT statement's VALUES clause
Which two SQL statements add rows of data to an existing table?
the INSERT and MERGE statements
"When the MIN group function is used with a DATE column, which date is displayed?"
the earliest date
What is the default number of decimal places for the ROUND number function?
"Zero. When the integer representing the number of decimal places is not provided, the ROUND function rounds to zero decimal places (i.e., to the nearest whole number)."
What is the regular expression that could be used to confirm that a given account code is properly formatted?
...
"Valid account codes are seven characters and follow this format:
Character 1: Upper or lowercase A Character 2: Any alpha, but always lowercase Characters 3 - 6: digits (0 thru 9) Character 7: an uppercase M or another digit " '^[Aa][[:lower:]][[:digit:]]{4}[M[:digit:]]$'
"Evaluating MOD(20,3) returns what value?"
2
What two keywords in a hierarchical query establishes the relationship between a parent and a child?
CONNECT BY
Which clause of the SELECT statement constrains columns that use the GROUPING functions?
the HAVING clause
Which option can you include with the CREATEVIEW statement to prevent DML through the view?
"The WITH READ ONLY option of the CREATE VIEW statement prevents any DML operations such as inserts, updates, and deletes from being performed on the view's underlying or base table(s) when using the view."
Which function could be used to return a date without the time portion?
"The TRUNC date function returns a date with the time portion of the day truncated to the specified format unit. If no 'fmt' (format model) is provided, the date is truncated to the nearest day. The syntax of the TRUNC date function is TRUNC(date, 'fmt')."
"To remove a constraint from a table, which clause of the ALTER TABLE statement should you use?"
The DROP clause. The syntax is ALTER TABLE tablename DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraintname [CASCADE];
How many PRIMARY KEY constraints can be defined on a table?
only one
Which clause in a MERGE statement precedes the INSERT statement?
WHEN NOT MATCHED THEN
Which type of join joins all rows in one table to all rows of another table?
a Cartesian product or cross join
"When the MAX group function is used with a NUMBER column, which number is displayed?"
the largest number
"When a GROUP BY clause and no ORDER BY clause is used, in what order does the Oracle server implicitly sort the results?"
in ascending order by the first grouping column
"If the same column name appears in more than one table in a join condition, by what must the column name be prefixed?"
by the table name or a table alias
What should you do to eliminate the need for all database users to qualify an object name with its schema name?
Create a public synonym using CREATEPUBLIC SYNONYM syn_name FOR obj_name;
"When you create an External Table in your schema, can you specify the tablespace in which this table will be stored?"
"No, the actual rows of an External Table are stored as a file in the Operating System."
"What two constraints, when created, also cause an index to be created?"
PRIMARY KEY and UNIQUE
What element of the SELECT FROM emp WHERE salary > ( SELECT 2 MAX(bonus_amt) FROM bonus); query assures that the subquery will return exactly one value?
The group function MAX in the subquery.
What is the name of the function which allows you to determine the exact GROUP BY level when using any of the GROUPING functions?
GROUPING_ID
"If no column list is specified in an INSERT statement, your VALUES clause must list the values in what order?"
the order in which the columns are specified in the table
How can a subquery make reference to a table in the main query?
Through the use of a table alias.
Which type of join is represented by the use of an operator other than an equality operator (=)?
a non-equijoin
Outer join conditions CANNOT use which logical operator?
the IN logical operator
"When using the TO_CHAR function to format dates, what does MON in a format model display?"
the three-letter month abbreviation using three capital letters
"For most small tables, ______ are faster than index searches."
full table scans
Unique indexes are automatically created on columns that have which two types of constraints?
UNIQUE and PRIMARY KEY
"To insert data through a simple view, must you include all NOT_NULL columns that do not have a default value assigned in the view definition?"
Yes
"In which specific area of database administration are the keywords CUBE, ROLLUP, and GROUPING SETS are most often used?"
Data Warehousing
How many primary keys can be in any given table?
Either 0 or 1
"If no schema is explicitly included in a CREATE TABLE statement, where is the table created?"
in the current user's schema
Which option of the CREATESEQUENCE statement allows the sequence to continue to generate values after the maximum sequence value has already been generated?
the CYCLE option
Which pseudocolumn can be used with a sequence to display the current value of that sequence?
CURRVAL
Which regular expression function can be used with a CHECK constraint on a column of a table in order to insure the specific format of the data entered for that column?
REGEXP_LIKE
"Which category of SQL statement includes the INSERT, UPDATE, DELETE, and MERGE statements?"
DML
How do group functions handle NULL values?
Group functions ignore NULL values.
Which two constructs can be used to emulate an IF-THEN-ELSE condition within a SELECT statement?
the DECODE function and the CASE expression
How do you grant UPDATE privileges on specific columns in a table?
"Include a column list in the GRANT statement. This syntax is only valid with the UPDATE, REFERENCES, and INSERT privileges."
How do you create an inline view?
include a subquery as a data source in a FROM clause
"To join n tables together using Oracle proprietary join syntax, how many join conditions are needed in the WHERE clause?"
n - 1
"If the statement SELECT * FROM emp WHERE UPPER(last_name) = :x; was executed many times in your application, what would you consider doing to improve performance?"
Build a function based index on UPPER(last_name).
What is the maximum number of decimal positions you can specify for a timestamp datatype?
9
"What value will be returned by this regular expression? REGEXP_COUNT('AEioUIOUAEiOU','Ei',2,i)"
2
What type of information is stored in the database regarding an External Table and where is that information stored?
"the metadata, which is stored in the data dictionary"
Which clause can you include in a CREATEVIEW statement to ensure that DML operations that would change the result of the view are not allowed?
WITH CHECK OPTION
Which category of SQL statements control access/permission to the data?
"DCL statements, such as GRANT and REVOKE, control access to data."
Will the query SELECT * FROM emp WHERE salary = commission; display rows in which the salary and the commission are both NULL?
"No. If two values are both NULL, they are not equal to each other. Because you don't know the values for salary or commission, there is not enough information to tell whether the condition is TRUE or FALSE. Therefore, the condition evaluates to a logical NULL. Rows in which the WHERE clause evaluates to NULL or FALSE are not displayed when you issue a SELECT."
Section 5
(50 cards)
"Which metacharacter, when used in a SQL regular expression, represents a match to any character in the database character set?"
the period (.)
Which pseudocolumn retrieves the next available sequence value?
NEXTVAL
What type or category of commands is MERGE?
DML
"SELECT title, author, pub_date FROM books WHERE author IN (SELECT name FROM classics WHERE genre = 'ROMANCE'); "
...
"If you are not the DBA, which privilege must you have to create a public synonym on another user's table?"
the CREATE PUBLIC SYNONYM privilege
Can subqueries contain group functions?
Yes
"Which DML statement updates rows conditionally, based on whether a row already exists or not?"
the MERGE statement
Which operator would immediately precede a subquery if that subquery can potentially return more than one row?
IN
Explain the difference between disabling and dropping a constraint.
"A constraint than has been disabled still exists but is not enforced. It can be reenabled a later time. If a constraint is dropped, it will need to be recreated should you need it again."
Outer join conditions CANNOT be linked to another condition by which operator?
the OR operator
"In order to issue a TRUNCATETABLE statement successfully, you must either own the table or have which privilege?"
the DROPANYTABLE system privilege
A subquery that returns a single row of data is known as which type of subquery?
a single-row subquery
Can you modify data through a view that contains a GROUP BY clause?
"No, you also CANNOT modify data if the view contains group functions, the DISTINCT keyword, or the ROWNUM pseudocolumn."
Which option of a CREATEVIEW statement creates the view regardless of whether the underlying tables exist?
the FORCE option
Can any user who owns an object grant object privileges for that object?
Yes
"What does the format of my_string have to be in order for the WHERE clause in the following query to evaluate to TRUE? SELECT * FROM dual WHERE REGEXP_LIKE(my_string,'^[a-z]+$');"
one or more all alphabetic lowercase characters
Rules enforced at the table level concerning acceptable data which can be entered into a table are called what?
constraints
"What is the benefit to setting a column that you no longer want in your table to UNUSED, rather than just dropping the column?"
"Improved performance. Dropping a column is usually labor intensive, so it is delayed until off hours. If you mark the column unused, Oracle adjusts the data dictionary to logically indicate the column and its data are no longer there. Then, during a period of little activity such as the evening, the physical drop can take place and its effect on the users will be minimal."
What's the advantage of using IN rather than = in the following query?
Multiple values could be returned by the subquery without causing the statement to fail.
What form of the INSERT statement will insert all rows from a table into two or more other tables?
INSERTALL
Must a HAVING clause be preceded by a GROUP BY clause?
"No, a HAVING clause can be listed first, but it is usually placed after the GROUP BY clause."
What two values can the GROUPING function return if the argument is a single column?
0 or 1
Which one of the regular expression functions returns a Boolean value?
REGEXP_LIKE
What pseudocolumn name is available for use in a hierarchical query which permits you to control the number of generations in the hierarchy which should be selected for output?
LEVEL
Which privilege must a user be granted to be able to change another user's password?
the ALTER USER privilege
"If today is March 12, 2012, under what conditions will the statement SELECT to_date('12-Mar-2012') sysdate FROM dual; produce the number 0 for its output?"
When it was executed at exactly midnight.
"When no WHERE clause is specified in a DELETE statement, which rows are deleted?"
all the rows in the table
Which function adds or subtracts months from a given date?
"ADD_MONTHS. The syntax of the ADD_MONTHS function is ADD_MONTHS(date, n), where n is a positive or negative integer."
Which option of the ALTER TABLE statement would you use to mark one or more columns in a table as being obsolete so that they may be dropped later?
SET UNUSED. The syntax is ALTER TABLE tablename SET UNUSED (column);
Updates are not allowed through a view if the view definition includes columns that are defined by what?
expressions
Which three functions convert case for character strings?
"UPPER, LOWER, and INITCAP. The UPPER character function converts mixed case or lowercase value to uppercase, LOWER converts mixed or uppercase to lowercase, and INITCAP converts a character string to a value with the first character uppercase and the remaining lowercase."
Which type of queries ask for the n largest or smallest values of a given column?
Top-n queries
Which clause in a SQL expression will enable a single statement to calculate all possible combinations of aggregations?
GROUP BY CUBE
"What command will create a new table which has a similar structure to an existing table, and also include all or some of the rows from that original table?"
Create Table As Select (CTAS)
Which system privilege allows a user to connect to the database?
CREATE SESSION
A WHERE clause condition that uses < ALL will return all records where the value is ______.
a value less than the minimum returned by the subquery
Can more than one row be inserted into the table at a time if the INSERT statement has a subquery instead of a VALUES clause?
Yes
Which datatype is a hexadecimal string representing the unique address of a row in its table?
ROWID
Name three TCL statements.
"COMMIT, ROLLBACK, and SAVEPOINT"
Which clause in an UPDATE statement specifies the rows that will be updated?
the WHERE clause
"What function and arguments would modify the way the hire_date is displayed in a SELECT statement such that hire_date values appear formatted in this way: January 27, 2006?"
"to_char(hire_date, 'fmMonth dd, yyyy')"
Which option of the CREATESEQUENCE or ALTERSEQUENCE statement indicates the largest value that the sequence can generate?
MAXVALUE n
"When a column is dropped using the DROPCOLUMN clause of the ALTER TABLE statement, which columns are physically dropped?"
the specified column and any other columns of that table that have been marked as UNUSED are dropped
Which SELECT statement clause allows you to limit which groups are displayed?
a HAVING clause
Which function can be used to determine the number of months between two dates?
"MONTHS_BETWEEN. The syntax of the MONTHS_BETWEEN function is MONTHS_BETWEEN(date1, date2)"
"If no GROUP BY clause or WHERE clause is included in a simple SELECT statement, what would the COUNT(*) function return?"
the number of rows in the table
In which set operator are duplicate rows included in the result set?
UNION ALL
Which set operator returns all the results from two queries after eliminating any duplicates?
UNION
What keyword(s) in a SQL SELECT can be used instead of the keyword CUBE in order to calculate just the aggregations you need from the GROUP BY clause?
GROUPING SETS
Which SELECT statement clause restricts included rows prior to grouping?
a WHERE clause
Section 6
(50 cards)
Which comparison operator is used when matching character patterns?
the LIKE operator
"According to operator precedence, which of the three logical operators is evaluated first?"
"NOT is evaluated first. The order of precedence for logical operators is NOT, AND, and OR."
"When testing each row in the emp table to determine whether any employee doesn't have an email, how should the WHERE clause be written if you want the information about those employees who don't have an email to be displayed?"
the IS NULL comparison operator
Which datatype holds variable-length character data?
VARCHAR2
"If no ORDER BY clause is included in a SELECT statement, how are the results sorted?"
"The order of the results will be unpredictable. Each time the query is run, the order in which the rows are displayed may be different."
Does one DML statement constitute a complete database transaction?
"No, one DDL or DCL statement constitutes a complete database transaction, but a transaction can contain more than one DML statement."
"When implementing a natural join, must columns with the same names have compatible datatypes?"
Yes
"If you have a five column list with the CUBE extension, how many combinations would be created in the result set?"
25 or 32 combinations
What would you use in an INSERT statement instead of a VALUES clause to copy rows from one table to another?
a subquery
"In a SELECT statement, subqueries are often used in which clause to return values for an unknown conditional value?"
a WHERE clause
What are the two requirements of expressions in a SELECT list in a compound query?
the expressions must match in number and in datatype
Which function replaces a NULL value with a specified value?
NVL
Which type of operators should be used with queries that return only one row?
"Single-row operators. Single-row operators expect only one value and include =, >, >=, <, <=, <>, and LIKE."
"When using the LIKE operator, which option must you include to use the percent (%) and underscore (_) characters as literal values?"
The ESCAPE option identifies a special escape character that when preceding these characters causes them to be interpreted literally.
Name three functions that can be used with group functions to replace NULL values.
"NVL, NVL2, and COALESCE"
Which datatype includes a time zone displacement value that allows Oracle to return data to the client in the user's local session time zone?
TIMESTAMP WITH LOCAL TIME ZONE
Which datatype stores a length of time in months and years?
INTERVAL YEAR TO MONTH
Which two sets of join keywords create a join that will include unmatched rows from the first table specified in the SELECT statement?
LEFT OUTER JOIN and FULL OUTER JOIN
Which comparison operator compares a value to every value returned by a subquery?
ALL
Which SQL capability is demonstrated when two or more tables are linked with a WHERE clause?
joining
What are functions that operate on sets of rows to give one result per group called?
"group functions, or aggregate functions"
Which statement removes all rows from a table while releasing all or most of the storage space used by the table?
the TRUNCATETABLE statement
"If an INSERT statement has a subquery instead of a VALUES clause, what criterion will allow the statement to execute successfully relative to the INSERT and the SELECT?"
The number and datatype of the columns returned by the SELECT must match the number and datatype of columns the INSERT is expecting.
What is the default sort order when using an ORDER BY clause?
ascending
Which SQL capability allows you to control the number of columns returned by a query by choosing the columns in the SELECT clause?
projection
What privilege(s) is required to drop a view?
ownership of the view or the DROPANY VIEW system privilege
In what order are nested functions evaluated?
from the innermost function to the outermost function
Which type of constraint can only be specified at the column level?
NOT NULL
Can a column alias be used in a GROUP BY clause?
No
Which option must you include in a GRANT statement if you intend for the user to grant this privilege to other users?
WITH GRANT OPTION
"SELECT * FROM dual WHERE REGEXP_LIKE(my_name, '^(R|B)ob(ert)?');"
...
Which option of the ROLLBACK statement should you include to roll back only the changes after a particular savepoint?
TO SAVEPOINT. The syntax is ROLLBACK TO SAVEPOINT name;
"If you fail to name a constraint, what will Oracle name the constraint?"
"SYS_Cn, where n is an integer making the constraint name unique in that schema"
In which clause of a SELECT statement can a column alias NOT be used?
A column alias CANNOT be used in a WHERE clause.
Which statement would you use to delete a synonym?
DROP SYNONYM synonym_name;
"In a hierarchical query, is it possible for a row to have more than one immediate ancestor?"
No
Which group function would you use to determine the number of rows that meet a certain condition?
COUNT(*)
"Does the MERGE statement allow you to insert, update, and delete rows from a table?"
Yes
Which SQL capability allows you to choose rows in a table to be returned by a query?
selection
"What are two possible literal names that, if assigned to the variable my_name in the following expression, would return a Boolean TRUE?"
"Robert, Bob"
What are the two operators that are functionally equivalent to the <> operator?
!= and ^=
Which three functions allow you to perform explicit datatype conversions?
"TO_CHAR, TO_DATE, and TO_NUMBER"
The CASCADE CONSTRAINTS option of the REVOKE statement removes any constraints made to the object using which privilege?
REFERENCES (foreign key constraints)
"In a SELECT list, what character should enclose date and character literal values?"
single quotation marks (')
Which set operator returns all the results from two queries without eliminating any duplicates?
UNION ALL
The <> (not equal) comparison operator can only be used with which type of subquery?
a single-row subquery
Which statement ends the current transaction by disregarding all pending changes?
ROLLBACK
"Which character function returns a portion of a character string, beginning at a specified character position up to a specified length?"
"SUBSTR. The syntax of the SUBSTR function is SUBSTR(column|expression, m [, n])"
"In nested functions, which pairs of parentheses are evaluated first?"
The innermost pair of parentheses will be evaluated first.
"When using the TO_CHAR function to format dates, which format model element will display the numeric day number of the month?"
DD or dd
Section 7
(31 cards)