A CASE SQL statement is which of the following?
Answer: A
Correct answer: A way to establish an IF-THEN-ELSE in SQL.
CASE evaluates conditions in order and yields the value belonging to the first that holds, which is a conditional expression rather than a statement. It can therefore appear anywhere a value is expected - in a select list, in an ORDER BY, or inside an aggregate.
Please log in or register to report this question.
Which of the following is a correlated subquery?
Answer: B
Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.
Please log in or register to report this question.
A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid.
Answer: A
Correct answer: True
This is the definition of atomicity. The set of updates either all take effect or none does, so the database moves from one consistent state to another and is never left part way - the transfer that debits one account and fails to credit the other being the standard illustration.
Please log in or register to report this question.
Which of the following statements is true concerning subqueries?
Answer: A
Correct answer: Involves the use of an inner and outer query.
A subquery is a SELECT nested inside another, the inner supplying a value or a set to the outer. Many subqueries can be rewritten as joins and return identical results, and the choice between them is usually a matter of readability rather than of what is possible.
Please log in or register to report this question.
Which of the following is one of the basic approaches for joining tables?
Answer: D
Correct answer: All of the above
Tables can be brought together by joining them in the FROM or WHERE clause, by a subquery whose result the outer query uses, or by a set operation such as UNION. Each suits a different shape of question, and many queries can be written in more than one of them.
Please log in or register to report this question.
Which of the following is true concerning a procedure?
Answer: C
Correct answer: They include procedural and SQL statements.
A stored procedure combines SQL with the procedural constructs SQL lacks - variables, conditionals, loops, exception handling - so logic that would otherwise sit in an application can live in the database. It is created with SQL, must be uniquely named, and differs from a function in that it need not return a value.
Please log in or register to report this question.
The most frequently used relational operation, which brings together data from two or more related tables into one resultant table, is called an equi-join.
Answer: B
Correct answer: False
The most frequently used join is the natural join, which matches on the common column and returns it once rather than twice. The equi-join is the same operation with the duplicate column retained, and it is the natural join that is wanted in nearly every practical query.
Please log in or register to report this question.
How many tables may be included with a join?
Answer: D
Correct answer: All of the above.
There is no limit in principle: a query may join as many tables as it needs, and reports joining half a dozen are commonplace. What rises with the count is the optimiser’s work in choosing an order, and the care needed to supply a condition for every pair.
Please log in or register to report this question.
The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID
Answer: A
Correct answer: Equi-join
The join condition tests two columns for equality, which is what makes it an equi-join, and both CUSTOMER_ID columns appear in the select list. A natural join would have dropped one of them; the presence of the duplicate is the distinguishing feature.
Please log in or register to report this question.
What type of join is needed when you wish to return rows that do have matching values?
Answer: D
Correct answer: All of the above.
All three return the matching rows; what distinguishes them is what else they do. The equi-join returns matching rows with both join columns, the natural join drops the duplicate column, and the outer join adds the unmatched rows besides. The matching rows appear in every case.
Please log in or register to report this question.
