Introduction to SQL Questions and Answers

Answer: A

Correct answer: True

Indexes are physical structures, separate from the logical design, so CREATE INDEX and DROP INDEX can be issued at any time without altering the tables or the queries that use them.

That independence is the point: an index changes how fast a query runs, never what it returns, so one can be added when a query proves slow and dropped when it is no longer earning its keep. The practical caveat is timing rather than legality - building an index on a large table takes time and may lock it, so it is usually done off-peak.

Answer: C

Correct answer: a data sublanguage.

SQL is called a data sublanguage because it does one part of the job and relies on a host language for the rest. It expresses what data is wanted, but has no general control flow, no user interface and no way to build an application on its own.

So it is embedded in C, Java or Python, which supply the loops, the branching and the presentation. It is not an operating system, and it is not a DBMS - Oracle and PostgreSQL are DBMSs, and SQL is the language through which they are addressed.

Answer: B

Correct answer: False

LIKE matches text patterns using % and _; it has no notion of a range and cannot express "between these two values".

Ranges are BETWEEN's work, or the comparison operators >= and <=. The two constructs also behave differently against an index: an equality or range test can use one, while a LIKE pattern beginning with a wildcard cannot, since there is no prefix to seek on.

Answer: A

Correct answer: True

The SELECT list may contain expressions as well as plain columns, so Number1 + Number2 is evaluated once per row and its value returned.

AS Total gives the resulting column a name; without it the heading would be the expression itself, which is awkward for a program or a report to reference. One caution: if either operand is null the whole expression is null, since arithmetic with an unknown value gives an unknown result. COALESCE is the usual remedy where zero is the sensible substitute.

Answer: B

Correct answer: percent sign (%); underscore (_)

In standard SQL, % matches any sequence of characters including none, and _ matches exactly one character.

So LIKE 'S%' finds anything beginning with S, while LIKE 'S_ith' finds Smith and Swith but not Sith. Both are used with LIKE. Microsoft Access departs from the standard, using * and ? for the same two roles - a difference that quietly breaks queries moved between the two.

Answer: B

Correct answer: False

SQL is a data sublanguage, not a general-purpose programming language. It has no loops, no branching and no way to build an interface, so it cannot express a complete program on its own and is embedded in a host language that supplies those.

It is also declarative: a query states what data is wanted and leaves the optimiser to decide how to get it, where a programming language specifies the steps. Procedural extensions such as PL/SQL and T-SQL add control flow, but those are dialect additions built on top of SQL rather than SQL itself.

Answer: B

Correct answer: Data Integrity

ON UPDATE CASCADE is part of a foreign key definition. When the referenced key changes in the parent table, the change is propagated automatically to every matching row in the child table.

That preserves referential integrity: without it, changing a parent key would either be refused or leave child rows pointing at a value that no longer exists. Normalization is a matter of table design rather than of runtime behaviour, and materialized views are a storage feature. The related clause ON DELETE CASCADE does the same work for deletions.

Answer: A

Correct answer: True

GROUP BY collects rows sharing the same value in the named column or columns into a single group, and the aggregate functions in the SELECT list are then computed once per group.

Two rules follow. Every column in the SELECT list must either appear in the GROUP BY or be inside an aggregate, since anything else has no single value per group. And filtering on the aggregates belongs in HAVING, which runs after the grouping, rather than in WHERE, which runs before it.

Answer: A

Correct answer: True

A plain SELECT returns every row that matches, duplicates included, because SQL tables are multisets rather than true sets. To collapse repeats, DISTINCT must be stated explicitly.

Two details are worth knowing. It applies to the whole selected row, so SELECT DISTINCT city, state removes only rows where both values repeat. And it is not free: the engine must sort or hash the result to find the duplicates, so on a large result set the cost is real.

Answer: A

Correct answer: Acts like a WHERE clause but is used for groups rather than rows.

HAVING filters after aggregation, WHERE before it, and the order is what makes them different rather than redundant.

WHERE decides which rows enter the groups; GROUP BY forms them; HAVING then decides which groups survive, and it may test aggregate values. So HAVING COUNT(*) > 5 is legal and WHERE COUNT(*) > 5 is not, because at the time WHERE runs no group exists to count. That is also why filtering on plain columns belongs in WHERE, where it reduces the work.