Introduction to SQL Questions and Answers

Answer: D

Correct answer: All of the above.

All three are part of defining a table, and each answers a different question.

Data types fix what may be stored in a column and how much space it takes. Primary keys identify each row uniquely and are what foreign keys elsewhere refer to. Default values decide what a column holds when an INSERT does not mention it, which is how a column can be NOT NULL without every statement supplying it. Constraints and indexes belong to the same design decision.

Answer: B

Correct answer: DML

DML is the Data Manipulation Language - the subset of SQL that works with the data itself: SELECT to query it and INSERT, UPDATE and DELETE to change it.

DDL, by contrast, defines the structures the data lives in, through CREATE, ALTER and DROP. The practical difference is not only conceptual: in most engines DDL statements commit implicitly, while DML statements participate in a transaction and can be rolled back.

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: D

Correct answer: question mark (?); asterisk (*)

Microsoft Access departs from the SQL standard here. It uses * for any sequence of characters and ? for exactly one - the same characters MS-DOS used for file matching.

Standard SQL uses % and _ for those two roles. The difference is a practical trap: a query written in Access and moved to another engine does not fail with an error but simply returns nothing, because * is then matched as a literal asterisk. Access in ANSI-92 mode accepts the standard characters instead.

Answer: D

Correct answer: SELECT NAME FROM CUSTOMER WHERE STATE IN ('VA');

IN tests membership of a list, and a list with one element is equivalent to an equality test, so STATE IN ('VA') means the same as STATE = 'VA'.

The other three options fail on syntax rather than on logic: they write IN CUSTOMER where the source table must follow FROM. That is the real test here - the clause naming the table is FROM, and IN is a predicate belonging inside WHERE.

Answer: C

Correct answer: ORDER BY

ORDER BY sorts the rows of a result, ascending by default and descending with DESC, and may name several columns for tie-breaking.

GROUP BY is the near-miss: it collects rows into groups for aggregation and does not guarantee any particular output order, though some engines happen to return groups sorted. SORT BY and ALIGN BY are not SQL. Note that without ORDER BY a result has no defined order at all - a set has no sequence - so any order observed is incidental.

Answer: C

Correct answer: ORDER BY.

ORDER BY is the only clause that sorts a result. It comes last in the statement, after WHERE, GROUP BY and HAVING, because sorting is the final step applied to what those have produced.

This is worth stating plainly: a relation is a set, so without ORDER BY the rows come back in whatever order the engine finds convenient, and that order can change when data grows or an index is added. Code that depends on an unspecified order works until it does not.

Answer: A

Correct answer: use FROM to name the source table(s) and list the columns to be shown after SELECT.

Each clause has one job. FROM names the source - a table, several joined tables, a view or a subquery - and the SELECT list names the columns to be returned from it.

In relational terms these are the two basic operations: SELECT performs projection, choosing columns, and WHERE performs selection, choosing rows. USING exists in SQL but for a narrower purpose - naming the shared column in a join, as in JOIN orders USING (customer_id) - and never names the source table.

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: A

Correct answer: SELECT

SELECT is the query statement, and the only one that reads data. It is also by far the most elaborate, carrying FROM, WHERE, GROUP BY, HAVING and ORDER BY, and able to nest inside itself.

READ and QUERY are not SQL keywords. The choice of word is a historical accident worth noting: SELECT suggests choosing rows, but in relational terms the statement performs projection as well as selection, and after GROUP BY it aggregates too.