Introduction to SQL Questions and Answers

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.

Answer: D

Correct answer: All of the above can be done by SQL.

SQL is a complete database language and covers all three. Creating structures is its data definition half, through CREATE, ALTER and DROP; querying and modifying data is its manipulation half, through SELECT, INSERT, UPDATE and DELETE.

A third part, data control, grants and revokes permissions. That breadth is precisely what made SQL displace the earlier database models, which needed separate languages for schema definition and for data access.

Answer: D

Correct answer: Both IN and NOT IN

A chain of OR conditions on the same column collapses into a single IN: state = 'VA' OR state = 'MD' OR state = 'DC' becomes state IN ('VA','MD','DC'), and its negation becomes NOT IN.

The gain is more than brevity. Long AND and OR chains need careful parenthesising, since AND binds tighter than OR and a missing bracket changes the meaning silently. IN removes the precedence question entirely. LIKE is for patterns and does not combine value lists.

Answer: A

Correct answer: DML

SELECT reads data from a relation without altering any structure, so it is a Data Manipulation Language statement.

DML has four members - SELECT, INSERT, UPDATE and DELETE - and SELECT is the only one that does not modify anything, which is why some texts place it in a query sublanguage of its own. A view would be created by a DDL statement, and an integrity constraint is a rule declared as part of a table definition.

Answer: B

Correct answer: False

The SQL-92 wildcard for "any sequence of characters" is the percent sign %, used with LIKE, and the underscore _ matches exactly one character.

The asterisk does appear in SQL, but in the SELECT list, where SELECT * means all columns - a different construct entirely. It serves as a wildcard character in Microsoft Access, which departs from the standard, and a pattern written for Access silently returns nothing elsewhere because * is then matched literally.