Which SQL command removes a table together with all its data and its definition ?
Answer: C
Correct answer: DROP TABLE STUDENT
DROP TABLE deletes the rows and the table structure itself. DELETE removes rows subject to a WHERE clause and TRUNCATE removes all rows quickly, but both leave the empty table in place.
Please log in or register to report this question.
What does the SQL keyword DISTINCT do ?
Answer: D
Correct answer: Eliminates duplicate rows from the result
DISTINCT removes repeated rows so that each combination of the selected columns appears once. Sorting is the work of ORDER BY and limiting the row count that of LIMIT or its equivalent.
Please log in or register to report this question.
A tool that can help designers understand the dependencies of database structures is a:
Answer: A
Correct answer: dependency graph.
A dependency graph shows the database’s structures as nodes and their dependencies as arcs, so that before changing anything the designer can see what refers to it - the views, constraints, triggers, stored procedures and application queries that would break. It is the tool that turns “what does this column touch?” from a search into a reading.
Please log in or register to report this question.
How many copies of the database schema are typically used in the redesign process?
Answer: C
Correct answer: Three
Three copies are used: the operational schema, which is left untouched; a test copy on which the changes and their SQL are proved; and a copy carrying the proposed new design. Working with fewer means either testing against live data or having nowhere to develop the new structure, and the three-copy arrangement is what allows the change to be rehearsed completely before it is applied.
Please log in or register to report this question.
Because of the importance of making data model changes correctly, many professionals are ________ about using an automated process for database redesign.
Answer: B
Correct answer: skeptical
Automated tools can generate the DDL for a structural change, but they cannot know what the data means, which values will fail a new constraint, or what the applications expect - and a mistake against live data may be irreversible. Practitioners therefore use such tools to draft the change and then review, test and often rewrite it by hand rather than trusting the output.
Please log in or register to report this question.
A correlated subquery is processed as a nested subquery.
Answer: A
Correct answer: True
A correlated subquery references a column from the outer query, so it cannot be evaluated once on its own; the database must re-execute it for each candidate row of the outer query, which is what nested processing means. That is also why it is generally the more expensive form and why an equivalent join is often preferred where one exists.
Please log in or register to report this question.
A regular subquery is processed from the bottom up.
Answer: A
Correct answer: True
A regular subquery is self-contained: it references nothing from the query above it, so it can be evaluated once, from the bottom up, and its result handed to the outer query. This independence is exactly what distinguishes it from a correlated subquery and what makes it the cheaper of the two.
Please log in or register to report this question.
Correlated subqueries are similar to a regular subquery.
Answer: B
Correct answer: False
The two are quite different. A regular subquery is independent and evaluated once; a correlated subquery references the outer query and must be re-evaluated for every candidate row. The difference shows in the processing order, in the cost, and in what each can express - only the correlated form can compare a row against others in the same table.
Please log in or register to report this question.
Database redesign is especially difficult if the database has no data.
Answer: B
Correct answer: False
The reverse is true: an empty database is the easy case, since the structure can be changed by issuing the DDL with nothing to convert, nothing to lose and no existing rows to violate a new constraint. Every real difficulty in redesign comes from the data already present.
Please log in or register to report this question.
Some organizations take the stand that no application should ever employ the true name of a table.
Answer: A
Correct answer: True
Some organisations require applications to address the database only through views, never by the true table names, so that tables can be restructured without touching application code - the view is rewritten to present the old shape from the new tables. It is a discipline that costs something to maintain and buys a great deal of freedom to redesign later.
Please log in or register to report this question.
