SQL
When you add the concept of time to your data model, you are:
ADDING COMPLEXITY TO YOUR MODEL
If the DISTINCT keyword is not included in the AVG function, the ____ keyword will be assumed.
ALL
Which operators can be combined with other comparison operators to treat the results of a subquery as a set of values, rather than as individual values?
ALL & ANY
To instruct Oracle11g to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
ASC
What is the rule of Second Normal Form?
All non-UID attributes must be dependent upon the entire UID.
When you resolve a M:M by creating an intersection entity, this new entity will always inherit:
A relationship to each entity from the original M:M.
A(n) ____ is used to indicate how data should relate to a given search condition.
COMPARISON OPERATOR
Combining the contents of two or more columns is known as ____.
CONCATENATION
The ____ function can be used to determine the number of rows containing a specified value.
COUNT
Consider the recommended drawing conventions for ERD's. Indicate which of the following accurately describes diagramming conventions for entities and attributes:
The * means that an attribute is mandatory or required; the entity name should be singular
The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
USING
A correlated subquery is one in which the inner query is executed first, and then the outer query is executed. t/f?
false
Group functions return a group of results per row processed. t/f?
false
No formal rules exist for drawing ERD's. The most important thing is to make sure that all entities, attributes, and relationships are documented on the diagram. The Layout is insignificant. True or False?
false
Which of the following operators is used when the criteria is based upon a search pattern?
like
The type of view that actually replicates data is called a(n) ____ view.
materialized
The entire column heading for a column will be displayed, regardless of the assigned width of the column, if the column is defined as a(n) ____ column.
numeric
SUM, AVG, and COUNT are all considered group functions. t/f?
true
The DECODE function allows the user to specify different actions to be taken, depending on the value being used for the comparison. t/f?
true
The IN operator can be used with single-row, multiple-row, or multiple-column subqueries. t/f?
true
The LOWER function can be used to display upper-case characters in lower-case. t/f?
true
When a view includes columns from more than one table, updates can only be applied to the table that includes the primary key for the view. t/f?
true
A Matrix Diagram will help you with all of the following except:
verifying relationships between entities
A(n) clause identifies what must exist or a requirement that must be met for a record to be included in the results of a query. T/F
FALSE
A(n) non-equality join is also known as an equijoin, inner join, or simple join. T/F
FALSE
An entity can be on 2nd Normal Form even if it has repeated values. True or False?
FALSE
Equality, non-equality, and self-joins are broadly categorized as outer joins
FALSE
Hierarchical queries MUST use the LEVEL pseudo column. True or False?
FALSE
If a numeric column is being used for comparison against the search condition, the search condition is enclosed in single quotation marks.T/F
FALSE
Results returned from a SELECT statement that include a GROUP BY clause will present the results in descending order of the column(s) listed in the GROUP BY clause. T/F
FALSE
Single-row functions return one row of results for each group or category of rows processed. T/F
FALSE
The COUNT function can only be used for numeric columns. T/F
FALSE
The JOIN keyword must be used in the WHERE clause of a SELECT statement. T/F
FALSE
The UPDATE command cannot be used on a view created with the WITH CHECK OPTION. T/F
FALSE
A group function can be nested inside a(n)____.
GROUP AND ORDER FUNCTIONS
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
HAVING
If the result returned from a subquery must be compared to a group function, then the inner query must be nested in the outer query's ____ clause.
HAVING
Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);
When resolving a M:M relationship, the new relationships will always be __________ on the many side.
MANDATORY
In Oracle11g, a(n) ____ allows a series of DML actions to occur.
MERGE STATEMENTS
When sorting the results in ascending order, which of the following values will be presented last in the output?
NULL
When sorting the results in ascending order, which of the following values will be presented first in the output?
NUMERIC
____ aren't allowed in the CREATE VIEW command.
ORDER BY
A barrred Relationship will result in a Foreign Key column that also is part of:
PRIMARY KEY
Which of the following types of joins refers to joining a table to itself?
SELF-JOIN
A(n) ____ is used to combine the results of two queries.
SET OPERATOR
A multiple-row subquery can be nested in a HAVING clause.
TRUE
A subtype can have a relationship not shared by the supertype. True or False?
TRUE
The IN logical operator is similar to using OR.
TRUE
The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column. T/F
TRUE
When functions are nested, the inner function is solved first. T/F
TRUE
How do you know when to use the different types of time in your design?
DEPENDS ON THE FUNCTIONAL NEEDS OF THE SYSTEM
What is another name for a simple join or an inner join?
EQUIJOIN
The ____ function is used to determine how widely data are spread out within a group.
VARIANCE
